Andmebaaside Projekteerimine: Projekt 1

--------------------------------------------------------------
-- Database creation Script

-- Auto-Generated by QSEE-SuperLite (c) 2001-2004 QSEE-Technologies Ltd.

-- Verbose generation: ON

-- note: spaces within table/column names have been replaced by underscores (_)

-- Target DB: SQL2

-- Entity Model :Entity Relationship Diagram

-- To drop the tables generated by this script run -
--   'C:\Users\Work\Desktop\GYM_PROJEKT_SQL_drop.sql'

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



--------------------------------------------------------------
-- Table Creation --

-- Each entity on the model is represented by a table that needs to be created within the Database.
-- Within SQL new tables are created using the CREATE TABLE command.
-- When a table is created its name and its attributes are defined.
-- The values of which are derived from those specified on the model.
-- Certain constraints are sometimes also specified, such as identification of primary keys.

-- Create a Database table to represent the "CLIENT" entity.
CREATE TABLE CLIENT(
    Client_ID   INTEGER NOT NULL,
    First_Name  VARCHAR(20) NOT NULL,
    Last_Name   VARCHAR(20) NOT NULL,
    Address VARCHAR(30) NOT NULL,
    Phone_Number    INTEGER NOT NULL,
    ID_Code INTEGER NOT NULL UNIQUE,
    Email   VARCHAR(30),
    -- Specify the PRIMARY KEY constraint for table "CLIENT".
    -- This indicates which attribute(s) uniquely identify each row of data.
    CONSTRAINT  pk_CLIENT PRIMARY KEY (Client_ID)
);

-- Create a Database table to represent the "PAYMENT" entity.
CREATE TABLE PAYMENT(
    Payment_ID  INTEGER NOT NULL,
    Client_ID   INTEGER NOT NULL,
    Package_ID  INTEGER NOT NULL,
    Payment_Date    DATE NOT NULL,
    Credit_Card_Nr  INTEGER NOT NULL,
    fk1_Client_ID   INTEGER NOT NULL,
    FOREIGN KEY (Client_ID) REFERENCES CLIENT(Client_ID),
    FOREIGN KEY (Package_ID) REFERENCES PACKAGE(Package_ID),
    -- Specify the PRIMARY KEY constraint for table "PAYMENT".
    -- This indicates which attribute(s) uniquely identify each row of data.
    CONSTRAINT  pk_PAYMENT PRIMARY KEY (Payment_ID)
);

-- Create a Database table to represent the "PACKAGE" entity.
CREATE TABLE PACKAGE(
    Package_ID  INTEGER NOT NULL,
    Price   FLOAT(10),
    Package_Name    VARCHAR(30) NOT NULL,
    Package_Start_Date  TIMESTAMP(20) NOT NULL,
    Package_End_Date    TIMESTAMP(20) NOT NULL,
    fk1_Payment_ID  INTEGER NOT NULL,
    fk1_Client_ID   INTEGER NOT NULL,
    fk1_Package_ID  INTEGER NOT NULL,
    -- Specify FK as unique to maintain 1:1 relationship
    UNIQUE(fk1_Payment_ID,fk1_Client_ID,fk1_Package_ID),
    -- Specify the PRIMARY KEY constraint for table "PACKAGE".
    -- This indicates which attribute(s) uniquely identify each row of data.
    CONSTRAINT  pk_PACKAGE PRIMARY KEY (Package_ID)
);

-- Create a Database table to represent the "ACCESS_LOG" entity.
CREATE TABLE ACCESS_LOG(
    Client_ID   INTEGER NOT NULL,
    Gym_ID  INTEGER NOT NULL,
    Package_ID  INTEGER NOT NULL,
    Time_Accessed_Gym   TIMESTAMP(20) NOT NULL,
    fk1_Package_ID  INTEGER NOT NULL,
    -- Specify FK as unique to maintain 1:1 relationship
    UNIQUE(fk1_Package_ID),
    fk2_Client_ID   INTEGER NOT NULL,
    -- Specify FK as unique to maintain 1:1 relationship
    UNIQUE(fk2_Client_ID),
    fk3_Gym_ID  INTEGER NOT NULL,
    -- Specify FK as unique to maintain 1:1 relationship
    UNIQUE(fk3_Gym_ID),
    FOREIGN KEY (Gym_ID) REFERENCES GYM(Gym_ID),
    FOREIGN KEY (Package_ID) REFERENCES PACKAGE(Package_ID),
    FOREIGN KEY (Client_ID) REFERENCES CLIENT(Client_ID)
    -- Specify the PRIMARY KEY constraint for table "ACCESS_LOG".
    -- This indicates which attribute(s) uniquely identify each row of data.
);

