/* Database Systems, Coronel/Morris */
/* Type of SQL : Oracle */

ALTER SESSION SET NLS_DATE_FORMAT = 'MM/DD/YYYY';

CREATE TABLE COMPONENT (
COMP_CODE varchar2(8),
PART_CODE varchar2(8),
COMP_PARTS_NEEDED integer
);
INSERT INTO COMPONENT VALUES('C-130','E129','1');
INSERT INTO COMPONENT VALUES('C-130','X10','4');
INSERT INTO COMPONENT VALUES('C-130','AA21-6','4');
INSERT INTO COMPONENT VALUES('C-130','AB-121','2');
INSERT INTO COMPONENT VALUES('C-130','X34AW','2');
INSERT INTO COMPONENT VALUES('C-131A2','E129','1');
INSERT INTO COMPONENT VALUES('C-131A2','X10','1');
INSERT INTO COMPONENT VALUES('C-131A2','X34AW','2');

/* -- */


CREATE TABLE EMPLOYEE (
EMP_NUM integer,
EMP_LNAME varchar2(15),
EMP_FNAME varchar2(15)
);
INSERT INTO EMPLOYEE VALUES('345','Ramirez','James');
INSERT INTO EMPLOYEE VALUES('346','Jones','Anne');
INSERT INTO EMPLOYEE VALUES('347','Ramirez','Louise');
INSERT INTO EMPLOYEE VALUES('348','Delaney','Robert');
INSERT INTO EMPLOYEE VALUES('349','Shapiro','Anton');

/* -- */

CREATE TABLE EMPLOYEE_V1 (
EMP_NUM integer,
EMP_LNAME varchar2(15),
EMP_FNAME varchar2(15),
EMP_SPOUSE integer
);
INSERT INTO EMPLOYEE_V1 VALUES('345','Ramirez','James','347');
INSERT INTO EMPLOYEE_V1 VALUES('346','Jones','Anne','349');
INSERT INTO EMPLOYEE_V1 VALUES('347','Ramirez','Louise','345');
INSERT INTO EMPLOYEE_V1 VALUES('348','Delaney','Robert','');
INSERT INTO EMPLOYEE_V1 VALUES('349','Shapiro','Anton','346');

/* -- */


CREATE TABLE EMPLOYEE_V2 (
EMP_CODE integer,
EMP_LNAME varchar2(50),
EMP_MANAGER integer
);
INSERT INTO EMPLOYEE_V2 VALUES('101','Waddell','102');
INSERT INTO EMPLOYEE_V2 VALUES('102','Orincona','');
INSERT INTO EMPLOYEE_V2 VALUES('103','Jones','102');
INSERT INTO EMPLOYEE_V2 VALUES('104','Reballoh','102');
INSERT INTO EMPLOYEE_V2 VALUES('105','Robertson','102');
INSERT INTO EMPLOYEE_V2 VALUES('106','Deltona','102');

/* -- */


CREATE TABLE MARPART (
MAR_NUM integer,
EMP_NUM integer
);
INSERT INTO MARPART VALUES('1','345');
INSERT INTO MARPART VALUES('1','347');
INSERT INTO MARPART VALUES('2','346');
INSERT INTO MARPART VALUES('2','349');

/* -- */

CREATE TABLE MARRIAGE (
MAR_NUM integer,
MAR_DATE date
);
INSERT INTO MARRIAGE VALUES('1','3/4/2003');
INSERT INTO MARRIAGE VALUES('2','2/2/1999');


/* -- */


CREATE TABLE MARRIED_V1 (
EMP_NUM integer,
EMP_SPOUSE integer
);
INSERT INTO MARRIED_V1 VALUES('345','347');
INSERT INTO MARRIED_V1 VALUES('346','349');
INSERT INTO MARRIED_V1 VALUES('347','345');
INSERT INTO MARRIED_V1 VALUES('349','346');

/* -- */


CREATE TABLE PART (
PART_CODE varchar2(6),
PART_DESCRIPTION varchar2(35),
PART_IN_STOCK integer
);
INSERT INTO PART VALUES('AA21-6','2.5 cm. washer, 1.0 mm. rim','432');
INSERT INTO PART VALUES('AB-121','Cotter pin, copper','1034');
INSERT INTO PART VALUES('C-130','Rotor assembly','36');
INSERT INTO PART VALUES('E129','2.5 cm. steel shank','128');
INSERT INTO PART VALUES('X10','10.25 cm. rotor blade','345');
INSERT INTO PART VALUES('X34AW','2.5 cm. hex nut','879');

/* -- */

CREATE TABLE PART_V1 (
PART_CODE varchar2(6),
PART_DESCRIPTION varchar2(35),
PART_IN_STOCK integer,
PART_UNITS_NEEDED integer,
PART_OF_PART varchar2(6)
);
INSERT INTO PART_V1 VALUES('AA21-6','2.5 cm. washer, 1.0 mm. rim','432','4','C-130');
INSERT INTO PART_V1 VALUES('AB-121','Cotter pin, copper','1034','2','C-130');
INSERT INTO PART_V1 VALUES('C-130','Rotor assembly','36','','');
INSERT INTO PART_V1 VALUES('E129','2.5 cm. steel shank','128','1','C-130');
INSERT INTO PART_V1 VALUES('X10','10.25 cm. rotor blade','345','4','C-130');
INSERT INTO PART_V1 VALUES('X34AW','2.5 cm. hex nut','879','2','C-130');

