Database work

create table EMPLOYEE (Ssno char (9) constraint pk_emp primary key, Last_name varchar2 (20) constraint nn_LastName not null, Mi char (1), First_name varchar2 (20) constraint nn_FirstName not null, Gender char (1) constraint nn_gen not null, constraint chk_gender check (Gender in ('M','F')), Bdate date, Address varchar2 (40), Salary number (7) constraint ck_sal check (Salary between 30000 and 70000), Supssno char (9) DEFAULT '123456789', Dno number (2), constraint ck_empnm unique (Last_name, Mi, First_name)); create table DEPARTMENT (Dept_name varchar (15) constraint nn_depname not null, constraint ck_depname check (Dept_name in ('ADMINISTRATION','HEADQUARTERS','PRODUCTION')), constraint ck_depname2 unique (Dept_name), Dept_no number (2) constraint pk_dep primary key, constraint ck_depno check (Dept_no between 1 and 10), Mgr_Start_date date, Mgrssno char (9)); create table LOCATION ( Location varchar2 (12) constraint ck_deploc check (Location in ('WACO', 'ORLANDO', 'CLEVELAND', 'CINCINNATI' )) CONSTRAINT pk_loc PRIMARY KEY, Loc_size number(3), State char(2) ); create table Department_Location ( Dnum number(2), Dep_Location varchar2(12), CONSTRAINT fk_DL_dept FOREIGN KEY (Dnum) REFERENCES Department(Dept_no), CONSTRAINT fk_Loc_Location FOREIGN KEY (Dep_Location) REFERENCES Location(Location), CONSTRAINT pk_Dep_Loc PRIMARY KEY(Dnum,Dep_Location) ); create table PROJECT (Proj_name varchar2 (15), constraint un_Projname unique (Proj_name), Proj_no number (2) constraint pk_project primary key, constraint ck_proj_no check (Proj_no between 1 and 30), Dnumb number (2)); create table Dependent (Essno char (9), Dependent_nm varchar2 (15), Dep_gender char (1) constraint nn_depgender not null, constraint ck_depgender check (Dep_gender in ('M','F')), Dep_bdate date, Related_how varchar2 (8) constraint ck_relhow check (Related_how in ('DAUGHTER', 'SPOUSE', 'SON')), constraint pk_depd primary key (Essno, Dependent_nm), constraint ck_gendrel check((Dep_gender in ('M') and Related_how in ('SPOUSE', 'SON')) or (Dep_gender in ('F') and Related_how in ('SPOUSE', 'DAUGHTER')))); create table WORKS_ON (Essno char (9), Pno number (2) , Work_hrs number (3,1) constraint ck_workhrs check (Work_hrs <= 80), constraint pk_work primary key (Essno, Pno)); INSERT ALL into EMPLOYEE values('123456789', 'MCCARTNEY', 'B', 'PAUL', 'M', '25-Dec-78', '731, FONDREN, WACO, TX', 30000, '666884444', 5) into EMPLOYEE values('222556666', 'WONG', 'T', 'FRANK', 'M', '25-Nov-78', '638, VOSS, CLEVELAND, OH', 55000, '987987987', 5) into EMPLOYEE values('453453453', 'ENGLISH', 'A', 'JOYCE', 'F', '25-Oct-78', '5631, RICE, ORLANDO, FL', 50000, '222556666', 5) into EMPLOYEE values('666884444', 'GOMEZ', 'B', 'JANE', 'F', '25-Sep-78', '975, FIREOAK, HUMBLE, TX', 45000, '222556666', 5) into EMPLOYEE values('777211234', 'RUTHERFORD', 'E', 'JAMES', 'M', '25-Aug-78', '3321, CASTLE, SPRING, TX', 64000, '123456789', 1) into EMPLOYEE values('987654321', 'SMITH', 'S', 'JENNIFER', 'F', '25-Jul-78', '2912, BERRY, CINCINNATI, OH', 60000, '777211234', 4) into EMPLOYEE values('987987987', 'JABBAR', 'V', 'AHMAD', 'M', '25-Jun-78', '980, CALHOUN, CINCINNATI, OH', 35000, '123456789', 4) into EMPLOYEE values('123123123', 'KEYS', 'J', 'ALICIA', 'F', '25-May-78', '3321, CASTLE, CINCINNATI, OH', 64000, '123456789', 4) select * from dual; INSERT ALL into DEPARTMENT values('HEADQUARTERS', 1, '19-Jun-91', '777211234') into DEPARTMENT values('ADMINISTRATION', 4, '01-Jan-89', '987654321') into DEPARTMENT values('PRODUCTION', 5, '22-May-93', '222556666') select * from dual; INSERT ALL into LOCATION values('WACO', 200, 'TX') into LOCATION values('CINCINNATI', 200, 'OH') into LOCATION values('ORLANDO', 300, 'FL') into LOCATION values('CLEVELAND', 400, 'OH') select * from dual; INSERT ALL into Department_Location values(1, 'WACO') into Department_Location values(4, 'CINCINNATI') into Department_Location values(5, 'CLEVELAND') into Department_Location values(5, 'WACO') into Department_Location values(5, 'ORLANDO') select * from dual; INSERT ALL into PROJECT values('PRODUCT A', 1, 5) into PROJECT values('PRODUCT B', 2, 5) into PROJECT values('PRODUCT C', 3, 5) into PROJECT values('COMPUTERIZATION', 10, 4) into PROJECT values('REORGANIZATION', 20, 1) into PROJECT values('BENEFITS PLAN', 30, 4) select * from dual; INSERT ALL into Dependent values('123456789', 'ALICE', 'F', '07-Jun-92', 'DAUGHTER') into Dependent values('123456789', 'ELIZABETH', 'F', '05-May-67', 'SPOUSE') into Dependent values('123456789', 'MICHAEL', 'M', '10-Jan-89', 'SON') into Dependent values('222556666', 'SARAH', 'F', '05-Apr-86', 'DAUGHTER') into Dependent values('222556666', 'JOY', 'M', '03-May-58', 'SPOUSE') into Dependent values('123123123', 'THEODORE', 'M', '12-Nov-96', 'SON') into Dependent values('987654321', 'BILL', 'M', '29-Feb-60', 'SPOUSE') select * from dual; INSERT ALL into WORKS_ON values('123456789', 1, 32.5) into WORKS_ON values('123456789', 2, 7.5) into WORKS_ON values('222556666', 2, 10) into WORKS_ON values('222556666', 3, 10) into WORKS_ON values('222556666', 10, 10) into WORKS_ON values('222556666', 30, 10) into WORKS_ON values('453453453', 1, 20) into WORKS_ON values('453453453', 2, 20) into WORKS_ON values('666884444', 3, 40) into WORKS_ON values('777211234', 20, 0) into WORKS_ON values('987654321', 20, 15) into WORKS_ON values('987654321', 30, 20) into WORKS_ON values('987987987', 10, 35) into WORKS_ON values('987987987', 30, 5) into WORKS_ON values('123123123', 10, 10) into WORKS_ON values('123123123', 30, 25.5) select * from dual; commit; alter table EMPLOYEE add constraint fk_emp foreign key (Supssno) references EMPLOYEE (Ssno); alter table EMPLOYEE add constraint fk_dept foreign key (Dno) references DEPARTMENT (Dept_no); alter table EMPLOYEE add constraint ck_ssno check (Ssno <> Supssno); alter table DEPARTMENT add constraint fk_emp2 foreign key (Mgrssno) references EMPLOYEE (Ssno); alter table PROJECT add constraint fk_dept3 foreign key (Dnumb) references DEPARTMENT (Dept_no) ON DELETE CASCADE; alter table DEPENDENT add constraint fk_emp3 foreign key (Essno) references EMPLOYEE (Ssno) ON DELETE CASCADE; alter table WORKS_ON add constraint fk_emp4 foreign key (Essno) references EMPLOYEE (Ssno); Alter table WORKS_ON add constraint fk_proj foreign key (Pno) references PROJECT (Proj_no);