Question

Normalize the un-normalized table given below through INF, 2NF and 3NF. Show the normalization process in a file (e.g., creating tables and drawing lines in MS Word or MS Excel), save as a .pdf file, and submit it through the link available on Canvas. Name your file according to this scheme: yourlastname a3_normalize.pdf. Do not protect your .pdf file. The presentation of your normalization process should mimic the normalization class exercise and the example provided on Canvas In order to complete this assignment successfully, please keep in mind the following points as you work on the normalization case. You may lose points if you do not follow these: Normalize the flat table through each of the INF, 2NF and 3NF separately and in sequence Make sure that you cover each normalization step (INF, 2NF, 3NF) separately, explicitly, Do not skip any normalization steps. In each normalization step, you should only resolve the issue(s) that pertain to the given step and in sequence (For example, do not resolve transitive dependencies in 1NF or 2NF, as transitive dependencie:s are 3NF business.) In each step, list all of the tables, including those do not change in the given step Identify and show primary keys and foreign keys in each step Show the relationships between the tables in each form (by drawing lines/arrows between corresponding PKs and FKs) The sample records are for your reference only to help you understand the nature of each field You should not include them in your work. Hint: Note that there are two groups of repeating fields in this un-normalized table. Sample Record 43 75 ompany Address 5555 Melrose A Production Company Phone No. (323) 956 5000 CA 90038 lon Vito Corleone l Corleone Adams Corleone 1272 Wisconsin A MD 20814 Theater 1 Phone Number 301) 718 9449 ille Stadium 13 199 East Montgomery Ave., Rockville, MD 20850 301) 340 9390 Theater 2 Phone Number n Stadium 14 0000 C MD 20874 Theater 3 Phone Number 301) 916 4435

Answer 1

Here is the Movies Table. It is in 1NF.

Movies Table

Movie ID Number PK

Movie Title

Movie Genre

Movie Length

Production Company Name PK

Production Company Address

Production Company Phone No.

Production Company Website

Actor/Actress 1 First Name PK

Actor/Actress 1 Last Name

Actor/Actress 1 Role

Actor/Actress 2 First Name PK

Actor/Actress 2 Last Name

Actor/Actress 2 Role

Actor/Actress 3 First Name PK

Actor/Actress 3 Last Name

Actor/Actress 3 Role

Showing at Theater 1 Name PK

Theater 1 Address

Theater 1 Phone Number

Showing at Theater 2 Name PK

Theater 2 Address

Theater 2 Phone Number

Showing at Theater 3 name PK

Theater 3 Address

Theater 3 Phone Number

Functional Dependencies:

Movie Title -> Movie ID Number

Movie Genre -> Movie ID Number

Movie Length -> Movie ID Number

Production Company Address -> Production Company Name

Production Company Phone No. -> Production Company Name

Production Company Website -> Production Company Name

Actor/Actress First Name -> Actor/Actress ID (or number) (Depends on individual actor/actress)

Actor/Actress Last Name -> Actor/Actress ID

Actor/Actress Role -> Actor/Actress ID

Theater Name -> Theater ID

Theater Phone Number -> Theater ID

Theater Address -> Theater ID

In original Movies Table the primary keys are in bold. We see that there are partial dependencies, and hence the table is not in 2NF. Hence, we will have to split the table as follows:

Movie

Movie ID Number PK

Movie Title

Movie Genre

Movie Length

Production Company

Production Company Name PK

Production Company Address

Production Company Phone No.

Production Company Website

Theater

Theater ID PK

Theater Name

Production Company Phone No.

Theater Address

Actor

Actor ID PK

Actor First Name

Actor Last Name

Actor Role

Movie Table Composite

Movie ID Number PK

Production Company Name PK

Theater ID PK

Actor ID PK

These tables are in 2NF, since there is no partial dependency.

In each table, we don’t see any transitive dependency, hence tables are in 3NF too.