Untitled
unknown
plain_text
a year ago
1.5 kB
4
Indexable
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 );
Editor is loading...
Leave a Comment