What errors prevent the table displayed above from

MMIS630

Database Management and Applications

Exam 3

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?

Each row has to be atomic values. No multiple values are allowed in a row.

2. List all the functional dependencies present in the table.

Order ID -> Order Date

Product ID -> Product Desc., Quantity Ordered

Customer ID -> Customer Name

Order ID, Customer ID – > Product ID

3. List all the candidate keys.

Order ID, Product ID, Customer ID

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.

A table that is in 1NF and in which the values of each non-primary-key column are determined by the values in all the columns that make up the primary key. To get the table into first normal form, each row has atomic value, therefore the output table is:

Order ID	Order Date	Customer ID	Customer Name	Product ID	Product Desc.	Product Price	Quantity Ordered
1006	10/24/10	2	Value Furniture	7	Dining Table	800.00	2
1006	10/24/10	2	Value Furniture	5	Writers Desk	325.00	2
1006	10/24/10	2	Value Furniture	4	Entertain Center	650.00	1
1007	10/25/10	6	Furniture Gallery	11	4 Drawer Dresser	500.00	4
1007	10/25/10	6	Furniture Gallery	4	Entertain Center	650.00	3

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

In order to prevent error, for 2nd normal form, the table has some duplicate values in the following attribute:

Order ID, Order Date, Product ID, Customer ID, Customer Name.

Therefore, to normalize into 2NF this redundancy should be removed. The functional dependencies have been already identified earlier.

6. List all the functional dependencies present in the first normal form compliant table(s).

To assess whether a table breaks 2NF form requires identification of the primary key and functional dependencies associated with that table. 2NF only applies to tables with composite primary keys.

Order ID -> Order Date, Ordered Quantity

Product ID -> Product Desc., Product Price

Customer ID -> Customer Name

Order ID – > Product ID, Ordered Quantity

7. List all the candidate keys.

Order ID, Product ID, Customer ID

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.

To assess whether a table breaks 2NF form requires identification of the primary key and functional dependencies associated with that table. 2NF only applies to tables with composite primary keys. Describes the relationship between columns in a table and indicates how columns relate to one another.

Order ID	Order Date
1006	10/24/10
1006	10/24/10
1006	10/24/10
1007	10/25/10
1007	10/25/10

Customer ID	Customer Name
2	Value Furniture
6	Furniture Gallery

Product ID	Product Desc.	Product Price	Quantity Ordered
7	Dining Table	800.00	2
5	Writers Desk	325.00	2
4	Entertain Center	650.00	1
11	4 Drawer Dresser	500.00	4
4	Entertain Center	650.00	3

Order ID	Product ID	Quantity Ordered
1006	7	2
1006	5	2
1006	4	1
1007	11	4
1007	4	3

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

A transitive dependency describes a relationship between columns a, b, and c. If a determines b (a→ b) and b determines c (b→ c), then c is transitively dependent on a via b (provided that b or c does not determine a). Identification of transitive dependencies on the primary key is evidence that a table is breaking 3NF and may suffer from update anomalies.

In this case, here no such error.

10. List all the functional dependencies present in the second normal form compliant table(s).

Order ID -> Order Date, Ordered Quantity

Product ID -> Product Desc., Product Price

Customer ID -> Customer Name

Order ID – > Product ID, Ordered Quantity

11. List all the candidate keys.

Order ID, Product ID, Customer ID

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.

Order ID (PK)	Order Date
1006	10/24/10
1006	10/24/10
1006	10/24/10
1007	10/25/10
1007	10/25/10

Customer ID (PK)	Customer Name
2	Value Furniture
6	Furniture Gallery

Product ID (PK)	Product Desc.	Product Price	Quantity Ordered
7	Dining Table	800.00	2
5	Writers Desk	325.00	2
4	Entertain Center	650.00	1
11	4 Drawer Dresser	500.00	4
4	Entertain Center	650.00	3

Order ID (FK)	Product ID (FK)	Quantity Ordered
1006	7	2
1006	5	2
1006	4	1
1007	11	4
1007	4	3

 

 

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?

A student can take a course more than once. Thus each row must have atomic value

2. List all the functional dependencies present in the table.

StudentID

Name

eMail

Department

Major

AdvisorID

Advisor

CoursesTaken

StudentID -> Name, eMail, Department, AdvisorID, CourseTaken

AdvisorID -> Advisor, StudentID

Department -> Major

3. List all the candidate keys.

StudentID, Advisor ID

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.

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

PK: StudentID, eMail, AdvisiorID, Department

Foreign Keys will be identified in 2nd NF

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

Following Columns have duplicate entries

StudentID

Name

eMail

Department

Major

AdvisorID

Advisor

6. List all the functional dependencies present in the first normal form compliant table(s).

StudentID -> Name, eMail, Department, AdvisorID, CourseTaken

AdvisorID -> Advisor, StudentID

Department -> Major

eMail -> StudentID

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.

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

Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteDemy. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.