Background

World BMW is a BMW dealership located in Westlake Village, California. World BMW requires a database to manage their inventory of BMW$\text{'}$s$.$ The following is a description of the process.

Using a shipping document provided by the BMW manufacturer, the World Sales Manager enters the new autos that are received at the dealership onto World's BMW Inventory Database and also enters a Target selling price for each vehicle.

When a BMW is sold the World Sales Secretary enters the Actual selling price for the auto onto the BMW Inventory Database.

Every morning a report is produced from the BMW Inventory Database that lists the BMWs that are "Available for Sale" along with the Target selling price. This report goes to all the World Sales staff.

Every month an analysis report is produced from the BMW Inventory Database that lists all the BMWs sold during that month & compares the Actual selling price to the Target selling price $($ Target Versus Actual $\%).$ This report goes to the World Sales Manager.

At the end of the year, all sold BMWs are deleted from the BMW Inventory Database and added to the "Archive Database" which retains the historical records.

World requires a database to manage their inventory of BMW$\text{'}$s$.$ World's goal is to sell their BMW$\text{'}$s at manufacturer's cost plus $20\%.$ A list of the required data is provided. Please refer to the prior Dataflow Exercise for any additional data fields that may be required.

The Data

$\backslash$table$[[$Vehicles$,$Exterior Color$/$Code$,$Interior Color$/$Code$],[,$White $001,$Tan T$1],[$Vehicle # $($VIN$),$Black $002,$Black B$1],[$BMW Model,Silver $003,$Grey G$1],[$Manufacturers Cost $$,$Grey $004,],[$Year$,$Red $005,$Models$],[$Color$,$Blue $006,3$ Series$],[$Interior$,,5$ Series$],[$Exterior$,,7$ Series$],[,,8$ Series$]]$

The $6$ Step Method Step $1-$Understand the business requirements

Step $2-$Assemble data $($files$,$ reports, forms, user interviews, etc.$)$

Step $3-$Identify major entities $($Start with the obvious$)$

$$ Make a separate table for each set of related attributes

$$ Name the table$($s$)$

$$ Give each table a primary $($unique$)$ key

Step $4-$Eliminate

$$ Remove attributes with finite or repeating properties

$$ Example: $($ active $/$ inactive status code$)$

$$ Example: $($Major$\_$Title, Major$\_$College$)$

Step $5-$Standardize attributes from step $4$ in separate table$($s$)$

$$ Name the table$($s$)$

$$ Give each table a primary key

Step $6-$Relate the Tables

$$ Define foreign keys

$$ Define cardinality Assignment

Construct a data model based on our work at World BMW$.$ Additional data items that

you need to capture in your model are referenced in the background section of this

document.

Utilize the $6$ Step Method

Steps $1$&$2$ have been provided $($see background$)$

Complete... step $3$ thru step $6$

Draw your tables as shown below

Apply proper naming conventions $($see final thoughts below$)$

Final Thoughts....

The goal is clarity and precision

$$ Be specific

$$ Avoid abbreviations $($don$\text{'}$t be cryptic $)$

$$ Don't use same name twice

Entities

$$ Singular names $($ STUDENT not STUDENTS$)$

$$ One word

$$ Uppercase

Attribute

$$ Singular names

$$ Precede with entity name in uppercase

$$ Underscore

$$ Subsequent name$($s$)$ in lowercase

$$ Example: STUDENT$\_$Firstname

Validate with users