Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

I mostly just need help normalizing the schema from the first page. INSTRUCTIONS: Please read through every instruction and question well, ANS questions on the

image text in transcribedimage text in transcribedimage text in transcribed

I mostly just need help normalizing the schema from the first page.

INSTRUCTIONS: Please read through every instruction and question well, ANS questions on the answer sheet provided, and turn in the question paper together with the sheets. All SQL query statements must be executed on a DBMS. Open-book and notes. 1. Convert the following relation into an equivalent collection of relations in 3" 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. (15 points) STUDENT (Sid: char(3), Firstname: char(10), Lastname: char(10). Status: char(10). (ISBN : char(6), Title : char(20), Publisher: char(20), YearPublished: numeric(5). Cover: char(4), DateBorrowed: date, DateOfReturn: date, DateReturned: date)) The following functional dependencies exist in the relation above. Sid > Firstname, Lastname, Status ISBN Title, Publisher, YearPublished, Cover, DateBorrowed, DateOfReturn, DateReturned ISBN, Sid > DateBorrowed, DateOfReturn, Datereturned Use SQL to create the normalized tables from your answer to question (1), specifying the primary keys for each table. You are ONLY required to submit the SQL statements used to create the tables. (15 points) Using SQL, insert the records (shown on a separate sheet) into the normalized tables created above. You are required to submit the SQL statements. (20 points) Use SQL statements to query the database created above. You are required to turn in the SQL query statements (2 points each) with their results (1 point each). (18 points) a) List the ISBN and titles of all the hard cover books. b) find out when (hint: YearPublished) 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. (3 points)

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Contemporary Issues In Database Design And Information Systems Development

Authors: Keng Siau

1st Edition

1599042894, 978-1599042893

More Books

Students also viewed these Databases questions