# automobile.sql
#
# ICS 184 Summer 2006
# Assignment #2
#
# This is an SQL script that (re-)creates the automobile sales database that
# we're working with in this assignment, along with some test data to
# populate it.  (Note that lines that begin with "#" are considered by
# MySQL to be comments, and empty lines are ignored.)


# (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.  Other than specifying primary keys, no attempt was
# made to specify constraints on the data or relationships between data
# in different tables.  We'll address these concerns later this quarter.

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

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

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

CREATE TABLE reports_to(
    salesperson_id INTEGER,
    managing_salesperson_id INTEGER,
    PRIMARY KEY(salesperson_id));

CREATE TABLE works_at(
    salesperson_id INTEGER,
    dealership_id INTEGER,
    month_worked DATE,
    base_salary_for_month NUMERIC(9, 2),
    PRIMARY KEY(salesperson_id, dealership_id, month_worked));

CREATE TABLE inventory(
    vin CHAR(17),
    dealership_id INTEGER,
    PRIMARY KEY(vin));


# Add some test data to the tables.  The data below is totally made up, so
# I'm sure the car prices are totally unrealistic.  :)

INSERT INTO car VALUES
    ('1A11BCD1E1111F1G1', 'Toyota', 'Camry', 2006, 0, 24750.00, 21400.00);
INSERT INTO car VALUES
    ('2B22CDE2F2222G2H2', 'Mazda', 'Protege', 1991, 143570, 2300.00, 1100.00);
INSERT INTO car VALUES
    ('3C33DEF3G3333H3I3', 'Ford', 'F-150', 2001, 67554, 9575.00, 7125.00);

INSERT INTO dealership VALUES
    (101, 'Irvine Toyota Sales', '100 Alton St', 'Irvine', 'CA');

INSERT INTO salesperson VALUES (201, 'Joe Salesperson');
INSERT INTO salesperson VALUES (202, 'Angela Manager');
INSERT INTO salesperson VALUES (203, 'Kyle Owner');

INSERT INTO reports_to VALUES (201, 202);
INSERT INTO reports_to VALUES (202, 203);

INSERT INTO works_at VALUES (201, 101, '2006-01-00', 2500.00);
INSERT INTO works_at VALUES (202, 101, '2006-01-00', 4000.00);
INSERT INTO works_at VALUES (203, 101, '2006-01-00', 12000.00);

INSERT INTO inventory VALUES ('1A11BCD1E1111F1G1', 101);

