using Microsoft SQL Server Management Studio 2012 (or later) or DataGrip

Lab Questions (5 @6 points each: total = 30)

The accompanying spreadsheet file contains five sheets, each with a non-normalized table of data; follow instructions below to normalize these tables, and insert your solution into each spreadsheet just below the SOLUTION: line. Save that file and submit through the Assignments menu before the due date.

Answers should follow the format of the problems. Notably, primary keys are labeled in RED in the header – be sure to do the same in your solutions. Note that in normalization you may need to break a single table into more than one; be certain that each has its appropriate primary key in RED.

Details of the various normal forms are covered in the Murach text in chapter 10; see that section for examples of the normalization process.

Problem #1: 1NF

This table shows a list of people with their contact information (it is not normalized to any degree).

Apply first normal form to this table. This will involve removal of repeating data (and consequently creating some redundancy). You will not need to break the table apart in order to achieve 1NF.

Problem #2: 2NF

This table contains data regarding U.S. cities, and some of their demographic data; it employes a composite primary key composed of the combination of CITY and STATE (since city names like “Portland” can apply to more than one city).

Apply second normal form to this table. You will need to ensure that all non-key columns are dependent on the whole key, not just part of it; any columns not meeting this requirement must be removed and placed into a separate table. Note that the primary key of this new table will be that portion of the original primary key on which the removed columns are dependent; this will become a foreign key linked to the original table.

Be sure to eliminate any duplicate data that might result from creation of your new table.

Problem #3: 3NF

This table contains invoice data, including customer contact fields. It has already been advanced to second normal form (all non-key columns are dependent on the whole key), but not to third normal form. This can be done by eliminating transitive dependencies – i.e. values which can be determined from other non-key columns.

Convert this table to 3NF. Don’t forget to lable the primary key of the resulting new table in RED.

Problem #4: 3NF

The table here contains data for performances and their entertainers; it has not been normalized to any degree. It includes dates and venues (locations) for the performances, and information about the entertainers. Note that more than one entertainer can be booked for a single performance; and that we can assume that salary is a standard charge for each entertainer (not performance-specific).

Advance the table to third normal form by applying the same methods from the previous questions:

· 1NF removes repeating data, breaking into separate rows

· 2NF removes columns not dependent on the whole key

· 3NF removes columns dependent on other non-key columns

Your resulting solution will contain multiple tables; ensure that each one has its primary key column(s) labeled in RED.

Problem #5: 3NF

This table contains data for student grades. It is in first normal form (no repeating data), but has not been normalized further. Advance it to third normal form by applying the methods from the previous questions:

· 2NF removes columns not dependent on the whole key

· 3NF removes columns dependent on other non-key columns

Your resulting solution will contain multiple tables; ensure that each one has its primary key column(s) labeled in RED.