-- Filename Library_TD_Partial -- Purpose To partially provide test data to support application -- development for the Library Database System. Some additional -- INSERT, UPDATE and DELETE statements required to create test -- data matching the case study description. -- See file Library_Tables for database schema creation script -- IMPORTANT TABLES MUST BE CREATED EXACTLY ACCORDING TO THE GIVEN ERD FOR -- THIS SCRIPT TO RUN CORRECTLY -- Language Oracle SQL -- Author Sheila Baron, Southampton Solent University -- Date 10-10-2005 -- Version 1.4 DELETE FROM reservation; DELETE FROM loan; DELETE FROM book; DELETE FROM title; DELETE FROM member; PROMPT Insert into member INSERT INTO member VALUES (1000000001, 'Jill', 'JONES', '15-Nov-98', '30-Jul-05', 'SFS'); INSERT INTO member VALUES (1000000002, 'Rose', 'Lange', '01-Dec-98', '27-Jul-05', 'SFS'); INSERT INTO member VALUES (1000000003, 'Paul', 'Smith', '06-Sep-99', NULL, 'SFS'); INSERT INTO member VALUES (1000000004, 'Mary', 'GREEN', '08-Oct-00', NULL, 'SS'); INSERT INTO member VALUES (1000000005, 'Steve', 'Zaman', '01-Nov-00', NULL, 'SS'); INSERT INTO member VALUES (1000000006, 'John', 'QASIM', '10-Sep-00', '30-Jul-05', 'SS'); INSERT INTO member VALUES (1000000007, 'Amar', 'Shah', '01-Sep-85', NULL, 'SM'); INSERT INTO member VALUES (1000000008, 'Fred', 'Harris', '01-Sep-87',NULL, 'SM'); INSERT INTO member VALUES (1000000010, 'Daffy', 'DUCK', '01-Jan-1900', '31-Dec-1930', 'SM'); PROMPT Insert into title INSERT INTO title VALUES (1,'The World of Gemstones','3rd','Addison Wesley','1994','Winstone, J.','N','A comprehensive text on the physical makeup and uses of gemstones from around the world.'); INSERT INTO title VALUES (2,'The Personal Adventures of a Travel Addict','2nd','Paragon Press', '1995','MacAdams, S.', 'Y', 'Describes two journeys through Persia and Asia Minor, including extensive commentary on the cultural differences between the areas visited and the author.'); INSERT INTO title VALUES (3,'World Geography: A Student''s Guide', '3rd', 'Prentice Hall', '1992', 'Patel, M.','N', 'An accessible text on geography whose purpose is to provide an introduction to the nature and scope of the subject.'); INSERT INTO title VALUES (4,'The Adventures of Katy Kelly',NULL,'Pergamon Press', '1984', 'James, G.', 'Y', 'Katy, a teenage schoolgirl, finds herself lost and frightened after her family''s move to a foreign country she has never before visited leads to her being kidnapped'); INSERT INTO title VALUES (5,'Magic and Music',NULL, 'Pergamon Press', '1978','Harris, T.', 'Y', 'A moving story of how a young boy who aspires to becoming a professional musician is inspired by a series of encountered with a groups of magicians.'); INSERT INTO title VALUES (6,'Basic Mathematics', '2nd','Pergammon Press', '1988', 'Hudson, P.','N','A standard text in school mathematics including extensive examples and practice exercises.'); INSERT INTO title VALUES (7,'Basic Mathematics', '4th','Pergamon Press', '1999', 'Hudson, P.','N','A standard text in school mathematics including extensive examples and practice exercises.'); PROMPT Insert into book INSERT INTO book VALUES (1,1, 28,'01-Aug-86', 18.97,NULL); INSERT INTO book VALUES (2,1, 0,'01-Aug-86', 18.97,NULL); INSERT INTO book VALUES (3,2, 10,'08-Aug-95', 8.50,NULL); INSERT INTO book VALUES (4,3, 21,'15-Aug-93', 32.50,NULL); INSERT INTO book VALUES (5,3, 21,'10-Oct-95', 34.99,NULL); INSERT INTO book VALUES (6,3, 21,'10-Oct-95', 34.99,NULL); INSERT INTO book VALUES (7,4, 21,'12-Dec-85', 4.99,NULL); INSERT INTO book VALUES (8,4, 21,'12-Dec-85', 4.99,NULL); INSERT INTO book VALUES (9,4, 21,'31-Aug-90', 5.75,NULL); INSERT INTO book VALUES (10,4,21,'31-Aug-90', 5.75,NULL); INSERT INTO book VALUES (11,5,14,'21-Jun-79', 3.50,NULL); INSERT INTO book VALUES (12,5,14,'12-Aug-84', 3.99,NULL); INSERT INTO book VALUES (13,6,21,'01-Aug-89', 8.99,NULL); INSERT INTO book VALUES (14,6,21,'01-Aug-89', 8.99,NULL); INSERT INTO book VALUES (15,7,21,'18-Aug-00', 12.99,NULL); INSERT INTO book VALUES (16,7,21,'18-Aug-00', 12.99,NULL); PROMPT insert into loan INSERT INTO loan VALUES (1000000001,1, '01-Jun-05', '29-Jun-05', '01-Jul-05'); INSERT INTO loan VALUES (1000000001,6, '01-Jun-05', '22-Jun-05', NULL); INSERT INTO loan VALUES (1000000002,5, '15-Jan-05', '07-Feb-05', '22-Feb-05'); INSERT INTO loan VALUES (1000000002,1, '01-Jul-05', '29-Jul-05', '27-Jul-05'); INSERT INTO loan VALUES (1000000003,14,'01-Oct-05', '29-Oct-05', '29-Oct-05'); INSERT INTO loan VALUES (1000000003,5, '8-Mar-05', '29-Mar-05', '01-Apr-05'); INSERT INTO loan VALUES (1000000004,16,'01-Oct-05', '29-Oct-05', '28-Oct-05'); INSERT INTO loan VALUES (1000000004,9, '10-Jun-05', '01-Jul-05', '03-Jul-05'); INSERT INTO loan VALUES (1000000005,12,'12-Jul-05', '26-Jul-05', NULL); INSERT INTO loan VALUES (1000000006,11,'14-Sep-05', '28-Sep-05', '24-Sep-05'); INSERT INTO loan VALUES (1000000007,1, '12-Sep-05', '10-Oct-05', NULL); INSERT INTO loan VALUES (1000000007,4, '01-Sep-05', '27-Sep-05', NULL); INSERT INTO loan VALUES (1000000008,13,'01-Oct-05', '22-Oct-05', '22-Oct-05'); INSERT INTO loan VALUES (1000000008,15,'01-Oct-05', '29-Oct-05', '29-Oct-05'); INSERT INTO loan VALUES (1000000001,3, '09-Mar-05', '19-Mar-05', '25-Mar-05'); INSERT INTO loan VALUES (1000000004,3, '25-Mar-05', '04-Apr-05', '29-Apr-05'); INSERT INTO loan VALUES (1000000005,3, '29-Apr-05', '09-May-05', '15-May-05'); INSERT INTO loan VALUES (1000000006,3, '15-May-05', '25-May-05', '03-Sep-05'); INSERT INTO loan VALUES (1000000007,3, '14-Sep-05', '24-Sep-05', '24-Sep-05'); INSERT INTO loan VALUES (1000000008,3, '04-Sep-05', '14-Sep-05', '15-Sep-05'); INSERT INTO loan VALUES (1000000003,3, '15-Sep-05', '25-Sep-05', NULL); INSERT INTO reservation VALUES(1000000005,7,'03-Oct-05','31-Oct-05','28-Oct-05'); INSERT INTO reservation VALUES(1000000007,7,'04-Oct-05','31-Oct-05','29-Oct-05'); INSERT INTO reservation VALUES(1000000004,2,'15-Mar-05','30-Apr-05','25-Mar-05'); INSERT INTO reservation VALUES(1000000005,2,'16-Mar-05','30-Apr-05','29-Apr-05'); INSERT INTO reservation VALUES(1000000006,2,'18-Apr-05','30-May-05','15-May-05'); INSERT INTO reservation VALUES(1000000008,2,'15-May-05','30-Jul-05', NULL); INSERT INTO reservation VALUES(1000000007,6,'04-Oct-05','31-Oct-05', NULL); commit; select * from member; select titleid, title, edition from title; select * from book; select * from loan order by bookid, loan_date; select * from reservation;