Maximus Database Relationships

Develop entities and attributes for a database for Maximus Motor Company from the information supplied by the Company. Develop an Entity-Relationship Diagram for Maximus Motor Company using the entities and attributes used to create the database.

Maximus Motor Company has a need for a database to record sales information. This database will be shared by the various dealerships owned by the Maximus Motor Company. The database will allow the dealerships to share customer information and allow for various reports to be run, such as total number of sales at a specific dealership. The database will supply the sales team with information on in house financing compared to cash sales or alternate financing. The Trade-in statistics will also be available to the sales team.

The following assumptions were made. This database is for sales information only and is not a vehicle inventory database. Trade-ins and financing are optional on a sale and will not be required as customers may either make a down payment on a vehicle or have their own financing, or the transactions may be a cash sale.

The following entities were created for the database.

Sales Table

PK Field Name Data Type Description Required Duplicates

customer_id Number Unique ID Yes Yes

dealershp_id Number Unique ID Yes Yes

Contract_id Number Unique ID No No

trde_in_id Number Unique ID No No

vehicle_stck Number Unique ID Yes No

Dealership Table

PK Field Name Data Type Description Required Duplicates

Yes dealershp_id Number Unique ID Yes No

dealershp_name Text Name Yes No

dealershp_addrs Text Address Yes No

dealershp_phn Text Phone Nr Yes No

dealershp_max_inv Number Max Inv No No

dealershp_crnt_inv Number Current Inv No No

Customer Table

PK Field Name Data Type Description Required Duplicates

Yes customer_id Autonumber Unique ID Yes No

customer_first_name Text First Name Yes Yes

customer_last_name Text Last Name Yes Yes

customer_mdl_int Text Middle Init No Yes

customer_addrs Text Address Yes Yes

customer_phone Text Phone Nr Yes Yes

customer_dob Date/Time Date of Birth Yes Yes

customer_ssn Text SSN Yes No

customer_gross_inc Currency Gross Income Yes Yes

customer_cred_rate Number Credit rating Yes Yes

customer_mrtg Yes/No Have an mrtg? Yes Yes

customer_mrtg_amt Currency Amt of mrtg Yes Yes

customer_contacts Text Contacts Yes Yes

Vehicle Table

PK Field Name Data Type Description Required Duplicates

Yes Vehicle_stck Autonumber Invent Nr Yes No

Vehicle_make Text Make Yes Yes

Vehicle_Model Text Model Yes Yes

Vehicle_yr Text Build Date Yes Yes

Vehicle_clr Text Color Yes Yes

Vehicle_sales_rate Currency Price for sale Yes Yes

Vehicle_purchased_price Currency Cost @ purchase Yes Yes

Vehicle_Mileage Number Mileage Yes Yes

Vehicle_VIN Text VIN