Order ID / Order Date / Customer ID / Customer Name / Product ID / Product Desc. / Product Price / Quantity Ordered
1006 / 10/24/10 / 2 / Value Furniture / 7
5
4 / Dining Table
Writers Desk
Entertain Center / 800.00
325.00
650.00 / 2
2
1
1007 / 10/25/10 / 6 / Furniture Gallery / 11
4 / 4 Drawer Dresser
Entertain Center / 500.00
650.00 / 4
3
  1. What errors prevent the table displayed above from being first normal form compliant?
  2. List all the functional dependencies present in the table.
  3. List all the candidate keys.
  4. Describe the steps you would take to bring the table into first normal form compliance. Display the resulting first normal form compliant table or tables, identifying primary and foreign keys.
  5. What error(s), if any, prevent the now first normal form compliant table or tables from being second normal form compliant?
  6. List all the functional dependencies present in the first normal form compliant table(s).
  7. List all the candidate keys.
  8. Describe the steps you would take to bring the table(s) into second normal form compliance. Display the resulting second normal form compliant table or tables, identifying primary and foreign keys.
  9. What error(s), if any, prevent the now second normal form compliant table or tables from being third normal form compliant?
  10. List all the functional dependencies present in the second normal form compliant table(s).
  11. List all the candidate keys.
  12. Describe the steps you would take to bring the table(s) into third normal form compliance. Display the resulting third normal form compliant table or tables, identifying primary and foreign keys.

Whatsamatter U also has a number of students, of course. The table on the following page lists the information gathered for each student, plus 5 sample records. Some business rules that Whatsamatter U follows includes:

  • Each student has a unique ID number
  • A student has only a single advisor, but an advisor can have either zero or many students.
  • Advisors are always faculty members (professors)
  • A student can have only a single major but, of course, many students can have the same major
  • A student can take a course more than once

Table 1

Students

StudentID / Name / eMail / Department / Major / AdvisorID / Advisor / CoursesTaken
N112233 / Rocky Squirrel / / GSCIS / Information Systems / P123 / John Data / MMIS630
MMIS620
N121212 / Bullwinkle Moose / / Business / Accounting / P456 / Carl Numbers / ACCT810
MMIS630
N212121 / Natasha Badenof / / GSCIS / Information Systems / P123 / John Data / MMIS630 MCIS610
N234432 / Boris Badenof / / GSICS / Computer Science / P789 / Rich Geek / MCIS610

Using the above business rules and the data in Table 1 on the following page, do the following:

  1. What errors prevent the table displayed above from being first normal form compliant?
  2. List all the functional dependencies present in the table.
  3. List all the candidate keys.
  4. Describe the steps you would take to bring the table into first normal form compliance. Display the resulting first normal form compliant table or tables, identifying primary and foreign keys.
  5. What error(s), if any, prevent the now first normal form compliant table or tables from being second normal form compliant?
  6. List all the functional dependencies present in the first normal form compliant table(s).
  7. List all the candidate keys.
  8. Describe the steps you would take to bring the table(s) into second normal form compliance. Display the resulting second normal form compliant table or tables, identifying primary and foreign keys.
  9. What error(s), if any, prevent the now second normal form compliant table or tables from being third normal form compliant?
  10. List all the functional dependencies present in the second normal form compliant table(s).
  11. List all the candidate keys.
  12. Describe the steps you would take to bring the table(s) into third normal form compliance. Display the resulting third normal form compliant table or tables, identifying primary and foreign keys.