Information Storage and Retrieval Concepts
Consider a dealership that sells both new and used cars. Data requirements are summarized as follow:
- All cars are identified by a VIN (unique alphanumeric identifier for each car), make, model, year, and color, millage, and state (new or used).
- A salesperson may sell many cars, but each car is sold by only one salesperson.
- A salesperson is identified by a unique employee ID, name, and date when started working for the dealership
- A customer may buy many cars, but each car is bought by only one customer.
- A customer is identified by a unique email address, name, phone number, and address
- A salesperson writes an invoice for each car that is sold.
- A buyer receives an invoice for each car that is bought.
- An invoice has a unique invoice number, price to which the car was sold, and purchase date.
- Create an ER diagram to represent the above data requirements.
- Create a relational data model based on the ER diagram.
- Write an SQL command to create the INVOICE table. Include all attributes, data types, and constraints for that table. Assume the database and all other tables have already been created.
- Write an SQL command to set the millage value to 150 for a car with VIN 123VNOP
