Database System: Design, Implementation, and Management 12th Edition.
Please answer all questions using the Oracle 11 Software or SQL Database Software
Show all work for all given problems.
Please only answer 4, 5, 6!
. Convert the following relation into an equivalent collection of the relation in 3rd normal form (3NF) by writing down the schema of the resulting tables. The relation concerns student information. The information listed on the students includes details about their books borrowed from the library Student (Sid, firstname, lastname, Status, (ISBN, title, publisher, yearpublisher, cover, dateborrowed, datofReturn, datereturned)) 2. 3. Use SQL to create the normalized tables from the answer above and specifying the primary keys for each table. Using SQL, insert the records below into the normalized tables created above Sid Fname LnameStatus ISBN Title Publisher Year Cover Date Date Date published Borrowed Of retur en Returned 111 Eric Clapton Graduate 1 Systems Jeff Books 2011 Soft 3/25/2012 4/15/2012 4/13/2012 Hard 11/20/2012 11/25/2012 11/28/2012 Hard 5/22/2012 5/28/2012 5/30/2012 123 Scecurity 222 Judy Higgins Fresh22 World Simple 2012 444 History PUblishing 33- PublicJack & 55 finance Jake 2013 333 Mike Brown Graduate 444David Higgins Senior 22 World Simple 2012 Hard 12/10/2011 12/31/2011 1/28/2012 444 History PUlbishing 4. Use SQL statements to query the database created above a) List the ISBN and titles of all the hard cover books b) Find out when (hint: year published) Public finance was published and by which publisher c) Find the first name, last name and status of all graduate students. d) List the complete student table. Sort the records by last name in ascending order and then first name in descending order e) Find how many times World History has been borrowed f) Find the name (first and last names) of the student who has never borrowed a book from the library 5. Use SQL statements to perform the following database update/administrative tasks on the database created above a) Create a copy of the student table as EnrolledStudent b) Add and age field to the EnrolledStudent table. The field should hold 3 numeric digits with no decimal places and capable of taking no (null) values. c) David Higgins is a sophomore and not a senior. Make this change in the EnrolledStudent table d) Create a view named GraduateStudent to include all the graduate students from the EnrolledStudent table e) Delete all the graduate students from the EnrolledStudent table f) Remove the EnrolledStudent table 6. Use PL/SQL statement to create a stored procedure that takes one argument as a condition to retrieve all graduate students and display all details about the graduate students
