(Solved): Final Project Steps 1-4:Business Needs:Efficiently manage a growing library of books.Allow users to ...

Final Project Steps 1-4:

1. Business Needs:
   1. Efficiently manage a growing library of books.
   2. Allow users to add books, check-in, and check out books.
   3. keep track of each book’s ownership, borrowing history, and purchase details for each book.
   4. Create a system for grouping books based on the genre and subject content.
2. Rules:
   1. Every book needs its own ISBN.
   2. An individual may own a limited number of books at any given time.
   3. An individual is allowed to borrow a specified number of books concurrently.
   4. Books can be checked out for a set amount of time by users.

1. Assumptions:
   1. The small library primarily serves a few close friends.
   2. Books are not digitally available, only hard copies.

Data Dictionary:

Step 3(Relational Diagram and Normalization):

BookDetails Table:

Fields:

ISBN(Primary Key), Title, Author, Publisher, YearPublished, Pages, PurchasedLocation, PurchasedDate, SubjectMatter, Category.

This table holds the information about the details of each book.

BookOwnership Table:

Fields:

ISBN(Foreign Key, referencing BookDetails) Owner, Price.

This table captures the ownership history of each book, including the owner’s name and the price paid.

BorrowHistory Table:

Fields:

ISBN(Foreign Key, referencing BookDetails), Borrower, CheckOutDate, CheckInDate, BorrowDuration.

Every book's borrowing history is tracked in this table, which includes information about who borrowed it when it was checked out, when it was checked back in, and how long the loan lasted.

Foreign key constraints define the links between these tables. The BookDetails table uses the ISBN column as a primary key, and the BookOwnership and BorrowHistory tables use it as a foreign key. Data consistency and integrity are guaranteed by this relationship between the tables.

Final Project – Steps 5-7

You are maintaining a small library for you and your friends to share so everyone can add books, and check in and out books for their own use. This small library has grown lately and you would like to create a small database to manage it better. After careful consideration, you think that the database should store the following information (which is not presented in any particular order or structure):

Book authors
Boot title
ISBN
Publisher
Year when the book was published
Number of pages
Book owner
Price paid for the book
Where the book was bought
When the book was bought
Book's subject matter
Book category
Who borrowed the book
When the book was checked out
When the book was checked in
For how long a book was borrowed by one of your friends

For this section, continue with your answer for the previous steps and complete the following steps 5-7:

1. Determine indexes you will create for each table.
2. Write the DLL statements that you will use to create the tables and indexes. Don't forget about the constraints identified from your list of business rules.
3. Build the tables in the class DBMS.