Burger_Shack_DDL.sql

/*
CMPU 2007 - Datbase 1 - Continous Assessment Part 2
Burger Shack Database
Authors:
    Adrian Thomas Capacite  C21348423
    Ernest John Decina      C21394933
*/
-- Generated by Oracle SQL Developer Data Modeler 20.2.0.167.1538
--   at:        2022-11-28 16:02:52 GMT
--   site:      Oracle Database 11g
--   type:      Oracle Database 11g

-- predefined type, no DDL - MDSYS.SDO_GEOMETRY

-- predefined type, no DDL - XMLTYPE

-- DROP TABLES --
DROP TABLE servedby;
DROP TABLE guest;
DROP TABLE booking;
DROP TABLE seating;
DROP TABLE tablelocation;
DROP TABLE tabletype;
DROP TABLE managementstaff;
DROP TABLE waiter;
DROP TABLE restaurant;
DROP TABLE staff;
DROP TABLE customer;
DROP TABLE person;

-- CREATE TABLES --
CREATE TABLE person (
    -- Attributes --
    personid           NUMBER(6),
    personname         VARCHAR2(50 CHAR) CONSTRAINT nnull_person_name NOT NULL,
    personaddress      VARCHAR2(200 CHAR) CONSTRAINT nnull_person_address NOT NULL,
    personphonenumber  VARCHAR2(10 CHAR) CONSTRAINT nnull_person_phone_number NOT NULL,
    personemail        VARCHAR2(50 CHAR) CONSTRAINT nnull_person_email NOT NULL,

    -- Primary Key --
    CONSTRAINT person_pk PRIMARY KEY ( personid )
);

CREATE TABLE customer (
    -- Attributes --
    customerid       NUMBER(6),
    customerage      NUMBER(3),
    person_personid  NUMBER(6),

    -- Primary Key --
    CONSTRAINT customer_pk PRIMARY KEY ( customerid ),

    -- Foriegn Key --
    CONSTRAINT customer_person_fk FOREIGN KEY ( person_personid )
        REFERENCES person ( personid ),

    -- Check --
    -- Ensure customer is over the age of 18
    CONSTRAINT customer_ck_over18 CHECK ( customerage >= 18 )
);

CREATE TABLE staff (
    -- Attributes --
    staffid           NUMBER(6),
    staffemail        VARCHAR2(50 CHAR),
    staffdateofbirth  DATE,
    staffstartdate    DATE,
    person_personid   NUMBER(6),

    -- Primary Key --
    CONSTRAINT staff_pk PRIMARY KEY ( staffid ),

    -- Foriegn Keys --
    CONSTRAINT staff_person_fk FOREIGN KEY ( person_personid )
        REFERENCES person ( personid ),

    -- Check --
    -- Ensure company emails must end with '@burger-shack.com'
    CONSTRAINT staff_ck_companyemail CHECK ( staffemail LIKE '%@burgershack.com' )
);

CREATE TABLE restaurant (
    -- Attributes --
    restaurantid           NUMBER(6),
    restaurantaddress      VARCHAR2(200 CHAR),
    restaurantphonenumber  VARCHAR2(10 CHAR),

    -- Primary Key --
    CONSTRAINT restaurant_pk PRIMARY KEY ( restaurantid )
);


CREATE TABLE waiter (
    waiterid                 NUMBER(6),
    restaurant_restaurantid  NUMBER(6),
    staff_staffid            NUMBER(6),

    -- Primary Key --
    CONSTRAINT waiter_pk PRIMARY KEY ( waiterid ),

    -- Foriegn Keys --
    CONSTRAINT waiter_restaurant_fk FOREIGN KEY ( restaurant_restaurantid )
        REFERENCES restaurant ( restaurantid ),
    CONSTRAINT waiter_staff_fk FOREIGN KEY ( staff_staffid )
        REFERENCES staff ( staffid )
);

CREATE TABLE managementstaff (
    -- Attributes
    managementid             NUMBER(6),
    staff_staffid            NUMBER(6),
    restaurant_restaurantid  NUMBER(6),

    -- Primary Key --
    CONSTRAINT managementstaff_pk PRIMARY KEY ( managementid ),

    -- Foriegn Keys --
    CONSTRAINT management_restaurant_fk FOREIGN KEY ( restaurant_restaurantid )
        REFERENCES restaurant ( restaurantid ),
    CONSTRAINT management_staff_fk FOREIGN KEY ( staff_staffid )
        REFERENCES staff ( staffid )
);

