For Part II of your final project, you will implement your plan for the scenario below, which was first provided in Module 14.

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.

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 InventoryUsed Vehicle InventoryVIN NumberVIN NumberPricePriceMakeMakeModelModelYearYearExterior ColorExterior ColorInterior ColorInterior ColorTransmission (Manual/Automatic)Transmission (Manual/Automatic)Engine (# of cylinders)Engine (# of cylinders)MileageMileageFully loaded (Yes/No)Fully loaded (Yes/No)# of previous ownersCarfax 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 SalesUsed Car SalesSales ID NumberSales ID NumberVIN NumberVIN NumberCustomer IDCustomer IDAdvertised PriceAdvertised PricePurchase PricePurchase PriceFinanced (Yes/No)Financed (Yes/No)Date soldDate sold

CustomerCustomer IDFirst NameLast NameAddressPhone NumberSales ID Number

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

1. List all tables with primary keys and foreign keys identified.
2. List all functional dependencies.
3. 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

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