IT468.A02_1209AssignmentsNormal Forms

NormalizationAssignment1.docx

Home >Business & Finance homework help >Management homework help >IT468.A02_1209AssignmentsNormal Forms

IT 468 Name: _______________________

Normalization Assignment

IT 468

Normalization Assignment

For each 1NF relation below, you will need to normalize the relation to 2NF, and then to 3NF. You must show both the 2NF and 3NF relations. As you know, not all relations have a partial dependency. Likewise, not all relations have a transitive dependency. In fact, once you have normalized the 1NF relation to 3NF, none of the 3NF relations will have a partial or transitive dependency.

This is an example of how you should complete the homework. Please be sure to look at the comments.

Click the check box next to any correct answers

(2 points each problem)

1NF Relation

Normalize 1NF relation to 2NF

(2 points each problem)

Normalize 2NF relation to 3NF

(2 points each problem)

Example

r(A,B,C,D)

FD1 (A,B) -> C Comment by Nathan White: FD1 is a good functional dependency as the non-prime attribute is dependent upon the entire primary key. FD2 is a partial dependency, as D is determined by A, which is only part of the primary key, not the entire primary key.

FD2 A -> D

Is the 1NF relation also in 2NF ☐?

Is the 2NF relation also in 3NF ☒? Comment by Nathan White: Since the 1NF relation has a partial dependency, the 1NF relation is not in 2NF. However, since there are no transitive dependencies in the 2NF relation, the 2NF relation is in 3NF as well.

What are the determinants in the 1NF relation? Comment by Nathan White: The determinants are any attribute that is on the left side of the arrow in a functional dependency. In this case, FD1’s determinants are A & B and FD2’s determinant is A; therefore, A and B are checked.

A ☒

B ☒

C ☐

D ☐

Instead of using the graphical notion to display the answer, you may use the relational notation instead. Here’s how that would look for the same problem.

Either graphical or relational notation is acceptable.

Click the check box next to any correct answers

(2 points each problem)

1NF Relation

Normalize 1NF relation to 2NF

(2 points each problem)

Normalize 2NF relation to 3NF

(2 points each problem)

Example

r(A,B,C,D)

FD1 (A,B) -> C

FD2 A -> D

Is the 1NF relation also in 2NF ☐?

Is the 2NF relation also in 3NF ☒?

What are the determinants in the 1NF relation?

A ☒

B ☒

C ☐

D ☐

r1(A,B,C)

r2(A,D)

r1(A,B,C)

r2(A,D)

	Click the check box next to any correct answers (2 points each problem)	1NF Relation	Normalize 1NF relation to 2NF (2 points each problem)	Normalize 2NF relation to 3NF (2 points each problem)
1. r(A,B,C,D) FD1 (A,B) -> C FD2 B -> D	Is the 1NF relation also in 2NF ☐? Is the 2NF relation also in 3NF ☐? What are the determinants in the 1NF relation? A ☐ B ☐ C ☐ D ☐
2. r(A,B,C,D) FD1 A -> B,C FD2 B -> D	Is the 1NF relation also in 2NF ☐? Is the 2NF relation also in 3NF ☐? What are the determinants in the 1NF relation? A ☐ B ☐ C ☐ D ☐
3. r(A,B,C,D,E) FD1 (A,B) -> C FD2 B -> D FD3 C -> E	Is the 1NF relation also in 2NF ☐? Is the 2NF relation also in 3NF ☐? What are the determinants in the 1NF relation? A ☐ B ☐ C ☐ D ☐ E ☐
4. r(A,B,C,D,E,F) FD1 (A,B,C) -> D FD2 (A,B) -> E FD3 C -> F	Is the 1NF relation also in 2NF ☐? Is the 2NF relation also in 3NF ☐? What are the determinants in the 1NF relation? A ☐ B ☐ C ☐ D ☐ E ☐ F ☐
5. r(A,B,C,D,E,F,G) FD1 (A,B,C) -> F FD2 (A,B) -> G FD3 G -> D,E	Is the 1NF relation also in 2NF ☐? Is the 2NF relation also in 3NF ☐? What are the determinants in the 1NF relation? A ☐ B ☐ C ☐ D ☐ E ☐ F ☐ G ☐

ABCDFD1FD2r

A B C D FD1 FD2 r

ABCADFD1FD2r1r2

A B C A D FD1 FD2 r1 r2

ABCADFD1FD2r1r2

A B C A D FD1 FD2 r1 r2

A B C D FD1 FD2 r

ABCDFD1FD2r

A B C D FD1 FD2 r

ABCDFD2FD1r

A B C D FD2 FD1 r

ABCDFD2FD1EFD3r

A B C D FD2 FD1 E FD3 r

ABCDFD2FD1EFD3Fr

A B C D FD2 FD1 E FD3 F r

ABCDFD2FD1EFD3FGr

A B C D FD2 FD1 E FD3 F G r