CRC Exercise 1
Relational Database Design – Assignment Scenarios (case study)
1. Identify requirements.
2. Draw an ER diagram.
3. Convert into relational tables.
4. Normalize up to 3NF.
5. Add keys and constraints.
6. Write at least 5 SQL queries to test the schema.
Scenario 1: Hospital Management System
Design a database to store details of patients, doctors, nurses, appointments, treatments,
and bills.
Constraints: A patient can book many appointments; a doctor can treat many patients.
Scenario 2: Online Shopping / E-Commerce
Design a system for customers, products, orders, payments, and shipping.
Constraints: A customer can place multiple orders; each order can contain multiple
products.
Scenario 3: University Exam System
Track students, courses, exams, results, and instructors.
Constraints: A student can enroll in multiple courses, and each course can have multiple
exam results.
Scenario 4: Airline Reservation System
Model flights, passengers, bookings, crew, and airports.
Constraints: A flight can have many passengers, and each booking is linked to one flight.
Scenario 5: Hotel Management System
Manage guests, rooms, reservations, payments, and staff.
Constraints: A room can be booked by one guest at a time, but guests can make multiple
reservations.
Scenario 6: Banking System
Design a database for customers, accounts, transactions, loans, and branches.
Constraints: A customer can hold multiple accounts, and each account can have many
transactions.
Scenario 7: Social Media Platform
Model users, posts, comments, likes, and friend/follow relationships.
Constraints: A user can follow many users, and posts can have multiple likes and
comments.
Scenario 8: Movie Ticket Booking System
Track movies, theaters, shows, customers, and bookings.
Constraints: Each show belongs to one movie and one theater; customers can book
multiple tickets.
Scenario 9: Supply Chain Management
Design a database for suppliers, products, warehouses, shipments, and retailers.
Constraints: A supplier provides multiple products, and shipments transport products to
retailers.
Scenario 10: Food Delivery System
Model restaurants, menus, customers, orders, and delivery staff.
Constraints: A customer can order from multiple restaurants, and each order is assigned
to one delivery person.
Example Case Study: Library Management System
1. Requirements
The library database must:
1. Store book details (title, author, ISBN, publisher, year, available copies).
2. Track members (students/faculty) with contact details.
3. Record borrowing activity (which member borrowed which book, issue date,
return date).
4. Ensure one book copy can only be issued to one member at a time.
5. Allow checking overdue books (not returned within 14 days).
2. Conceptual Design (ER Model)
Entities & Attributes:
Book (Book_ID, Title, Author, ISBN, Publisher, Year, Copies_Available)
Member (Member_ID, Name, Type {Student/Faculty}, Contact, Email)
Loan (Loan_ID, Issue_Date, Return_Date)
Relationships:
Member borrows Book → M:N → converted into Loan table.
3. Relational Schema
CREATE TABLE Book (
Book_ID INT PRIMARY KEY,
Title VARCHAR(200) NOT NULL,
Author VARCHAR(100),
ISBN VARCHAR(20) UNIQUE,
Publisher VARCHAR(100),
Year INT,
Copies_Available INT CHECK (Copies_Available >= 0)
);
CREATE TABLE Member (
Member_ID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Type VARCHAR(20) CHECK (Type IN ('Student','Faculty')),
Contact VARCHAR(15),
Email VARCHAR(100)
);
CREATE TABLE Loan (
Loan_ID INT PRIMARY KEY,
Member_ID INT,
Book_ID INT,
Issue_Date DATE,
Return_Date DATE,
FOREIGN KEY (Member_ID) REFERENCES Member(Member_ID),
FOREIGN KEY (Book_ID) REFERENCES Book(Book_ID)
);
Sample SQL Queries
1. List all books borrowed by a member
SELECT m.Name, b.Title, l.Issue_Date, l.Return_Date
FROM Loan l
JOIN Member m ON l.Member_ID = m.Member_ID
JOIN Book b ON l.Book_ID = b.Book_ID
WHERE m.Member_ID = 101;
2. Find overdue books (not returned in 14 days)
SELECT m.Name, b.Title, l.Issue_Date
FROM Loan l
JOIN Member m ON l.Member_ID = m.Member_ID
JOIN Book b ON l.Book_ID = b.Book_ID
WHERE l.Return_Date IS NULL
AND l.Issue_Date <= CURRENT_DATE - INTERVAL '14' DAY;
3. Count how many books each member has borrowed
SELECT m.Name, COUNT(l.Loan_ID) AS Books_Borrowed
FROM Member m
LEFT JOIN Loan l ON m.Member_ID = l.Member_ID
GROUP BY m.Name;
4. Find the most borrowed book
SELECT b.Title, COUNT(l.Loan_ID) AS Times_Borrowed
FROM Loan l
JOIN Book b ON l.Book_ID = b.Book_ID
GROUP BY b.Title
ORDER BY Times_Borrowed DESC
LIMIT 1;
5. List available books (not borrowed currently)
SELECT b.Title
FROM Book b
WHERE b.Book_ID NOT IN (
SELECT Book_ID FROM Loan WHERE Return_Date IS NULL
);
