• 0

Question about database normalization


Go to solution Solved by f5s4t3,

Question

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!

Link to post
Share on other sites

15 answers to this question

Recommended Posts

  • 0
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?

Link to post
Share on other sites
  • 0
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?

Link to post
Share on other sites
  • 0
NJ Louch

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

Link to post
Share on other sites
  • 0
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?:

 

        

Untitled.png

 

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

Link to post
Share on other sites
  • 0
NJ Louch
 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 :)

Link to post
Share on other sites
  • 0
Builds

So it's good?

 

THANKS!!!!  :D  :D

Link to post
Share on other sites
  • 0
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)

Link to post
Share on other sites
  • 0
f5s4t3

post-450287-0-02555500-1430593134.png

 

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.

Link to post
Share on other sites
  • 0
f5s4t3

post-450287-0-00815800-1430596220.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...

Link to post
Share on other sites
  • 0
notta

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

Link to post
Share on other sites
  • 0
123456789A

What are you using to make those graphics?

Link to post
Share on other sites
  • 0
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? ;)

Link to post
Share on other sites
  • 0
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.

  • Like 1
Link to post
Share on other sites
  • 0
Builds

attachicon.gifitmitica2.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?...

Link to post
Share on other sites
  • 0
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.

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Recently Browsing   0 members

    No registered users viewing this page.