641 Week 5 Outline

Lecture 1: Normalization (Chapter 11 of LIU)

1. Criteria for a good database design
2. Issues with data redundancies
3. The concept of normalization
4. Functional dependency: Tables 1 and 2
5. First Normal Form: Tables 3
6. Second Normal Form: Tables 1 and 5
7. Third Normal Form: Author Editor Example and Job Class Example

Lecture 2: Normal Forms

1. Third Normal Form: Table 6
2. Boyce-Codd Normal Form

Additional Examples:

Assume a customer can make several loans, with or without other partners. Each branch can can write loans independently but no two loans have the same loan number. List all possible functional dependencies among the attributes/columns in the following tables. Are the tables in 1^st normal form? Why? Are the tables in the 2^nd and 3rd normal form? Why? Draw the Entity Relationship Diagram to to show the original data model for the resulting relational model after fixing the normal form violations.

Customer (SSN, Name, Street, City, BankBranch, Branch_City)

Loan_Info (BranchName, CustomerID, Loan_number, Amount)

Lecture 3: Boyce Codd Normal Forms

    Every determinant must be a key or the table violate BCNF.

Example 1: Bank Loan Table (Note two different cases: 1) Each branch issues loans independently; 2) Each branch issues loans for the bank

Assume a customer can make several loans, with or without other partners. Each branch can can write loans independently but no two loans have the same loan number. List all possible functional dependencies among the attributes/columns in the following tables. Are the tables in 1^st normal form? Why? Are the tables in the 2^nd and 3rd normal form? Why? Draw the Entity Relationship Diagram to to show the original data model for the resulting relational model after fixing the normal form violations.

Customer (SSN, Name, Street, City, BankBranch, Branch_City)

Loan_Info (BranchName, CustomerID, Loan_number, Amount)

Example 2: Warehouse Table

Conceptual Questions: 

• Does Boyce-Codd norm form require the 3^rd normal form to begin with?
• What is the difference between the 3^rd and Boyce-Codd normal form?
• Give an example of the violation of the Boyce-Codd normal Form.

Additional Exercise: 

1) Is the following table in BCNF? If not, fix it to be so.

2) Identify the functional dependencies in the following tables and then states whether each one is in 1^st normal form and 2^nd normal form or not. If not, fix it.

Review Questions:

• What is the deletion anomaly?
• Write one sentence to summary 2^nd and 3^rd normal forms
• Under what conditions, the 2^nd normal form is automatically satisfied if a relation is in the 1^st normal form?
• What is the procedure to convert a relation that violates 3NF into ones in 3NF?

Homework:

Reading: Chapter 11

Hands-on Questions (due along with HW6 hands-on Questions):

1) Identify the functional dependencies in the following table and then states whether each one is in 1^st normal form, 2^nd normal form, and 3rd normal form or not. If not, fix it for each normal form in order. (Hint: mapping cardinality between Customer and Order)

2) Normal Forms: (a) Does the following table confirm to the 2nd normal form? Explain the reason. If not, decompose it into 2nd normal form relations.