Andre Long
CISP 350 Oracle
March 9, 2006
Chapter 3A PSC 

CASE#1
a)
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(1, 'CR', '101', 150);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(2, 'CR', '202', 40);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(3, 'CR', '103', 35);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(4, 'CR', '105', 35);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(5, 'BUS', '105', 42);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(6, 'BUS', '404', 35);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(7, 'BUS', '421', 35);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(8, 'BUS', '211', 55);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(9, 'BUS', '424', 1);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(10, 'BUS', '402', 1);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(11, 'BUS', '433', 1);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(12, 'LIB', '217', 2);
INSERT INTO location (loc_id, bldg_code, room, capacity)
VALUES	(13, 'LIB', '222', 1);


INSERT INTO faculty (f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank, f_pin, f_image)
VALUES	(1, 'Cox', 'Kim', 'J', 9, '7155551234', 'ASSO', '1181', EMPTY_BLOB());
INSERT INTO faculty (f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank, f_pin, f_image)
VALUES	(2, 'Blanchard', 'John', 'R', 10, '7155559087', 'FULL', '1075', EMPTY_BLOB());
INSERT INTO faculty (f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank, f_pin, f_image)
VALUES	(3, 'Williams', 'Jerry', 'F', 12, '7155555412', 'ASST', '8531', EMPTY_BLOB());
INSERT INTO faculty (f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank, f_pin, f_image)
VALUES	(4, 'Sheng', 'Laura', 'M', 11, '7155556409', 'INST', '1690', EMPTY_BLOB());
INSERT INTO faculty (f_id, f_last, f_first, f_mi, loc_id, f_phone, f_rank, f_pin, f_image)
VALUES	(5, 'Brown', 'Phillip', 'E', 13, '7155556082', 'ASSO', '9899', EMPTY_BLOB());

INSERT INTO students (s_id, s_last, s_first, s_mi, s_address, s_city, s_state, s_zip, s_phone, s_class, s_dob, s_pin, f_id, time_enroll)
VALUES	(1, 'Miller', 'Sarah', 'M', '144 Windridge Blvd.', 'Eau Claire', 'WI', '54703', '7155559876', 'SR', TO_DATE('07/14/85', 'MM/DD/YYYY'), '8891', 1, TO_YMINTERVAL('3-2'));

INSERT INTO students (s_id, s_last, s_first, s_mi, s_address, s_city, s_state, s_zip, s_phone, s_class, s_dob, s_pin, f_id, time_enroll)
VALUES	(2, 'Umato', 'Brian', 'D',  '454 St. John''s Place', 'Eau Claire', 'WI','54702', '7155552345', 'SR', TO_DATE('08/19/85', 'MM/DD/YYYY'), '1230', 1, TO_YMINTERVAL('4-6'));

INSERT INTO students (s_id, s_last, s_first, s_mi, s_address, s_city, s_state, s_zip, s_phone, s_class, s_dob, s_pin, f_id, time_enroll)
VALUES	(3, 'Black', 'Daniel', NULL,'8921 Circle Drive', 'Bloomer', 'WI','54715', '7155553907', 'JR', TO_DATE('10/10/82', 'MM/DD/YYYY'), '1613', 1, TO_YMINTERVAL('3-0'));

INSERT INTO students (s_id, s_last, s_first, s_mi, s_address, s_city, s_state, s_zip, s_phone, s_class, s_dob, s_pin, f_id, time_enroll)
VALUES	(4, 'Mobley', 'Amanda', 'J', '1716 Summit St.', 'Eau Claire', 'WI','54703', '7155556902', 'SO', TO_DATE('9/24/86', 'MM/DD/YYYY'), '1841', 2, TO_YMINTERVAL('2-2'));

INSERT INTO students (s_id, s_last, s_first, s_mi, s_address, s_city, s_state, s_zip, s_phone, s_class, s_dob, s_pin, f_id, time_enroll)
VALUES	(5, 'Sanchez', 'Ruben', 'R', '1780 Samantha Court', 'Eau Claire', 'WI','54701', '7155558899', 'SO', TO_DATE('11/20/86', 'MM/DD/YYYY'), '4420', 4, TO_YMINTERVAL('1-11'));

INSERT INTO students (s_id, s_last, s_first, s_mi, s_address, s_city, s_state, s_zip, s_phone, s_class, s_dob, s_pin, f_id, time_enroll)
VALUES	(6, 'Connoly', 'Michael', 'S', '1818 Sliver Street', 'Elk Mound', 'WI','54712', '7155554944', 'FR', TO_DATE('12/4/87', 'MM/DD/YYYY'), '9188', 3, TO_YMINTERVAL('0-4'));


