Reference no: EM13839309

Database Management and Applications

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. Bring the table(s) into first normal form compliance without loss of any data. Identify primary and foreign keys (when present) for all tables.

3. What error(s), if any, prevent the now first normal form compliant table or tables from being second normal form compliant?

4. Bring the table(s) into second normal form compliance without loss of any data. Identify primary and foreign keys. Be sure to present all tables, not just those changed in this step of the normalization process.

5. What error(s), if any, prevent the now second normal form compliant table or tables from being third normal form compliant?

6. Bring the table(s) into third normal form compliance without loss of any data. Identify primary and foreign keys. Be sure to present all tables, not just those changed in this step of the normalization process.

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

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. Bring the table(s) into first normal form compliance without loss of any data. Identify primary and foreign keys (when present) for all tables.

3. What error(s), if any, prevent the now first normal form compliant table or tables from being second normal form compliant?

4. Bring the table(s) into second normal form compliance without loss of any data. Identify primary and foreign keys. Be sure to present all tables, not just those changed in this step of the normalization process.

5. What error(s), if any, prevent the now second normal form compliant table or tables from being third normal form compliant?

6. Bring the table(s) into third normal form compliance without loss of any data. Identify primary and foreign keys. Be sure to present all tables, not just those changed in this step of the normalization process.

Table 1

Students

StudentID	Name	eMail	Department	Major	AdvisorID	Advisor	CoursesTaken
N112233	Rocky Squirrel	[email protected]	GSCIS	Information Systems	P123	John Data	MMIS630 (Fall 14) MMIS620 (Winter 15)
N121212	Bullwinkle Moose	[email protected]	Business	Accounting	P456	Carl Numbers	ACCT810 (Summer 14) MMIS630 (Fall 14)
N212121	Natasha Badenof	[email protected]	GSCIS	Information Systems	P123	John Data	MMIS630  (Winter 14) MCIS610 (Winter 14)
N234432	Boris Badenof	[email protected]	GSICS	Computer Science	P789	Rich Geek	MCIS610 (Fall 14) MCIS610 (Winter 15)