So I have the following SQL commands for the description below. I am not sure I have the constraints right especially for the foreign keys, and when to use not null and when not too. Can someone verify if the commands are correct, based on the database description?
- 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.
create table Car_dealership( Business_id int check ( 1000 >= business_id <= 9999), b_name varchar(30) not null, b_city varchar(20) not null, b_state char(2) not null, web_url varchar(100) not null, primary key (Business_id) ); create table salesperson ( employee_id int check ( 100 >= employee_id <= 999), first_name varchar (30) not null, last_name varchar (30), age int check (0 < age < 130) , gender char(1), /* assumed gender is either Male (M) or Female (F) */ primary key (employee_id) ); create table customers ( taxpayer_id decimal(9,0), first_name varchar (30) not null, last_name varchar (30), age int check (0 < age < 130), gender char(1), /* assumed gender is either Male (M) or Female (F) */ city varchar (20), state char(2), primary key (taypayer_id), ); create table cars ( vehicle_id int, make varchar(30), model varchar(30), make_year year, suggested_price int, business_id int, date_acquired date, price_paid int, primary key (vehicle_id), foreign key(business_id) references Car_Dealerships (business_id) ); create table car_colors ( vehicle_id int, colors varchar (30), primary key (vehicle_id), foreign key(vehicle_id) references cars (vehicle_id) ); create table works_for ( business_id int, employee_id int, start_date date not null, primary key (business_id, employee_id), foreign key(business_id) references Car_Dealerships (business_id), foreign key(employee_id) references salesperson (employee_id) ); create table sells ( vehicle_id int, taxpayer_id int, employee_id int, sale_price int, date_sold date, primary key (vehicle_id) , foreign key(vehicle_id) references cars (vehicle_id), foreign key(taxpayer_id) references customers (taxpayer_id), foreign key(employee_id) references salesperson (employee_id) );