INSERT INTO term (term_id, term_desc, status)
VALUES	(1, 'Fall 2005', 'close');
INSERT INTO term (term_id, term_desc, status)
VALUES	(2, 'Spring 2006', 'close');
INSERT INTO term (term_id, term_desc, status)
VALUES	(3, 'Summer 2006', 'close');
INSERT INTO term (term_id, term_desc, status)
VALUES	(4, 'Fall 2006', 'close');
INSERT INTO term (term_id, term_desc, status)
VALUES	(5, 'Spring 2007', 'close');
INSERT INTO term (term_id, term_desc, status)
VALUES	(6, 'Summer 2007', 'open');


INSERT INTO course (course_id, call_id, course_name, credits)
VALUES	(1, 'MIS 101', 'Intro. to Info. Systems', 3);
INSERT INTO course (course_id, call_id, course_name, credits)
VALUES	(2, 'MIS 301', 'Systems Analysis', 3);
INSERT INTO course (course_id, call_id, course_name, credits)
VALUES	(3, 'MIS 441', 'Database Management', 3);
INSERT INTO course (course_id, call_id, course_name, credits)
VALUES	(4, 'CS 155', 'Programming in C++', 3);
INSERT INTO course (course_id, call_id, course_name, credits)
VALUES	(5, 'MIS 451', 'Web Based Systems', 3);

b)
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(1, 1, 4, 1, 2, 'MWF', TO_DATE('10:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:50:00'), 1, 140);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(2, 1, 4, 2, 3, 'TR', TO_DATE('9:30 AM', 'HH:MI AM'), TO_DSINTERVAL('0 01:15:00'), 7, 35);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(3, 1, 4, 3, 3, 'MWF', TO_DATE('8:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:50:00'), 2, 35);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(4, 2, 4, 1, 4, 'TR', TO_DATE('11:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 01:15:00'), 6, 35);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(5, 2, 5, 2, 4, 'TR', TO_DATE('2:00 PM', 'HH:MI AM'), TO_DSINTERVAL('0 01:15:00'), 6, 35);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(6, 3, 5, 1, 1, 'MWF', TO_DATE('9:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:50:00'), 5, 30);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(7, 3, 5, 2, 1, 'MWF', TO_DATE('10:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 00:50:00'), 5, 30);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(8, 4, 5, 1, 5, 'TR', TO_DATE('8:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 01:15:00'), 3, 35);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(9, 5, 5, 1, 2, 'MWF', TO_DATE('2:00 PM', 'HH:MI AM'), TO_DSINTERVAL('0 00:50:00'), 5, 35);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(10, 5, 5, 2, 2, 'MWF', TO_DATE('3:00 PM', 'HH:MI AM'), TO_DSINTERVAL('0 00:50:00'), 5, 35);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(11, 1, 6, 1, 1, 'MTWRF', TO_DATE('8:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 01:30:00'), 1, 50);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(12, 2, 6, 1, 2, 'MTWRF', TO_DATE('8:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 01:30:00'), 6, 35);
INSERT INTO course_section (c_sec_id, course_id, term_id, sec_num, f_id, c_sec_day, c_sec_time, c_sec_duration, loc_id, max_enrl)
VALUES	(13, 3, 6, 1, 3, 'MTWRF', TO_DATE('9:00 AM', 'HH:MI AM'), TO_DSINTERVAL('0 01:30:00'), 5, 35);

c)
GRANT select
ON    location
TO    PUBLIC;

GRANT select
ON    faculty
TO    PUBLIC;

GRANT select
ON    students
TO    PUBLIC;

GRANT select
ON    term
TO    PUBLIC;

GRANT select
ON    course
TO    PUBLIC;

GRANT select
ON    course_section
TO    PUBLIC;

d)
ALTER TABLE faculty
DISABLE CONSTRAINT FACULTY_f_id_fk;

ALTER TABLE course_section
DISABLE CONSTRAINT	course_section_loc_id_fk;

TRUNCATE TABLE location;

ALTER TABLE faculty
ENABLE CONSTRAINT FACULTY_f_id_fk;

ALTER TABLE course_section
ENABLE CONSTRAINT course_section_loc_id_fk;   
Note:(For the ENABLE commmand to work you must reinsert the data in the "location" table)

DELETE FROM faculty;
DELETE FROM course;
DELETE FROM course_section;
DELETE FROM term;