For Part II of your final project, you will implement your plan for the scenario below, which was first provided in Module $14.$ Your project plan should include the entity relationship diagram $($ERD$),$ tables, and SQL statements.

Scenario

Imagine that you just started a new job at ABC Car Sales. You$$ve been given the task of adding all the new and used car inventory to a new database management system $($DBMS$).$ You would like to manage the new and used car sales in the new DBMS system.

Listed below are the categories of information that you will need to input in the new database for each type of inventory:

New Vehicle Inventory Used Vehicle Inventory

VIN Number VIN Number

Price Price

Make Make

Model Model

Year Year

Exterior Color Exterior Color

Interior Color Interior Color

Transmission $($Manual$/$Automatic$)$ Transmission $($Manual$/$Automatic$)$

Engine $($# of cylinders$)$ Engine $($# of cylinders$)$

Mileage Mileage

Fully loaded $($Yes$/$No$)$ Fully loaded $($Yes$/$No$)$

# of previous owners

Carfax Report $($Yes$/$No$)$

You will also want to track the following types of information for new and used car sales, including specific customer data.

New Car Sales Used Car Sales

Sales ID Number Sales ID Number

VIN Number VIN Number

Customer ID Customer ID

Advertised Price Advertised Price

Purchase Price Purchase Price

Financed $($Yes$/$No$)$ Financed $($Yes$/$No$)$

Date sold Date sold

Customer

Customer ID

First Name

Last Name

Address

Phone Number

Sales ID Number

Instructions

For Part II of the final project, complete the following and submit it as a Word document.

List all tables with primary keys and foreign keys identified.

List all functional dependencies.

Provide the following SQL Statements:

CREATE query for the New Car Inventory table

CREATE query for the Used Car Inventory table

INSERT Used Car Inventory

INSERT New Car Inventory

INSERT Used Car Sale

INSERT New Car Sale

INSERT Customer

UPDATE New Car Inventory

UPDATE Used Car Inventory

UPDATE Customer

Provide a query for each task listed below:

identify which color car was the most popular customer choice

give a list of all the used cars that were sold for less than $$20,000$

lists all new cars for sale that are priced higher than $$40,000$

lists all fully loaded used cars for sale that have less than $$30,000$ miles

lists all cars sold this year

list customers who bought more than one car in the past three years

lists all manual cars with a $6-$cylinder engine and the list displays the VIN number, whether it is a new or used car and the interior and exterior color

Save your completed assignment as a Word document. You should also include the ERD that you completed in Part I. Click the Submit Assignment button to upload your files. You will be graded using the rubric below.