DBMS EXERCISE - 3 TO 5
DEPARTMENT TABLE: CREATE TABLE DEPARTMENT (DNO VARCHAR(20) PRIMARY KEY, DNAME VARCHAR(20),MGRSTARTDATE DATE); EMPLOYEE TABLE: CREATE TABLE EMPLOYEE (FNAME VARCHAR(20), MNAME VARCHAR(20), LNAME VARCHAR(20), SSN VARCHAR (20) PRIMARY KEY, DOB DATE, ADDRESS VARCHAR (20), GENDER VARCHAR(10), SALARY INTEGER, SUPERSSN VARCHAR(20) REFERENCES EMPLOYEE (SSN), DNO VARCHAR(20) REFERENCES DEPARTMENT (DNO)); NOTE: Once DEPARTMENT and EMPLOYEE tables are created we must alter department table to add foreign constraint MGRSSN using sql command ALTER TABLE DEPARTMENT ADD MGRSSN VARCHAR(20) REFERENCES EMPLOYEE (SSN); DEPENDENT TABLE: CREATE TABLE DEPENDENT (ESSN VARCHAR(20) REFERENCES EMPLOYEE(SSN), DEPENDENTNAME VARCHAR(20), GENDER VARCHAR(20), DOB DATE, RELATIONSHIP VARCHAR(20)); DLOCATION TABLE: CREATE TABLE DLOCATION(DLOC VARCHAR(20),DNO VARCHAR(20) REFERENCES DEPARTMENT(DNO), PRIMARY KEY(DNO, DLOC)); PROJECT TABLE: CREATE TABLE PROJECT (PNAME VARCHAR(20),PNO INTEGER PRIMARY KEY, PLOCATION VARCH