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

CASE#3

a)
savepoint insert_students;

b)
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 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());

c)
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'));

d)
savepoint update_class;

ALTER TABLE students
DISABLE CONSTRAINT students_s_class_cc ;

UPDATE students
SET s_class='GR'
WHERE f_id=1;

SELECT *  from students;

e)
ROLLBACK TO update_class;

ROLLBACK TO insert_students;

SELECT *  from students;

UPDATE students
SET s_class='SR'
WHERE f_id=1;

ALTER TABLE students
ENABLE CONSTRAINT students_s_class_cc ;