tds_schema.sql

-- Generated by Oracle SQL Developer Data Modeler 17.3.0.261.1541
--   at:        2019-09-26 14:06:33 AEST
--   site:      Oracle Database 12c
--   type:      Oracle Database 12c

SET ECHO ON
SPOOL tds_schema_output.txt;

DROP TABLE assn_hist CASCADE CONSTRAINTS;

DROP TABLE cancellation CASCADE CONSTRAINTS;

DROP TABLE country CASCADE CONSTRAINTS;

DROP TABLE demerit CASCADE CONSTRAINTS;

DROP TABLE driver CASCADE CONSTRAINTS;

DROP TABLE engine CASCADE CONSTRAINTS;

DROP TABLE license CASCADE CONSTRAINTS;

DROP TABLE license_type CASCADE CONSTRAINTS;

DROP TABLE manufacturer CASCADE CONSTRAINTS;

DROP TABLE model CASCADE CONSTRAINTS;

DROP TABLE offence CASCADE CONSTRAINTS;

DROP TABLE police_off CASCADE CONSTRAINTS;

DROP TABLE police_station CASCADE CONSTRAINTS;

DROP TABLE registration CASCADE CONSTRAINTS;

DROP TABLE suspension CASCADE CONSTRAINTS;

DROP TABLE vehicle CASCADE CONSTRAINTS;

CREATE TABLE assn_hist (
    station_start_date              DATE NOT NULL,
    station_end_date                DATE NOT NULL,
    police_off_officer_id           NUMBER(10) NOT NULL,
    police_station_station_number   NUMBER(10) NOT NULL
);

COMMENT ON COLUMN assn_hist.station_start_date IS
    'gives the start date from when the police officer was assigned to that station ';

COMMENT ON COLUMN assn_hist.station_end_date IS
    'gives the end date till when the police officer was incharge of the stationn ';

COMMENT ON COLUMN assn_hist.police_off_officer_id IS
    'gives the officerid ';

COMMENT ON COLUMN assn_hist.police_station_station_number IS
    'gives the unique station number ';

ALTER TABLE assn_hist ADD CONSTRAINT assn_hist_pk PRIMARY KEY ( station_start_date,
police_off_officer_id );

CREATE TABLE cancellation (
    cancellation_date     DATE NOT NULL,
    cancellation_period   NUMBER NOT NULL,
    cancellation_reason   CHAR(50) NOT NULL,
    court_hearing_date    DATE,
    reinstatement_date    DATE,
    driver_l_num          NUMBER(20) NOT NULL
);

COMMENT ON COLUMN cancellation.cancellation_date IS
    'date of cancellation';

COMMENT ON COLUMN cancellation.cancellation_period IS
    'period of cancellation in months

';

COMMENT ON COLUMN cancellation.cancellation_reason IS
    'reason for cancellation
';

COMMENT ON COLUMN cancellation.court_hearing_date IS
    'Court hearing date ';

COMMENT ON COLUMN cancellation.reinstatement_date IS
    'reinstatement date ';

COMMENT ON COLUMN cancellation.driver_l_num IS
    'license number';

ALTER TABLE cancellation ADD CONSTRAINT cancellation_pk PRIMARY KEY ( cancellation_date,
driver_l_num );

CREATE TABLE country (
    m_country_code   VARCHAR2(10) NOT NULL,
    m_country_name   VARCHAR2(10) NOT NULL
);

COMMENT ON COLUMN country.m_country_code IS
    'country code';

ALTER TABLE country ADD CONSTRAINT country_pk PRIMARY KEY ( m_country_code );

CREATE TABLE demerit (
    demerit_code     NUMBER NOT NULL,
    demerit_points   NUMBER(10) NOT NULL,
    description      VARCHAR2(20) NOT NULL,
    auto_cancel      VARCHAR2(10) NOT NULL
);

ALTER TABLE demerit
    ADD CONSTRAINT auto_cancellation CHECK ( auto_cancel IN (
        'N',
        'Y'
    ) );

COMMENT ON COLUMN demerit.demerit_code IS
    'Demerit Code ';

COMMENT ON COLUMN demerit.demerit_points IS
    'demerit points';

COMMENT ON COLUMN demerit.description IS
    'gives a description of the demeirt';

COMMENT ON COLUMN demerit.auto_cancel IS
    'For Blood Alcohol content - only possible values are yes or no!';

