/********************EXECUTE SEQUENCE 2********************/

-- ----------------------------

-- Table structure for "MAKE"

-- ----------------------------

DROP TABLE MAKE;

CREATE TABLE MAKE (

MAKE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

NAME VARCHAR(60) NOT NULL,

DESCRIPTION VARCHAR(100),

YEAR_START INT NOT NULL,

YEAR_END INT

);

-- ----------------------------

-- Table structure for "MODEL"

-- ----------------------------

DROP TABLE MODEL;

CREATE TABLE MODEL (

MODEL_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

NAME VARCHAR(60) NOT NULL,

DESCRIPTION VARCHAR(100),

YEAR_START INT NOT NULL,

YEAR_END INT,

MAKE_ID INT NOT NULL

);

-- ----------------------------

-- Table structure for "STYLE"

-- ----------------------------

DROP TABLE STYLE;

CREATE TABLE STYLE (

STYLE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

NAME VARCHAR(60) NOT NULL,

DESCRIPTION VARCHAR(100),

STYLE_TYPE INT(3), -- TRIM, PACKAGE

BODY_TYPE INT(3) NOT NULL, -- SEDAN, WAGON, CROSSOVER, LUXURY, COUPE, HATCHBACK, SUV, HYBRID ETC..

YEAR_START INT NOT NULL,

YEAR_END INT,

MODEL_ID INT NOT NULL

);

-- ----------------------------

-- Table structure for "SPEC"

-- ----------------------------

DROP TABLE MILEAGE;

CREATE TABLE MILEAGE (

MILEAGE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

MILEAGE_CITY INT(3),

MILEAGE_HWY INT(3),

YEAR INT(4), -- POPULATED WHEN MILEAGE CHANGES FOR A STYLE

STYLE_ID INT NOT NULL

);

-- ----------------------------

-- Table structure for "CATEGORY"

-- ----------------------------

DROP TABLE CATEGORY;

CREATE TABLE CATEGORY (

CATEGORY_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

NAME VARCHAR(60) NOT NULL,

DESCRIPTION VARCHAR(100),

MISC_DATA VARCHAR(250),

CATEGORY_TYPE INT(3), -- FUTURE USE.

SEQ_ORDER INT(3),

AUTH_REQUIRED TINYINT(1),

PARENT_ID INT,

EFFECTIVE_DATE DATE NOT NULL,

EXPIRY_DATE DATE,

CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

CREATED_BY INT NOT NULL

);

-- ----------------------------

-- Table structure for "VEHICLE"

-- ----------------------------

DROP TABLE VEHICLE;

CREATE TABLE VEHICLE (

VEHICLE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

YEAR INT NOT NULL,

MAKE_ID INT NOT NULL,

MODEL_ID INT NOT NULL,

STYLE_ID INT, -- MADE NULLABLE TO SUPPORT ADDING VEHICLE FOR INQUIRY BY CUSTOMERS.

STOCK_NBR VARCHAR(60),

DESCRIPTION VARCHAR(100),

ENGINE VARCHAR(60),

TRANS_TYPE INT(3), -- AUTOMATIC, 5 SPEED MANUAL

DRV_TRN_TYPE INT(3), -- AWD, FWD, RWD, 4WD

FUEL_TYPE INT(3),

VIN VARCHAR(60),

MILEAGE INT,

DEALER_PRICE DECIMAL(8, 2) NOT NULL,

SALE_PRICE DECIMAL(8, 2) NOT NULL,

SPECIAL_PRICE DECIMAL(8, 2),

COLOR_EXT VARCHAR(60),

COLOR_INT VARCHAR(60),

WARRANTY_TYPE INT(3), -- STANDARD, EXTENDED

WARRANTY_TERM INT(4), -- EX: IN MONTHS 12, 24, 36, 48, 60

WARRANTY_MILES INT, -- EX: 30000, 50000, 100000

STATUS INT(3) NOT NULL, -- AVAILABLE, SOLD, ONHOLD

VEH_CONDITION INT(3), -- NEW, USED, CERTIFIED

CARFAX_APPRV_URL VARCHAR(200), -- IF THE VEHICLE IS CARFAX APPROVED, THEN ENTER THE URL HERE.

ADDITIONAL_INFO VARCHAR(1000),

DATE_SOLD DATE,

LOCATION_ID INT NOT NULL,

CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

CREATED_BY INT NOT NULL

);

-- ----------------------------

-- Table structure for "FEATURE"

-- ----------------------------

DROP TABLE FEATURE_GROUP;

CREATE TABLE FEATURE_GROUP (

FEATURE_GROUP_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

NAME VARCHAR(60) NOT NULL,

DISPLAY_TYPE INT(3), -- CHECKBOX: CAN SELECT MORE THAN ONE VALUE, RADIOBUTTON: SELECT ONLY ONE VALUE.

DESCRIPTION VARCHAR(100),

GROUP_TYPE INT(3), -- POSSIBLE VALUES COULD BE POWER TRAIN, DRIVE TRAIN, OPTIONS

PARENT_ID INT -- REFERS TO THE FEATURE_GROUP_ID TO PROVIDE SUPPORT FOR SUB-GROUPING

);

