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 VARCHAR(20), DNO VARCHAR(20) REFERENCES DEPARTMENT (DNO));

WORKS_ON TABLE:
CREATE TABLE WORKS_ON(ESSN VARCHAR(20) REFERENCES EMPLOYEE(SSN),PNO INTEGER REFERENCES PROJECT(PNO), PRIMARY KEY (ESSN, PNO), HOURS INTEGER);

INSERT DATA INTO EMPLOYEE:
--------------------------------------------
INSERT INTO EMPLOYEE (FNAME , MNAME , LNAME , SSN , DOB , ADDRESS , GENDER ,  SALARY , SUPERSSN , DNO ) VALUES
('John','B','Smith','123456789','1965-02-09','731 Fondren,Houstan, TX','M',30000,'333445555',5),
('Franklin','T','Wong','333445555','1955-12-08','638 Voss,Houstan,TX','M',40000,'888665555',5),
('Alicia','J','Zelaya','999887777','1968-01-19','3321 Castle,Spring,TX','F',25000,'987654321',4),('Jennifer','S','Wallance','987654321','1941-06-20','291 Berry, Bellaire, TX','F',43000,'888665555',4),('Ramesh','K','Narayana','666884444','1962-09-15','975 Fire Oak, Humble, TX ','M',38000,'333445555',5),('Joyce','A','English','453453453','1972-07-31','5631 Rice,Houstan, TX','F',25000,'333445555',5),('Ahmad','V','Jabbar','987987987','1969-03-22','980 Dallas, Houstan, TX','M',25000,'987987987',4),('James','E','Brog','888665555','1937-10-10','450 Stone,Houstan, TX','M',55000,'NULL',1);

INSERT INTO DEPARTMENT:
----------------------------------------
INSERT INTO DEPARTMENT (DNO, DNAME,MGRSTARTDATE,MGRSSN) VALUES
('5','Research','1988-05-22','333445555'),('4','Administration','1995-01-01','987654321'),('1','Headquarters','1981-06-19','888665555');

INSERT INTO DEPENDENT:
--------------------------------------
INSERT INTO DEPENDENT (ESSN,DEPENDENTNAME,GENDER,DOB, RELATIONSHIP) VALUES('333445555','Alice','F','1986-04-05','Daughter'),('333445555','Theodore','M','1983-10-25','Son'),('333445555','Joy','F','1958-05-03','Spouse'),('987654321','Abner','M','1942-02-28','Spouse'),('123456789','Michael','M','1988-01-04','Son'),('123456789','Elizabeth','F','1967-05-05','Spouse');

INSERT INTO DLOCATION:
--------------------------------------
INSERT INTO DLOCATION(DLOC,DNO) VALUES
('Houstan','1'),
('Stafford','4'),
('Bellaire','5'),
('Sugarland','5'),
('Houstan','5');

INSERT INTO PROJECT:
----------------------------------
INSERT INTO PROJECT (PNAME,PNO,  PLOCATION, DNO) VALUES
('ProductX',1,'Bellaire','5'),
('ProductY',2,'Sugarland','5'),
('ProductZ',3,'Houstan','5'),
('Computerization',10,'Stafford','4'),
('Reorganization',20,'Houstan','1'),
('Newbenefits',30,'Stafford','4');

INSERT INTO WORKS_ON:

-------------------------------------

INSERT INTO WORKS_ON(ESSN,PNO,HOURS) VALUES('123456789',1,32),('123456789',2,47),

('666884444',3,40),

('453453453',1,20),

('333445555',2,20),

('333445555',1,10),

('333445555',3,10),

('333445555',10,10),

('999887777',20,10),

('999887777',30,30),

('987987987',10,10),

('987987987',11,35),

('987654321',30,5),

('987654321',31,20),

('888665555',20,15);