ISBN Title AuthorIDAuthorFirstName
AuthorLastNamePublisherID
ISBN12345678
Introduction to DB 1 John Smith 1
ISBN87654321
Data Structures 2 Jane Doe 2
ISBN45678901Algorithms 3 Robert Johnson 1
Unnormalized Form: raw data
Library Catalog System (Edition_ID, Edition_Name, Publication_Date, Format, Language_ID, Language
EditionID EditionName PublicationDate
1NF: PK defined + atomic values
FFD: (ISBN, AuthorFName) - Edition_ID, Edition_Name, Publication_Date, Format, Language_ID, Langu
2NF: 1NF + no PD
PD1: ISBN - Title
PD2: AuthorID - Author_Fname, Author_LName, Author_BOD
3NF: 2NF + no TD
TD1: EditionID - EditionName, PublicationDate, Format
TD2: LanguageId - LanguageName
TD3: StatusID - Status
TD4: PublisherID - PublisherName, PublisherContact, PublisherLocation
TD5: GenreID - GenreName, GenreDescription
TD6: ReviewID - Rating, ReviewDate
TD7: MemberID - MemberFName, MemberLName, MemberContact, MemberEmail, MemberAddress,
TD8: LoanID - LoanDate, LoanStatus, ReturnDate, FineAmount, LoanStatus
TD9: ReservationID - ReservationDate, ReservationStatus
TD10: FineID - FineAmount, FineReason, FinePayStatus
TD11: ShelfID - ShelfNumber, ShelfCapacity, ShelfType
TD12: BranchID - BranchName, BranchLocation, BranchContact
TD13: LibrarianID - LibrarianFName, LibrarianLName, LibrarianContact
Table after 1NF:
EditionID EditionName PublicationDate
Tables after 2NF:
PK
ISBN Title 3NF✔
PK
AuthorID AuthorFName AuthorLName
ISBN AuthorID EditionID
Tables after 3NF:
PK
EditionID EditionName PublicationDate
PK
LanguageID LanguageName 3NF✔
PK
StatusID Status 3NF✔
PK
PublisherID PublisherName PublisherContact
PK
GenreID GenreName GenreDescription
PK
ReviewID Rating ReviewDate
PK
MemberID MemberFName MemberLName
PK
LoanID LoanDate DueDate
PK
ReservationID ReservationDate ReservationStatus
PK
FineID FineAmount FineReason
PK
ShelfID ShelfNumber ShelfCapacity
PK
BranchID BranchName BranchLocation
PK
LibrarianID LibrarianFName LibrarianLName
PK PK FK
ISBN AuthorID EditionID
PK
ISBN Title 3NF✔
PK
AuthorID AuthorFName AuthorLName
A list of Primary Keys.
Book_Edition: Each book edition is uniquely identified by its Edition ID.
Language: Language entities are distinguished by their Language_ID.
Availability Status: Availability status records are identified by their Status_ID.
Publisher: Publishers are uniquely identified by their Publisher_ID.
Genre: Genre entities are distinguished by their Genre_ID.
Review: Each review is uniquely identified by its Review_ID.
Book: Books are uniquely identified by their ISBN (International Standard Book Number).
Author: Authors are uniquely identified by their AuthorID.
Member: Each library member is uniquely identified by their Member_ID.
AuthorBookAssociation: The association between authors and books is identified by a composite prim
BookGenreAssociation: The association between books and genre is identified by a composite primary
Loan: Each loan record is uniquely identified by its Loan_ID.
Reservation: Reservations are uniquely identified by their Reservation_ID.
Fine: Fine records are uniquely identified by their Fine_ID.
Shelf: Shelves are uniquely identified by their Shelf ID.
Library Branch: Library branches are uniquely identified by their Branch_ID.
Librarian: Librarians are uniquely identified by their Librarian_ID.
PublisherName GenreID GenreName MemberID
MemberFirstName
MemberLastName
ContactInformation
Akashic Books 1 Technology 101 Alice White 355-567-6544
Dzanc Books 2 Science 102 Bob Green 355-888-765
Graywolf Press 3 Fiction null null null null
Format, Language_ID, Language_Name, Status_ID, Status, Publisher_ID, Publisher_Name, Publisher_Contact, Publisher_Locatio
blicationDate Format LanguageID LanguageName
te, Format, Language_ID, Language_Name, Status_ID, Status, Publisher_ID, Publisher_Name, Publisher_Contact, Publisher_Loc
MemberEmail, MemberAddress, MembershipStatus
Format LanguageID LanguageName StatusID
AuthorBOD 3NF✔
EditionName PublicationDate Format LanguageID
Format 3NF✔
PublisherLocation 3NF✔
3NF✔
3NF✔
MemberContact MemberEmail MemberAddress MembershipStatus
ReturnDate FineAmount LoanStatus 3NF✔
3NF✔
FinePayStatus 3NF✔
ShelfType 3NF✔
BranchContact 3NF✔
LibrarianContact 3NF✔
FK FK FK FK
LanguageID StatusID PublisherID GenreID
AuthorBOD 3NF✔
rd Book Number).
identified by a composite primary key consisting of the foreign keys BookID and AuthorID.
entified by a composite primary key consisting of the foreign keys BookID and GenreID.
MembershipStatus LoanID LoanDate DueDate ReturnDate FineAmountReservationID
Active 201 1/1/2023 1/15/2023 1/10/2023 $5.00 301
Suspended 202 2/1/2023 2/15/2023 null null 302
null null null null null null null
her_Contact, Publisher_Location, Genre_ID, Genre_Name, Genre_Description, Review_ID, Rating, Review_Date, Title, AuthorID
StatusID Status PublisherID PublisherName
ublisher_Contact, Publisher_Location, Genre_ID, Genre_Name, Genre_Description, Review_ID, Rating, Review_Date, Title, Auth
Status PublisherID PublisherName
LanguageName StatusID Status
embershipStatus 3NF✔
FK FK FK
ReviewID MemberID LoanID
A list of Foreign Keys.
Edition_ID (Potentially a foreign key linking to Book table)
Language_ID (Potentially a foreign key linking to Book table)
Availability_Status_ID (Potentially a foreign key linking to Book table)
Publisher_ID (Potentially a foreign key linking to Book table)
Genre_ID (Potentially a foreign key linking to Book table)
Review_ID (Potentially a foreign key linking to Book and Member tables)
ISBN (Potentially a foreign key linking to Author, Reservation, Shelf table)
Member_ID (Potentially a foreign key linking to Fine, Reservation, Loan table)
Branch_ID (Potentially a foreign key linking to Member, Librarian, Shelf table)
Author_ID (Potentially a foreign key linking to Author table)
ReservationDate Status ShelfID ShelfNumber Location Capacity LibraryBranchID
3/1/2023 Pending 501 101 Main 100 1
3/5/2023 Active 502 201 East 50 2
null null null null null null null
ng, Review_Date, Title, AuthorID, Author_FName, Author_LName, Author_BOD, Member_ID, Member_FName, Member_Lnam
blisherName PublisherContact PublisherLocation GenreID
Rating, Review_Date, Title, AuthorFName, Author_LName, Author_BOD, Member_ID, Member_FName, Member_Lname, Mem
PublisherContact PublisherLocation GenreID GenreName
PublisherID PublisherName PublisherContact PublisherLocation
FK FK FK FK
ReservationID FineID ShelfID BranchID
ISBNNumberCoverImageID EditionID LanguageID StatusID
1 1 2 1 1
2 1 3 1 2
3 1 1 1 3
Member_FName, Member_Lname, Member_Contact, Member_Email, Member_Address, Membership_Status, AuthorBookAsso
GenreName GenreDescription ReviewID Rating
_FName, Member_Lname, Member_Contact, Member_Email, Member_Address, Membership_Status, AuthorBookAssociation_
GenreDescription ReviewID Rating
blisherLocation GenreID GenreName GenreDescription
FK
LibrarianID 3NF✔
bership_Status, AuthorBookAssociation_ID, BookGenreAssociation_ID, Loan_ID, Due_Date, Return_Date, Fine_Amount, Loan_S
ReviewDate ISBN Title
Status, AuthorBookAssociation_ID, BookGenreAssociation_ID, Loan_ID, Due_Date, Return_Date, Fine_Amount, Loan_Status, R
PK1 PK2
ReviewDate ISBN Title AuthorID
ReviewID Rating ReviewDate MemberID
urn_Date, Fine_Amount, Loan_Status, Reservation_ID, Reservation_Date, Reservation_Status, ISBN, Fine_ID, Fine_Amount, Fine
AuthorID AuthorFName AuthorLName AuthorBOD
e, Fine_Amount, Loan_Status, Reservation_ID, Reservation_Date, Reservation_Status, Fine_ID, Fine_Amount, Fine_Reason, Fin
AuthorFName AuthorLName AuthorBOD
MemberFName MemberLName MemberContact
SBN, Fine_ID, Fine_Amount, Fine_Reason, Fine_Pay_Status, Shelf_ID, Shelf_Number, Shelf_Capacity, Shelf_Type, Branch_ID, Br
MemberID MemberFName MemberLName
Fine_Amount, Fine_Reason, Fine_Pay_Status, Shelf_ID, Shelf_Number, Shelf_Capacity, Shelf_Type, Branch_ID, Branch_Name, B
MemberID MemberFName MemberLName MemberContact
MemberEmail MemberAddress MembershipStatus LoanID
acity, Shelf_Type, Branch_ID, Branch_Name, Branch_Location, Branch_Contact, Librarian_ID, Librarian_FName, Librarian_LNam
MemberContact MemberEmail MemberAddress MembershipStatus
pe, Branch_ID, Branch_Name, Branch_Location, Branch_Contact, Librarian_ID, Librarian_FName, Librarian_LName, Librarian_C
emberContact MemberEmail MemberAddress MembershipStatus
LoanDate DueDate ReturnDate
brarian_FName, Librarian_LName, Librarian_Contact)
embershipStatus LoanID LoanDate DueDate
e, Librarian_LName, Librarian_Contact)
LoanID LoanDate DueDate ReturnDate
FineAmount LoanStatus ReservationID ReservationDate
ReturnDate FineAmount LoanStatus ReservationID
FineAmount LoanStatus ReservationID
servationDate ReservationStatus FineID FineAmount
servationID ReservationDate ReservationStatus FineID
ReservationDate ReservationStatus FineID FineAmount
FineReason FinePayStatus ShelfID ShelfNumber
FineAmount FineReason FinePayStatus ShelfID
FineReason FinePayStatus ShelfID
elfNumber ShelfCapacity ShelfType BranchID
ShelfNumber ShelfCapacity ShelfType
ShelfNumber ShelfCapacity ShelfType BranchID
BranchName BranchLocation BranchContact LibrarianID
BranchID BranchName BranchLocation BranchContact
BranchName BranchLocation BranchContact
LibrarianFName LibrarianLName LibrarianContact
anchContact LibrarianID LibrarianFName LibrarianLName
LibrarianID LibrarianFName LibrarianLName LibrarianContact
LibrarianContact
rarianContact 2NF✔
