Transcribed Text
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 squirrel@wmu.edu GSCIS Information
Systems P123 John
Data
MMIS630
MMIS620
N121212 Bullwinkle
Moose moose@wmu.edu Business Accounting P456 Carl
Numbers
ACCT810
MMIS630
N212121 Natasha
Badenof Nat@Bad.net GSCIS Information
Systems P123 John
Data
MMIS630
MCIS610
N234432 Boris
Badenof Boris@Bad.net 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.
These solutions may offer step-by-step problem-solving explanations or good writing examples that include modern styles of formatting and construction
of bibliographies out of text citations and references. Students may use these solutions for personal skill-building and practice.
Unethical use is strictly forbidden.
2. List all the functional dependencies present in the table.
1. Order ID -> Order Date
2. Customer ID -> Customer Name
3. Product ID -> Product Desc, Product Price
4. (Order ID, Customer ID ) -> Quantity Ordered
3. List all the candidate keys.
5. What error(s), if any, prevent the now first normal form compliant table or tables from being second normal form compliant?
1. Order Date is only determined by Order ID
2. Customer Name is only determined by Customer ID
6. List all the functional dependencies present in the first normal form compliant table(s).
1. (Order ID, Product ID) -> Quantity Ordered
2. ProductID -> Product Desc., Product Price
3. (Order ID, Customer ID) -> Order Date, Customer Name...