INSY3402: Database Management

The University of Texas at Arlington

Professor Kirs

 

Normalization Sample Problems and Exercises

 

NOTE: Answers to Each Question are supplied. However, IT DOES YOU NO GOOD TO CHECK THE SOLUTION BEFORE YOU HAVE ATTEMPTED TO ANSWER THE QUESTION FIRST.

 

1.   Match the Following Terms on the Left with the Correct Definitions on the Right:

 

____  Normalization

____  Referential Integrity

____  Anomoly

____  Functional Dependency

____  Transitive Dependency

____  Composite Key

____  Multi-valued Key

____  1NF

____  2NF

____  3NF

____  BCNF

A.

B.

C.

D.

E.

F.

G.

H.

I.

J.

K.

Functional Dependency Between non-key attributes

An Attribute that can be broken down into component parts

Contains no partial Dependencies

Inconsistency or errors created in editing

Contains no repeating groups

Accounts for Editing Anomolies

The value of one attribute depends on a primary key

Constraint between 2 attributes

Transitive Dependencies eliminated

The process of determining how attributes should be grouped

An attribute that may take on more than one value

 

SEE SOLUTION

 

2.   A table is said to be in 1st Normal Form (1NF) if it has no ____________.

 

SEE SOLUTION

 

3.   A table is said to be in 2nd Normal Form (2NF) if it is in 1NF and ______.

 

SEE SOLUTION

 

4.   A table is said to be in 3rd Normal Form (3NF) if it is in 2NF and ______.

 

SEE SOLUTION

 

5.   List Three (3) transitive anomalies: 

 

SEE SOLUTION

 

6.   Boyce-Codd Normal Form (BCNF) implies that a relation is in 3NF and that __________ have been avoided.

 

SEE SOLUTION

 

7.   Given the Tables Below, Match them with the normalized form (NOT in 1NF, 1NF, 2NF, 3NF, BCDNF, 4NF, 5NF) they are in:

 

 
TABLE A

 

 

 

 
TABLE B

 

PatID

Name

PhysID

PhysName

 

PatID

Name

PhysName

54321

Gore, A.

12345

Scholl, F.

 

54321

Gore, A.

Scholl, F.

43210

Bush, G.

23456

Seuss, M.

 

43210

Bush, G.

Seuss, M.

32109

Cheney, J.

34567

Doolittle, J.

 

32109

Cheney, J.

Doolittle, J.

21098

Clinton, H.

45678

Spock, L.

 

21098

Clinton, H.

Spock, L.

10987

Nader, R.

23456

Seuss, M.

 

10987

Nader, R.

Seuss, M.

 

 

 

 

 

 

 

 

 

 

 
TABLE C

 

 

 

 

 

 
TABLE D

 

PatID

Name

PhysID

PhysID

Name

 

PhysID

Name

PatID

54321

Gore, A.

12345

 

12345

Scholl, F.

 

12345

Scholl, F.

54321

43210

Bush, G.

23456

 

23456

Seuss, M.

 

12345

Scholl, F.

33445

32109

Cheney, J.

34567

 

34567

Doolittle, J.

 

12345

Scholl, F.

44556

21098

Clinton, H.

45678

 

45678

Spock, L.

 

23456

Seuss, M.

43210

10987

Nader, R.

23456

 

 

 

 

45678

Spock, L.

21098

 

 

 

 

 

 

 

45678

Spock, L.

55667

 

                                     

 

 

 

 

 

 

 

 

 

 

 

 

TABLE E

 

 

 

 

TABLE F

 

PatID

Name

PhysID

RX Need

PhysID

Name

Specialty

54321

Gore, A.

12345

Podiatry

 

12345

Scholl, F.

Podiatry

43210

Bush, G.

23456

Pediatrics

 

23456

Seuss, M.

Pediatrics

32109

Cheney, J.

34567

Veterinary

 

34567

Doolittle, J.

Veterinary

21098

Clinton, H.

45678

Hynosis

 

45678

Spock, L.

Hynosis

10987

Nader, R.

23456

Pediatrics

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

_____ Table A

_____ Table B

_____ Table C

_____ Table D

_____ Table E

 

SEE SOLUTION

 1.   Not in Normal Form

 2.   1NF

 3.   2NF

 4.   3NF

 5.   BCNF

 6.   4NF

 7.   5NF

 

 

 

8.   Given the Following ERD:

 

 

 

 

 

 

 

 

 

 

 

 

 

 


NOTE: TO ANSWER MOST OF THESE QUESTIONS, YOU WILL HAVE TO ASSUME ADDITIONAL ATTRIBUTES, ASIDE FROM THOSE GIVEN IN THE ERD. ADDITIONALLY, YOU MAY HAVE TO INCLUDE SOME ENTITIES NOT LISTED ABOVE.

 

A. Show at Least 1 Relation in NON-NORMALIZED Form. 

 

      SEE SOLUTION

 

B.   Show at Least 1 Relation in 1NF. 

 

      SEE SOLUTION

 

C.   Show at Least 1 Relation in 2NF. 

 

      SEE SOLUTION

 

D. Show at Least 1 Relation in 3NF. 

 

      SEE SOLUTION

 

E.   Show at Least 1 Relation in BCNF. 

 

      SEE SOLUTION

 

F.   Put The ENTIRE ERD (i.e., show all relations) in BCNF. 

 

      SEE SOLUTION