for the following database:

create PL/SQL (3 procdures, 3 tirggers,3 functions, packages,3 users, tranactions, XML).

with screenshots please

library management system ERD

database tables

CREATE TABLE Status ( code INTEGER, description CHAR(30), PRIMARY KEY (code) ); CREATE TABLE Media( media_id INTEGER, code INTEGER, PRIMARY KEY (media_id), FOREIGN KEY (code) REFERENCES Status ); CREATE TABLE LIB_Book( ISBN CHAR(14), title VARCHAR2(64), author VARCHAR2(64), year NUMBER, dewey NUMBER, price NUMBER, PRIMARY KEY (ISBN) ); CREATE TABLE BookMedia( media_id INTEGER, ISBN CHAR(14), PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (ISBN) REFERENCES LIB_Book); CREATE TABLE LIB_Customer( ID INTEGER, name CHAR(64), addr CHAR(256), DOB CHAR(10), phone CHAR(30), username CHAR(16), password CHAR(32), PRIMARY KEY (ID), UNIQUE (username) ); CREATE TABLE LIB_Card( num INTEGER, fines REAL, ID INTEGER, PRIMARY KEY (num), FOREIGN KEY (ID) REFERENCES LIB_Customer ); CREATE TABLE LIB_Checkout( media_id INTEGER, num INTEGER, since CHAR(10), until CHAR(10), PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (num) REFERENCES LIB_Card ); CREATE TABLE LIB_Location( name CHAR(64), addr CHAR(256), phone CHAR(30), PRIMARY KEY (name) ); CREATE TABLE LIB_Hold( media_id INTEGER, num INTEGER, name CHAR(64), until CHAR(10), queue INTEGER, PRIMARY KEY (media_id, num), FOREIGN KEY (name) REFERENCES LIB_Location, FOREIGN KEY (num) REFERENCES LIB_Card, FOREIGN KEY (media_id) REFERENCES Media ); CREATE TABLE Stored_In( media_id INTEGER, name char(64), PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media ON DELETE CASCADE, FOREIGN KEY (name) REFERENCES LIB_Location ); CREATE TABLE LIB_Librarian( eid INTEGER, ID INTEGER NOT NULL, Pay REAL, Loc_name CHAR(64) NOT NULL, PRIMARY KEY (eid), FOREIGN KEY (ID) REFERENCES LIB_Customer ON DELETE CASCADE, FOREIGN KEY (Loc_name) REFERENCES LIB_Location(name) ); CREATE TABLE Video( title CHAR(128), year INTEGER, director CHAR(64), rating REAL, price REAL, PRIMARY KEY (title, year) ); CREATE TABLE VideoMedia( media_id INTEGER, title CHAR(128), year INTEGER, PRIMARY KEY (media_id), FOREIGN KEY (media_id) REFERENCES Media, FOREIGN KEY (title, year) REFERENCES Video );

-----------------------------------------

data :

INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (60201, 'Jason L. Gray', '2087 Timberbrook Lane, Gypsum, CO 81637', '09/09/1958', '970-273-9237', 'jlgray', 'password1'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (89682, 'Mary L. Prieto', '1465 Marion Drive, Tampa, FL 33602', '11/20/1961', '813-487-4873', 'mlprieto', 'password2'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (64937, 'Roger Hurst', '974 Bingamon Branch Rd, Bensenville, IL 60106', '08/22/1973', '847-221-4986', 'rhurst', 'password3'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (31430, 'Warren V. Woodson', '3022 Lords Way, Parsons, TN 38363', '03/07/1945', '731-845-0077', 'wvwoodson', 'password4'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (79916, 'Steven Jensen', '93 Sunny Glen Ln, Garfield Heights, OH 44125', '12/14/1968', '216-789-6442', 'sjensen', 'password5'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (93265, 'David Bain', '4356 Pooh Bear Lane, Travelers Rest, SC 29690', '08/10/1947', '864-610-9558', 'dbain', 'password6'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (58359, 'Ruth P. Alber', '3842 Willow Oaks Lane, Lafayette, LA 70507', '02/18/1976', '337-316-3161', 'rpalber', 'password7'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (88564, 'Sally J. Schilling', '1894 Wines Lane, Houston, TX 77002', '07/02/1954', '832-366-9035', 'sjschilling', 'password8'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (57054, 'John M. Byler', '279 Raver Croft Drive, La Follette, TN 37766', '11/27/1954', '423-592-8630', 'jmbyler', 'password9'); INSERT INTO LIB_Customer(ID, name, addr, DOB, phone, username, password) VALUES (49312, 'Kevin Spruell', '1124 Broadcast Drive, Beltsville, VA 20705', '03/04/1984', '703-953-1216', 'kspruell', 'password10'); ---------------------------------------------------- INSERT INTO LIB_Card(num, fines, ID) VALUES ( 5767052, 0.0, 60201); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 5532681, 0.0, 60201); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 2197620, 10.0, 89682); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 9780749, 0.0, 64937); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 1521412, 0.0, 31430); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 3920486, 0.0, 79916); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 2323953, 0.0, 93265); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 4387969, 0.0, 58359); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 4444172, 0.0, 88564); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 2645634, 0.0, 57054); INSERT INTO LIB_Card(num, fines, ID) VALUES ( 3688632, 0.0, 49312); INSERT INTO LIB_Location(name, addr, phone) VALUES ('Texas Branch', '4832 Deercove Drive, Dallas, TX 75208', '214-948-7102'); INSERT INTO LIB_Location(name, addr, phone) VALUES ('Illinois Branch', '2888 Oak Avenue, Des Plaines, IL 60016', '847-953-8130'); INSERT INTO LIB_Location(name, addr, phone) VALUES ('Louisiana Branch', '2063 Washburn Street, Baton Rouge, LA 70802', '225-346-0068'); --------------------------------------------- INSERT INTO Status(code, description) VALUES (1, 'Available'); INSERT INTO Status(code, description) VALUES (2, 'In Transit'); INSERT INTO Status(code, description) VALUES (3, 'Checked Out'); INSERT INTO Status(code, description) VALUES (4, 'On Hold'); INSERT INTO Media( media_id, code) VALUES (8733, 1); INSERT INTO Media( media_id, code) VALUES (9982, 1); INSERT INTO Media( media_id, code) VALUES (3725, 1); INSERT INTO Media( media_id, code) VALUES (2150, 1); INSERT INTO Media( media_id, code) VALUES (4188, 1); INSERT INTO Media( media_id, code) VALUES (5271, 2); INSERT INTO Media( media_id, code) VALUES (2220, 3); INSERT INTO Media( media_id, code) VALUES (7757, 1); INSERT INTO Media( media_id, code) VALUES (4589, 1); INSERT INTO Media( media_id, code) VALUES (5748, 1); INSERT INTO Media( media_id, code) VALUES (1734, 1); INSERT INTO Media( media_id, code) VALUES (5725, 1); INSERT INTO Media( media_id, code) VALUES (1716, 4); INSERT INTO Media( media_id, code) VALUES (8388, 1); INSERT INTO Media( media_id, code) VALUES (8714, 1); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-0743289412', 'Lisey''s Story', 'Stephen King', 2006, 813, 10.0); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-1596912366', 'Restless: A Novel', 'William Boyd', 2006, 813, 10.0); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-0312351588', 'Beachglass', 'Wendy Blackburn', 2006, 813, 10.0); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-0156031561', 'The Places In Between', 'Rory Stewart', 2006, 910, 10.0); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-0060583002', 'The Last Season', 'Eric Blehm', 2006, 902, 10.0); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-0316740401', 'Case Histories: A Novel', 'Kate Atkinson', 2006, 813, 10.0); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-0316013949', 'Step on a Crack', 'James Patterson, et al.', 2007, 813, 10.0); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-0374105235', 'Long Way Gone: Memoirs of a Boy Soldier', 'Ishmael Beah', 2007, 916, 10.0); INSERT INTO LIB_Book(ISBN, title, author, year, dewey, price) VALUES ('978-0385340229', 'Sisters', 'Danielle Steel', 2006, 813, 10.0); ------------------------------------------- INSERT INTO BookMedia(media_id, ISBN) VALUES (8733, '978-0743289412'); INSERT INTO BookMedia(media_id, ISBN) VALUES (9982, '978-1596912366'); INSERT INTO BookMedia(media_id, ISBN) VALUES (3725, '978-1596912366'); INSERT INTO BookMedia(media_id, ISBN) VALUES (2150, '978-0312351588'); INSERT INTO BookMedia(media_id, ISBN) VALUES (4188, '978-0156031561'); INSERT INTO BookMedia(media_id, ISBN) VALUES (5271, '978-0060583002'); INSERT INTO BookMedia(media_id, ISBN) VALUES (2220, '978-0316740401'); INSERT INTO BookMedia(media_id, ISBN) VALUES (7757, '978-0316013949'); INSERT INTO BookMedia(media_id, ISBN) VALUES (4589, '978-0374105235'); INSERT INTO BookMedia(media_id, ISBN) VALUES (5748, '978-0385340229'); INSERT INTO LIB_Checkout(media_id, num, since, until) VALUES (2220, 9780749, '02/15/2007', '03/15/2007'); INSERT INTO Video(title, year, director, rating, price) VALUES ('Terminator 2: Judgment Day', 1991, 'James Cameron', 8.3, 20.0); INSERT INTO Video(title, year, director, rating, price) VALUES ('Raiders of the Lost Ark', 1981, 'Steven Spielberg', 8.7, 20.0); INSERT INTO Video(title, year, director, rating, price) VALUES ('Aliens', 1986, 'James Cameron', 8.3, 20.0); INSERT INTO Video(title, year, director, rating, price) VALUES ('Die Hard', 1988, 'John McTiernan', 8.0, 20.0); INSERT INTO VideoMedia(media_id, title, year) VALUES ( 1734, 'Terminator 2: Judgment Day', 1991); INSERT INTO VideoMedia(media_id, title, year) VALUES ( 5725, 'Raiders of the Lost Ark', 1981); INSERT INTO VideoMedia(media_id, title, year) VALUES ( 1716, 'Aliens', 1986); INSERT INTO VideoMedia(media_id, title, year) VALUES ( 8388, 'Aliens', 1986); INSERT INTO VideoMedia(media_id, title, year) VALUES ( 8714, 'Die Hard', 1988); INSERT INTO LIB_Hold(media_id, num, name, until, queue) VALUES (1716, 4444172, 'Texas Branch', '02/20/2008', 1); INSERT INTO LIB_Librarian(eid, ID, pay, Loc_name) Values (2591051, 88564, 30000.00, 'Texas Branch'); INSERT INTO LIB_Librarian(eid, ID, pay, Loc_name) Values (6190164, 64937, 30000.00, 'Illinois Branch'); INSERT INTO LIB_Librarian(eid, ID, pay, Loc_name) Values (1810386, 58359, 30000.00, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(8733, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(9982, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(1716, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(1734, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(4589, 'Texas Branch'); INSERT INTO Stored_In(media_id, name) VALUES(4188, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5271, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(3725, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(8388, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5748, 'Illinois Branch'); INSERT INTO Stored_In(media_id, name) VALUES(2150, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(8714, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(7757, 'Louisiana Branch'); INSERT INTO Stored_In(media_id, name) VALUES(5725, 'Louisiana Branch');