ALTER TABLE demerit ADD CONSTRAINT demerit_pk PRIMARY KEY ( demerit_code );

CREATE TABLE driver (
    l_num                  NUMBER(20) NOT NULL,
    l_status               CHAR(20) NOT NULL,
    f_name                 CHAR(20) NOT NULL,
    l_name                 CHAR(20) NOT NULL,
    dob                    DATE NOT NULL,
    address_street         CHAR(20) NOT NULL,
    address_town           CHAR(20) NOT NULL,
    address_postcode       NUMBER(10) NOT NULL,
    l_expiry_date          DATE NOT NULL,
    total_demerit_points   NUMBER NOT NULL
);

COMMENT ON COLUMN driver.l_num IS
    'license number';

COMMENT ON COLUMN driver.l_status IS
    'license status';

COMMENT ON COLUMN driver.f_name IS
    'Driver''s first name';

COMMENT ON COLUMN driver.l_name IS
    'driver''s last name ';

COMMENT ON COLUMN driver.dob IS
    'driver''s date of birth';

COMMENT ON COLUMN driver.address_street IS
    'the street section in address';

COMMENT ON COLUMN driver.address_town IS
    'the town section in address';

COMMENT ON COLUMN driver.address_postcode IS
    'the postcode section in address';

COMMENT ON COLUMN driver.l_expiry_date IS
    'license expiry date ';

COMMENT ON COLUMN driver.total_demerit_points IS
    'Gives the total demerit points';

ALTER TABLE driver ADD CONSTRAINT driver_pk PRIMARY KEY ( l_num );

CREATE TABLE engine (
    engine_type     CHAR(20) NOT NULL,
    model_v_model   CHAR(10) NOT NULL
);

COMMENT ON COLUMN engine.engine_type IS
    'engine type';

COMMENT ON COLUMN engine.model_v_model IS
    'Vehicle - model';

ALTER TABLE engine ADD CONSTRAINT engine_pk PRIMARY KEY ( engine_type,
model_v_model );

CREATE TABLE license (
    driver_l_num          NUMBER(20) NOT NULL,
    license_type_l_code   NUMBER(10) NOT NULL
);

COMMENT ON COLUMN license.driver_l_num IS
    'license number';

COMMENT ON COLUMN license.license_type_l_code IS
    'license code';

ALTER TABLE license ADD CONSTRAINT license_pk PRIMARY KEY ( driver_l_num,
license_type_l_code );

CREATE TABLE license_type (
    l_code   NUMBER(10) NOT NULL,
    l_type   VARCHAR2(10) NOT NULL
);

COMMENT ON COLUMN license_type.l_code IS
    'license code';

ALTER TABLE license_type ADD CONSTRAINT license_type_pk PRIMARY KEY ( l_code );

CREATE TABLE manufacturer (
    m_code                   NUMBER(10) NOT NULL,
    m_name                   VARCHAR2(10) NOT NULL,
    country_m_country_code   VARCHAR2(10) NOT NULL
);

COMMENT ON COLUMN manufacturer.m_code IS
    'manufacturer code';

COMMENT ON COLUMN manufacturer.m_name IS
    'manufacturer name ';

COMMENT ON COLUMN manufacturer.country_m_country_code IS
    'country code';

ALTER TABLE manufacturer ADD CONSTRAINT manufacturer_pk PRIMARY KEY ( m_code );

CREATE TABLE model (
    v_model               CHAR(10) NOT NULL,
    transmission_mode     CHAR(20) NOT NULL,
    v_laden               NUMBER(7) NOT NULL,
    v_unladen             NUMBER(7) NOT NULL,
    engine_size           NUMBER(7) NOT NULL,
    manufacturer_m_code   NUMBER(10) NOT NULL,
    vehicle_vin           NUMBER NOT NULL
);

COMMENT ON COLUMN model.v_model IS
    'Vehicle - model';

COMMENT ON COLUMN model.transmission_mode IS
    'Gives the transmission type ';

COMMENT ON COLUMN model.v_laden IS
    'Gives the laden in mm';

COMMENT ON COLUMN model.v_unladen IS
    'Gives the unladen value in mm.';

COMMENT ON COLUMN model.engine_size IS
    'engine size in mm';

COMMENT ON COLUMN model.manufacturer_m_code IS
    'manufacturer code';

ALTER TABLE model ADD CONSTRAINT model_pk PRIMARY KEY ( v_model );

