• 0

Question about database normalization


Question

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!

15 answers to this question

Recommended Posts

  • 0

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?

  • 0

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?

  • 0

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"

  • 0
  On 02/05/2015 at 17:19, Builds said:

[...] 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)

  • 0
  On 02/05/2015 at 19:53, itmitic? said:

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

  • 0

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.

This topic is now closed to further replies.
  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Posts

    • Cortana's real value was that early on it let you connect assistants like Alexa to it so you could use Cortana while at your PC to control other assistants. That was what MS envisioned for it, a sort of assistant hub that let you manage the others. Then Amazon killed the ability in Alexa which ended that. MS just never brought out any new ideas to make up for it. It was then just "yet another" assistant, which who is going to use an assistant while at their PC then Alexa in the kitchen then also Google Nest when in another room?
    • Swallowed Pride? Try more "they got tired of making YT work, just for Google to break the site on purpose again to SPECIFICALLY not work in Edge."
    • You joke, but BSOD is an important feature of Windows, one that Linux doesn't exactly have, which can lead to some wasted time troubleshooting issues. What I mean by that is that when Linux does a kernel panic, it is far from guaranteed that the normal kernel panic text actually gets shown on the screen, depending on what mode you are in. Sometimes a kernel panic will just result in your desktop manager seeming to go unresponsive, which may lead you down the wrong troubleshooting path.
    • Great write up. Microsoft had much from developers when it comes to new features and frameworks. That fortune seems only Apple appears to have. The day they announce new visuals or API’s, almost all developers jump immediately on implementing it when the first beta is out, so it can be released asap. And when a developer is slacking, Apple users actively call them out.
    • Tiles on 10 was great for touch devices, just few people use WIndwos devices in an actual Tablet way. Groove as GREAT it seamlessly pulled my music to stream from my OneDrive even on the mobile app. Legacy Edge was also great it was only failed because Google kept screwing with their sites to break them for non-Chromium browsers. Cortana also had great possibilities and was just never fully realized. Kind of like Siri. IMO they should have just revamped it to be what Copilot is.
  • Recent Achievements

    • Week One Done
      SmileWorks Dental earned a badge
      Week One Done
    • Community Regular
      vZeroG went up a rank
      Community Regular
    • Collaborator
      Snake Doc earned a badge
      Collaborator
    • Week One Done
      Snake Doc earned a badge
      Week One Done
    • One Month Later
      Johnny Mrkvička earned a badge
      One Month Later
  • Popular Contributors

    1. 1
      +primortal
      589
    2. 2
      Michael Scrip
      197
    3. 3
      ATLien_0
      195
    4. 4
      +FloatingFatMan
      131
    5. 5
      Xenon
      122
  • Tell a friend

    Love Neowin? Tell a friend!