-- Restaurant Table DB Table
CREATE TABLE tablelocation (
    -- Attributes
    tablelocationid    NUMBER(6),
    tablelocationname  VARCHAR2(50 CHAR),

    -- Primary Key --
    CONSTRAINT tablelocation_pk PRIMARY KEY ( tablelocationid )
);

CREATE TABLE tabletype (
    -- Attributes
    tabletypeid  NUMBER(6),
    seats        NUMBER(2),

    -- Primary Key --
    CONSTRAINT tabletype_pk PRIMARY KEY ( tabletypeid )
);

CREATE TABLE seating (
    -- Attributes --
    seatingid                      NUMBER(6),
    tablelocation_tablelocationid  NUMBER(6),
    tabletype_tabletypeid          NUMBER(6),
    restaurant_restaurantid        NUMBER(6),

    -- Primary Key --
    CONSTRAINT seating_pk PRIMARY KEY ( seatingid ),

    -- Foriegn Keys --
    CONSTRAINT seating_tablelocation_fk FOREIGN KEY ( tablelocation_tablelocationid )
        REFERENCES tablelocation ( tablelocationid ),
    CONSTRAINT seating_tabletype_fk FOREIGN KEY ( tabletype_tabletypeid )
        REFERENCES tabletype ( tabletypeid ),
    CONSTRAINT seating_restaurantid_fk FOREIGN KEY ( restaurant_restaurantid )
        REFERENCES restaurant ( restaurantid )
);

CREATE TABLE booking (
    -- Attributes --
    bookingid                NUMBER(6),
    bookingtime              DATE,
    numberofpeople           NUMBER(2),
    customer_customerid      NUMBER(6),
    seating_seatingid        NUMBER(6),

    -- Primary Key --
    CONSTRAINT booking_pk PRIMARY KEY (bookingid),

    -- Unique Constaint --
    -- Make sure there are no booking on the same table seating at the same time
    CONSTRAINT booking_bookingtime_steatingid_unq UNIQUE (bookingtime, seating_seatingid),

    -- Foriegn Keys --
    CONSTRAINT booking_customerid_fk FOREIGN KEY ( customer_customerid )
        REFERENCES customer ( customerid ),
    CONSTRAINT booking_seatingid_fk FOREIGN KEY ( seating_seatingid )
        REFERENCES seating ( seatingid ),

    -- Check --
    -- Make sure there are less than 8 people in a booking
    CONSTRAINT booking_ck_8orbelowpeople CHECK ( numberofpeople <= 8 )
);

CREATE TABLE guest (
    -- Attributes --
    guestid            NUMBER(6),
    booking_bookingid  NUMBER(6),
    person_personid    NUMBER(6),

    -- Primary Key --
    CONSTRAINT guest_pk PRIMARY KEY ( guestid ),

    -- Foriegn Keys --
    CONSTRAINT guest_booking_fk FOREIGN KEY ( booking_bookingid )
        REFERENCES booking ( bookingid ),
    CONSTRAINT guest_person_fk FOREIGN KEY ( person_personid )
        REFERENCES person ( personid )
);

CREATE TABLE servedby (
    -- Attributes --
    booking_bookingid  NUMBER(6),
    staff_staffid      NUMBER(6),

    -- Primary Key --
    CONSTRAINT servedby_pk PRIMARY KEY ( booking_bookingid, staff_staffid ),

    -- Foriegn Key --
    CONSTRAINT servedby_booking_fk FOREIGN KEY ( booking_bookingid )
        REFERENCES booking ( bookingid ),
    CONSTRAINT servedby_staff_fk FOREIGN KEY ( staff_staffid )
        REFERENCES staff ( staffid )
);





-- Oracle SQL Developer Data Modeler Summary Report:
--
-- CREATE TABLE                            12
-- CREATE INDEX                             0
-- ALTER TABLE                             31
-- 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 MATERIALIZED VIEW LOG             0
-- CREATE SYNONYM                           0
-- CREATE TABLESPACE                        0
-- CREATE USER                              0
--
-- DROP TABLESPACE                          0
-- DROP DATABASE                            0
--
-- REDACTION POLICY                         0
--
-- ORDS DROP SCHEMA                         0
-- ORDS ENABLE SCHEMA                       0
-- ORDS ENABLE OBJECT                       0
--
-- ERRORS                                   0
-- WARNINGS                                 0