-- Create a Database table to represent the "GYM" entity.
CREATE TABLE GYM(
    Gym_ID  INTEGER NOT NULL,
    Gym_Name    VARCHAR(30) NOT NULL,
    Address VARCHAR(40) NOT NULL UNIQUE,
    Rooms   INTEGER NOT NULL,
    fk1_Gym_ID  INTEGER NOT NULL,
    -- Specify FK as unique to maintain 1:1 relationship
    UNIQUE(fk1_Gym_ID),
    -- Specify the PRIMARY KEY constraint for table "GYM".
    -- This indicates which attribute(s) uniquely identify each row of data.
    CONSTRAINT  pk_GYM PRIMARY KEY (Gym_ID)
);

-- Create a Database table to represent the "EMPLOYEE" entity.
CREATE TABLE EMPLOYEE(
    Employee_ID INTEGER NOT NULL,
    Occupation_ID   INTEGER NOT NULL,
    Gym_ID  INTEGER NOT NULL,
    Employee_Name   VARCHAR(40),
    Employee_Start  DATE NOT NULL,
    Employee_End    DATE NOT NULL,
    fk1_Gym_ID  INTEGER NOT NULL,
    fk2_Occupation_ID   INTEGER NOT NULL,
    fk2_Employee_ID INTEGER NOT NULL,
    -- Specify FK as unique to maintain 1:1 relationship
    UNIQUE(fk2_Occupation_ID,fk2_Employee_ID),
    -- Specify the PRIMARY KEY constraint for table "EMPLOYEE".
    -- This indicates which attribute(s) uniquely identify each row of data.
    FOREIGN KEY (Occupation_ID) REFERENCES OCCUPATION(Occupation_ID),
    FOREIGN KEY (Gym_ID) REFERENCES GYM(Gym_ID),
    CONSTRAINT  pk_EMPLOYEE PRIMARY KEY (Employee_ID)
);

-- Create a Database table to represent the "OCCUPATION" entity.
CREATE TABLE OCCUPATION(
    Occupation_ID   INTEGER NOT NULL,
    Employee_ID INTEGER NOT NULL,
    Occupation_Name VARCHAR(30) NOT NULL,
    Month_Salary    FLOAT(10),
    -- Specify the PRIMARY KEY constraint for table "OCCUPATION".
    -- This indicates which attribute(s) uniquely identify each row of data.
    FOREIGN KEY (Employee_ID) REFERENCES EMPLOYEE(Employee_ID),
    CONSTRAINT  pk_OCCUPATION PRIMARY KEY (Occupation_ID)
);

-- Create a Database table to represent the "EQUIPMENT" entity.
CREATE TABLE EQUIPMENT(
    Equipment_ID    INTEGER NOT NULL,
    Gym_ID  INTEGER NOT NULL,
    Equipment_Name  VARCHAR(30) NOT NULL,
    FOREIGN KEY (Gym_ID) REFERENCES GYM(Gym_ID),
    -- Specify the PRIMARY KEY constraint for table "EQUIPMENT".
    -- This indicates which attribute(s) uniquely identify each row of data.
    CONSTRAINT  pk_EQUIPMENT PRIMARY KEY (Equipment_ID)
);

-- Create a Database table to represent the "GYM_CONTACT" entity.
CREATE TABLE GYM_CONTACT(
    Gym_ID  INTEGER NOT NULL,
    EMAIL   VARCHAR(30) NOT NULL,
    Contact_Number  INTEGER NOT NULL,
    -- Specify the PRIMARY KEY constraint for table "GYM_CONTACT".
    -- This indicates which attribute(s) uniquely identify each row of data.
    CONSTRAINT  pk_GYM_CONTACT PRIMARY KEY (Gym_ID)
);


