database.sql

-- This file defines the structure of the database

-- Table creation

-- Parent Table
-- Role Table
CREATE TABLE ROLE(
    ROLE_ID CHAR(3) PRIMARY KEY,
    ROLE_NAME VARCHAR(20)
);

-- Child Tables
-- Employees Table
CREATE TABLE EMPLOYEES(
    EMP_ID CHAR(9) PRIMARY KEY,
    FIRST_NAME VARCHAR(40),
    SURNAME VARCHAR(20),
    EMP_DOB DATE,
    ROLE_ID CHAR(3),
    DOJ DATE,
    CONTACT_INFO BIGINT,
    EMAIL_ID VARCHAR(30),
    FOOD_PREFERENCE VARCHAR(40),
    CONSTRAINT FK_EMPLOYEE_ROLE FOREIGN KEY (ROLE_ID) REFERENCES ROLE(ROLE_ID)
);


-- Events Table
CREATE TABLE EVENTS(
    EVENT_ID CHAR(5) PRIMARY KEY,
    EVENT_NAME VARCHAR(100) NOT NULL,
    EVENT_DETAILS VARCHAR(10000),
    DATE_CONDUCTED DATE,
    HEAD_1 CHAR(9),
    HEAD_2 CHAR(9),
    CONSTRAINT FK_EVENTS_EMPLOYEES_HEAD1 FOREIGN KEY (HEAD_1) REFERENCES EMPLOYEES(EMP_ID),
    CONSTRAINT FK_EVENTS_EMPLOYEES_HEAD2 FOREIGN KEY (HEAD_2) REFERENCES EMPLOYEES(EMP_ID),
);

-- Attendence Table
CREATE TABLE ATTENDENCE(
    DATE DATE,
    EMPI_ID CHAR(9),
    PRESENCE CHAR(1) NOT NULL,
    REASON VARCHAR(1000),
    EVENT_ID CHAR(5),
    CONSTRAINT FK_ATTENDENCE_EMPLOYEE FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEES(EMP_ID),
    CONSTRAINT FK_ATTENDENCE_EVENT FOREIGN KEY (EVENT_ID) REFERENCES EVENTS(EVENT_ID)
);