Final Project Steps 1-4:
Data Dictionary:
FIELD | DATA TYPE | DESCRIPTION |
ISBN | VARCHAR(13) | Unique Identifier for each book |
Title | VARCHAR(255) | Title of the book |
Author | VARCHAR(255) | Author of the book |
Publisher | VARCHAR(255) | Publisher of the book |
YearPublished | INT | The year when the book was published |
Pages | INT | Number of pages in the book |
Owner | VARCHAR(255) | Current owner |
Price | DECIMAL(10,2) | Price paid for the book |
PurchasedLocation | VARCHAR(255) | Location of purchased |
PurchasedDate | DATE | When was the book bought |
SubjectMatter | VARCHAR(255) | Genre |
Category | VARCHAR(255) | Category of the book |
Borrower | VARCHAR(255) | Who borrowed |
CheckOutDate | DATE | When was the book checked out |
CheckInDate | DATE | When was the book checked in |
BorrowDuration | INT | Borrow duration for book |
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: