Week 8 - Logical design (Part 1) Flashcards
(35 cards)
What is the default mapping from ER-models to database schemas?
By default, every entity and relationship becomes its own table.
What is the default 3-table schema for the Book-Borrow-Member example?
Book(book id, authors, title, publisher, year)
Member(member num, last name, first name, address, date of joining)
Borrow(book id, member num, borrow date, due date, return date, fine)
How can the Book-Borrow-Member schema be simplified from 3 to 2 tables?
Combine the “Borrow” relationship into either “Book” or “Member” tables, removing the separate “Borrow” table.
How can the original 3-table library schema be optimised into 2 tables?
By moving borrowing details into either the Member table or the Book table.
What is the optimised schema if borrowing details are added to the Member table?
Book (book id, authors, title, publisher, year)
Member (member num, last name, first name, address, date of joining, book id, borrow date, due date, return date, fine)
What is the optimised schema if borrowing details are added to the Book table?
Book (book id, authors, title, publisher, year, member num, borrow date, due date)
Member (member num, last name, first name, address, date of joining)
What are the trade-offs of these optimised schemas?
Member-focused schema suits libraries tracking individual member borrowing histories.
Book-focused schema suits tracking current borrowing status of books.
(Both have limitations in handling historical or multiple concurrent borrowings.)
What issue arises when a member is not borrowing any book in the current design?
The borrow-related fields (book id, borrow date, due date, etc.) would be null, leading to inefficient and inconsistent data storage.
Why is it problematic to include borrow fields directly in the Member table?
This design assumes a member can borrow only one book at a time. If the library changes its policy to allow multiple borrowings, the design fails.
When is it safe to merge relationships into a single table in a database design?
Only when the multiplicity of the relationship is at most one (1:1).
Why is hard-wiring business policies into a database schema a bad idea?
Business policies (like borrowing limits) may change, and embedding them into the design reduces flexibility and scalability.
What happens in the Book table design when a book is not currently borrowed?
The borrow-related fields (member number, borrow date, due date) would be null, leading to sparsity in the table.
Why can’t fines be handled well in this Book table design?
It’s not possible to track both a current borrower and a past member with an outstanding fine for the same book.
Why is adding Borrow fields to both Book and Member tables not ideal?
It introduces redundancy, which can lead to inconsistencies and complicate updates.
What is the preferred design approach for handling borrowing details and fines effectively?
A 3-table design (Book, Member, Borrow) is best, as it cleanly separates entities and supports many-to-many relationships.
What is a “straight design” in database modeling?
A straight design creates a separate table for each entity and each relationship.
When can a relationship table be merged with an entity table in a database design?
When the relationship has a multiplicity of (0,1) or (1,1) with the entity.
What should be considered if a relationship has (0,1) multiplicity and is merged into an entity table?
The relationship’s attributes must allow for NULL values, since the relationship might not always exist.
What is the main idea behind keeping all three: superclass and subclasses?
Each subclass entity inherits the identity and common attributes from the superclass, while adding its own specific attributes.
Where are common attributes stored in the superclass-subclass design?
In the superclass table, which holds shared fields like name, address, salary, etc.
Give an example of how identity and attributes are managed in a superclass-subclass setup.
staffid is defined in the Staff (superclass) table and inherited by Tech and Admin (subclass) tables, which store attributes like speciality and role.
What are the three possible choices for the table design for hierarchies?
- Keep A, and omit B and C
- Keep B and C, and omit A
- Keep all three
What does the Staff table represent in the superclass-subclass schema?
It is the superclass containing common attributes like staffid, lastname, firstname, joindate, grade, and salary.
How are Techstaff and Adminstaff tables related to the Staff table?
They are subclasses identified by staffid, inheriting from Staff, and containing additional fields: speciality and department for Techstaff; role and unit for Adminstaff.
