EXERCISE 3
CHAPTER 6: DATABASE NORMALIZATION
BERL2232 APPLICATION SYSTEM DEVELOPMENT
1
NAME: MUHAMMAD NAJMI BIN MOHD NOOR SHAHAL (B122410690)
Case Study 1: Bookstore Inventory
Scenario:
A bookstore maintains a record of books and their authors in a single table.
Unnormalized Table:
BookID Title AuthorID AuthorName AuthorEmail Publisher
Database A001, John Smith, Mary john@[Link], TechBooks
B001
Design A002 Lim mary@[Link] Inc.
SQL LearnSQL
B002 A003 Alex Tan [Link]@[Link]
Essentials Ltd.
Answer:
Books Table
BookID Title Publisher
B001 Database Design TechBooks Inc.
B002 SQL Essentials LearnSQL Ltd.
Authors Table
AuthorID AuthorName AuthorEmail
A001 John Smith john@[Link]
A002 Mary Lim mary@[Link]
A003 Alex Tan [Link]@[Link]
Book_Authors Table
BookID AuthorID
B001 A001
B001 A002
B002 A003
Case Study 2: Student Course Enrollment
Scenario:
A university stores student enrolments and course details in one table.
Unnormalized Table:
StudentID StudentName CourseCode CourseName Instructor InstructorEmail
S101 Aida CSC101 Database Systems Dr. Goh goh@[Link]
S101 Aida CSC102 Programming I Ms. Lee lee@[Link]
S102 Zaki CSC101 Database Systems Dr. Goh goh@[Link]
Answer:
Students Table
StudentID StudentName
S101 Aida
S102 Zaki
Course Table
CourseCode CourseName Instructor
CSC101 Database Systems Dr. Goh
CSC102 Programming I Ms. Lee
Instructors Table
Instructor Instructor Email
Dr. Goh goh@[Link]
Ms. Lee lee@[Link]
Enrollments Table
StudentID CourseCode
S101 CSC101
S101 CSC102
S102 CSC101
Case Study 3: Supplier Orders
Scenario:
A company keeps order records with supplier details in a single table.
Unnormalized Table:
OrderID SupplierName ProductID ProductName TotalAmount
O001 TechMart P001, P002 Mouse, Keyboard 250
O002 OfficeDepot P003 Printer 500
O003 TechMart P004, P001 Monitor, Mouse 700
Answer
Orders Table
OrderID Supplier Name TotalAmount
0001 TechMart 250
0002 Office Depot 500
0003 TechMart 700
Products Table
ProductID ProductName
P001 Mouse
P002 Keyboard
P003 Printer
P004 Monitor
Order_Details Table
OrderID ProductID
0001 P001
0001 P002
0002 P003
0003 P004
0003 P001
Case Study 4: Employee Skills Database
Scenario:
An HR department records employee details and the skills they possess.
Unnormalized Table:
EmpID EmpName Department Skills Manager
E001 Hafiz IT SQL, Python Mr. Kamarul
E002 Salma IT Java, C++ Mr. Kamarul
E003 Nisha HR Communication, Excel Ms. Farah
Answer:
Employee Table
EmpID EmpName Department
E001 Hafiz IT
E002 Salma IT
E003 Nisha HR
Departments Table
Department Manager
IT Mr. Kamarul
HR Ms. Farah
Employee_Skills Table
EmpID Skills
E001 SQL
E001 Python
E002 Java
E002 C++
E003 Communication
E003 Excel
Case Study 5: Car Service Records
Scenario:
An automotive center tracks customer services in one table.
Unnormalized Table:
ServiceID CustomerName CarPlate ServicesDone Mechanic MechanicContact
Oil Change, Tire
S001 Amir WXP1234 Rahim 012-3456789
Rotation
S002 Siti BGH9876 Brake Inspection Fadhil 013-2233445
S003 Amir WXP1234 Battery Replacement Rahim 012-3456789
Answer:
Services Table
ServiceID CarPlate Mechanic
S001 WXP1234 Rahim
S002 BGH9876 Fadhil
S003 WXP1234 Rahim
Customers Table
CarPlate CustomerName
WXP1234 Amir
BGH9876 Siti
Mechanics Table
Mechanic MechanicContact
Rahim 012-3456789
Fadhil 013-2233445
Services_Rendered Table
ServiceID Services Done
S001 Oil Change
S001 Tire Rotation
S002 Brake Inspection
S003 Battery Replacement
Case Study 6: Hotel Room Reservation
Scenario:
A hotel stores guest reservations, room types, and a list of services used during the stay in a
single row.
Unnormalized Table:
ReservationID GuestName RoomType Date ServicesUsed
R001 Aminah Deluxe 2024-04-10 Spa, Breakfast
R002 Ben Standard 2024-04-11 Dinner
R003 Chong Suite 2024-04-12 Spa, Dinner
Answer:
Reservations Table
ReservationID GuestName Room Type Date
R001 Aminah Deluxe 2024-04-10
R002 Ben Standard 2024-04-11
R003 Chong Suite 2024-04-12
Reservation_Services Table
ReservationID Services Used
R001 Spa
R001 Breakfast
R002 Dinner
R003 Spa
R003 Dinner
Case Study 7: Medical Test Records
Scenario:
A clinic writes down a patient’s name, the date they came, and all the tests and results in one
row. This makes the table messy and hard to understand.
Unnormalized Table:
VisitID PatientName VisitDate TestsDone Results
V001 Adam 2024-03-01 Blood Test, X-Ray Normal, Clear
V002 Bella 2024-03-02 MRI No Abnormalities
V003 Chris 2024-03-03 Blood Test, MRI High WBC, Normal
Answer:
Visits Table
VisitID PatientName VisitDate
V001 Adam 2024-03-01
V002 Bella 2024-03-02
V003 Chris 2024-03-03
Visit_Tests Table
VisitID Tests Done Results
V001 Blood Test Normal
V001 X-Ray Clear
V002 MRI No Abnormalities
V003 Blood Test High WBC
V003 MRI Normal
Case Study 7: Airline Booking System
Scenario:
An airline records bookings including flight details and a list of passengers in one row.
Unnormalized Table:
BookingID FlightID Origin Destination Passengers SeatNumbers
B001 F100 KLIA Singapore Ali, Sara 1A, 1B
B002 F101 KLIA Jakarta John 2C
Answer:
Bookings Table
BookingID FlightID
B001 F100
B002 F101
Flights Table
FlightID Origin Destination
F100 KLIA Singapore
F101 KLIA Jakarta
Booking_Passengers Table
BookingID Passengers SeatNumbers
B001 Ali 1A
B001 Sara 1B
B002 John 2C
Case Study 8: Online Product Reviews
Scenario:
Each product review includes customer name, product name, and multiple ratings/comments
in one row.
Unnormalized Table:
ReviewID CustomerName Product Aspect Ratings Comments
R001 Aiman Phone Design, Performance 5, 4 Great, Good
R002 Farah Laptop Performance 3 Average
Answer:
Reviews Table
ReviewID CustomerName Product
R001 Aiman Phone
R002 Farah Laptop
Review_Feedback Table
ReviewID Aspect Ratings Comments
R001 Design 5 Great
R001 Performance 4 Good
R002 Performance 3 Average
Case Study 9: School Attendance Records
Scenario:
Each attendance entry lists student, class, and multiple subjects with attendance status in one
row.
Unnormalized Table:
Date StudentName Class Subjects Status
2024-04-01 Lisa 5A Math, Science Present, Absent
2024-04-01 Tom 5A Math Present
Answer:
Students_Class Table
StudentName Class
Lisa 5A
Tom 5A
Attendance Table
Date StudentName Subjects Status
2024-04-01 Lisa Math Present
2024-04-01 Lisa Science Absent
2024-04-01 Tom Math Present
Case Study 10: Hospital Prescription Records
Scenario:
A hospital records patient visits by listing the patient's name, doctor's name, visit date, and
multiple medicines with doses in a single row. To make the data clearer and easier to manage,
you need to organize it into separate tables using 1NF, 2NF, and 3NF steps.
Unnormalized Table:
VisitID Patient Doctor Date Medicines Doses
V001 Amir Dr. Hana 10 April Paracetamol, Ibuprofen 500mg, 200mg
V002 Zain Dr. Lim 11 April Amoxicillin 250mg
Answer:
Visit_Details Table
VisitID Patient Doctor Date
V001 Amir Dr. Hana 10 April
V002 Zain Dr. Lim 11 April
Prescriptions Table
VisitID Medicines Doses
V001 Paracetamol 500mg
V001 Ibuprofen 200mg
V002 Amoxicillin 250mg