The relational schema of the database, which is about the Library Management System, is provided below:...

 

Transcribed Image Text:

The relational schema of the database, which is about the Library Management System, is provided below: BOOK BookID, Title, Author, Publisher, PublishDate, ISBN, Category, Language, TotalCopies, AvailableCopies BORROWING BorrowingID, BookID, MemberID, BorrowDate, ReturnDate, FineAmount MEMBER MemberID, Name, Address, Phone Number, Email, MembershipStatus, MembershipDate [Primary key is underlined; the foreign key is dotted underlined] Based on the relational schema, you are required to produce the following: Create a Database BookLoan or any suitable name. 1) 2) 3) 4) Create all relevant tables as required. Choose appropriate data types for each attribute. Indicate the primary key and foreign key in each table created. Produce sample records for each table that observes the Referential Integrity Constraint. The data inserted must satisfy the following constraints: There are at least FIVE books for each category (should have more than THREE categories), and each book has at least TWO copies available for loan. Each library member can borrow more than one book and is not restricted to borrowing books of a particular category or author. Make sure you create the relationships between the tables. The maximum and minimum number of records per table is based on the above mentioned constraints. Write SQL query and its result based on the records added earlier for the following tasks: (one query per question) a) Retrieve the book with the title "The Catcher in the Rye" b) Retrieve all members with a membership status of "Active" c) Retrieve all members who have borrowed at least ONE book d) Retrieve the total number of borrowed books e) Retrieve the total number of available books f) Retrieve the top 5 most borrowed books g) Retrieve all overdue books h) Retrieve the total fine amount owed by all members i) Retrieve the most popular category of books j) Delete all borrowing records that have a fine amount greater than RM10 5) Create ONE Form to handle the Borrowing-Entry. The form must be able to enter and display data. The relational schema of the database, which is about the Library Management System, is provided below: BOOK BookID, Title, Author, Publisher, PublishDate, ISBN, Category, Language, TotalCopies, AvailableCopies BORROWING BorrowingID, BookID, MemberID, BorrowDate, ReturnDate, FineAmount MEMBER MemberID, Name, Address, Phone Number, Email, MembershipStatus, MembershipDate [Primary key is underlined; the foreign key is dotted underlined] Based on the relational schema, you are required to produce the following: Create a Database BookLoan or any suitable name. 1) 2) 3) 4) Create all relevant tables as required. Choose appropriate data types for each attribute. Indicate the primary key and foreign key in each table created. Produce sample records for each table that observes the Referential Integrity Constraint. The data inserted must satisfy the following constraints: There are at least FIVE books for each category (should have more than THREE categories), and each book has at least TWO copies available for loan. Each library member can borrow more than one book and is not restricted to borrowing books of a particular category or author. Make sure you create the relationships between the tables. The maximum and minimum number of records per table is based on the above mentioned constraints. Write SQL query and its result based on the records added earlier for the following tasks: (one query per question) a) Retrieve the book with the title "The Catcher in the Rye" b) Retrieve all members with a membership status of "Active" c) Retrieve all members who have borrowed at least ONE book d) Retrieve the total number of borrowed books e) Retrieve the total number of available books f) Retrieve the top 5 most borrowed books g) Retrieve all overdue books h) Retrieve the total fine amount owed by all members i) Retrieve the most popular category of books j) Delete all borrowing records that have a fine amount greater than RM10 5) Create ONE Form to handle the Borrowing-Entry. The form must be able to enter and display data.