Question 1:
Run script 7northwoods in schemas des03
Create a procedure to display all the faculty member (f_id, f_last, f_first, f_rank), under each faculty member, display all the student advised by that faculty member (s_id, s_last, s_first, birthdate, s_class).
Question 2:
Run script 7software in schemas des04
Create a procedure to display all the consultants. Under each consultant display all his/her skill (skill description) and the status of the skill (certified or not)
Question 3:
Run script 7clearwater in schemas des02
Create a procedure to display all items (item_id, item_desc, cat_id) under each item, display all the inventories belong to it.
Question 4:
Modify question 3 to display beside the item description the value of the item (value = inv_price * inv_qoh).
Question 5:
Run script 7software in schemas des04
Create a procedure that accepts a consultant id, and a character used to update the status (certified or not) of all the SKILLs belonged to the consultant inserted.
Display 4 information about the consultant such as id, name, Under each consultant display all his/her skill (skill description) and the OLD and NEW status of the skill (certified or not).
Question 1:
Run script 7northwoods in schemas des03
Using CURSOR FOR LOOP syntax 1 in a procedure to display all the faculty member (f_id, f_last, f_first, f_rank), under each faculty member, display all the student advised by that faculty member (s_id, s_last, s_first, birthdate, s_class).
Question 2:
Run script 7software in schemas des04
Using %ROWTYPE in a procedure, display all the consultants. Under each consultant display all his/her skill (skill description) and the status of the skill (certified or not)
Question 3:
Run script 7clearwater in schemas des02
Using CURSOR FOR LOOP syntax 2 in a procedure to display all items (item_id, item_desc, cat_id) under each item, display all the inventories belong to it.
Question 4:
Modify question 3 to display beside the item description the value of the item (value = inv_price * inv_qoh).
Clearwater Database
DROP TABLE order_line CASCADE CONSTRAINTS;
DROP TABLE shipment_line CASCADE CONSTRAINTS;
DROP TABLE shipment CASCADE CONSTRAINTS;
DROP TABLE inventory CASCADE CONSTRAINTS;
DROP TABLE color CASCADE CONSTRAINTS;
DROP TABLE item CASCADE CONSTRAINTS;
DROP TABLE category CASCADE CONSTRAINTS;
DROP TABLE orders CASCADE CONSTRAINTS;
DROP TABLE order_source CASCADE CONSTRAINTS;
DROP TABLE customer CASCADE CONSTRAINTS;
CREATE TABLE customer
(c_id NUMBER(5),
c_last VARCHAR2(30),
c_first VARCHAR2(30),
c_mi CHAR(1),
c_birthdate DATE,
c_address VARCHAR2(30),
c_city VARCHAR2(30),
c_state CHAR(2),
c_zip VARCHAR2(10),
c_dphone VARCHAR2(10),
c_ephone VARCHAR2(10),
c_userid VARCHAR2(50),
c_password VARCHAR2(15),
CONSTRAINT customer_c_id_pk PRIMARY KEY (c_id));
CREATE TABLE order_source
(os_id NUMBER(3),
os_desc VARCHAR2(30),
CONSTRAINT order_source_os_id_pk PRIMARY KEY(os_id));
CREATE TABLE orders
(o_id NUMBER(8),
o_date DATE,
o_methpmt VARCHAR2(10),
c_id NUMBER(5),
os_id NUMBER(3),
CONSTRAINT orders_o_id_pk PRIMARY KEY (o_id),
CONSTRAINT orders_c_id_fk FOREIGN KEY (c_id) REFERENCES customer(c_id),
CONSTRAINT orders_os_id_fk FOREIGN KEY (os_id) REFERENCES order_source(os_id));
CREATE TABLE category
(cat_id NUMBER(2),
cat_desc VARCHAR2(20),
CONSTRAINT category_cat_id_pk PRIMARY KEY (cat_id));
CREATE TABLE item
(item_id NUMBER(8),
item_desc VARCHAR2(30),
cat_id NUMBER(2),
item_image BLOB,
CONSTRAINT item_item_id_pk PRIMARY KEY (item_id),
CONSTRAINT item_cat_id_fk FOREIGN KEY (cat_id) REFERENCES category(cat_id));
CREATE TABLE color
(color VARCHAR2(20),
CONSTRAINT color_color_pk PRIMARY KEY (color));
CREATE TABLE inventory
(inv_id NUMBER(10),
item_id NUMBER(8),
color VARCHAR2(20),
inv_size VARCHAR2(10),
inv_price NUMBER(6,2),
inv_qoh NUMBER(4),
CONSTRAINT inventory_inv_id_pk PRIMARY KEY (inv_id),
CONSTRAINT inventory_item_id_fk FOREIGN KEY (item_id) REFERENCES item(item_id),
CONSTRAINT inventory_color_fk FOREIGN KEY (color) REFERENCES color(color));
CREATE TABLE shipment
(ship_id NUMBER(10),
ship_date_expected DATE,
CONSTRAINT shipment_ship_id_pk PRIMARY KEY (ship_id));
CREATE TABLE shipment_line
(ship_id NUMBER(10),
inv_id NUMBER(10),
sl_quantity NUMBER(4),
sl_date_received DATE,
CONSTRAINT shipment_line_ship_id_fk FOREIGN KEY (ship_id) REFERENCES shipment(ship_id),
CONSTRAINT shipment_line_inv_id_fk FOREIGN KEY (inv_id) REFERENCES inventory(inv_id),
CONSTRAINT shipment_line_shipid_invid_pk PRIMARY KEY(ship_id, inv_id));
CREATE TABLE order_line
(o_id NUMBER(8),
inv_id NUMBER(10),
ol_quantity NUMBER(4) NOT NULL,
CONSTRAINT order_line_o_id_fk FOREIGN KEY (o_id) REFERENCES orders(o_id),
CONSTRAINT order_line_inv_id_fk FOREIGN KEY (inv_id) REFERENCES inventory(inv_id),
CONSTRAINT order_line_oid_invid_pk PRIMARY KEY (o_id, inv_id));
--- inserting records into CUSTOMER
INSERT INTO CUSTOMER VALUES
(1, 'Harris', 'Paula', 'E', to_date('04/09/1953', 'mm/dd/yyyy'), '1156 Water Street, Apt. #3', 'Osseo', 'WI',
'54705', '7155558943', '7155559035', 'harrispe', 'asdfjk');
INSERT INTO CUSTOMER VALUES
(2, 'Garcia', 'Maria', 'H', to_date('07/14/1958', 'mm/dd/yyyy'), '2211 Pine Drive', 'Radisson', 'WI',
'54867', '7155558332', '7155558332', 'garciamm', '12345');
INSERT INTO CUSTOMER VALUES
(3, 'Miller', 'Lee', NULL, to_date('01/05/1936', 'mm/dd/yyyy'), '699 Pluto St. NW', 'Silver Lake', 'WI',
'53821', '7155554978', '7155559002', 'millerl', 'zxcvb');
INSERT INTO CUSTOMER VALUES
(4, 'Chang', 'Alissa', 'R', to_date('10/01/1976', 'mm/dd/yyyy'), '987 Durham Rd.', 'Apple Valley', 'MN',
'55712', '7155557651', '7155550087', 'changar', 'qwerui');
INSERT INTO CUSTOMER VALUES
(5, 'Edwards', 'Mitch', 'M', to_date('11/20/1986', 'mm/dd/yyyy'), '4204 Garner Street', 'Washburn', 'WI',
'54891', '7155558243', '7155556975', 'edwardsmm', 'qwerty');
INSERT INTO CUSTOMER VALUES
(6, 'Nelson', 'Kyle', 'E', to_date('12/04/1984', 'mm/dd/yyyy'), '232 Echo Rd.', 'Minnetonka', 'MN',
'55438', '7151113333', '7155552222', 'nelsonke', 'clever');
--- inserting records into ORDER_SOURCE
INSERT INTO order_source VALUES (1, 'Winter 2005');
INSERT INTO order_source VALUES (2, 'Spring 2006');
INSERT INTO order_source VALUES (3, 'Summer 2006');
INSERT INTO order_source VALUES (4, 'Outdoor 2006');
INSERT INTO order_source VALUES (5, 'Children''s 2006');
INSERT INTO order_source VALUES (6, 'Web Site');
--- inserting records into orders
INSERT INTO orders VALUES
(1, TO_DATE('05/29/2006', 'MM/DD/YYYY'), 'CC', 1, 2);
INSERT INTO orders VALUES
(2, TO_DATE('05/29/2006', 'MM/DD/YYYY'), 'CC', 5, 6);
INSERT INTO orders VALUES
(3, TO_DATE('05/31/2006', 'MM/DD/YYYY'), 'CHECK', 2, 2);
INSERT INTO orders VALUES
(4, TO_DATE('05/31/2006', 'MM/DD/YYYY'), 'CC', 3, 3);
INSERT INTO orders VALUES
(5, TO_DATE('06/01/2006', 'MM/DD/YYYY'), 'CC', 4, 6);
INSERT INTO orders VALUES
(6, TO_DATE('06/01/2006', 'MM/DD/YYYY'), 'CC', 4, 3);
--- inserting records into CATEGORY
INSERT INTO category VALUES (1, 'Women''s Clothing');
INSERT INTO category VALUES (2, 'Children''s Clothing');
INSERT INTO category VALUES (3, 'Men''s Clothing');
INSERT INTO category VALUES (4, 'Outdoor Gear');
--- inserting records into ITEM
INSERT INTO item VALUES
(1, 'Men''s Expedition Parka', 3, EMPTY_BLOB());
INSERT INTO item VALUES
(2, '3-Season Tent', 4, EMPTY_BLOB());
INSERT INTO item VALUES
(3, 'Women''s Hiking Shorts', 1, EMPTY_BLOB());
INSERT INTO item VALUES
(4, 'Women''s Fleece Pullover', 1, EMPTY_BLOB());
INSERT INTO item VALUES
(5, 'Children''s Beachcomber Sandals', 2, EMPTY_BLOB());
INSERT INTO item VALUES
(6, 'Boy''s Surf Shorts', 2, EMPTY_BLOB());
INSERT INTO item VALUES
(7, 'Girl''s Soccer Tee', 2, EMPTY_BLOB());
--- inserting records into COLOR
INSERT INTO color VALUES ('Sky Blue');
INSERT INTO color VALUES ('Light Grey');
INSERT INTO color VALUES ('Khaki');
INSERT INTO color VALUES ('Navy');
INSERT INTO color VALUES ('Royal');
INSERT INTO color VALUES ('Eggplant');
INSERT INTO color VALUES ('Blue');
INSERT INTO color VALUES ('Red');
INSERT INTO color VALUES ('Spruce');
INSERT INTO color VALUES ('Turquoise');
INSERT INTO color VALUES ('Bright Pink');
INSERT INTO color VALUES ('White');
--- inserting records into INVENTORY
INSERT INTO inventory VALUES
(1, 2, 'Sky Blue', NULL, 259.99, 16);
INSERT INTO inventory VALUES
(2, 2, 'Light Grey', NULL, 259.99, 12);
INSERT INTO inventory VALUES
(3, 3, 'Khaki', 'S', 29.95, 150);
INSERT INTO inventory VALUES
(4, 3, 'Khaki', 'M', 29.95, 147);
INSERT INTO inventory VALUES
(5, 3, 'Khaki', 'L', 29.95, 0);
INSERT INTO inventory VALUES
(6, 3, 'Navy', 'S', 29.95, 139);
INSERT INTO inventory VALUES
(7, 3, 'Navy', 'M', 29.95, 137);
INSERT INTO inventory VALUES
(8, 3, 'Navy', 'L', 29.95, 115);
INSERT INTO inventory VALUES
(9, 4, 'Eggplant', 'S', 59.95, 135);
INSERT INTO inventory VALUES
(10, 4, 'Eggplant', 'M', 59.95, 168);
INSERT INTO inventory VALUES
(11, 4, 'Eggplant', 'L', 59.95, 187);
INSERT INTO inventory VALUES
(12, 4, 'Royal', 'S', 59.95, 0);
INSERT INTO inventory VALUES
(13, 4, 'Royal', 'M', 59.95, 124);
INSERT INTO inventory VALUES
(14, 4, 'Royal', 'L', 59.95, 112);
INSERT INTO inventory VALUES
(15, 5, 'Turquoise', '10', 15.99, 121);
INSERT INTO inventory VALUES
(16, 5, 'Turquoise', '11', 15.99, 111);
INSERT INTO inventory VALUES
(17, 5, 'Turquoise', '12', 15.99, 113);
INSERT INTO inventory VALUES
(18, 5, 'Turquoise', '1', 15.99, 121);
INSERT INTO inventory VALUES
(19, 5, 'Bright Pink', '10', 15.99, 148);
INSERT INTO inventory VALUES
(20, 5, 'Bright Pink', '11', 15.99, 137);
INSERT INTO inventory VALUES
(21, 5, 'Bright Pink', '12', 15.99, 134);
INSERT INTO inventory VALUES
(22, 5, 'Bright Pink', '1', 15.99, 123);
INSERT INTO inventory VALUES
(23, 1, 'Spruce', 'S', 199.95, 114);
INSERT INTO inventory VALUES
(24, 1, 'Spruce', 'M',199.95, 17);
INSERT INTO inventory VALUES
(25, 1, 'Spruce', 'L', 209.95, 0);
INSERT INTO inventory VALUES
(26, 1, 'Spruce', 'XL', 209.95, 12);
INSERT INTO inventory VALUES
(27, 6, 'Blue', 'S', 15.95, 50);
INSERT INTO inventory VALUES
(28, 6, 'Blue', 'M', 15.95, 100);
INSERT INTO inventory VALUES
(29, 6, 'Blue', 'L', 15.95, 100);
INSERT INTO inventory VALUES
(30, 7, 'White', 'S', 19.99, 100);
INSERT INTO inventory VALUES
(31, 7, 'White', 'M', 19.99, 100);
INSERT INTO inventory VALUES
(32, 7, 'White', 'L', 19.99, 100);
--inserting records into SHIPMENT
INSERT INTO shipment VALUES
(1, TO_DATE('09/15/2006', 'MM/DD/YYYY'));
INSERT INTO shipment VALUES
(2, TO_DATE('11/15/2006', 'MM/DD/YYYY'));
INSERT INTO shipment VALUES
(3, TO_DATE('06/25/2006', 'MM/DD/YYYY'));
INSERT INTO shipment VALUES
(4, TO_DATE('06/25/2006', 'MM/DD/YYYY'));
INSERT INTO shipment VALUES
(5, TO_DATE('08/15/2006', 'MM/DD/YYYY'));
--inserting records into SHIPMENT_LINE
INSERT INTO shipment_line VALUES
(1, 1, 25, TO_DATE('09/10/2006', 'MM/DD/YYYY'));
INSERT INTO shipment_line VALUES
(1, 2, 25, TO_DATE('09/10/2006', 'MM/DD/YYYY'));
INSERT INTO shipment_line VALUES
(2, 2, 25, NULL);
INSERT INTO shipment_line VALUES
(3, 5, 200, NULL);
INSERT INTO shipment_line VALUES
(3, 6, 200, NULL);
INSERT INTO shipment_line VALUES
(3, 7, 200, NULL);
INSERT INTO shipment_line VALUES
(4, 12, 100, TO_DATE('08/15/2006', 'MM/DD/YYYY'));
INSERT INTO shipment_line VALUES
(4, 13, 100, TO_DATE('08/25/2006', 'MM/DD/YYYY'));
INSERT INTO shipment_line VALUES
(5, 23, 50, TO_DATE('08/15/2006', 'MM/DD/YYYY'));
INSERT INTO shipment_line VALUES
(5, 24, 100, TO_DATE('08/15/2006', 'MM/DD/YYYY'));
INSERT INTO shipment_line VALUES
(5, 25, 100, TO_DATE('08/15/2006', 'MM/DD/YYYY'));
--- inserting records into ORDER_LINE
INSERT INTO order_line VALUES (1, 1, 1);
INSERT INTO order_line VALUES (1, 14, 2);
INSERT INTO order_line VALUES (2, 19, 1);
INSERT INTO order_line VALUES (3, 24, 1);
INSERT INTO order_line VALUES (3, 26, 1);
INSERT INTO order_line VALUES (4, 12, 2);
INSERT INTO order_line VALUES (5, 8, 1);
INSERT INTO order_line VALUES (5, 13, 1);
INSERT INTO order_line VALUES (6, 2, 1);
INSERT INTO order_line VALUES (6, 7, 3);
COMMIT;
Northwoods Database
-- script to create NORTHWOODS database
DROP TABLE enrollment CASCADE CONSTRAINTS;
DROP TABLE course_section CASCADE CONSTRAINTS;
DROP TABLE term CASCADE CONSTRAINTS;
DROP TABLE course CASCADE CONSTRAINTS;
DROP TABLE student CASCADE CONSTRAINTS;
DROP TABLE faculty CASCADE CONSTRAINTS;
DROP TABLE location CASCADE CONSTRAINTS;
CREATE TABLE LOCATION
(loc_id NUMBER(6),
bldg_code VARCHAR2(10),
room VARCHAR2(6),
capacity NUMBER(5),
CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));
CREATE TABLE faculty
(f_id NUMBER(6),
f_last VARCHAR2(30),
f_first VARCHAR2(30),
f_mi CHAR(1),
loc_id NUMBER(5),
f_phone VARCHAR2(10),
f_rank VARCHAR2(8),
f_pin NUMBER(4),
f_image BLOB,
CONSTRAINT faculty_f_id_pk PRIMARY KEY(f_id),
CONSTRAINT faculty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id));
CREATE TABLE student
(s_id NUMBER(6),
s_last VARCHAR2(30),
s_first VARCHAR2(30),
s_mi CHAR(1),
s_address VARCHAR2(25),
s_city VARCHAR2(20),
s_state CHAR(2),
s_zip VARCHAR2(10),
s_phone VARCHAR2(10),
s_class CHAR(2),
s_dob DATE,
s_pin NUMBER(4),
f_id NUMBER(6),
time_enrolled INTERVAL YEAR TO MONTH,
CONSTRAINT student_s_id_pk PRIMARY KEY (s_id),
CONSTRAINT student_f_id_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));
CREATE TABLE TERM
(term_id NUMBER(6),
term_desc VARCHAR2(20),
status VARCHAR2(20),
CONSTRAINT term_term_id_pk PRIMARY KEY (term_id),
CONSTRAINT term_status_cc CHECK ((status = 'OPEN') OR (status = 'CLOSED')));
CREATE TABLE COURSE
(course_id NUMBER(6),
call_id VARCHAR2(10),
course_name VARCHAR2(25),
credits NUMBER(2),
CONSTRAINT course_course_id_pk PRIMARY KEY(course_id));
CREATE TABLE COURSE_SECTION
(c_sec_id NUMBER(6),
course_id NUMBER(6) CONSTRAINT course_section_courseid_nn NOT NULL,
term_id NUMBER(6) CONSTRAINT course_section_termid_nn NOT NULL,
sec_num NUMBER(2) CONSTRAINT course_section_secnum_nn NOT NULL,
f_id NUMBER(5),
c_sec_day VARCHAR2(10),
c_sec_time DATE,
c_sec_duration INTERVAL DAY TO SECOND,
loc_id NUMBER(6),
max_enrl NUMBER(4) CONSTRAINT course_section_maxenrl_nn NOT NULL,
CONSTRAINT course_section_csec_id_pk PRIMARY KEY (c_sec_id),
CONSTRAINT course_section_cid_fk FOREIGN KEY (course_id) REFERENCES course(course_id),
CONSTRAINT course_section_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id),
CONSTRAINT course_section_termid_fk FOREIGN KEY (term_id) REFERENCES term(term_id),
CONSTRAINT course_section_fid_fk FOREIGN KEY (f_id) REFERENCES faculty(f_id));
CREATE TABLE ENROLLMENT
(s_id NUMBER(6),
c_sec_id NUMBER(6),
grade CHAR(1),
CONSTRAINT enrollment_pk PRIMARY KEY (s_id, c_sec_id),
CONSTRAINT enrollment_sid_fk FOREIGN KEY (s_id) REFERENCES student(s_id),
CONSTRAINT enrollment_csecid_fk FOREIGN KEY (c_sec_id) REFERENCES course_section (c_sec_id));
---- inserting into LOCATION table
INSERT INTO location VALUES
(1, 'CR', '101', 150);
INSERT INTO location VALUES
(2, 'CR', '202', 40);
INSERT INTO location VALUES
(3, 'CR', '103', 35);
INSERT INTO location VALUES
(4, 'CR', '105', 35);
INSERT INTO location VALUES
(5, 'BUS', '105', 42);
INSERT INTO location VALUES
(6, 'BUS', '404', 35);
INSERT INTO location VALUES
(7, 'BUS', '421', 35);
INSERT INTO location VALUES
(8, 'BUS', '211', 55);
INSERT INTO location VALUES
(9, 'BUS', '424', 1);
INSERT INTO location VALUES
(10, 'BUS', '402', 1);
INSERT INTO location VALUES
(11, 'BUS', '433', 1);
INSERT INTO location VALUES
(12, 'LIB', '217', 2);
INSERT INTO location VALUES
(13, 'LIB', '222', 1);
--- inserting records into FACULTY
INSERT INTO faculty VALUES
(1, 'Cox', 'Kim', 'J', 9, '7155551234', 'ASSO', 1181, EMPTY_BLOB());
INSERT INTO faculty VALUES
(2, 'Blanchard', 'John', 'R', 10, '7155559087', 'FULL', 1075, EMPTY_BLOB());
INSERT INTO faculty VALUES
(3, 'Williams', 'Jerry', 'F', 12, '7155555412', 'ASST', 8531, EMPTY_BLOB());
INSERT INTO faculty VALUES
(4, 'Sheng', 'Laura', 'M', 11, '7155556409', 'INST', 1690, EMPTY_BLOB());
INSERT INTO faculty VALUES
(5, 'Brown', 'Philip', 'E', 13, '7155556082', 'ASSO', 9899, EMPTY_BLOB());
--- inserting records into STUDENT
INSERT INTO student VALUES
(1, 'Miller', 'Sarah', 'M', '144 Windridge Blvd.', 'Eau Claire',
'WI', '54703', '7155559876', 'SR', TO_DATE('07/14/1985', 'MM/DD/YYYY'), 8891, 1, TO_YMINTERVAL('3-2'));
INSERT INTO student VALUES
(2, 'Umato', 'Brian', 'D', '454 St. John''s Place', 'Eau Claire',
'WI', '54702', '7155552345', 'SR', TO_DATE('08/19/1985', 'MM/DD/YYYY'), 1230, 1, TO_YMINTERVAL('4-2'));
INSERT INTO student VALUES
(3, 'Black', 'Daniel', NULL, '8921 Circle Drive', 'Bloomer',
'WI', '54715', '7155553907', 'JR', TO_DATE('10/10/1982', 'MM/DD/YYYY'), 1613, 1, TO_YMINTERVAL('3-0'));
INSERT INTO student VALUES
(4, 'Mobley', 'Amanda', 'J', '1716 Summit St.', 'Eau Claire',
'WI', '54703', '7155556902', 'SO', TO_DATE('09/24/1986', 'MM/DD/YYYY'), 1841, 2, TO_YMINTERVAL('2-2'));
INSERT INTO student VALUES
(5, 'Sanchez', 'Ruben', 'R', '1780 Samantha Court', 'Eau Claire',
'WI', '54701', '7155558899', 'SO', TO_DATE('11/20/1986', 'MM/DD/YYYY'), 4420, 4, TO_YMINTERVAL('1-11'));
INSERT INTO student VALUES
(6, 'Connoly', 'Michael', 'S', '1818 Silver Street', 'Elk Mound',
'WI', '54712', '7155554944', 'FR', TO_DATE('12/4/1986', 'MM/DD/YYYY'), 9188, 3, TO_YMINTERVAL('0-4'));
--- inserting records into TERM
INSERT INTO term VALUES
(1, 'Fall 2005', 'CLOSED');
INSERT INTO term VALUES
(2, 'Spring 2006', 'CLOSED');
INSERT INTO term VALUES
(3, 'Summer 2006', 'CLOSED');
INSERT INTO term VALUES
(4, 'Fall 2006', 'CLOSED');
INSERT INTO term VALUES
(5, 'Spring 2007', 'CLOSED');
INSERT INTO term VALUES
(6, 'Summer 2007', 'OPEN');
--- inserting records into COURSE
INSERT INTO course VALUES
(1, 'MIS 101', 'Intro. to Info. Systems', 3);
INSERT INTO course VALUES
(2, 'MIS 301', 'Systems Analysis', 3);
INSERT INTO course VALUES
(3, 'MIS 441', 'Database Management', 3);
INSERT INTO course VALUES
(4, 'CS 155', 'Programming in C++', 3);
INSERT INTO course VALUES
(5, 'MIS 451', 'Web-Based Systems', 3);
--- inserting records into COURSE_SECTION
INSERT INTO course_section VALUES
(1, 1, 4, 1, 2, 'MWF', TO_DATE('10:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 1, 140);
INSERT INTO course_section VALUES
(2, 1, 4, 2, 3, 'TR', TO_DATE('09:30 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:01:15.00'), 7, 35);
INSERT INTO course_section VALUES
(3, 1, 4, 3, 3, 'MWF', TO_DATE('08:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 2, 35);
INSERT INTO course_section VALUES
(4, 2, 4, 1, 4, 'TR', TO_DATE('11:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:01:15.00'), 6, 35);
INSERT INTO course_section VALUES
(5, 2, 5, 2, 4, 'TR', TO_DATE('02:00 PM', 'HH:MI PM'), TO_DSINTERVAL('0 00:01:15.00'), 6, 35);
INSERT INTO course_section VALUES
(6, 3, 5, 1, 1, 'MWF', TO_DATE('09:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 5, 30);
INSERT INTO course_section VALUES
(7, 3, 5, 2, 1, 'MWF', TO_DATE('10:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:00:50.00'), 5, 30);
INSERT INTO course_section VALUES
(8, 4, 5, 1, 5, 'TR', TO_DATE('08:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:01:15.00'), 3, 35);
INSERT INTO course_section VALUES
(9, 5, 5, 1, 2, 'MWF', TO_DATE('02:00 PM', 'HH:MI PM'), TO_DSINTERVAL('0 00:00:50.00'), 5, 35);
INSERT INTO course_section VALUES
(10, 5, 5, 2, 2, 'MWF', TO_DATE('03:00 PM', 'HH:MI PM'), TO_DSINTERVAL('0 00:00:50.00'), 5, 35);
INSERT INTO course_section VALUES
(11, 1, 6, 1, 1, 'MTWRF', TO_DATE('08:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:01:30.00'), 1, 50);
INSERT INTO course_section VALUES
(12, 2, 6, 1, 2, 'MTWRF', TO_DATE('08:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:01:30.00'), 6, 35);
INSERT INTO course_section VALUES
(13, 3, 6, 1, 3, 'MTWRF', TO_DATE('09:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:01:30.00'), 5, 35);
--- inserting records into ENROLLMENT
INSERT INTO enrollment VALUES
(1, 1, 'A');
INSERT INTO enrollment VALUES
(1, 4, 'A');
INSERT INTO enrollment VALUES
(1, 6, 'B');
INSERT INTO enrollment VALUES
(1, 9, 'B');
INSERT INTO enrollment VALUES
(2, 1, 'C');
INSERT INTO enrollment VALUES
(2, 5, 'B');
INSERT INTO enrollment VALUES
(2, 6, 'A');
INSERT INTO enrollment VALUES
(2, 9, 'B');
INSERT INTO enrollment VALUES
(3, 1, 'C');
INSERT INTO enrollment VALUES
(3, 12, NULL);
INSERT INTO enrollment VALUES
(3, 13, NULL);
INSERT INTO enrollment VALUES
(4, 11, NULL);
INSERT INTO enrollment VALUES
(4, 12, NULL);
INSERT INTO enrollment VALUES
(5, 1, 'B');
INSERT INTO enrollment VALUES
(5, 5, 'C');
INSERT INTO enrollment VALUES
(5, 9, 'C');
INSERT INTO enrollment VALUES
(5, 11, NULL);
INSERT INTO enrollment VALUES
(5, 13, NULL);
INSERT INTO enrollment VALUES
(6, 11, NULL);
INSERT INTO enrollment VALUES
(6, 12, NULL);
COMMIT;
Software Database
DROP TABLE evaluation CASCADE CONSTRAINTS;
DROP TABLE project_consultant CASCADE CONSTRAINTS;
DROP TABLE project_skill CASCADE CONSTRAINTS;
DROP TABLE project CASCADE CONSTRAINTS;
DROP TABLE client CASCADE CONSTRAINTS;
DROP TABLE consultant_skill CASCADE CONSTRAINTS;
DROP TABLE skill CASCADE CONSTRAINTS;
DROP TABLE consultant CASCADE CONSTRAINTS;
CREATE TABLE consultant
(c_id NUMBER(6),
c_last VARCHAR2(20),
c_first VARCHAR2(20),
c_mi CHAR(1),
c_add VARCHAR2(30),
c_city VARCHAR2(20),
c_state CHAR(2),
c_zip VARCHAR2(10),
c_phone VARCHAR2(15),
c_email VARCHAR2(30),
CONSTRAINT consultant_c_id_pk PRIMARY KEY (c_id));
CREATE TABLE skill
(skill_id NUMBER(3),
skill_description VARCHAR2(50),
CONSTRAINT skill_skill_id_pk PRIMARY KEY (skill_id));
CREATE TABLE consultant_skill
(c_id NUMBER(6),
skill_id NUMBER(3),
certification VARCHAR2(8),
CONSTRAINT consultant_skill_pk PRIMARY KEY (c_id, skill_id),
CONSTRAINT consultant_skill_c_id_fk FOREIGN KEY (c_id) REFERENCES consultant(c_id),
CONSTRAINT consultant_skill_skill_id_fk FOREIGN KEY (skill_id) REFERENCES skill(skill_id));
CREATE TABLE client
(client_id NUMBER(6),
client_name VARCHAR2(30),
contact_last VARCHAR2(30),
contact_first VARCHAR2(30),
contact_phone VARCHAR2(15),
CONSTRAINT client_client_id_pk PRIMARY KEY (client_id));
CREATE TABLE project
(p_id NUMBER(6),
project_name VARCHAR2(30),
client_id NUMBER(6),
mgr_id NUMBER(6),
parent_p_id NUMBER(6),
CONSTRAINT project_pid_pk PRIMARY KEY (p_id),
CONSTRAINT project_client_id_fk FOREIGN KEY (client_id) REFERENCES client(client_id),
CONSTRAINT project_mgr_id_fk FOREIGN KEY (mgr_id) REFERENCES consultant(c_id));
ALTER TABLE project
ADD CONSTRAINT project_parent_pid_fk FOREIGN KEY (parent_p_id) REFERENCES project(p_id);
CREATE TABLE project_skill
(p_id NUMBER(6),
skill_id NUMBER(3),
CONSTRAINT project_skill_pk PRIMARY KEY (p_id, skill_id),
CONSTRAINT project_skill_pid_fk FOREIGN KEY (p_id) REFERENCES project(p_id),
CONSTRAINT project_skill_skill_id_fk FOREIGN KEY (skill_id) REFERENCES skill(skill_id));
CREATE TABLE project_consultant
(p_id NUMBER(6),
c_id NUMBER(6),
roll_on_date DATE,
roll_off_date DATE,
elapsed_time VARCHAR2(30),
CONSTRAINT project_consultant_pk PRIMARY KEY (p_id, c_id),
CONSTRAINT project_consultant_p_id_fk FOREIGN KEY (p_id) REFERENCES project(p_id),
CONSTRAINT project_consultant_c_id_fk FOREIGN KEY (c_id) REFERENCES consultant(c_id));
CREATE TABLE evaluation
(e_id NUMBER(8),
e_date DATE,
p_id NUMBER(6),
evaluator_id NUMBER(6),
evaluatee_id NUMBER(6),
score NUMBER(3),
comments VARCHAR2(300),
CONSTRAINT evaluation_e_id_pk PRIMARY KEY (e_id),
CONSTRAINT evaluation_p_id_fk FOREIGN KEY (p_id) REFERENCES project(p_id),
CONSTRAINT evaluation_evaluator_id_fk FOREIGN KEY (evaluator_id) REFERENCES consultant(c_id),
CONSTRAINT evaluation_evaluatee_id_fk FOREIGN KEY (evaluatee_id) REFERENCES consultant(c_id));
--insert values into CONSULTANT
INSERT INTO consultant VALUES
(100, 'Myers', 'Mark', 'F', '1383 Alexander Ave.', 'Eau Claire', 'WI', '54703', '7155559652', 'mmyers@swexpert.com');
INSERT INTO consultant VALUES
(101, 'Hernandez', 'Sheila', 'R', '3227 Brian Street', 'Eau Claire', 'WI', '54702', '7155550282', 'shernandez@earthware.com');
INSERT INTO consultant VALUES
(102, 'Zhang', 'Brian', NULL, '2227 Calumet Place', 'Altoona', 'WI', '54720', '7155558383', 'zhang@swexpert.com');
INSERT INTO consultant VALUES
(103, 'Carlson', 'Sarah', 'J', '1334 Water Street', 'Eau Claire', 'WI', '54703', '7155558008', 'carlsons@swexpert.com');
INSERT INTO consultant VALUES
(104, 'Courtlandt', 'Paul', 'R', '1911 Pine Drive', 'Eau Claire', 'WI', '54701', '7155555225', 'courtlpr@yamail.com');
INSERT INTO consultant VALUES
(105, 'Park', 'Janet', 'S', '2333 157th Street', 'Chippewa Falls', 'WI', '54712', '7155554944', 'jpark@swexpert.com');
--insert values into SKILL
INSERT INTO skill VALUES
(1, 'VB .NET Programming');
INSERT INTO skill VALUES
(2, 'COBOL Programming');
INSERT INTO skill VALUES
(3, 'Java Programming');
INSERT INTO skill VALUES
(4, 'Project Management');
INSERT INTO skill VALUES
(5, 'Web Application Programming');
INSERT INTO skill VALUES
(6, 'Oracle Developer Programming');
INSERT INTO skill VALUES
(7, 'Oracle Database Administration');
INSERT INTO skill VALUES
(8, 'Windows NT/2000 Network Administration');
INSERT INTO skill VALUES
(9, 'Windows XP Network Administration');
--insert values into CONSULTANT_SKILL
INSERT INTO consultant_skill VALUES
(100, 1, 'Y');
INSERT INTO consultant_skill VALUES
(100, 3, 'N');
INSERT INTO consultant_skill VALUES
(100, 6, 'Y');
INSERT INTO consultant_skill VALUES
(101, 4, 'N');
INSERT INTO consultant_skill VALUES
(101, 5, 'N');
INSERT INTO consultant_skill VALUES
(102, 7, 'Y');
INSERT INTO consultant_skill VALUES
(103, 1, 'Y');
INSERT INTO consultant_skill VALUES
(103, 6, 'Y');
INSERT INTO consultant_skill VALUES
(103, 8, 'Y');
INSERT INTO consultant_skill VALUES
(103, 9, 'Y');
INSERT INTO consultant_skill VALUES
(104, 8, 'N');
INSERT INTO consultant_skill VALUES
(104, 9, 'Y');
INSERT INTO consultant_skill VALUES
(105, 2, 'N');
INSERT INTO consultant_skill VALUES
(105, 3, 'N');
INSERT INTO consultant_skill VALUES
(105, 4, 'Y');
--insert into CLIENT
INSERT INTO client VALUES (1, 'Crisco Systems', 'Martin', 'Andrew', '5215557220');
INSERT INTO client VALUES (2, 'Supreme Data Corporation', 'Martinez', 'Michelle', '5205559821');
INSERT INTO client VALUES (3, 'Lucid Technologies', 'Brown', 'Jack', '7155552311');
INSERT INTO client VALUES (4, 'Morningstar Bank', 'Wright', 'Linda', '9215553320');
INSERT INTO client VALUES (5, 'Maverick Petroleum', 'Miller', 'Tom', '4085559822');
INSERT INTO client VALUES (6, 'Birchwood Mall', 'Brenner', 'Nicole', '7155550828');
--insert into PROJECT
INSERT INTO project VALUES
(1, 'Hardware Support Intranet', 2, 105, NULL);
INSERT INTO project VALUES
(2, 'Hardware Support Interface', 2, 103, 1);
INSERT INTO project VALUES
(3, 'Hardware Support Database', 2, 102, 1);
INSERT INTO project VALUES
(4, 'Teller Support System', 4, 105, NULL);
INSERT INTO project VALUES
(5, 'Internet Advertising', 6, 105, NULL);
INSERT INTO project VALUES
(6, 'Network Design', 6, 104, 5);
INSERT INTO project VALUES
(7, 'Exploration Database', 5, 102, NULL);
--insert into PROJECT_SKILL
INSERT INTO project_skill VALUES (1, 8);
INSERT INTO project_skill VALUES (1, 9);
INSERT INTO project_skill VALUES (2, 3);
INSERT INTO project_skill VALUES (3, 6);
INSERT INTO project_skill VALUES (3, 7);
INSERT INTO project_skill VALUES (4, 2);
INSERT INTO project_skill VALUES (4, 7);
INSERT INTO project_skill VALUES (5, 5);
INSERT INTO project_skill VALUES (5, 9);
INSERT INTO project_skill VALUES (6, 9);
INSERT INTO project_skill VALUES (7, 6);
INSERT INTO project_skill VALUES (7, 7);
--insert into PROJECT_CONSULTANT
INSERT INTO project_consultant VALUES
(1, 101, TO_DATE('06/15/2006', 'MM/DD/YYYY'), TO_DATE('12/15/2006', 'MM/DD/YYYY'), '92 00:00:00.00');
INSERT INTO project_consultant VALUES
(1, 104, TO_DATE('01/05/2006', 'MM/DD/YYYY'), TO_DATE('12/15/2006', 'MM/DD/YYYY'), '126 00:00:00.00');
INSERT INTO project_consultant VALUES
(1, 103, TO_DATE('01/05/2006', 'MM/DD/YYYY'), TO_DATE('06/05/2006', 'MM/DD/YYYY'), '5 00:00:00.00');
INSERT INTO project_consultant VALUES
(1, 105, TO_DATE('01/05/2006', 'MM/DD/YYYY'), TO_DATE('12/15/2006', 'MM/DD/YYYY'), '45 00:00:00.00');
INSERT INTO project_consultant VALUES
(2, 105, TO_DATE('07/17/2006', 'MM/DD/YYYY'), TO_DATE('09/17/2006', 'MM/DD/YYYY'), '25 00:00:00.00');
INSERT INTO project_consultant VALUES
(2, 100, TO_DATE('07/17/2006', 'MM/DD/YYYY'), TO_DATE('09/17/2006', 'MM/DD/YYYY'), '0 00:00:00.00');
INSERT INTO project_consultant VALUES
(3, 103, TO_DATE('09/15/2006', 'MM/DD/YYYY'), TO_DATE('03/15/2007', 'MM/DD/YYYY'), '125 00:00:00.00');
INSERT INTO project_consultant VALUES
(3, 104, TO_DATE('10/15/2006', 'MM/DD/YYYY'), TO_DATE('12/15/2006', 'MM/DD/YYYY'), '50 00:00:00.00');
INSERT INTO project_consultant VALUES
(4, 105, TO_DATE('06/05/2006', 'MM/DD/YYYY'), TO_DATE('06/05/2007', 'MM/DD/YYYY'), '25 00:00:00.00');
INSERT INTO project_consultant VALUES
(4, 104, TO_DATE('06/15/2006', 'MM/DD/YYYY'), TO_DATE('12/15/2006', 'MM/DD/YYYY'), '53 00:00:00.00');
INSERT INTO project_consultant VALUES
(4, 102, TO_DATE('07/15/2006', 'MM/DD/YYYY'), TO_DATE('12/15/2006', 'MM/DD/YYYY'), '30 00:00:00.00');
INSERT INTO project_consultant VALUES
(5, 105, TO_DATE('09/19/2006', 'MM/DD/YYYY'), TO_DATE('03/19/2007', 'MM/DD/YYYY'), '15 00:00:00.00');
INSERT INTO project_consultant VALUES
(5, 103, TO_DATE('09/19/2006', 'MM/DD/YYYY'), TO_DATE('03/19/2007', 'MM/DD/YYYY'), '15 00:00:00.00');
INSERT INTO project_consultant VALUES
(6, 103, TO_DATE('09/19/2006', 'MM/DD/YYYY'), TO_DATE('03/19/2007', 'MM/DD/YYYY'), '5 00:00:00.00');
INSERT INTO project_consultant VALUES
(6, 104, TO_DATE('09/19/2006', 'MM/DD/YYYY'), TO_DATE('03/19/2007', 'MM/DD/YYYY'), '10 00:00:00.00');
INSERT INTO project_consultant VALUES
(7, 102, TO_DATE('05/20/2006', 'MM/DD/YYYY'), TO_DATE('12/20/2006', 'MM/DD/YYYY'), '55 00:00:00.00');
INSERT INTO project_consultant VALUES
(7, 100, TO_DATE('05/25/2006', 'MM/DD/YYYY'), TO_DATE('12/20/2006', 'MM/DD/YYYY'), '100 00:00:00.00');
--insert into EVALUATION
INSERT INTO evaluation VALUES
(100, TO_DATE('01/07/2007', 'MM/DD/YYYY'), 1, 105, 101, 90, NULL);
INSERT INTO evaluation VALUES
(101, TO_DATE('01/07/2007', 'MM/DD/YYYY'), 1, 105, 104, 85, NULL);
INSERT INTO evaluation VALUES
(102, TO_DATE('01/08/2007', 'MM/DD/YYYY'), 1, 105, 103, 90, NULL);
INSERT INTO evaluation VALUES
(103, TO_DATE('12/20/2006', 'MM/DD/YYYY'), 1, 103, 105, 100, NULL);
INSERT INTO evaluation VALUES
(104, TO_DATE('12/29/2006', 'MM/DD/YYYY'), 1, 104, 105, 75, NULL);
INSERT INTO evaluation VALUES
(105, TO_DATE('01/15/2007', 'MM/DD/YYYY'), 1, 101, 105, 90, NULL);
COMMIT;