Answered step by step
Verified Expert Solution
Question
1 Approved Answer
The relational schema of the database, which is about the Library Management System, is provided below: BOOK BookID, Title, Author, Publisher, PublishDate, ISBN, Category,
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.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started