-- ----------------------------

-- Table structure for "FEATURE_VALUE"

-- ----------------------------

DROP TABLE FEATURE;

CREATE TABLE FEATURE (

FEATURE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

FEATURE_VALUE VARCHAR(100),

ADD_DEFAULT TINYINT(1), -- If True, will be automatically added by default for each new entry in the Inventory

MAKE_ID INT, -- IF VALUE INSERTED HERE, THIS WILL BE SHOWN ONLY FOR THE SPECIFIED MAKES. IF NULL, THEN WILL BE SHOWN FOR ALL. EX: ACURALINK

YEAR_START INT(4), -- IF POPULATED, THIS FEATURE WILL BE SHOWN ONLY TO RELEVANT MODELS STARTING THIS YEAR.

YEAR_END INT(4), -- IF POPULATED, THIS FEATURE WILL NOT BE SHOWN TO MODELS AFTER THIS YEAR.

FEATURE_GROUP_ID INT NOT NULL

);

-- ----------------------------

-- Table structure for "IMAGE"

-- ----------------------------

DROP TABLE IMAGE;

CREATE TABLE IMAGE (

IMAGE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

NAME VARCHAR(60) NOT NULL,

DESCRIPTION VARCHAR(100),

IMAGE_EXTENSION VARCHAR(10),

MIME_TYPE VARCHAR(40),

IMAGE_LOCATION VARCHAR(100),

IMAGE_TYPE INT(3), -- PRIMARY, BANNER, LOGO

IMAGE_SIZE INT,

BANNER TINYINT(1), -- USE THIS IMAGE FOR BANNER.

VEHICLE_ID INT, -- POPULATED IF THE IMAGES ARE OF VEHICLES

USER_INFO_ID INT, -- POPULATED IF THE IMAGES ARE OF USERS

CLIENT_ID INT, -- POPULATED IF THE IMAGES ARE OF CLIENTS

CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

);

-- ----------------------------

-- Table structure for "ADVERTISEMENT"

-- ----------------------------

DROP TABLE ADVERTISEMENT;

CREATE TABLE ADVERTISEMENT (

ADVERTISEMENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

EFFECTIVE_DATE DATE NOT NULL,

EXPIRY_DATE DATE,

STATUS INT(3) NOT NULL, -- ACTIVE, SUSPENDED, CANCELLED

UNIT_PRICE DECIMAL(8, 2),

UNITS INT(3), -- PRICE PER UNIT WHICH CAN BE: HOURLY, DAILY, WEEKLY, MONTHLY ETC.

VEHICLE_ID INT NOT NULL, -- REFERS TO THE VEHICLE WHICH NEEDS TO BE ADVERTISED.

CREATED_BY INT NOT NULL, -- REFERS TO THE USER WHO IS ADVERTISING THE VEHICLE. CAN BE EITHER CLIENT OR CUSTOMER.

CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

);

-- ----------------------------

-- Table structure for "LOAN_APPLICATION"

-- ----------------------------

DROP TABLE LOAN_APPLICATION;

CREATE TABLE LOAN_APPLICATION (

LOAN_APPLICATION_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

VEHICLE_ID INT NOT NULL,

STATUS INT(3) NOT NULL, -- SUBMITTED, UNDER REVIEW, APPROVED

FINANCE_TYPE INT(3), -- PURCHASE, LEASE

LOAN_TERM INT(4), -- IN MONTHS

LOAN_AMOUNT DECIMAL(8, 2),

DOWNPAYMENT DECIMAL(8, 2),

ADDTIONAL_INFO VARCHAR(1000),

CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

CREATED_BY INT,

UPDATE_DATE TIMESTAMP NOT NULL,

UPDATED_BY INT

);

-- ----------------------------

-- Table structure for "APPLICANT"

-- ----------------------------

DROP TABLE APPLICANT;

CREATE TABLE APPLICANT (

APPLICANT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

LOAN_APPLICATION_ID INT NOT NULL,

FIRST_NAME VARCHAR(60) NOT NULL,

MIDDLE_NAME VARCHAR(60),

LAST_NAME VARCHAR(60) NOT NULL,

DATE_OF_BIRTH DATE NOT NULL,

SSN INT NOT NULL,

DMV_LICENSE_NUM VARCHAR(15),

LICENSE_STATE VARCHAR(2),

LICENSE_EXPIRY DATE

);

-- ----------------------------

-- Table structure for "RESIDENCE"

-- ----------------------------

DROP TABLE RESIDENCE;

CREATE TABLE RESIDENCE (

RESIDENCE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

APPLICANT_ID INT NOT NULL,

LOCATION_ID INT NOT NULL,

RESIDENCE_TYPE INT(3) NOT NULL, -- RENTED, OWNED

RENT_MORG_AMOUNT DECIMAL(8, 2), -- AMOUNT PER MONTH

TOTAL_YEARS INT(2),

TOTAL_MONTHS INT(2)

);

-- ----------------------------

-- Table structure for "EMPLOYMENT"

-- ----------------------------

DROP TABLE EMPLOYMENT;

