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.

1. Create an ER diagram to represent the above data requirements.

2. Create a relational data model based on the ER diagram.

3. 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.

4. Write an SQL command to set the millage value to 150 for a car with VIN 123VNOP