Answered step by step
Verified Expert Solution
Link Copied!

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

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_2

Step: 3

blur-text-image_3

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

Systems Analysis and Design in a Changing World

Authors: John W. Satzinger, Robert B. Jackson, Stephen D. Burd

7th edition

1305117204, 176902031, 9780176902032, 9781305465268 , 978-1305117204

More Books

Students also viewed these Databases questions

Question

2. Discuss the evidence for psychopathy as a heritable disorder.

Answered: 1 week ago

Question

What is meant by a tooltip? How does it show up?

Answered: 1 week ago

Question

What two types of input controls are included in groups?

Answered: 1 week ago