# automobile.sql
#
# ICS 184 Summer 2006
# Assignment #6
#
# This is an SQL script that (re-)creates the automobile sales database that
# we're working with in this assignment.  No test data is included; you'll
# want to include some, such as the test data you developed in Assignment #2.
#
# It is not necessary for you to submit this script; we'll be testing your
# program using our own database.


# (Re-)create the database.

DROP DATABASE IF EXISTS automobile;
CREATE DATABASE automobile;


# "Use" the database, so that subsequent commands will be executed on it.

USE automobile;


# Create the tables.  I've included foreign key constraints here, unlike
# the similar script from a previous assignment.
#
# Do not change anything about the creation of these tables!  Changes may
# cause your program to work with your script, but not with ours, which can
# have a negative effect on your grade.

CREATE TABLE car(
    vin CHAR(17) NOT NULL,
    make CHAR(20) NOT NULL,
    model CHAR(20) NOT NULL,
    year INTEGER NOT NULL,
    mileage INTEGER NOT NULL,
    asking_price NUMERIC(9, 2) NOT NULL,
    invoice_price NUMERIC(9, 2) NOT NULL,
    PRIMARY KEY(vin));

CREATE TABLE dealership(
    dealership_id INTEGER NOT NULL,
    dealership_name CHAR(30) NOT NULL,
    dealership_street_address CHAR(50) NOT NULL,
    dealership_city CHAR(20) NOT NULL,
    dealership_state CHAR(2) NOT NULL,
    PRIMARY KEY(dealership_id));

CREATE TABLE salesperson(
    salesperson_id INTEGER NOT NULL,
    salesperson_name CHAR(30) NOT NULL,
    PRIMARY KEY(salesperson_id));

CREATE TABLE customer(
    social_security_number CHAR(9) NOT NULL,
    customer_name CHAR(30) NOT NULL,
    customer_street_address CHAR(50) NOT NULL,
    customer_city CHAR(20) NOT NULL,
    customer_state CHAR(2) NOT NULL,
    PRIMARY KEY(social_security_number));

CREATE TABLE reports_to(
    salesperson_id INTEGER NOT NULL,
    managing_salesperson_id INTEGER NOT NULL,
    PRIMARY KEY(salesperson_id),
    FOREIGN KEY(salesperson_id) REFERENCES salesperson(salesperson_id),
    FOREIGN KEY(managing_salesperson_id) REFERENCES salesperson(salesperson_id));

CREATE TABLE works_at(
    salesperson_id INTEGER NOT NULL,
    dealership_id INTEGER NOT NULL,
    month_worked DATE NOT NULL,
    base_salary_for_month NUMERIC(9, 2) NOT NULL,
    PRIMARY KEY(salesperson_id, dealership_id, month_worked),
    FOREIGN KEY(salesperson_id) REFERENCES salesperson(salesperson_id),
    FOREIGN KEY(dealership_id) REFERENCES dealership(dealership_id));

CREATE TABLE inventory(
    vin CHAR(17) NOT NULL,
    dealership_id INTEGER NOT NULL,
    PRIMARY KEY(vin),
    FOREIGN KEY(dealership_id) REFERENCES dealership(dealership_id));

CREATE TABLE sale(
    vin CHAR(17) NOT NULL,
    social_security_number CHAR(9) NOT NULL,
    salesperson_id INTEGER NOT NULL,
    dealership_id INTEGER NOT NULL,
    sale_price NUMERIC(9, 2) NOT NULL,
    sale_date DATE NOT NULL,
    PRIMARY KEY(vin),
    FOREIGN KEY(vin) REFERENCES car(vin),
    FOREIGN KEY(social_security_number) REFERENCES customer(social_security_number),
    FOREIGN KEY(salesperson_id) REFERENCES salesperson(salesperson_id),
    FOREIGN KEY(dealership_id) REFERENCES dealership(dealership_id));

GRANT ALL ON automobile.* TO ics184jdbc;

