Er & Normalization Exercises

Normalization Exercises Normalization and E-R problem examples This week we will walk through additional examples to give you practice normalizing tables and drawing E-R diagrams. Quick Review First Normal Form: No repeating groups or multi-valued attributes Second Normal Form: Non-key attributes must be fully functionally dependent based on the primary key. Third Normal Form: Eliminate transitive dependencies. BCNF : Eliminate dependencies from the key on non-key attributes. Normalization Examples For the next four problems, we will indicate a) Which normal form(s) are violated? b) Give an example of insert, deletion, and modification anomalies associated with the data. c) Present a data structure diagram showing a reorganized table structure that is in at least third normal form. Problem 1 A public library keeps the following table of data about its patrons:

Which normal forms are violated?

Anomalies?

Normalization Exercises

Data structure diagram?

Problem 2 A school keeps the following information about its students:

Which normal forms are violated?

Normalization Exercises

Anomalies?

Data structure diagram?

Normalization Exercises

Problem 3 A company keeps the following table of data about orders.

Which normal forms are violated?

Anomalies?

Normalization Exercises

Data structure diagram?

Extra Problem) A video store uses the following table to keep track of movies.

Normalization Exercises

Can you suggest a better primary key to uniquely identify a movie? (Ok to add attributes)

Which normal forms are violated?

Anomalies?

Data structure diagram? 1NF and then 2NF.

Normalization Exercises

A Movie ID would help us here in many ways! E.g., saving space for redundant data. Problem 4 The following set of data represents a sample of the types of data used by a clerk in a library. The clerk also indicates that there can be borrowers who do not have a book checked out and books that have not yet been checked out. All other relationships are mandatory. Draw an ER diagram and a set of domain descriptions for this user view.

Normalization Exercises

Data Domain Descriptions:

Problem 5 Based on the following document and description, create an E-R diagram for the data of this user’s view and the domain description. Registration clerk: I produce the class schedule report you see here. Each student is sent

Normalization Exercises a copy of his/her class schedule. Some restrictions, which may not be obvious from the sample data, should be noted. The advisor assigned to a student must be a professor in the department in which the student is majoring (a CIS major must be advised by a CIS professor). Course numbers consist of a 3-digit alphabetic prefix followed by 3 numeric digits. The alphabetic prefix must correspond to a valid department code.

E-R Diagrams:

Normalization Exercises

Domain description:

Normalization Exercises