/* VISION CONSULTING */

DROP TABLE CAR CASCADE CONSTRAINTS /
DROP TABLE CAR_RATE CASCADE CONSTRAINTS /
DROP TABLE CUSTOMER CASCADE CONSTRAINTS /
DROP TABLE OPTIONS CASCADE CONSTRAINTS /
DROP TABLE PARKING_LOT CASCADE CONSTRAINTS/
DROP TABLE SITE CASCADE CONSTRAINTS /
DROP TABLE RENTAL CASCADE CONSTRAINTS /
DROP TABLE RESERVATION CASCADE CONSTRAINTS/

/* VISION CONSULTING*/
/* revised 9-8-99 */

CREATE TABLE CAR (

VIN VARCHAR2 ( 20) NOT NULL,
CAR_TYPE NUMBER ( 3, 0) NOT NULL,
CAR_STATUS VARCHAR2 ( 10) NOT NULL,
LOCATION VARCHAR2 ( 10) NULL,
SPACE VARCHAR2 ( 3) NULL,
MAKE VARCHAR2 ( 10) NOT NULL,
MODEL VARCHAR2 ( 10) NOT NULL,
CAR_REG_NO VARCHAR2 ( 15) NOT NULL,
REG_STATE VARCHAR2 ( 5) NOT NULL,
REG_EXP VARCHAR2 ( 10) NOT NULL,
COLOR VARCHAR2 ( 7) NOT NULL,
YEAR VARCHAR2 ( 5) NOT NULL,
OWNED_CAR VARCHAR2 ( 3) NULL,
MILEAGE VARCHAR2 ( 9) NULL,

CONSTRAINT CAR_PK PRIMARY KEY (VIN))

/

CREATE TABLE CAR_RATE (

CAR_TYPE NUMBER ( 3, 0) NOT NULL,
RATE NUMBER ( 5, 2) NOT NULL,
DESCRIPTION VARCHAR2 ( 10) NULL,

CONSTRAINT CAR_RATE_PK PRIMARY KEY (CAR_TYPE))

/

CREATE TABLE CUSTOMER (

CUST_ID NUMBER ( 5, 0) NOT NULL,
CONT_LNAME VARCHAR2 ( 20) NOT NULL,
CONT_FNAME VARCHAR2 ( 20) NULL,
CO_NAME VARCHAR2 ( 25) NULL,
LNAME VARCHAR2 ( 20) NOT NULL,
FNAME VARCHAR2 ( 20) NOT NULL,
ADDRESS VARCHAR2 ( 35) NOT NULL,
CITY VARCHAR2 ( 25) NOT NULL,
STATE VARCHAR2 ( 5) NOT NULL,
POSTAL_CODE VARCHAR2 ( 12) NOT NULL,
COUNTRY VARCHAR2 ( 10) NOT NULL,
PHONE VARCHAR2 ( 12) NOT NULL,
DOB DATE NOT NULL,
LICENSE_NO VARCHAR2 ( 15) NOT NULL,
LICENSE_STATE VARCHAR2 ( 5) NOT NULL,

CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUST_ID))

/

CREATE TABLE OPTIONS (

OPTION_ID NUMBER ( 3, 0) NOT NULL,
OPTIONNAME VARCHAR2 ( 10) NULL,
OPTION_DESCRIPTION VARCHAR2 ( 55) NULL,

CONSTRAINT OPTIONS_PK PRIMARY KEY (OPTION_ID))

/

CREATE TABLE PARKING_LOT (

SPACE VARCHAR2 ( 3) NOT NULL,
LOCATION VARCHAR2 ( 10) NOT NULL,  
DIRECTIONS VARCHAR2 ( 150) NULL,

CONSTRAINT PARKING_LOT_PK PRIMARY KEY (SPACE, LOCATION))

/

CREATE TABLE SITE (

LOCATION VARCHAR2 ( 10) NOT NULL,
ADDRESS VARCHAR2 ( 35) NOT NULL,
CITY VARCHAR2 ( 20) NOT NULL,
STATE VARCHAR2 ( 5) NOT NULL,
POSTAL_CODE VARCHAR2 ( 12) NOT NULL,
PHONE VARCHAR2 ( 12) NOT NULL,
MANAGER VARCHAR2 ( 15) NOT NULL,
TOTAL_SPACES VARCHAR2 ( 5) NOT NULL,

CONSTRAINT SITE_PK PRIMARY KEY (LOCATION))

