Answered step by step
Verified Expert Solution
Question
1 Approved Answer
in the photo a database lab Questions using orcal live sql and down here the company schema: DDL script: create table DEPARTMENT ( DNAME VARCHAR
in the photo a database lab Questions using orcal live sql and down here the company schema: DDL script: create table DEPARTMENT DNAME VARCHARDNUMBER NUMBER PRIMARY KEY,MGRSSN CHARMGRSTARTDATE DATE;create table EMPLOYEE FNAME VARCHARMINIT CHARLNAME VARCHARSSN CHAR PRIMARY KEY,BDATE DATE,ADDRESS VARCHARSEX CHARSALARY NUMBERSUPERSSN CHARDNO NUMBER; ALTER TABLE EMPLOYEE ADD CONSTRAINT SEXCHECK CHECK SEX IN MF; ALTER TABLE EMPLOYEE ADD CONSTRAINT SALARYCHECK CHECK SALARY AND SALARY ;
ALTER TABLE DEPARTMENT ADD CONSTRAINT deptmgrssnfk FOREIGN KEY MGRSSN REFERENCES EMPLOYEESSN;
ALTER TABLE EMPLOYEE ADD CONSTRAINT empsuperssnfk FOREIGN KEY SUPERSSN REFERENCES EMPLOYEESSN;
ALTER TABLE EMPLOYEE ADD CONSTRAINT empdnofk FOREIGN KEY DNO REFERENCES DEPARTMENTDNUMBER;
create table DEPTLOCATIONS
DNUMBER NUMBER
DLOCATION VARCHAR
PRIMARY KEYDNUMBERDLOCATION;
ALTER TABLE DEPTLOCATIONS ADD CONSTRAINT deptlocdnumfk FOREIGN KEY DNUMBER REFERENCES DEPARTMENTDNUMBER;
create table PROJECTPNAME VARCHARPNUMBER NUMBER PRIMARY KEY,PLOCATION VARCHARDNUM NUMBER;
ALTER TABLE PROJECT ADD CONSTRAINT projdnumfk FOREIGN KEY DNUM REFERENCES DEPARTMENTDNUMBER;create table WORKSONESSN CHARPNO NUMBER, HOURS NUMBERPRIMARY KEYESSNPNO;ALTER TABLE WORKSON ADD CONSTRAINT worksonessnfk FOREIGN KEY ESSN REFERENCES EMPLOYEESSN;
ALTER TABLE WORKSON ADD CONSTRAINT worksonpnofk FOREIGN KEY PNO REFERENCES PROJECTPNUMBER;create table DEPENDENTESSN CHARDEPENDENTNAME VARCHARSEX CHARBDATE DATE,RELATIONSHIP VARCHARPRIMARY KEYESSNDEPENDENTNAME;ALTER TABLE DEPENDENT ADD CONSTRAINT depessnfk FOREIGN KEY ESSN REFERENCES EMPLOYEESSN; DML script: INSERT INTO DEPARTMENT VALUESResearchnull,todateyyyymmdd;
INSERT INTO DEPARTMENT VALUESAdministrationnull,todateyyyymmdd; INSERT INTO DEPARTMENT VALUESHeadquartersnull,todateyyyymmdd;INSERT INTO EMPLOYEE VALUESJamesE'Borg',todateyyyymmdd Stone,Houston,TXMnull,;INSERT INTO EMPLOYEE VALUESFranklinT'Wong',todateyyyymmdd Voss,Houston,TXM;INSERT INTO EMPLOYEE VALUESJohnB'Smith',todateyyyymmdd Fondren,Houston,TXM;
INSERT INTO EMPLOYEE VALUESJenniferS'Wallace',todateyyyymmdd Berry,Bellaire,TXF;INSERT INTO EMPLOYEE VALUESAliciaJ'Zelaya',todateyyyymmdd Castle,Spring,TXF; INSERT INTO EMPLOYEE VALUESRameshK'Narayan',todateyyyymmdd Fire Oak,Humble,TXM;INSERT INTO EMPLOYEE VALUESJoyceA'English',todateyyyymmdd Rice,Houston,TXF;INSERT INTO EMPLOYEE VALUESAhmadV'Jabbar',todateyyyymmdd Dallas,Houston,TXM;
UPDATE DEPARTMENT SET MGRSSN WHERE DNUMBER;
UPDATE DEPARTMENT SET MGRSSN WHERE DNUMBER;
UPDATE DEPARTMENT SET MGRSSN WHERE DNUMBER;
INSERT INTO DEPTLOCATIONS VALUES'Houston';
INSERT INTO DEPTLOCATIONS VALUES'Stafford';
INSERT INTO DEPTLOCATIONS VALUES'Bellaire';
INSERT INTO DEPTLOCATIONS VALUES'Sugarland';
INSERT INTO DEPTLOCATIONS VALUES'Houston';
INSERT INTO PROJECT VALUESProductX'Bellaire',;
INSERT INTO PROJECT VALUESProductY'Sugarland',;
INSERT INTO PROJECT VALUESProductZ'Houston',;
INSERT INTO PROJECT VALUESComputerization'Stafford',;
INSERT INTO PROJECT VALUESReorganization'Houston',;
INSERT INTO PROJECT VALUESNewbenefits'Stafford',;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;
INSERT INTO WORKSON VALUES;INSERT INTO WORKSON VALUES;INSERT INTO WORKSON VALUES;INSERT INTO WORKSON VALUES;INSERT INTO WORKSON VALUES;INSERT INTO WORKSON VALUES
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started