Question

Question 1: Car Dealership Scenario. Complete parts A – D based on the database specifications listed below.

Database Specification

• Car Dealerships have a unique business id number, dealership name, city, state, and a unique website URL. The business id is a number in the range from 1000-9999.
• Salespersons have a first name, last name, age, gender, and a unique employee id number. The employee number is in the range 100-999.
• Customers have a first name, last name, city, state, gender, age, and a unique taxpayer id number. The taxpayer id number is 9 digits.
• Cars have a make, model, year, suggested price, and a unique vehicle id number. A car can have (possibly multiple) colors that should be modeled as an attribute. Note: the make of a car is the manufacturer (e.g., Ford, Honda, BMW) and the model is the name of the model (e.g. Civic, Accord, CRX).
• Salespersons work for dealerships. In order to be in the database, a salesperson must work for a dealership. However, a dealership may exist in the database without any salespersons. Dealerships have many salespersons who work for them, and a salesperson may work for many different dealerships. The database should record the start date when a salesperson began working for a dealership.
• Dealerships own cars. A dealership may own many cars, or they may be completely out of inventory and own zero cars. A car can only be owned by one dealership, and must be owned by a dealership in order to be in the database. The database should record the date that the dealership acquired the car and the price that the dealership paid for the car.
• Salespersons sell cars to customers. Even if a salesperson has not sold any cars to customers they should still be stored in the database. However, the database should only store information about customers who have purchased a car from a salesperson. Information about cars owned by dealerships should be stored in the database regardless of whether they have been sold or not. A salesperson may sell a particular car to only one customer. Similarly, a customer may purchase a particular car from only one salesperson. However, a customer may purchase more than one car from the same salesperson. When a salesperson sells a car to a customer, the sales price and date should be recorded in the database.

Parts A - D

1. Based on the database specification given above, draw an ER diagram for the database. Include entities, attributes, relationships, cardinality, and participation constraints. The ER diagram should accurately reflect the specifications.  Clearly state any assumptions.

2. Map your ER diagram to relations. Make sure to represent the relations, attributes, primary keys, and foreign keys.

3. Implement your database. Based on your ER and relation diagrams, create tables, specify primary and foreign keys, and include any other necessary integrity constraints such as checks. Turn in a list of the SQL DDL commands (e.g., create table commands) that you used to create your database.

4. Populate your database with data. You may do this either by writing SQL insert statements to load data, or by using the data entry or import features of MySQL Workbench. You need to create enough and appropriate data to illustrate that your database works for the queries below. At a minimum, load at least three records for each table. However, you may need to load more records to illustrate that particular queries work correctly.

5. Write and run SQL queries in MySQL Workbench for each of the queries listed below using your database with the data loaded. In your report, for each query, restate the query in English, then list the SQL query you wrote for it, and finally, list the output from the database in response to the query.

Queries: Write and run SQL queries for the following questions. Use the minimum number of tables required for each query.

1. List the first and last names of customers over 40 years old who purchased a Ford car.
2. List the first and last names of salespersons who have NEVER sold a Ford car.
3. List the total value (suggested price) of all cars owned by the dealership named ‘SILS Car Deals’.
4. Find the average sales price (actual sales price, not suggested price) of a 2008 Honda Civic.
5. List the dealership name and the total number of cars that each dealership owns.
6. List the average age of customers who have purchased a car from salesperson ‘Homer Simpson’.
7. List the last name, employee id number, and the overall number of cars sold by each salesperson who has sold at least one Honda Civic.
8. List the names of salespersons who have worked for all the car dealerships.

Answer 1

SQL queries :

i) We have to make a relational table for the cars sold to the customers by the salesperson. Hence the table will contain attributes from the tables Customer, Salesperson & Cars. The query will be :
SELECT FIRST_NAME, LAST_NAME FROM PURCHASE WHERE AGE>40 AND MAKE='FORD'

ii) We have created a PURCHASE table as described in the solution above and hence we can use this table and the Salesperson table. The query will be :

SELECT FIRST_NAME, LAST_NAME FROM SALESPERSON WHERE EMPLOYEE_ID NOT IN (SELECT EMPLOYEE_ID FROM PURCHASE WHERE MAKE='FORD')

iii) We can build a table OWNS to store data for which dealer bought which car and now owns it. We can then use this table to answer this query as it will contain data from both DEALERS and CARS table:

SELECT SUM(SUGGESTED_PRICE) FROM OWNS WHERE DEALERSHIP_NAME='Sils Car Deals'

iv) The PURCHASE table will have all the information of cars purchased by any customer and also cars sold by the salespersons and also the price at which deal is made between the two. Hence we can use this to solve this query:

SELECT AVG(ACTUAL_PRICE) FROM PURCHASE WHERE YEAR='2008' AND MAKE='HONDA' AND MODEL='CIVIC'