CREATE TABLE offence (
    offence_num             NUMBER(10) NOT NULL,
    location                CHAR(50) NOT NULL,
    "date"                  DATE NOT NULL,
    time                    DATE NOT NULL,
    police_off_officer_id   NUMBER(10) NOT NULL,
    demerit_demerit_code    NUMBER NOT NULL,
    vehicle_vin             NUMBER NOT NULL,
    driver_l_num            NUMBER(20) NOT NULL
);

COMMENT ON COLUMN offence.offence_num IS
    'Offence Number ';

COMMENT ON COLUMN offence.location IS
    'location of the offence';

COMMENT ON COLUMN offence."date" IS
    'date of the offence ';

COMMENT ON COLUMN offence.time IS
    'time of the offence';

COMMENT ON COLUMN offence.police_off_officer_id IS
    'gives the officerid ';

COMMENT ON COLUMN offence.demerit_demerit_code IS
    'Demerit Code ';

COMMENT ON COLUMN offence.driver_l_num IS
    'license number';

ALTER TABLE offence ADD CONSTRAINT offence_pk PRIMARY KEY ( offence_num );

CREATE TABLE police_off (
    officer_id           NUMBER(10) NOT NULL,
    officer_first_name   CHAR(10) NOT NULL,
    officer_last_name    CHAR(10) NOT NULL,
    rank                 CHAR(10) NOT NULL
);

COMMENT ON COLUMN police_off.officer_id IS
    'gives the officerid ';

COMMENT ON COLUMN police_off.officer_first_name IS
    'gives the officer first name ';

COMMENT ON COLUMN police_off.officer_last_name IS
    'gives us the officer last name ';

COMMENT ON COLUMN police_off.rank IS
    'gives the rank of the officer ';

ALTER TABLE police_off ADD CONSTRAINT police_off_pk PRIMARY KEY ( officer_id );

CREATE TABLE police_station (
    station_number          NUMBER(10) NOT NULL,
    station_address         NUMBER(10) NOT NULL,
    phone_number            NUMBER(10) NOT NULL,
    opening_hours           CHAR(10) NOT NULL,
    police_off_officer_id   NUMBER(10) NOT NULL
);

COMMENT ON COLUMN police_station.station_number IS
    'gives the unique station number ';

COMMENT ON COLUMN police_station.station_address IS
    'gives the address of the station ';

COMMENT ON COLUMN police_station.phone_number IS
    'gives the station phone number';

COMMENT ON COLUMN police_station.opening_hours IS
    'gives the time the station is open ';

COMMENT ON COLUMN police_station.police_off_officer_id IS
    'gives the officerid ';

CREATE UNIQUE INDEX police_station__idx ON
    police_station ( police_off_officer_id ASC );

ALTER TABLE police_station ADD CONSTRAINT police_station_pk PRIMARY KEY ( station_number );

CREATE TABLE registration (
    r_num         CHAR(20) NOT NULL,
    r_date        DATE NOT NULL,
    r_re_date     DATE,
    vehicle_vin   NUMBER NOT NULL
);

COMMENT ON COLUMN registration.r_num IS
    'registration number ';

COMMENT ON COLUMN registration.r_date IS
    'registration date ';

COMMENT ON COLUMN registration.r_re_date IS
    'Date - re registerered';

ALTER TABLE registration ADD CONSTRAINT registration_pk PRIMARY KEY ( vehicle_vin,
r_date );

CREATE TABLE suspension (
    suspension_start_date   DATE NOT NULL,
    suspension_end_date     DATE NOT NULL,
    driver_l_num            NUMBER(20) NOT NULL
);

COMMENT ON COLUMN suspension.suspension_start_date IS
    'Suspension start date';

COMMENT ON COLUMN suspension.suspension_end_date IS
    'Suspension End Date ';

COMMENT ON COLUMN suspension.driver_l_num IS
    'license number';

ALTER TABLE suspension ADD CONSTRAINT suspension_pk PRIMARY KEY ( driver_l_num,
suspension_start_date );

CREATE TABLE vehicle (
    vin        NUMBER NOT NULL,
    v_type     CHAR(50) NOT NULL,
    v_year     CHAR(10) NOT NULL,
    v_colour   CHAR(10) NOT NULL
);

COMMENT ON COLUMN vehicle.v_type IS
    'vehicle type';

