Question Title: Library Database System Basics
- What is the main purpose of the โSchool Library Systemโ database shown above?
- Why was โBookIDโ chosen as the primary key in the โBooksโ table?
- Why is โCopiesAvailableโ set as a Number data type instead of Text in the โBooksโ table?
- How does the โBookIDโ field connect the โBooksโ and โBorrowersโ tables?
- How would you set up the relationship between โBooksโ and โBorrowersโ in a DBMS like Microsoft Access?
Question Title: Advanced Library Database Operations
- Based on the sample data, which book has been borrowed more than once, and how can the database confirm this?
- What would happen in the DBMS if you tried to add a record to โBorrowersโ with a BookID that doesnโt exist in โBooksโ (e.g., BookID 4)?
Ruwan Suraweera Changed status to publish May 23, 2025
Answers and Descriptions:
- Answer: The main purpose is to track books and their borrowers, ensuring efficient management of library resources.
Description: This database helps librarians know which books are available, who has borrowed them, and when, reducing manual tracking errors and improving efficiency. - Answer: โBookIDโ is unique for each book, ensuring no two books have the same identifier, which prevents duplication.
Description: A primary key like โBookIDโ (e.g., 1, 2, 3) uniquely identifies each record. Titles or authors could repeat (e.g., multiple books by Tolkien), but โBookIDโ cannot. - Answer: โCopiesAvailableโ is a numeric value (e.g., 5, 3) that may need calculations (e.g., subtracting when borrowed), which requires a Number data type.
Description: If it were Text, you couldnโt perform math operations like reducing stock when a book is borrowed. Number ensures functionality in an electronic system. - Answer: โBookIDโ is the primary key in โBooksโ and a foreign key in โBorrowers,โ linking each borrowed book to its details in the โBooksโ table.
Description: For example, BorrowerID 101 (Alice) has BookID 1, which matches โThe Hobbitโ in the โBooksโ table, showing what she borrowed. - Answer: In the DBMS, drag โBookIDโ from the โBooksโ table to โBookIDโ in the โBorrowersโ table in the Relationships window to create a one-to-many link.
Description: One book (e.g., BookID 1) can be borrowed by many students (e.g., Alice and Clara), and the DBMS enforces this link to maintain data integrity.
Question Title: Advanced Library Database Operations
Answers and Descriptions:
- Answer: โThe Hobbitโ (BookID 1) has been borrowed twice (by Alice and Clara), confirmed by checking the โBookIDโ in the โBorrowersโ table.
Description: The relational link shows BookID 1 appears in two records (BorrowerID 101 and 103), proving its popularity without needing manual counts. - Answer: The DBMS would reject the record or show an error because the foreign key โBookIDโ must match an existing primary key in โBooksโ to maintain referential integrity.
Description: If BookID 4 isnโt in โBooks,โ adding it to โBorrowersโ would break the relationship (e.g., no book details exist), so the system prevents this.
Ruwan Suraweera Changed status to publish May 22, 2025