StudyDaddy Engineering

Answered You can hire a professional tutor to get the answer.

QUESTION

May 06, 2019

For Part II of your final project, you will implement your plan for the scenario below. Project plan should include the entity relationship diagram...

For Part II of your final project, you will implement your plan for the scenario below. 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

Price

Make

Model

Year

Exterior Color

Interior Color

Transmission (Manual/Automatic)

Engine (# of cylinders)

Mileage

Fully loaded (Yes/No)

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

VIN Number

Customer ID

Advertised Price

Purchase Price

Financed (Yes/No)

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