Assignment
Use the correct functional dependencies identified to normalize each of the tables (table A through table D) listed in Assignment 3-1. There is a ‘Normalization process handout’ in Week 12 model to remind you of the step-by-step process that we went over many times in class.
List the new tables (with a table name, a primary key, foreign key(s) if any, and other columns) in a Word document. Be sure to underline primary keys and bold foreign keys. Do not forget to provide a name for each table.
Draw an ERWin diagram after the normalization process, and copy the diagram to the Word document, next to the definitions of the normalized, new tables. At this point, the answer should look like the answers in chapter 6 exercises posted in Week 12 Module on Canvas.
Download the data files (Assignment_6-1.mdf and Assignment_6-1_log.ldf) from this assignment and save them in the C:/Users/Public folder.
Run the SSMS and attach the data files. This database has four tables: Theater, Pet, Truck, and Supplier. You will populate the new tables (next step) with the data in these original data tables in step #7
Use your ERWin diagrams as guides to create new tables in the SQL server. When you create new tables, you will need to know the data type of each column. Use the Object Explorer in the SSMS to find this information. The screenshot on the right is an example. Copy the SQL table creation codes to the Word document mentioned in step #2. After creating each table, use the Refresh button to refresh the Object Explorer to confirm that the table has been created. Include a screenshot of each new table with all column names and data types in the Word document.
After creating all tables, use SQL DDL to insert records into the new tables from the existing tables provided in the data file. Copy the SQL codes that you use to insert records to the Word document.
Submit the Word document to the assignment.