diff --git a/UsedCars/UsedCars_schema.sql b/UsedCars/UsedCars_schema.sql new file mode 100644 index 0000000..e0cc230 --- /dev/null +++ b/UsedCars/UsedCars_schema.sql @@ -0,0 +1,297 @@ +-------------------------------------------------------------------------------- +-- +-- SQL schema definition of the Southern Technical Institute of Natural Knowledge +-- student records database, as specified in INFO 214 Assignment 1. +-- +-- Schema written for Oracle11g. +-- +-------------------------------------------------------------------------------- +-- +-- NOTE: this script will drop any existing tables automatically so +-- that they can be re-created. +-- + + +DECLARE + TYPE Table_Names_T IS TABLE OF VARCHAR2 ( 32 ) ; + Table_Names Table_Names_T := Table_Names_T ( 'STAFF', 'SERVICE', 'OTHER', 'SALES', 'CUSTOMER', 'CAR', 'FEATURE', 'CAR_FEATURE', 'WARRANTY', 'PURCHASE', 'SALE' ) ; + Table_Nonexistent EXCEPTION; + PRAGMA Exception_Init ( Table_Nonexistent, -942 ) ; +BEGIN + + FOR T IN Table_Names.FIRST..Table_Names.LAST + LOOP + BEGIN + EXECUTE IMMEDIATE 'DROP TABLE ' || Table_Names ( T ) || '' || ' CASCADE CONSTRAINTS'; + + EXCEPTION + -- We only want to suppress exceptions due to the table not existing. That's + -- -942: ORA-00942: table or view does not exist + -- Other exceptions, such as being unable to drop a table because of locks(!), should not be trapped, so that the user sees an error message. + + WHEN Table_Nonexistent THEN + DBMS_Output.Put_Line ( SQLCODE || ': ' || SQLERRM || ': Ignoring nonexistent table ' || T ) ; + -- null; + + END; + + END LOOP; + +END; +/ + + +-------------------------------------------------------------------------------- +-- +-- Staff table +-- +-- CREATE SEQUENCE Staff_ID_Seq START WITH 1000 MAXVALUE 9999; + +CREATE TABLE Staff +( Staff_ID NUMBER(4), + Firstname VARCHAR2(50) NOT NULL, + Lastname VARCHAR2(50) NOT NULL, + Address VARCHAR2(150) NOT NULL, + -- Use VARCHAR2 for phone numbers in order to retain leading zeros. + -- Format: leading 0 plus 1-digit area code plus up to 9 digits, e.g., + -- 02187654321; longer if we allow punctuation to be included. + -- (http://en.wikipedia.org/wiki/Telephone_numbers_in_New_Zealand) + Phone VARCHAR2(11) NOT NULL, + Date_Hired DATE DEFAULT SYSDATE + NOT NULL, + Date_of_Birth DATE NOT NULL, + -- + CONSTRAINT Staff_Valid_Age + CHECK ( ( Date_Hired - TO_YMINTERVAL( '18-0' ) ) >= Date_of_Birth ), + -- + CONSTRAINT Staff_PK PRIMARY KEY ( Staff_ID ) +); + + +-------------------------------------------------------------------------------- +-- +-- Service table +-- +CREATE TABLE Service +( Staff_ID NUMBER(4), + Hourly_Rate NUMBER(5,2) NOT NULL + CONSTRAINT Service_Hourly_Rate_Min + CHECK ( Hourly_Rate >= 13.50 ), + Total_Hours NUMBER(6,2) DEFAULT 0 NOT NULL + CONSTRAINT Service_Total_Hours_Range + CHECK ( Total_Hours BETWEEN 0 AND 4500 ) + CONSTRAINT Service_Total_Hours_Quarters + CHECK ( TRUNC( Total_Hours * 4 ) = ( Total_Hours * 4 ) ), + -- + CONSTRAINT Service_PK PRIMARY KEY ( Staff_ID ), + CONSTRAINT Service_FK_to_Staff + FOREIGN KEY ( Staff_ID ) REFERENCES Staff ON DELETE CASCADE +); + + +-------------------------------------------------------------------------------- +-- +-- Other table +-- +CREATE TABLE Other +( Staff_ID NUMBER(4), + Salary NUMBER(8,2) NOT NULL + CONSTRAINT Other_Salary_Min + CHECK ( Salary >= 28080 ), + -- + CONSTRAINT Other_PK PRIMARY KEY ( Staff_ID ), + CONSTRAINT Other_FK_to_Staff + FOREIGN KEY ( Staff_ID ) REFERENCES Staff ON DELETE CASCADE +); + + +-------------------------------------------------------------------------------- +-- +-- Sales table +-- +CREATE TABLE Sales +( Staff_ID NUMBER(4), + On_Commission CHAR(1) DEFAULT 'N' NOT NULL + CONSTRAINT Sales_Valid_On_Commission + CHECK ( On_Commission IN ( 'Y', 'N' ) ), + Commission_Rate NUMBER(3,2) NOT NULL + CONSTRAINT Sales_Valid_Commission_Rate + CHECK ( Commission_Rate BETWEEN 0.00 AND 0.30 ), + -- We can't make Gross_Earnings a computed column, because it requires data + -- from other tables. + Gross_Earnings NUMBER(8,2) NOT NULL + CONSTRAINT Sales_Valid_Gross_Earnings + CHECK ( Gross_Earnings >= 0.00 ), + -- + CONSTRAINT Sales_Check_Commission + CHECK ( ( ( On_Commission = 'N' ) AND ( Commission_Rate = 0 ) ) + OR ( ( On_Commission = 'Y' ) AND ( Commission_Rate > 0 ) ) ), + -- + CONSTRAINT Sales_PK PRIMARY KEY ( Staff_ID ), + CONSTRAINT Sales_FK_to_Staff + FOREIGN KEY ( Staff_ID ) REFERENCES Staff ON DELETE CASCADE +); + + +-------------------------------------------------------------------------------- +-- +-- Customer table +-- +-- CREATE SEQUENCE Customer_ID_Seq START WITH 100000 MAXVALUE 999999; + +CREATE TABLE Customer +( Customer_ID NUMBER(6), + Firstname VARCHAR2(50) NOT NULL, + Lastname VARCHAR2(50) NOT NULL, + Address VARCHAR2(150) NOT NULL, + Phone VARCHAR2(11) NOT NULL, + Email VARCHAR2(50), -- optionally CHECK format if desired + Credit_Rating CHAR(1) + CONSTRAINT Customer_Valid_Credit_Rating + CHECK ( Credit_Rating IN ( 'A', 'B', 'C', 'D' ) ), + Comments CLOB, + -- + CONSTRAINT Customer_PK PRIMARY KEY ( Customer_ID ) +); + + +-------------------------------------------------------------------------------- +-- +-- Car table +-- +CREATE TABLE Car +( VIN CHAR(17), -- optionally CHECK format if desired + Registration VARCHAR2(6) NOT NULL, + Make VARCHAR2(20) NOT NULL, + Model VARCHAR2(30) NOT NULL, + Year NUMBER(4) NOT NULL + CONSTRAINT Car_Valid_Year CHECK ( Year >= 1995 ), + Colour VARCHAR2(20) NOT NULL, + Odometer NUMBER(7,1) NOT NULL + CONSTRAINT Car_Valid_Odometer + CHECK ( Odometer BETWEEN 0.0 AND 999999.9 ), + First_Registered DATE NOT NULL, + Last_Serviced DATE, + Price NUMBER(6) NOT NULL + CONSTRAINT Car_Valid_Price CHECK ( Price >= 0 ), + Flat_Rate NUMBER(4) NOT NULL + CONSTRAINT Car_Valid_Flat_Rate CHECK ( Flat_Rate > 0 ), + -- + -- Not specified, but makes sense. Bonus marks! + CONSTRAINT Car_Valid_Service_Date + CHECK ( Last_Serviced >= First_Registered ), + -- + CONSTRAINT Car_PK PRIMARY KEY ( VIN ) +); + + +-------------------------------------------------------------------------------- +-- +-- Feature table +-- +CREATE TABLE Feature +( Feature_Code VARCHAR2(5), + Description VARCHAR2(100) NOT NULL, + -- + CONSTRAINT Feature_PK PRIMARY KEY ( Feature_Code ) +); + + +-------------------------------------------------------------------------------- +-- +-- Car_Feature table +-- +CREATE TABLE Car_Feature +( VIN CHAR(17), + Feature_Code VARCHAR2(5), + -- + CONSTRAINT Car_Feature_PK PRIMARY KEY ( VIN, Feature_Code ), + CONSTRAINT Car_Feature_FK_to_Car + FOREIGN KEY ( VIN ) REFERENCES Car ON DELETE CASCADE, + CONSTRAINT Car_Feature_FK_to_Feature + FOREIGN KEY ( Feature_Code ) REFERENCES Feature ON DELETE CASCADE +); + + +-------------------------------------------------------------------------------- +-- +-- Warranty table +-- +CREATE TABLE Warranty +( W_Code CHAR(1), + Max_Age NUMBER(1), + Max_KM NUMBER(6), + Duration NUMBER(1), + Distance NUMBER(4), + Notes VARCHAR2(250), + -- + CONSTRAINT Warranty_PK PRIMARY KEY ( W_Code ) +); + +-- INSERT INTO Warranty ( W_Code, Duration, Distance, Description ) VALUES ( 'A', 4, 50000, 3, 5000, 'Category A motor vehicle' ); +-- INSERT INTO Warranty ( W_Code, Duration, Distance, Description ) VALUES ( 'B', 6, 75000, 2, 3000, 'Category B motor vehicle' ); +-- INSERT INTO Warranty ( W_Code, Duration, Distance, Description ) VALUES ( 'C', 8, 100000, 1, 1500, 'Category C motor vehicle' ); +-- INSERT INTO Warranty ( W_Code, Duration, Distance, Description ) VALUES ( 'D', NULL, NULL, 0, 0, 'Category D motor vehicle' ); + + +-------------------------------------------------------------------------------- +-- +-- Purchase table +-- +-- CREATE SEQUENCE Purchase_ID_Seq START WITH 10000000 MAXVALUE 99999999; + +CREATE TABLE Purchase +( Purchase_ID NUMBER(8), + Purchase_Date DATE NOT NULL, + Details CLOB NOT NULL, + Amount NUMBER(6) NOT NULL + CONSTRAINT Purchase_Valid_Amount CHECK ( Amount >= 0 ), + VIN CHAR(17) NOT NULL, + Customer_ID NUMBER(6) NOT NULL, + Salesrep_ID NUMBER(4) NOT NULL, + -- + CONSTRAINT Purchase_PK PRIMARY KEY ( Purchase_ID ), + CONSTRAINT Purchase_FK_to_Car FOREIGN KEY ( VIN ) REFERENCES Car, + CONSTRAINT Purchase_FK_to_Customer + FOREIGN KEY ( Customer_ID ) REFERENCES Customer, + CONSTRAINT Purchase_FK_to_Sales + FOREIGN KEY ( Salesrep_ID ) REFERENCES Sales +); + + +-------------------------------------------------------------------------------- +-- +-- Sale table +-- +-- CREATE SEQUENCE Sale_ID_Seq START WITH 10000000 MAXVALUE 99999999; + +CREATE TABLE Sale +( Sale_ID NUMBER(8), + Sale_Date DATE NOT NULL, + Details CLOB NOT NULL, + Amount NUMBER(6) NOT NULL + CONSTRAINT Sale_Valid_Amount CHECK ( Amount >= 0 ), + VIN CHAR(17) NOT NULL, + Customer_ID NUMBER(6) NOT NULL, + Salesrep_ID NUMBER(4) NOT NULL, + W_Code CHAR(1) NOT NULL, + Tradein_ID NUMBER(8), + -- + CONSTRAINT Sale_PK PRIMARY KEY ( Sale_ID ), + CONSTRAINT Sale_FK_to_Car FOREIGN KEY ( VIN ) REFERENCES Car, + CONSTRAINT Sale_FK_to_Customer + FOREIGN KEY ( Customer_ID ) REFERENCES Customer, + CONSTRAINT Sale_FK_to_Warranty + FOREIGN KEY ( W_Code ) REFERENCES Warranty, + CONSTRAINT Sale_FK_to_Sales + FOREIGN KEY ( Salesrep_ID ) REFERENCES Sales, + CONSTRAINT Sale_FK_to_Purchase + FOREIGN KEY ( Tradein_ID ) REFERENCES Purchase +); + + +-------------------------------------------------------------------------------- +-- +-- Here endeth the schema. +-- +--------------------------------------------------------------------------------