Normalisation

I know the may seem a little out there but i am new tho the world databases and could use some pretty basic help with 1nf, 2nf, and 3nf. i am trying to use the process of normalisation in order to complete a mock database that I created for a class I am taking but could use some help. My issue is that once I enter the data into the database I keep getting a duplicate value error and can't seem to figure out what new tables to create. I cannot seem to up load any excel files but will send out my email was I hear from someone. Thank you all an again I am the newest you can come across but am looking as this is my last resort.

 

If you're getting duplicate value-errors, you're doing something wrong. Depending on what you're actually trying to do, this can be a multitude of different things, but first things first:

1. Do you have auto-increment IDs in the tables? If so, you cannot input the IDs when inserting data - just let the database do that itself.
2. Do you have any columns set as "unique"? If so, the same content won't be allowed in two rows.

What is the exact error you're getting? And also, show us your table-structure.

 

Here is what i am working with.


I have not set the PK on either but here is the last table that i believe to be good

The Employee ID is Set as PK
Thank you

 

Well...

First off, you should have a PK in each table, and use a numerical ID (instead of, like in the Department-table, where you seem to be using the name of the department as key). That way, you can change the name/content of a row without disabling relations in other tables.

Second - where exactly do you get a duplicate value error? What does the error say, exactly?

Third - your tables could use some work

You have one table for Employee Info - that one is okay, I think (although it would be wise to have a direct way of contacting employees, ie phone or email or both in that same table (or in a separate table, if an employee can have multiple phones and emails).
The department table is a bit... off. Why not just create a department table with a numerical id and the name of the department? If you need a way to see who's managing the specific department (which might be different from the info in Sheet 3), just create a separate table with id, dev-id, employee-id
As for the Sheet 3 table, this could probably use the employee-id as PK, although you MIGHT have employees with more than one title, so better: id (PK), dev-id, employee-id, salary and position (although, for position, I would consider having a separate table with all the positions in the company, and just pulling the id from that table).

 

I will have a look at the information and reply in a few. again thank you for your help. once completed i would like to ask you a few questions about ins and outs of design if you don't mind.

 

I don't mind, but please, unless it's confidential, do it via a thread in the forum - I drop by the forum frequently, but having to do PMs is a bit of a hassle, and also, you'll miss out on other's help as well. And, of course, if it's not DB-design, remember to post in the correct category, and just tag me, and I'll see it (most likely)

 

Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anamolies. It is a multi-step process that puts data into tabular form by removing duplicated data from the relation tables.

Normalization degrees of relational database tables have been defined and include:

First normal form (1NF)- This is the "basic" level of normalization and generally corresponds to the definition of any database, namely:

- It contains two-dimensional tables with rows and columns.
- Each column corresponds to a sub-object or an attribute of the object represented by the entire table.
- Each row represents a unique instance of that sub-object or attribute and must be different in some way from any other row (that is, no duplicate rows are possible).
- All entries in any column must be of the same kind. For example, in the column labeled "Customer," only customer names or numbers are permitted.

Second normal form (2NF)- At this level of normalization, each column in a table that is not a determiner of the contents of another column must itself be a function of the other columns in the table. For example, in a table with three columns containing customer ID, product sold, and price of the product when sold, the price would be a function of the customer ID (entitled to a discount) and the specific product.

Third normal form (3NF)- At the second normal form, modifications are still possible because a change to one row in a table may affect data that refers to this information from another table. For example, using the customer table just cited, removing a row describing a customer purchase (because of a return perhaps) will also remove the fact that the product has a certain price. In the third normal form, these tables would be divided into two tables so that product pricing would be tracked separately.

Domain/key normal form (DKNF)- A key uniquely identifies each row in a table. A domain is the set of permissible values for an attribute. By enforcing key and domain restrictions, the database is assured of being freed from modification anomalies. DKNF is the normalization level that most designers aim to achieve.