This is the written version of an interactive, slide-by-slide deck. The live diagrams, worked examples and practice quiz are in the interactive deck.
In this lesson
- Library
- University
- Hospital
- Online Store
- Hotel
ER Diagram Activities
Chen's Notation · 5 Real-World Scenarios
Chen's Notation — Symbol Reference
Use this guide while completing each activity
Library Management System
A library lends books to its members. Each book has an ISBN, title, and genre. Each member has a member ID, name, and email address. A member can borrow multiple books over time, and the same book may be borrowed by many different members. Each borrowing transaction records a borrow date and a return date.
MEMBER BOOK BORROWS
- Identify all entities and their attributes
- Mark each primary key (underline it)
- Draw the BORROWS relationship with correct cardinality
- Add relationship attributes (BorrowDate, ReturnDate)
University Course Enrollment
A university manages student enrollments in courses. Each student has a student ID, full name, and GPA. Each course has a course code, title, and number of credits. Students can enroll in multiple courses each semester, and each course can have many students enrolled. The enrollment records the semester and grade the student received.
STUDENT COURSE ENROLLS_IN
- Identify all entities and their key attributes
- Determine the cardinality of the enrollment relationship
- Add Semester and Grade as relationship attributes
- Underline the primary key in each entity
Hospital Patient Management
A hospital manages doctors, patients, and departments. Each doctor has a doctor ID, name, and specialization. Each patient has a patient ID, name, and date of birth. Each department has a department ID and name. Each doctor works in exactly one department (a department has many doctors). Doctors can treat many patients, and patients may be treated by many doctors. Each treatment records a treatment date.
DOCTOR PATIENT DEPARTMENT WORKS_IN TREATS
- Draw all three entities with their attributes and PKs
- Show WORKS_IN (M:1) between DOCTOR and DEPARTMENT
- Show TREATS (M:N) between DOCTOR and PATIENT
- Add TreatmentDate as a relationship attribute on TREATS
Online Store Orders
An online store tracks customers, their orders, and products. Each customer has a customer ID, name, and address. Each product has a product ID, name, and unit price. Each order has an order ID and order date. A customer can place many orders (each order belongs to one customer). An order can contain multiple products, and a product can appear in many orders. Each order-line records the quantity ordered.
CUSTOMER ORDER PRODUCT PLACES CONTAINS
- Draw all three entities with their key attributes
- Show PLACES (1:N) between CUSTOMER and ORDER
- Show CONTAINS (M:N) between ORDER and PRODUCT
- Add Quantity as a relationship attribute on CONTAINS
Hotel Room Booking
A hotel manages guest reservations for its rooms. Each guest has a guest ID, full name, and phone number. Each room has a room number, room type (single/double/suite), and nightly rate. A guest can book multiple rooms over different stays, and the same room can be booked by many guests across different periods. Each booking records a check-in date and a check-out date.
GUEST ROOM BOOKS
- Identify all entities and their primary keys
- Determine the correct cardinality for BOOKS
- Add CheckInDate and CheckOutDate as relationship attributes
- Ensure all attributes connect to the correct entity or relationship
