In-Class Exercise 06-2

Lincoln Autos is a small car dealership located in the city of Chatoon, IL. Most of its customers are either students or households looking for a second car. Appendix A shows basic information needed in order to run the dealership. The data shown in Appendix A is not organized in an effective way. It needs to be organized - or normalized to use the database designers’ terminology.

Database normalization is the process of organizing data to minimize redundancy. Normalization, usually, involves (a) dividing large tables into smaller (and less redundant) tables, and (b) defining relationships between them. The goal is to eliminate anomalies from the database in order to produce smaller and well structured related tables. This means isolating data in smaller tables so that additions, deletions, and modifications of fields can be made just in one table and then propagated through the rest of the database by means of the relationships.

Your Assignment

Normalize the tables shown in Appendix A in order to come up with four (4) tables so that the database is free of redundancy – except foreign keys. You need to use the template given in Appendix B of this assignment to create the design of your tables. The tables must be called: Employees, Sales, Inventory, and Customers. Make sure you have added the necessary fields for creating the relationships between the tables where applicable. Also, all addresses must be broken into street (e.g. 600 Lincoln Avenue), city, and state.

Telephone numbers and people’s names cannot be used as primary keys. In some cases, you may need to add a new field (not shown in Appendix A) in order to have a valid primary key if nome of the existing fields can be used to uniquely identify a record in a table.

What you need to submit:

-Your completed Appendix B for all four tables.

Appendix A: Lincoln Autos available data

Table 1

Sale_ID / SaleDate / Inventory_ID / Make / Model / Body / Engine / Trans
S001 / 1/1/2011 / IV005 / Toyota / Camry SE / Sedan / V4 / Automatic
S002 / 2/1/2011 / IV009 / Chevy / Impala XE / Sedan / V4 / Automatic
S003 / 2/3/2011 / IV001 / Honda / Accord LE / Sedan / V4 / Manual
S004 / 1/7/2011 / IV010 / Chrysler / 300M / Sedan / V4 / Automatic
S005 / 3/1/2011 / IV016 / Toyota / Camry XE / Sedan / V6 / Automatic
S006 / 1/28/2011 / IV003 / Ford / Escape-i / SUV / V6 / Manual
S007 / 1/20/2011 / IV004 / Ford / Explorer / SUV / V8 / Automatic

Table 1 (continued)

VehicleYear / SalePrice / CustomerName / CustomerAddress / CustomerPhone
2009 / 10250 / John Knew / 200 Lincoln Ave, Chatoon IL 61920 / 2173451111
2010 / 21000 / Isabel Williams / 234 Elm Street, Worth IL 60482 / 2177818569
2008 / 8500 / George Busch / 128 Mckinley Ave, Salem KY 42078 / 3255669000
2005 / 9000 / Elaine Wong / 41 Dawn Street, Rardin, IL 61920 / 2175420010
2003 / 6000 / Steve Simpson / 210 10th Street, Chatoon, IL 61920 / 2177453210
2010 / 15000 / Thether Saw / 102 Ohio Rd, Decatur, IL 62522 / 2179504566
2011 / 28000 / Lisa Bloom / 74 Indian Road, Chester, OH 45069 / 4109581000

**********************************************************************************

Table 2

Employee_ID / FirstName / LastName / Title / Experience
E001 / John / Longhorn / Salesperson / 10
E002 / Audry / Brown / Secretary / 5
E003 / Luc / Jambon / Salesperson / 5
E005 / Ken / Williams / Mecanic / 5
E006 / Diane / Lawson / Salesperson / 3
E007 / Roberto / Gonzales / Salesperson / 5
E008 / Steve / Johns / Manager / 10

Table 2 (continued)

Address / Phone
102 Ohio Street, Chatoon, IL 61920 / 2175824102
14 Lerna Road, Chatoon, IL 61920 / 2178542100
120 Jacki Lane, Chatoon, IL 61920 / 2178542121
128 Lincoln Avenue, Chatoon, IL 61920 / 2175242100
100 Main Street, Salem, KY 42078 / 2178754100
145 Neil Avenue, Champaign, IL 61820 / 2173251000
123 Prospect Avenue, Urbana, IL 61801 / 2176251015

MBA5670InClassExercise061/6

Appendix B: tables’ structure

Student Name: ______

Table’s name: ______

Column / Data type / Field size / Default value

Note: The table’s primary key must be shown with the PK suffix. Foreign keys (if any) must have the FK suffix.

Table’s name: ______

Column / Data type / Field size / Default value

Note: The table’s primary key must be shown with the PK suffix. Foreign keys (if any) must have the FK suffix.

Table’s name: ______

Column / Data type / Field size / Default value

Note: The table’s primary key must be shown with the PK suffix. Foreign keys (if any) must have the FK suffix.

Table’s name: ______

Column / Data type / Field size / Default value

Note: The table’s primary key must be shown with the PK suffix. Foreign keys (if any) must have the FK suffix.

MBA5670InClassExercise061/6