COMMENT ON COLUMN vehicle.v_year IS
    'vehicle - year manufactured';

COMMENT ON COLUMN vehicle.v_colour IS
    'vehicle colour';

ALTER TABLE vehicle ADD CONSTRAINT vehicle_pk PRIMARY KEY ( vin );

ALTER TABLE assn_hist
    ADD CONSTRAINT assn_hist_police_off_fk FOREIGN KEY ( police_off_officer_id )
        REFERENCES police_off ( officer_id );

ALTER TABLE assn_hist
    ADD CONSTRAINT assn_hist_police_station_fk FOREIGN KEY ( police_station_station_number )
        REFERENCES police_station ( station_number );

ALTER TABLE cancellation
    ADD CONSTRAINT cancellation_driver_fk FOREIGN KEY ( driver_l_num )
        REFERENCES driver ( l_num );

ALTER TABLE engine
    ADD CONSTRAINT engine_model_fk FOREIGN KEY ( model_v_model )
        REFERENCES model ( v_model );

ALTER TABLE license
    ADD CONSTRAINT license_driver_fk FOREIGN KEY ( driver_l_num )
        REFERENCES driver ( l_num );

ALTER TABLE license
    ADD CONSTRAINT license_license_type_fk FOREIGN KEY ( license_type_l_code )
        REFERENCES license_type ( l_code );

ALTER TABLE manufacturer
    ADD CONSTRAINT manufacturer_country_fk FOREIGN KEY ( country_m_country_code )
        REFERENCES country ( m_country_code );

ALTER TABLE model
    ADD CONSTRAINT model_manufacturer_fk FOREIGN KEY ( manufacturer_m_code )
        REFERENCES manufacturer ( m_code );

ALTER TABLE model
    ADD CONSTRAINT model_vehicle_fk FOREIGN KEY ( vehicle_vin )
        REFERENCES vehicle ( vin );

ALTER TABLE offence
    ADD CONSTRAINT offence_demerit_fk FOREIGN KEY ( demerit_demerit_code )
        REFERENCES demerit ( demerit_code );

ALTER TABLE offence
    ADD CONSTRAINT offence_driver_fk FOREIGN KEY ( driver_l_num )
        REFERENCES driver ( l_num );

ALTER TABLE offence
    ADD CONSTRAINT offence_police_off_fk FOREIGN KEY ( police_off_officer_id )
        REFERENCES police_off ( officer_id );

ALTER TABLE offence
    ADD CONSTRAINT offence_vehicle_fk FOREIGN KEY ( vehicle_vin )
        REFERENCES vehicle ( vin );

ALTER TABLE police_station
    ADD CONSTRAINT police_station_police_off_fk FOREIGN KEY ( police_off_officer_id )
        REFERENCES police_off ( officer_id );

ALTER TABLE registration
    ADD CONSTRAINT registration_vehicle_fk FOREIGN KEY ( vehicle_vin )
        REFERENCES vehicle ( vin );

ALTER TABLE suspension
    ADD CONSTRAINT suspension_driver_fk FOREIGN KEY ( driver_l_num )
        REFERENCES driver ( l_num );



-- Oracle SQL Developer Data Modeler Summary Report: 
-- 
-- CREATE TABLE                            16
-- CREATE INDEX                             1
-- ALTER TABLE                             33
-- CREATE VIEW                              0
-- ALTER VIEW                               0
-- CREATE PACKAGE                           0
-- CREATE PACKAGE BODY                      0
-- CREATE PROCEDURE                         0
-- CREATE FUNCTION                          0
-- CREATE TRIGGER                           0
-- ALTER TRIGGER                            0
-- CREATE COLLECTION TYPE                   0
-- CREATE STRUCTURED TYPE                   0
-- CREATE STRUCTURED TYPE BODY              0
-- CREATE CLUSTER                           0
-- CREATE CONTEXT                           0
-- CREATE DATABASE                          0
-- CREATE DIMENSION                         0
-- CREATE DIRECTORY                         0
-- CREATE DISK GROUP                        0
-- CREATE ROLE                              0
-- CREATE ROLLBACK SEGMENT                  0
-- CREATE SEQUENCE                          0
-- CREATE MATERIALIZED VIEW                 0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
-- 
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
-- 
-- REDACTION POLICY                         0
-- TSDP POLICY                              0
-- 
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
-- 
-- ERRORS                                   0
-- WARNINGS                                 0

SET ECHO OFF