--------------------------------------------------------------
-- Alter Tables to add fk constraints --

-- Now all the tables have been created the ALTER TABLE command is used to define some additional
-- constraints.  These typically constrain values of foreign keys to be associated in some way
-- with the primary keys of related tables.  Foreign key constraints can actually be specified
-- when each table is created, but doing so can lead to dependency problems within the script
-- i.e. tables may be referenced before they have been created.  This method is therefore safer.

-- Alter table to add new constraints required to implement the "client_payment" relationship

-- This constraint ensures that the foreign key of table "PAYMENT"
-- correctly references the primary key of table "CLIENT"

ALTER TABLE PAYMENT ADD CONSTRAINT fk1_PAYMENT_to_CLIENT FOREIGN KEY(fk1_Client_ID) REFERENCES CLIENT(Client_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

-- Alter table to add new constraints required to implement the "PACKAGE_PAYMENT" relationship

-- This constraint ensures that the foreign key of table "PACKAGE"
-- correctly references the primary key of table "PAYMENT"

ALTER TABLE PACKAGE ADD CONSTRAINT fk1_PACKAGE_to_PAYMENT FOREIGN KEY(fk1_Payment_ID,fk1_Client_ID,fk1_Package_ID) REFERENCES PAYMENT(Payment_ID,Client_ID,Package_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

-- Alter table to add new constraints required to implement the "ACCESS_LOG_PACKAGE" relationship

-- This constraint ensures that the foreign key of table "ACCESS_LOG"
-- correctly references the primary key of table "PACKAGE"

ALTER TABLE ACCESS_LOG ADD CONSTRAINT fk1_ACCESS_LOG_to_PACKAGE FOREIGN KEY(fk1_Package_ID) REFERENCES PACKAGE(Package_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

-- Alter table to add new constraints required to implement the "ACCESS_LOG_CLIENT" relationship

-- This constraint ensures that the foreign key of table "ACCESS_LOG"
-- correctly references the primary key of table "CLIENT"

ALTER TABLE ACCESS_LOG ADD CONSTRAINT fk2_ACCESS_LOG_to_CLIENT FOREIGN KEY(fk2_Client_ID) REFERENCES CLIENT(Client_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

-- Alter table to add new constraints required to implement the "ACCESS_LOG_GYM" relationship

-- This constraint ensures that the foreign key of table "ACCESS_LOG"
-- correctly references the primary key of table "GYM"

ALTER TABLE ACCESS_LOG ADD CONSTRAINT fk3_ACCESS_LOG_to_GYM FOREIGN KEY(fk3_Gym_ID) REFERENCES GYM(Gym_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

-- Alter table to add new constraints required to implement the "EMPLOYEE_GYM" relationship

-- This constraint ensures that the foreign key of table "EMPLOYEE"
-- correctly references the primary key of table "GYM"

ALTER TABLE EMPLOYEE ADD CONSTRAINT fk1_EMPLOYEE_to_GYM FOREIGN KEY(fk1_Gym_ID) REFERENCES GYM(Gym_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;

-- Alter table to add new constraints required to implement the "GYM_GYM_CONTACT" relationship

-- This constraint ensures that the foreign key of table "GYM"
-- correctly references the primary key of table "GYM_CONTACT"

ALTER TABLE GYM ADD CONSTRAINT fk1_GYM_to_GYM_CONTACT FOREIGN KEY(fk1_Gym_ID) REFERENCES GYM_CONTACT(Gym_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;



-- unable to generate alter table command to enforce foreign key constraints between EQUIPMENT and GYM due to missing primary keys

-- Alter table to add new constraints required to implement the "EMPLOYEE_OCCUPATION" relationship

-- This constraint ensures that the foreign key of table "EMPLOYEE"
-- correctly references the primary key of table "OCCUPATION"

ALTER TABLE EMPLOYEE ADD CONSTRAINT fk2_EMPLOYEE_to_OCCUPATION FOREIGN KEY(fk2_Occupation_ID,fk2_Employee_ID) REFERENCES OCCUPATION(Occupation_ID,Employee_ID) ON DELETE RESTRICT ON UPDATE RESTRICT;


--------------------------------------------------------------
-- End of DDL file auto-generation
--------------------------------------------------------------