Homework 1 Answer all the following questions. Edit your answers in format of wo
ID: 3536257 • Letter: H
Question
Homework 1
Answer all the following questions. Edit your answers in format of word document and submit through assignment folders. The homework weights 5 points in total to your final grade.
The following is a database that stores library system information:
Libraries
Library_Id
Library_name
Location
10
Bethesda Regional Library
20
Rockville Regional Library
434 Curly Wood Street, Rockville 20851
30
Gaithersburg Regional Library
12773 Queens Orchard Road, Gaithersburg20878
Books
ISBN
Title
Author
Publisher
Library_Id
287136821
Computer Application
E. Johnson
Peterson & Brother
10
4857439
Essential Woodworking
D. Smith
Golden Publisher
20
83497392
Best Cooking
N. Parker
American Publish Corp
30
Patrons
User_id
First_name
Last_name
Register_date
Expriration_date
Library_Id
Loan_number
123
John
Woods
01-Jan-2010
01-Jan-2013
10
1
234
Mary
Power
02-Jun-2007
02-Jun-2012
20
1
567
Larry
Goldenberg
08-Aug-2009
08-Aug-2012
30
1
Book_transaction_records
Transaction_Id
Library_id
User_id
ISBN
Out_date
Due_date
Return_date
101
10
567
4857439
03-Aug-2011
12-Aug-2011
102
20
123
287136821
05-Aug-2011
20-Aug-2011
103
30
234
83497392
06-Aug-2011
30-Aug-2011
Based on the database provided on this page, answer or discuss the following questions (1 point each):
1. Identify informal queries and update operations that you would expect to apply to this database. At least five in total.
2. Specify all the relationship among the records.
3. Patrons and Librarians should use different views to look at the records. What are they?
4. What are the common views that can be seen by both Patrons and Librarians?
5. If a patron load a book from Bethesda Regional Library, how to update database? If a Patron returns a book, what records are updated?
Library_Id
Library_name
Location
10
Bethesda Regional Library
123 Main Street , Bethesda2081720
Rockville Regional Library
434 Curly Wood Street, Rockville 20851
30
Gaithersburg Regional Library
12773 Queens Orchard Road, Gaithersburg20878
Explanation / Answer
1.
Adding a new book to a particular library
user mapping to a particular library
issuing a book to a user
updating the return of a book from a user
2.
from library to Book_transaction_records one to many
from books to Book_transaction_records one to many
from library to Books one to many
from patrons to Book_transaction_records one to manyu
from library to patrons one to many
3.
Librarian can look at all views
Patrons should look at Books and their Transactions
4.
Libraries
Books
5.
// To add a book to library
Insert into Book values(isbn_value, title of book, author of book, pulisher name, 10 );
// To take a book from Bethesda Regional Library
insert into Book_transaction_records values(transaction_id, 10, user_id, isbn,out_date, Due_date,,);
// if a person returns a book
update Book_transaction_records set Return_date= %u201CDate of Return%u201D where Transaction_id = (select max(Transaction_id) from Book_transaction_records where ISBN = %u201Cbook ISBN%u201D and user_id = %u201Cuser_id%u201D);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.