• 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

    • Display Driver Uninstaller (DDU) 18.1.2.3 by Razvan Serea Display Driver Uninstaller (DDU) is a utility for completely removing AMD/NVIDIA/INTEL graphics drivers and related packages from your system, attempting to eliminate all leftovers (including registry entries, folders and files, driver store). Though AMD/NVIDIA/INTEL drivers can usually be removed via the Windows Control Panel, this uninstaller tool was created for situations where standard uninstall fails, or when you need to fully remove NVIDIA or ATI graphics card drivers. After using this driver cleaner, your system will behave as though it’s the first time you’re installing a new driver—similar to a fresh Windows installation. As with all such tools, we recommend creating a restore point beforehand, allowing you to undo changes if issues arise. If you're having trouble installing an older or newer driver, try it—there are reports that it resolves such problems. Recommended usage: The tool can be used in Normal mode but for absolute stability when using DDU, Safemode is always the best. Make a backup or a system restore (but it should normally be pretty safe). It is best to exclude the DDU folder completely from any security software to avoid issues. You do NOT need to uninstall the driver prior using DDU. Requirements: .NET Framework 4.8 Compatible with Windows 7, 8, 8.1, 10, and 11 (32-bit or 64-bit) Note: Using on Insider Preview builds is at your own risk. Display Driver Uninstaller (DDU) 18.1.2.3 changelog: All GPUs: Fixed hardware rescan issue when cleaning all GPUs without restarting or shutting down UI: Added Guru3D mention in the About section. General: Various fixes and enhancements. Download: Display Driver Uninstaller (DDU) 18.1.2.3 | 1.7 MB (Freeware) Download: DDU Portable | 1.2 MB Links: Display Driver Uninstaller Home Page | Screenshot | Forum Get alerted to all of our Software updates on Twitter at @NeowinSoftware
    • That would be nice, if excel could refresh pivot tables without bugs. Frequently you refresh a pivot and all parameters get lost. Now this will happen automatically. Bravo MS!
    • If one could ever put all the crap of the world in one box, that would be Microsoft.
  • Recent Achievements

    • Week One Done
      Homayoun Hotak earned a badge
      Week One Done
    • Dedicated
      Profit earned a badge
      Dedicated
    • One Month Later
      hhgygy earned a badge
      One Month Later
    • Week One Done
      hhgygy earned a badge
      Week One Done
    • One Year In
      NIKI77 earned a badge
      One Year In
  • Popular Contributors

    1. 1
      +primortal
      646
    2. 2
      ATLien_0
      241
    3. 3
      Xenon
      168
    4. 4
      neufuse
      148
    5. 5
      +FloatingFatMan
      123
  • Tell a friend

    Love Neowin? Tell a friend!