[SOLVED] database CSE 4701 Fall 2019

CSE 4701 Fall 2019
Homework 3: Due October 16 (Wed) 2019, midnight, at HuskyCT
Problem 1. (30 pts)
Design a database for automobile dealers to assist them in maintaining customer records and dealer inventory and to assist sales staff in ordering cars.
Customer information is maintained such as Name, Address, Phone_No, and email_address.
Dealer information is maintained such as Name, SSN, Start-Date, Phone_No and email_address.
Each vehicle is identified by a vehicle identification number (VIN). Each individual vehicle is a particular model of a particular brand offered by the company (e.g., the GT is a model of the car brand Mustang of Ford Motor Company). Each model can be offered with a set of standard features (e.g., Drive: FWD, SeatType: Cloth, etc.) and a set of options (e.g., SeatType: Leather). Note that some model may not offer any option at all.
Sales information for who bought which vehicle is maintained (e.g., sale price, sales date, etc.)
The database need to store information about models, brands, and options as well as information about individual dealers, customers and cars.
1. Produce a ER diagram,
2. Produce a set of relational schemas based on your ER diagram
3. Identify primary keys and foreign keys in your relational schemas given in 2.
Note: Use of the word etc. in the description is to indicate that there could be other information you can include, but your design to include only the minimal information as those explicitly given in the problem description is allowed.
Note: 5% penalty for late submission. No acceptance after 10 am 10/17 (Thu) which is when the solution for Homework 3 will be uploaded.