Question about database normalization

[Builds]

Howdy.

I have an IT class in which we're studying SQL.

We have a midterm coming up and we've been given a sample exam for studying. I've got everything locked down, just one thing left - Database normalization. I answered the first two parts of a question, and I'm not sure how to answer the 3rd. Would appreciate if someone can verify my answer for the 1st and 2nd parts and perhaps advice on the 3rd part.
 

The question is:

1. When is the process of normalization used?

If a database is designed in a flawed way, it may contain anomalies, which makes managing that database nearly impossible. The normalization of a database is a method to remove all anomalies and bring the database to a consistent state by designing it according to specific rules. In the normalization process we systematically examine relations for anomalies and when detecting some, remove those anomalies by splitting up the relation into two, related, relations. Finding problems with the database structure at an early stage is strongly important, when the other option being those problems detected only at a later stage.
 

2. Describe BCNF

BCNF stand for "Boyce and Codd Normal Form", named after the two who invented it. It is another version of the 3NF, and deals with certain type of anomaly that is isn't handled by 3NF.
 

3. For the given relation, find all functional dependencies and possible anomalies implied from the functional dependencies. Put the relation into the appropriate BCNF.

 

EmployeeID  |  Name  |    Dept.         |        Title

100                 Jones       HR                   Executive director

200                 Chau        HR                   PR

205                 Parker      Marketing         Clerk

300                 Garret      Accounting        Accountant

400                  Miller      Marketing          Clerk

 

Thanks!

[f5s4t3]

The question you should ask yourself first is

 

Why is there a "EmployeeID" column in the first place?

 

and then:

 

What happens if "Accounting" changes to "US Accounting" and "Europe Accounting"?

 

What happens if "Clerk" changes to "Front Office Clerk" and "Back Office Clerk"?

 

What happens when Jones retires and is no longer "Executive director"?

 

 

One last thing I should add is that normalization is as good as it is bad. You can under-normalize and you can over-normalize. It all depends on business logic, that's your reference, so it's not a clear cut.

 

To answer shortly, you could create a DEPARTMENTS table and a TITLES table and then create two more tables: EMPS_DEPS and EMPS_TITLES. Can you figure it out by yourself from here?

[Builds]

OK. That's a good start for the understanding process Basically, I understand that EmployeeID is there as a primary key to identify every employee and make sure there's no other duplicates, because there can be more than one "Jones" for instance, but there can't be 2 "100" employeeID, no? I don't really know how to answer the next 2 things you've said, but as for the third - wouldn't we just delete the record for "Jones" when he retires?

[+Dick Montage]

Can I just ask, have they not taught you any of this in your lectures?

[Builds]

It's a small course, 2 credits out of my entire bachelor degree, and I skipped the last class which was the class in which all of this was taught. Prior to that, we were studying basic SQL, queries etc which I'm good with.

 

After re-reading the questions, I gave it a try, can this be the answer?:

 

        

 

EDIT: In the 3rd new table, the second column is "Dept"

[+Dick Montage]

 I skipped the last class which was the class in which all of this was taught

 

Ooops

 

BUT... looking at your change - you caught up

[Builds]

So it's good?

 

THANKS!!!!   

[ramesees]

[...] wouldn't we just delete the record for "Jones" when he retires?

 

And what if you wanted to run a report which overlapped the period of time that "Jones" was employed and then retired ?

 

By removing the record you lose his data forever.

 

Physically deleting data (especially primary data) is a big no-no. You could have another Department of retired to get around that (there are other ways to model that as well)

[f5s4t3]

 

1. You'd create DEPARTMENTS.

2. You'd come up with TITLES.

3. You'd hire EMPLOYESS.

4. You'd give them titles, assign them to departments; in time, titles and departments may change name, employees may change title and switch departments: EMPS_TITS and EMPS_DEPS.

[f5s4t3]

1. You'd create DEPARTMENTS for your business.

2. You'd come up with TITLES for your business.

3. You'd assign titles per departments: DEPS_TITS.

4. You'd hire EMPLOYEES.

5. You'd assign them to departments and give them titles: EMPS_DEPS_TITS. In time...

[notta]

Sorry I don't mean to side track this thread but what is the name of that table? emps_t*ts?

[123456789A]

What are you using to make those graphics?

[f5s4t3]

Oracle SQL Developer Data Modeler

Sorry I don't mean to side track this thread but what is the name of that table? emps_t*ts?

I don't see anything wrong with them, do you?

[simplezz]

DEPS_TITS.

I've known a few Department T.i.t.s before

As for the OP, it already looks like you understand the basics by the way you split the columns into their own tables.

[Builds]

itmitica2.png

1. You'd create DEPARTMENTS for your business.

2. You'd come up with TITLES for your business.

3. You'd assign titles per departments: DEPS_TITS.

4. You'd hire EMPLOYEES.

5. You'd assign them to departments and give them titles: EMPS_DEPS_TITS. In time...

 

I'm not sure I understand the division you've made in the graph, I'd create 5 tables out of 1?...

[f5s4t3]

Yes. It's about the atomicity of the employee data.

 

Ask yourself this: what happens if an employee changes title or if an employee changes department or if an employee changes both title and department?

 

The way you've normalized the tables it only accounts for a single change in department and title for an employee.

 

The way I've done it, it doesn't matter how many changes there are, they're all accounted for.

 

The first normalization is not explicit about the changes in title and department for an employee. It requires additional work, a perfect candidate for views or stored procedures.

 

The second normalization is about classifying the titles inside departments as well and, as such, it's explicit about the correlated department-title changes.