/

CREATE TABLE RENTAL (

RENTAL_ID NUMBER ( 6, 0) NOT NULL,
CUST_ID NUMBER ( 5, 0) NOT NULL,
CAR_TYPE NUMBER ( 3, 0) NOT NULL,
VIN VARCHAR2 ( 20) NOT NULL,
RESV_ID NUMBER ( 6, 0) NULL,
PICK_UP_SITE VARCHAR2 ( 20) NOT NULL,
PICK_UP_PERIOD DATE NOT NULL,
DROP_OFF_SITE VARCHAR2 ( 20) NULL,
DROP_OFF_PERIOD DATE NULL,
CAR_STATUS VARCHAR2 ( 10) NULL,
CC_TYPE VARCHAR2 ( 5) NOT NULL,
CC_NO VARCHAR2 ( 20) NOT NULL,
CC_EXP DATE NOT NULL,
CC_AUTH_NO VARCHAR2 ( 7) NULL,
PAYMENT_METHOD VARCHAR2 ( 6) NULL,
DRIVER_LICENSE_NO VARCHAR2 ( 30) NOT NULL,
DAILY_RATE NUMBER ( 5, 2) NOT NULL,
CAR_REG_NO VARCHAR2 ( 15) NOT NULL,
AMOUNT NUMBER ( 5, 2) NULL,

CONSTRAINT RENTAL_PK PRIMARY KEY (RENTAL_ID))

/

CREATE TABLE RESERVATION (

RESV_ID NUMBER ( 3, 0) NOT NULL,
CUST_ID NUMBER ( 3, 0) NULL,
LNAME VARCHAR2 ( 20) NOT NULL,
FNAME VARCHAR2 ( 20) NULL,
CAR_TYPE NUMBER ( 3, 0) NOT NULL,
PICK_UP_PERIOD DATE NOT NULL,
PICK_UP_SITE VARCHAR2 ( 20) NOT NULL,
DROP_OFF_PERIOD DATE NOT NULL,
DROP_OFF_SITE VARCHAR2 ( 20) NOT NULL,
COMMENTS VARCHAR2 ( 200) NULL,
QUANTITY NUMBER ( 3, 0) NOT NULL,
CC_NO VARCHAR2 ( 20) NOT NULL,
CC_TYPE VARCHAR2 ( 5) NOT NULL,
CC_EXP DATE NOT NULL,
OPTION_ID NUMBER ( 3, 0) NULL,

CONSTRAINT RESERVATION_PK PRIMARY KEY (RESV_ID))
/

/* VISION CONSULTING */
/* relationship, foreign keys */

ALTER TABLE CAR ADD CONSTRAINT
CAR_RATECAR FOREIGN KEY(CAR_TYPE )
REFERENCES CAR_RATE(CAR_TYPE )
/
ALTER TABLE RENTAL ADD CONSTRAINT
CARRENTAL FOREIGN KEY(VIN )
REFERENCES CAR(VIN )
/
ALTER TABLE RENTAL ADD CONSTRAINT
CUSTOMERRENTAL FOREIGN KEY(CUST_ID )
REFERENCES CUSTOMER(CUST_ID )
/
ALTER TABLE RESERVATION ADD CONSTRAINT
OPTIONSRESERVATION FOREIGN KEY(OPTION_ID )
REFERENCES OPTIONS(OPTION_ID ) ON DELETE CASCADE
/
ALTER TABLE CAR ADD CONSTRAINT
PARKING_LOTCAR FOREIGN KEY(LOCATION, SPACE )
REFERENCES PARKING_LOT(LOCATION, SPACE )
/
ALTER TABLE RENTAL ADD CONSTRAINT
RESERVATIONRENTAL FOREIGN KEY(RESV_ID )
REFERENCES RESERVATION(RESV_ID )
/

/* VISION CONSULTING*/
/* indexes */ 

CREATE INDEX LASTNAME ON
CUSTOMER (LNAME )
/
CREATE INDEX SPACE ON
PARKING_LOT (LOCATION, SPACE )
/
CREATE INDEX CONTACT on
CUSTOMER (CONT_LNAME)
/

Questions or problems regarding this web site should be directed to Donna_W@msn.com
Copyright © 2002 Donna Walsh. All rights reserved.
Last modified: Tuesday, March 26, 2002