Week 5 Design Assignment | Complete Solution

Week 5 Design Assignment Instructions (Revision 2)

Part A

You own a used car lot and want to keep better track of your sales activities.

You want to keep track of the each car sold, the buyer, and the sales rep. For each car you also want to track the manufacturer, the model, the selling price, the Vin#, and the color. You would like to track each buyer’s name, address, email address, and their telephone number. You want to maintain each sales rep’s name, home address and telephone number. (You may include additional fields but that is not necessary.)

For Part A – only one person must be listed as the buyer of the car.

Using the normalization rules and process we have studied - determine which entities (tables) are needed, the attributes (fields) for each entity, and the set of 3NF tables. Document the 2NF and the 3NF stages by explaining how each table, and each field in that table complies with the appropriate normalization rule. Use your own words, incorporating normalization and functional dependency terminology.

Use the standard table notation found on page 34 and 161 to define each table. You must also include functional dependency notation (see page 161), and you must sufficient narration for the reader to understand the meaning of the accompanying notational representation. Do not include spreadsheets, data, or spreadsheet formatted content.

 You may start with a 1NF table or a set of tables you think may be in 2NF. If you elect the later – briefly explain your rationale behind each table and then confirm its compliance with 2NF rules.

Part B (worth 5% of the total grade)

The firm now realizes that in some cases they would like to list more than one person as the buyer of the car. The database now needs to support the situation where one car can be purchased by more than one person. You may work through the entire normalization process again, or you may explain the changes needed to the set of tables provided for Part A. For this part, you only need to explain how the 3NF tables comply with the third normal form rule.

Clearly delineate Part A from Part B, and the normal form stages in your submission.

Contact the instructor with any questions.

Submit the assignment in a word document under the week's Assignment. Name your document Last Name_Design (i.e. Smith_Design).