CREATE TABLE EMPLOYMENT (

EMPLOYMENT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

APPLICANT_ID INT NOT NULL,

LOCATION_ID INT NOT NULL,

EMPLOYER_NAME VARCHAR(60) NOT NULL,

DESIGNATION VARCHAR(60) NOT NULL,

OCCUPATION VARCHAR(60),

INCOME_MONTHLY INT NOT NULL,

TOTAL_YEARS INT(2),

TOTAL_MONTHS INT(2),

CURRENT_EMPLOYER TINYINT(1) NOT NULL

);

-- ----------------------------

-- Table structure for "ACCOUNT"

-- ----------------------------

DROP TABLE ACCOUNT;

CREATE TABLE ACCOUNT (

ACCOUNT_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

APPLICANT_ID INT NOT NULL,

LOCATION_ID INT NOT NULL,

BANK_NAME VARCHAR(100) NOT NULL,

ACCOUNT_TYPE INT(3) NOT NULL, -- CHECKING, SAVING

ACCOUNT_NUMBER VARCHAR(20) NOT NULL,

ROUTING_NUMBER VARCHAR(9)

);

-- ----------------------------

-- Table structure for "APPLICANT"

-- ----------------------------

DROP TABLE TRADEIN_INFO;

CREATE TABLE TRADEIN_INFO (

TRADEIN_INFO_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

LOAN_APPLICATION_ID INT NOT NULL,

MAKE_ID INT NOT NULL,

MODEL_ID INT NOT NULL,

STYLE_ID INT NOT NULL,

YEAR INT(4),

COLOR VARCHAR(20),

MILEAGE INT,

VIN VARCHAR(60),

PAYOFF_AMOUNT DECIMAL(8,2),

PAYOFF_THRU_DATE DATE,

LIEN_HOLDER_ADD1 VARCHAR(100),

LIEN_HOLDER_ADD2 VARCHAR(100),

LIEN_HOLDER_CITY VARCHAR(20),

LIEN_HOLDER_STATE VARCHAR(2),

LIEN_HOLDER_ZIP VARCHAR(5)

);

-- ----------------------------

-- Table structure for "INQUIRY"

-- ----------------------------

DROP TABLE INQUIRY;

CREATE TABLE INQUIRY (

INQUIRY_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

FIRST_NAME VARCHAR(20) NOT NULL,

LAST_NAME VARCHAR(20) NOT NULL,

PHONE_NUMBER VARCHAR(20) NOT NULL,

EMAIL VARCHAR(50) NOT NULL,

INQUIRY_TYPE INT(3), -- FUTURE USE

NOTES VARCHAR(800),

VEHICLE_ID INT, -- IF AN INQUIRY IS PLACED FOR THIS VEHICLE

USER_INFO_ID INT, -- IF THIS IS AN EXISTING USER, THEN POPULATE.

CREATE_DATE TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP

);

-- ----------------------------

-- Table structure for "FIND_VEHICLE"

-- ----------------------------

DROP TABLE FIND_VEHICLE;

CREATE TABLE FIND_VEHICLE (

FIND_VEHICLE_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

BODY_TYPE INT(3), -- SUV, SEDAN ETC.

MODEL_ID INT, -- REFERS THE MODEL_ID COLUMN

STYLE_ID INT, -- REFERS THE STYLE_ID COLUMN

MILEAGE INT,

INQUIRY_ID INT NOT NULL -- -- REFERS THE INQUIRY_ID COLUMN

);

-- ----------------------------

-- Table structure for "TRASH_CAN"

-- ----------------------------

DROP TABLE TRASH_CAN;

CREATE TABLE TRASH_CAN (

TRASH_CAN_ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,

LOCATION VARCHAR(100) NOT NULL, -- LOCATION TO BE TRASHED

TRASHED_BY INT NOT NULL

);

-- ----------------------------

-- MANY TO MANY RELATIONSHIP --

-- ----------------------------

-- ----------------------------

-- Table structure for "RL_VEHICLE_CATEGORY"

-- ----------------------------

DROP TABLE RL_VEHICLE_CATEGORY;

CREATE TABLE RL_VEHICLE_CATEGORY (

VEHICLE_ID INT NOT NULL,

CATEGORY_ID INT NOT NULL,

PRIMARY KEY(VEHICLE_ID, CATEGORY_ID)

);

-- ----------------------------

-- Table structure for "RL_VEHICLE_FEATURE"

-- ----------------------------

DROP TABLE RL_VEHICLE_FEATURE;

CREATE TABLE RL_VEHICLE_FEATURE (

VEHICLE_ID INT NOT NULL,

FEATURE_ID INT NOT NULL,

PRIMARY KEY(VEHICLE_ID, FEATURE_ID)

);

-- ----------------------------

-- Table structure for "RL_STYLE_FEATURE"

-- ----------------------------

DROP TABLE RL_STYLE_FEATURE;

CREATE TABLE RL_STYLE_FEATURE (

STYLE_ID INT NOT NULL,

FEATURE_ID INT NOT NULL,

PRIMARY KEY(STYLE_ID, FEATURE_ID)

);