ERD Design
Problem Statement
A school library needs a database to track books and borrowing. The system should store information about students, books, and which books each student has borrowed. A student can borrow many books, and each book can be borrowed by many students (over time). Design an Entity-Relationship Diagram (ERD) for this system.
Step-by-Step Solution
Identify the Entities
Read the problem and find the main "things" we need to store data about. Look for nouns that represent objects or concepts.
From the problem:
- • Student - people who borrow books
- • Book - items that can be borrowed
- • Loan - the act of borrowing (links students to books)
Key Insight: The "Loan" entity is needed because students and books have a many-to-many relationship. A linking table (junction table) resolves M:M relationships.
Define Attributes for Each Entity
What information do we need to store about each entity? Include a primary key (PK) for each.
Student
PKStudentID- FirstName
- LastName
- YearLevel
Book
PKBookID- Title
- Author
- ISBN
- Genre
- PublishedYear
Loan
PKLoanIDFKStudentIDFKBookID- BorrowDate
- DueDate
- ReturnDate
Determine Relationships and Cardinality
How do the entities connect? What are the business rules?
Student → Loan
One student can have many loans (1:M)
A student can borrow multiple books over time
Book → Loan
One book can appear in many loans (1:M)
A book can be borrowed by different students at different times
Student ↔ Book (via Loan)
Many-to-Many resolved through the Loan table
The junction table converts M:M into two 1:M relationships
Choose Data Types
Select appropriate data types for each attribute.
| Entity | Attribute | Data Type | Reason |
|---|---|---|---|
| Student | StudentID | INTEGER | Auto-increment number |
| Student | FirstName | VARCHAR(50) | Variable length text |
| Book | ISBN | CHAR(13) | Fixed 13 characters |
| Book | PublishedYear | INTEGER | Year as number |
| Loan | BorrowDate | DATE | Date values only |
Complete ERD
STUDENT
- PK StudentID
- FirstName
- LastName
- YearLevel
LOAN
- PK LoanID
- FK StudentID
- FK BookID
- BorrowDate
- DueDate
- ReturnDate
BOOK
- PK BookID
- Title
- Author
- ISBN
- Genre
- PublishedYear
Student (1) ←→ (M) Loan (M) ←→ (1) Book
One student has many loans; One book appears in many loans
Common Mistakes to Avoid
- ✗ Not using a junction table for many-to-many relationships
- ✗ Forgetting to include primary keys for each entity
- ✗ Using meaningful data (like Name) as primary key instead of ID numbers
- ✗ Storing calculated data (e.g., Age) when it can be derived from other fields (DateOfBirth)
- ✗ Not showing cardinality on relationship lines