Duration: 2 hours | Theory: 20 min | Practice: 100 min
This lecture is about drawing before coding. Students learn ER diagrams (slides 2-4), practice on paper with real scenarios (slides 5-7), then implement those designs in SQL (slides 8+). The patient appointment system on slide 5 is the main exercise.
Slide 2 shows a complex university ER diagram with Crow's Foot notation. Don't explain every relationship - just point out the symbols: || = one, o{ = zero or more, |{ = one or more.
Slide 3 explains connector meanings. Students confuse this fast. Use real examples: "A customer places many orders (one-to-many). A book has many authors, an author writes many books (many-to-many)."
Most important: tell students "many-to-many always needs a junction table" - they'll forget this and try to jam everything into two tables.
Slide 5 describes the system. Read it once, then give students 20 minutes to draw an ER diagram on paper or whiteboard. No computers yet.
Expected entities: DOCTORS, PATIENTS, APPOINTMENTS, BILLS, PAYMENTS, INSURANCE_COMPANIES. If students are missing tables, ask: "Where do you store the bill information?" until they find it.
After 20 minutes, have 2-3 groups present their diagrams. Discuss differences. Common mistakes:
Once you've converged on a design, move to slide 6 and start implementing in SQL.
Slide 6 shows the CREATE TABLE syntax for DOCTORS. Code this live, then have students code PATIENTS and APPOINTMENTS themselves.
Slide 7 (ALTER TABLE) is important - students will need to add columns they forgot. Show them how to add and drop columns, but keep it quick.
Slide 8-9 cover constraints. When students ask "do I really need foreign keys?", insert bad data without constraints, show orphaned records, then add the constraints and watch inserts fail. They learn better from breaking things.
CREATE DATABASE clinic_db;
USE clinic_db;
CREATE TABLE doctors (
doctor_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
specialization VARCHAR(100)
);
CREATE TABLE patients (
patient_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(100) NOT NULL,
last_name VARCHAR(100) NOT NULL,
insurance_company_id INT
);
CREATE TABLE appointments (
appointment_id INT PRIMARY KEY AUTO_INCREMENT,
doctor_id INT NOT NULL,
patient_id INT NOT NULL,
appointment_date DATETIME NOT NULL,
FOREIGN KEY (doctor_id) REFERENCES doctors(doctor_id),
FOREIGN KEY (patient_id) REFERENCES patients(patient_id)
);Students implement the rest (bills, payments, insurance tables) following the same pattern.
Foreign key errors: "Cannot add or update a child row"
They're trying to reference a record that doesn't exist. Have them SELECT * FROM parent_table to see available IDs.
Creating FK before parent table exists
Remind them: create tables in dependency order. Parents first, then children.
Forgetting AUTO_INCREMENT on primary keys
They'll manually enter IDs and wonder why it's tedious. Show them AUTO_INCREMENT magic.
Give students one of these scenarios to diagram and implement (15 min each):
E-commerce: Products, Categories, Orders, Customers, OrderItems
University: Students, Courses, Enrollments, Professors, Grades
Check the answers/ folder for complete solutions they can reference later.
Design and implement an online bookstore: Books (can have multiple authors), Authors, Categories, Customers, Orders, Reviews. Must include ER diagram (photo/scan) and .sql file. See answers/answer-bookstore.html for reference.