delete delete delete delete delete delete delete delete delete from from from from from from from from from transaction; physical_item; dvd; book; catalog_item; library_card; branch; customer; library; Insert into LIBRARY (LIBRARY_ID, LIBRARY_NAME, LIBRARY_PHONE, LIBRARY_ADDRESS) Values (1, 'New York Public Library', '212-555-1234', '476 5th Avenue, New York, NY'); Insert into LIBRARY (LIBRARY_ID, LIBRARY_NAME, LIBRARY_PHONE, LIBRARY_ADDRESS) Values (2, 'Baltimore County Library', '443-555-3456', '320 York Road, Towson, MD'); Insert into LIBRARY (LIBRARY_ID, LIBRARY_NAME, LIBRARY_PHONE, LIBRARY_ADDRESS) Values (3, 'UMUC Library', '240-684-2020', '1616 McCormick Dr, Largo, MD'); Insert into LIBRARY (LIBRARY_ID, LIBRARY_NAME, LIBRARY_PHONE, LIBRARY_ADDRESS) Values (4, 'Virtual Library', '202-202-2020', 'Online'); Insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CUSTOMER_STREET, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_ZIP) Values (1, 'Christian', 'Walker', '96 Clark Drive', 'Hicksvile', 'NY', '11801'); Insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CUSTOMER_STREET, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_ZIP) Values (2, 'Deena', 'Pilgrim', '838 Orange Street', 'Frederick', 'MD', '21701'); Insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CUSTOMER_STREET, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_ZIP) Values (3, 'Calista', 'Secor', '9330 El Dorado Lane', 'Temple Hills', 'MD', '20748'); Insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CUSTOMER_STREET, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_ZIP) Values (4, 'Emile', 'Cross', '387 Circle Ave', 'Vincentown', 'NJ', '08088'); Insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CUSTOMER_STREET, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_ZIP) Values (5, 'Johnny', 'Royalle', '9948 Campfire Drive', 'East Elmhurst', 'NY', '11369'); Insert into CUSTOMER (CUSTOMER_ID, CUSTOMER_FIRSTNAME, CUSTOMER_LASTNAME, CUSTOMER_STREET, CUSTOMER_CITY, CUSTOMER_STATE, CUSTOMER_ZIP) Values (6, 'Harley', 'Cohen', '96 Vine Street', 'Bowie', 'MD', '20715'); Insert into BRANCH (BRANCH_ID, BRANCH_NAME, BRANCH_PHONE, BRANCH_ADDRESS, LIBRARY_ID) Values (101, '125th Street Library', '212-555-2345', '224 E 125th St, New York, NY', 1); Insert into BRANCH (BRANCH_ID, BRANCH_NAME, BRANCH_PHONE, BRANCH_ADDRESS, LIBRARY_ID) Values (102, 'Grand Central Library', '212-555-3456', '135 E 46th St, New York, NY', 1); Insert into BRANCH (BRANCH_ID, BRANCH_NAME, BRANCH_PHONE, BRANCH_ADDRESS, LIBRARY_ID) Values (201, 'Essex Branch', '443-555-4567', '1100 Eastern Blvd, Essex, MD', 2); Insert into BRANCH (BRANCH_ID, BRANCH_NAME, BRANCH_PHONE, BRANCH_ADDRESS, LIBRARY_ID) Values (202, 'Woodlawn Branch', '443-555-5678', '1811 Woodlawn Dr, Woodlawn, MD', 2); Insert into BRANCH (BRANCH_ID, BRANCH_NAME, BRANCH_PHONE, BRANCH_ADDRESS, LIBRARY_ID) Values (301, 'UMUC Library Largo', '240-684-2020', '1616 McCormick Dr, Largo, MD', 3); Insert into BRANCH (BRANCH_ID, BRANCH_NAME, BRANCH_PHONE, BRANCH_ADDRESS, LIBRARY_ID) Values (302, 'McKeldin Library', '301-405-9046', 'UMD, College Park, MD', 3); Insert into LIBRARY_CARD (LIBRARY_ID, CUSTOMER_ID, LIBRARY_CARD_ID, CARD_NUMBER, PIN, DATE_EXPIRE) Values (1, 1, 1, '16777216', '1234', TO_DATE('12/31/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into LIBRARY_CARD (LIBRARY_ID, CUSTOMER_ID, LIBRARY_CARD_ID, CARD_NUMBER, PIN, DATE_EXPIRE) Values (1, 4, 2, '33554432', '8192', TO_DATE('07/04/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into LIBRARY_CARD (LIBRARY_ID, CUSTOMER_ID, LIBRARY_CARD_ID, CARD_NUMBER, PIN, DATE_EXPIRE) Values (2, 2, 3, '10485760', '8888', TO_DATE('02/29/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into LIBRARY_CARD (LIBRARY_ID, CUSTOMER_ID, LIBRARY_CARD_ID, CARD_NUMBER, PIN, DATE_EXPIRE) Values (2, 3, 4, '20971520', '0911', TO_DATE('05/17/2021 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into LIBRARY_CARD (LIBRARY_ID, CUSTOMER_ID, LIBRARY_CARD_ID, CARD_NUMBER, PIN, DATE_EXPIRE) Values (3, 6, 5, '65536128', '4711', TO_DATE('10/09/2019 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into LIBRARY_CARD (LIBRARY_ID, CUSTOMER_ID, LIBRARY_CARD_ID, CARD_NUMBER, PIN, DATE_EXPIRE) Values (3, 5, 6, '32768256', '8008', TO_DATE('03/11/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into LIBRARY_CARD (LIBRARY_ID, CUSTOMER_ID, LIBRARY_CARD_ID, CARD_NUMBER, PIN, DATE_EXPIRE) Values (3, 4, 7, '16384512', '8192', TO_DATE('02/15/2020 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (1, 'Way of Kings', 'Fantasy', 'Tor Books', TO_DATE('08/31/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'BOOK'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (2, 'Words of Radiance', 'Fantasy', 'Tor Books', TO_DATE('03/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'BOOK'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (3, 'Inception', 'Science Fiction/Thriller', 'Warner Bros', TO_DATE('07/08/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DVD'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (4, 'Infinite Jest', 'Tragicomedy', 'Brown, Little, and Co.', TO_DATE('02/01/1996 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'BOOK'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (5, 'La La Land', 'Musical', 'Summit Entertainment', TO_DATE('12/09/2016 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DVD'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (6, 'Moonrise Kingdom', 'Drama Comedy', 'Focus Features', TO_DATE('05/25/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DVD'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (7, 'The Name of the Wind', 'Fantasy', 'DAW Books', TO_DATE('03/27/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'BOOK'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (8, 'The Social Network', 'Drama', 'Columbia Pictures', TO_DATE('10/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DVD'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (9, 'The Secret Life of the Brain', 'Fantasy', 'DAW Books', TO_DATE('03/27/2007 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'BOOK'); Insert into CATALOG_ITEM (CATALOG_ITEM_ID, TITLE, DESCRIPTION, PUBLISHER, RELEASE_DATE, TYPE) Values (10, 'The Secret Life of the Brain', 'Drama', 'Columbia Pictures', TO_DATE('10/01/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'DVD'); Insert into BOOK (ISBN, PAGES, CATALOG_ITEM_ID) Values ('9780765365279', 1280, 1); Insert into BOOK (ISBN, PAGES, CATALOG_ITEM_ID) Values ('9780765365286', 1328, 2); Insert into BOOK (ISBN, PAGES, CATALOG_ITEM_ID) Values ('9780316066525', 1079, 4); Insert into BOOK (ISBN, PAGES, CATALOG_ITEM_ID) Values ('9780756404079', 722, 7); Insert into BOOK (ISBN, PAGES, CATALOG_ITEM_ID) Values ('9990756404079', 700, 9); Insert into DVD (LENGTH, CATALOG_ITEM_ID) Values ('2:28', 3); Insert into (LENGTH, Values ('2:08', Insert into (LENGTH, Values ('1:34', Insert into (LENGTH, Values ('2:01', Insert into (LENGTH, Values ('2:00', DVD CATALOG_ITEM_ID) 5); DVD CATALOG_ITEM_ID) 6); DVD CATALOG_ITEM_ID) 8); DVD CATALOG_ITEM_ID) 10); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 1, 1, 1, TO_DATE('02/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 1, 2, 2, TO_DATE('03/15/2012 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 2, 3, 1, TO_DATE('03/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 2, 4, 2, TO_DATE('03/04/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 4, 5, 1, TO_DATE('02/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 4, 6, 2, TO_DATE('02/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 4, 7, 3, TO_DATE('10/09/2015 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 7, 8, 1, TO_DATE('02/01/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 7, 9, 2, TO_DATE('04/15/2013 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 7, 10, 3, TO_DATE('12/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 7, 11, 4, TO_DATE('12/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (101, 7, 12, 5, TO_DATE('12/20/2014 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (102, 3, 13, 1, TO_DATE('07/08/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, DATE_PURCHASED) Values (102, 3, 14, 2, TO_DATE('07/08/2011 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (102, 3, 15, 3, TO_DATE('07/08/2011 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (102, 5, 16, 1, TO_DATE('03/01/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (102, 6, 17, 1, TO_DATE('12/12/2013 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (102, 8, 18, 1, TO_DATE('10/01/2011 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (102, 8, 19, 2, TO_DATE('10/01/2011 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (102, 8, 20, 3, TO_DATE('02/23/2013 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 1, 21, 1, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 1, 22, 2, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 2, 23, 1, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 2, 24, 2, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 3, 25, 1, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 4, 26, 1, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 5, 27, 1, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 6, 28, 1, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 7, 29, 1, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 7, 30, 2, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, DATE_PURCHASED) Values (201, 7, 31, 3, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (201, 8, 32, 1, TO_DATE('06/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 1, 33, 1, TO_DATE('03/03/2011 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 2, 34, 1, TO_DATE('08/19/2014 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 3, 35, 1, TO_DATE('08/19/2014 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 3, 36, 2, TO_DATE('09/21/2015 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 3, 37, 3, TO_DATE('09/21/2015 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 4, 38, 1, TO_DATE('11/25/2014 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, (202, 4, 39, 2, TO_DATE('02/23/2015 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 5, 40, 1, TO_DATE('12/12/2016 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 6, 41, 1, TO_DATE('05/15/2015 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (202, 8, 42, 1, TO_DATE('09/15/2014 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 1, 43, 1, TO_DATE('09/11/2011 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 2, 44, 1, TO_DATE('01/12/2015 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 3, 45, 1, TO_DATE('09/09/2012 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 3, 46, 2, TO_DATE('09/09/2012 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 3, 47, 3, TO_DATE('07/14/2014 00:00:00', HH24:MI:SS')); 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 3, 48, 4, TO_DATE('10/19/2016 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 4, 49, 1, TO_DATE('01/20/2013 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 4, 50, 2, TO_DATE('02/05/2015 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 5, 51, 1, TO_DATE('07/11/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 6, 52, 1, TO_DATE('07/11/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 7, 53, 1, TO_DATE('04/09/2016 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (301, 8, 54, 1, TO_DATE('12/17/2015 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (302, 1, 55, 1, TO_DATE('03/08/2016 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, DATE_PURCHASED) Values (302, 2, 56, 1, TO_DATE('07/12/2016 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (302, 4, 57, 1, TO_DATE('12/01/2016 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (302, 7, 58, 1, TO_DATE('01/14/2017 00:00:00', HH24:MI:SS')); Insert into PHYSICAL_ITEM (BRANCH_ID, CATALOG_ITEM_ID, PHYSICAL_ITEM_ID, DATE_PURCHASED) Values (102, 7, 59, 1, TO_DATE('01/14/2017 00:00:00', HH24:MI:SS')); 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY COPY_NUMBER, 'MM/DD/YYYY Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (1, TO_DATE('02/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/02/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('02/28/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 1); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (2, TO_DATE('02/28/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/28/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 2); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (3, TO_DATE('04/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 13); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (4, TO_DATE('04/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/30/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1, 20); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (5, TO_DATE('03/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('04/03/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('03/28/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 1); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (6, TO_DATE('12/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/12/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 2, 3); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (7, TO_DATE('12/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/12/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 2, 4); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (8, TO_DATE('07/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 21); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (9, TO_DATE('08/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 22); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (10, TO_DATE('09/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/19/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('09/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 3, 29); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (11, TO_DATE('09/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 3, 42); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (12, TO_DATE('05/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/31/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 42); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (13, TO_DATE('05/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/31/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 41); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (14, TO_DATE('05/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('06/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('05/31/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 4, 40); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (15, TO_DATE('11/11/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/11/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6, 51); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (16, TO_DATE('12/09/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/09/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), NULL, 6, 45); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (17, TO_DATE('12/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/12/2018 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('12/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 6, 51); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (18, TO_DATE('07/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/14/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('07/20/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 51); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (19, TO_DATE('07/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('08/21/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('07/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 7, 54); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (20, TO_DATE('10/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/12/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 3); Insert into TRANSACTION (TRANSACTION_ID, DATE_CHECKOUT, DATE_DUE, DATE_RETURNED, LIBRARY_CARD_ID, PHYSICAL_ITEM_ID) Values (21, TO_DATE('10/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('11/10/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('10/22/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 2, 4); COMMIT; Name: Date: Question 1: (25 Points) The diagram below (also attached in DBST651_final_part2_ERD.png ) is an ERD for a public library database. NOTE: There are two types of catalog items – Book and DVD. A customer can check out one item at a time. Study the ERD and answer the following questions: I. II. III. IV. Identify all relationships and specify cardinality and business rules. For example: 1:M between Library and Branch: a library has many branches; a branch belongs to one and only one library. For each entity, identify primary key and foreign key, if any. For foreign key, also specify parent entity and matching attribute in parent entity. Write SQL DDL statements to create tables in Oracle and also implement primary key, foreign key, and NOT NULL constraint. Include all columns listed with the correct data type. This step is important, as in Question 2 you will run INSERT statements to populate the tables you created and then write SELECT statement to query those tables. Write SQL statement for the following scenario: a. Add a new customer Eric Short with Customer_ID 10 and Customer_Zip 23456. b. Save changes permanently. c. d. e. f. Change customer Eric Short zip from 23456 to 20231. Cancel the change made in step c, restore data to its original status prior to step c. Delete customer Eric Short. Save changes permanently. For SQL code, submit both source statements and results of running your statements. Question 2: (25 Points) Continue Question 1 above. It is important for you to create those tables with the exact table/column name and exact column data type/length as shown in the ERD. Run the script final_Part2_q2.sql attached and then respond the following SQL problems: Create SQL statements for the following scenarios. Your response should include SQL statement, output and any other assumptions you have made to arrive at the solution. Please provide a single SQL query for each problem. a. For each book in the catalog, display its title, publisher, ISBN, release date, number of pages, and whether it's carried in any library ("Yes" or "No"). Sort results by title. Make sure there is no duplicate information. b. For a book with ISBN 9780756404079, display it’s title, publisher, ISBN, which library (not branch) has it, and total number of copies each library has. Do not show libraries that don’t carry it. Sort results by number of copies in decending order. c. For each DVD in the catalog, display it’s title, length, release_date, and how many times it has been checked out by all customers across all libraries. Include those that have not been checked out yet (display as 0). Sort results by title. d. Display those customers who have checked out the same catalog item more than once. Show customer name, and for each catalog item show title, type, number of check out times, first check out date and last check out date for the item. Sort results by customer first/last name and catalog item title. e. Show all customers who have checked more books than DVDs. Display customer name, total book checkouts and total DVD checkouts. Sort results by customer first name and last name.
Purchase answer to see full attachment