Question

Description

In this project you will practice what we have learned in class about Design, ER Diagrams, Relational Models, DDL, SQL, CRUD (Create, Update, Delete) operations, associated queries, and mock data population. The goal is to create a realistic professional database/development experience. This assignment will describe the requirements for the database as you might receive them. You will need to fill in the details as you work on it. You will find that your work may be iterative, and you will need to update the ER Diagrams as you go so that it reflects your final design.

Set up a database for a new web site that provides a tool for Car dealers to sell over a large market without having to maintain their own web site.

The site will provide search capabilities across dealers.

Dealer Operations

-These sellers will sign up for an account to post vehicles on the site. There would be a monthly billing of this account. It could be an automatic or online ACH payment. A third-party application will be used for card payments. The card information won’t be stored in our database, but the payment will be received from the third party.

-The dealers will post their ad and include all the details (photo(s) of the make, model, year, mileage, color, condition (from good, fair, operational, poor, parts), VIN, general location of the car (city and state)), and the car dealer and their contact information.   

-They will also be able to post dealership information that can be linked to from the posting. It would entail gps coordinates for the dealership, contact information, photo, and a link to their own site (optional)

- The dealer can remove a posting or mark sold. The dealer can close their account.

-The site will not handle the actual purchase of the vehicle. That will be managed by the dealer in person.

- Information on the Business profile would include their business name, physical address(es) (multiple), mailing address, billing address, phone number, email address. They should be allowed to set up an unlimited number of dealers, users that could post/remove ads but not manage the account itself, dealerships, and have a single account administrator account that can deal with activation of the account.

The dealers would have their own login/profiles which would consist of name, phone(s), dealership with which they are associated, email, user, and password.

- The dealer can receive messages in the site from potential buyers with questions to which they can privately respond or block/report the buyer from further messages.

Buyer Operations

-The buyers can search by auto make, model, year, and location. This can be a real-time search or it can be a saved search that will email or text the user when cars meeting their saved search criteria are met. These can be ran and sent out daily or weekly.   

-The buyer can view the details of the car including make, model, year, mileage, color, a photo(s) of the car, color, condition (from good, fair, operational, poor, parts), VIN, and dealership information.

- The buyer can save contact preferences for marketing, search, and general account maintenance information.

- The buyer can save a search job or save their favorite searches so they need a login tied to this information.

- The buyer can make an appointment to see a dealer through the site.

- The buyer can post questions to the dealer and view responses.

Deliverables

• ER Diagram
• Relational Model – DDL with SQL script for creating the tables, constraints
• Script for populating with mock data (could use a tool to generate mock data).
• Standard CRUD (Create, Retrieve, Update/Insert, Delete) SQL queries saved as stored functions for the common operations as defined above.
• 3 report queries that a dealership might want from the site.

Answer 1

SQL QUERIES

CREATE TABLE Persons (PersonID int, LastName varchar(255), FirstName varchar(255),Address varchar(255), City varchar(255)
);

UPDATE CustomersSET ContactName = 'Amit', City= 'France' WHERE CustomerID = 1;

DELETE FROM table_name WHERE condition;

Queries:

Customer Legal Address proof

Customer ID

Buyer ID Proof