Untitled

tables.sql

-- Create Gender Table
CREATE TABLE Gender (
    Gender_ID NUMBER PRIMARY KEY,
    Name VARCHAR2(50) NOT NULL CHECK (Name IN ('Male', 'Female', 'Other'))
);

-- Create University Table
CREATE TABLE University (
    ID NUMBER PRIMARY KEY,
    Name VARCHAR2(100) NOT NULL
);

-- Create MyDept Table
CREATE TABLE MyDept (
    Dept_ID NUMBER PRIMARY KEY,
    Name VARCHAR2(100) NOT NULL
);

-- Create MyEmployee Table
CREATE TABLE MyEmployee (
    ID NUMBER PRIMARY KEY,
    LAST_NAME VARCHAR2(50) NOT NULL,
    FIRST_NAME VARCHAR2(50) NOT NULL,
    HIRE_DATE DATE NOT NULL,
    USERID NUMBER UNIQUE NOT NULL,
    SALARY NUMBER CHECK (SALARY >= 0),
    DEPT_ID NUMBER NOT NULL,
    Gender_ID NUMBER NOT NULL,
    University_ID NUMBER,
    EMP_IMAGE BLOB,
    -- Foreign Key Constraints
    CONSTRAINT fk_employee_dept
        FOREIGN KEY (DEPT_ID)
        REFERENCES MyDept(Dept_ID),
    CONSTRAINT fk_employee_gender
        FOREIGN KEY (Gender_ID)
        REFERENCES Gender(Gender_ID),
    CONSTRAINT fk_employee_university
        FOREIGN KEY (University_ID)
        REFERENCES University(ID)
);