Design a relational schema for a library (books, authors, members, loans). Explain the relationships.
Answer
authors(id, name), books(id, title, isbn UNIQUE), members(id, name, email UNIQUE).- Book to author is many-to-many (a book can have several authors): junction
book_authors(book_id, author_id). - A book can have several physical copies:
copies(id, book_id FK, status). loans(id, copy_id FK, member_id FK, loaned_at, due_at, returned_at NULL): one-to-many from member to loans and from copy to loans.- Constraints: primary keys, foreign keys, NOT NULL, UNIQUE (isbn, email), CHECK (due_at > loaned_at).
- Indexes on foreign keys and on search columns (title). Normalize to 3NF; denormalize only with a measured reason.
- Query example: members with overdue loans:
WHERE returned_at IS NULL AND due_at < now().
Practice this question in Repaso
