Untitled

CREATE TABLE EMPLOYEE (
    EMPLOYEE_ID 	SERIAL 			PRIMARY KEY,
    FIRST_NAME 		VARCHAR(50) 	NOT NULL,
	MIDDLE_NAME 	VARCHAR(50),
    LAST_NAME 		VARCHAR(50) 	NOT NULL,
    EMAIL 			VARCHAR(100) 	UNIQUE NOT NULL,
    PHONE 			VARCHAR(13),
	PROVINCE 		VARCHAR(25),
	CITY 			VARCHAR(25),
	BARANGAY 		VARCHAR(50),
	POSTAL_CODE 	INT,
    HIRE_DATE 		DATE 			NOT NULL,
	PIN 			VARCHAR(4)
);

CREATE TABLE SCHEDULE (
    SCHEDULE_ID 	SERIAL 			PRIMARY KEY,
    SHIFT_DATE 		DATE 			NOT NULL,
    START_TIME 		INT 			NOT NULL,
END_TIME 			INT 			NOT NULL,
    STATUS 			VARCHAR(20) 	CHECK (status IN ('REGULAR', 'RESERVE', 'DAY OFF')) DEFAULT 'REGULAR',
	EMPLOYEE_ID 	INT 			REFERENCES EMPLOYEE(EMPLOYEE_ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE PAYROLL (
    PAYROLL_ID 		SERIAL 			PRIMARY KEY,
    PAY_DATE 		DATE,
    REG_HRS			INT,
	OT_HRS			INT,
	UT_HRS			INT, 
    PAY_RATE 		DECIMAL(10, 2) 	NOT NULL,
    GROSS_PAY 		DECIMAL(10, 2) 	NOT NULL,
    NET_PAY 		DECIMAL(10, 2) 	NOT NULL,
    EMPLOYEE_ID 	INT 			REFERENCES EMPLOYEE(EMPLOYEE_ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE SHIFT (
    SHIFT_ID 		SERIAL 			PRIMARY KEY,
    SECONDS 		INT,
	STATUS 			INT 			NOT NULL,
	SCHEDULE_ID 	INT 			REFERENCES SCHEDULE(SCHEDULE_ID) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE ADMINISTRATOR (
    ADMIN_USERNAME 	VARCHAR(20) 	PRIMARY KEY,
    ADMIN_PASSWORD 	VARCHAR(20) 	NOT NULL
);