Consider the following database schema. It represents a database used to manage library books. Library Authors...

Transcribed Image Text:

Consider the following database schema. It represents a database used to manage library books. Library Authors NoBiblio: Integer (PK) LibraryName: Text Copies CopyNo: Integer(PK) NoBiblio:Integer(FK) NoLive:Integer(FK) Borrowing NoCopy :Integer(PK,FK) NoUser:Integer(PK,FK) NoAuthor: Integer (PK) AuthorName: Text Books BookNo;Integer Title:Text AuthorNo:Integer(FK) Users NoUser:Integer(PK) UserNo:Text You have to write different queries to evaluate your understanding of this scheme. 1) Write the 6 queries to create the different tables. 2) Write 6 data insertion queries, one for each table in the database. You can choose your ID numbers but the 6 queries must be consistent 3) Display the list of books (title) and the name of the associated author. 4) Display the title of the books of which there is at least one copy at UQAM (NoBiblio). 5) Display the names of users who have a current loan or who have already borrowed. 6) There would have been an error in the numbering of copies of books in UQAM libraries. Copies of books by the author "John Smith" were numbered between 1000 and 2000 when they should have been numbered between 5000 and 6000. You need to write a query that will update the copy numbers involved. Consider the following database schema. It represents a database used to manage library books. Library Authors NoBiblio: Integer (PK) LibraryName: Text Copies CopyNo: Integer(PK) NoBiblio:Integer(FK) NoLive:Integer(FK) Borrowing NoCopy :Integer(PK,FK) NoUser:Integer(PK,FK) NoAuthor: Integer (PK) AuthorName: Text Books BookNo;Integer Title:Text AuthorNo:Integer(FK) Users NoUser:Integer(PK) UserNo:Text You have to write different queries to evaluate your understanding of this scheme. 1) Write the 6 queries to create the different tables. 2) Write 6 data insertion queries, one for each table in the database. You can choose your ID numbers but the 6 queries must be consistent 3) Display the list of books (title) and the name of the associated author. 4) Display the title of the books of which there is at least one copy at UQAM (NoBiblio). 5) Display the names of users who have a current loan or who have already borrowed. 6) There would have been an error in the numbering of copies of books in UQAM libraries. Copies of books by the author "John Smith" were numbered between 1000 and 2000 when they should have been numbered between 5000 and 6000. You need to write a query that will update the copy numbers involved.