1.
Student Course Registration System
Scenario
A college wants to store information about students and the courses they take. Currently, the data
is stored in one table where multiple courses and instructors are recorded in the same row. This
causes problems when managing and updating the data.
A college stores the following information in one table.
StudentID StudentName Courses Instructor
101 Abebe Database, Mr. John, Ms. Sara
Programming
102 Hana Database Mr. John
103 Dawit Networking, Security Mr. Daniel, Mr. Sami
Problem:
The Courses and Instructor columns contain multiple values, which violates First Normal
Form (1NF).
Tasks
Identify why the table violates 1NF.
Convert the table into First Normal Form (1NF).
Identify the Primary Key.
Convert the table into Second Normal Form (2NF).
Convert the table into Third Normal Form (3NF).
Additional Questions
What are the repeating groups in this table?
What will be the new tables after applying normalization?
Which attributes are dependent on the primary key?
Explain how normalization reduces data redundancy in this example.
2. Library Borrowing System
Scenario
A library records information about members and the books they borrow. Sometimes one
member borrows multiple books, but the library stores all book titles in one column.
A library stores the following information.
MemberID MemberName BooksBorrowed BorrowDate
M01 Sara Database Systems, Data Structures 01-02-2024
M02 Daniel Programming in C 03-02-2024
M03 Hana Database Systems, Networking 05-02-2024
Problem:
The BooksBorrowed column contains multiple values, violating 1NF.
Tasks
Identify the 1NF violation.
Convert the table into 1NF.
Identify Functional Dependencies.
Normalize the table to 2NF and 3NF.
Additional Questions
Which column contains repeating groups?
What should be the primary key after normalization?
How many tables will be created after normalization?
Explain the advantage of normalization for a library system.
3. Hospital Patient System
Scenario
A hospital stores patient information along with the doctors they visit. Some patients visit
multiple doctors, so several doctor IDs and names are recorded in the same row.
A hospital records patient information.
PatientID PatientName DoctorIDs DoctorNames AppointmentDate
P01 Abebe D01, D02 Dr. Sami, Dr. Sara 10-01-2024
P02 Hana D03 Dr. Daniel 11-01-2024
P03 Dawit D01, D04 Dr. Sami, Dr. John 12-01-2024
Problem:
The DoctorIDs and DoctorNames columns contain multiple values, violating 1NF.
Tasks
Explain the 1NF violation.
Convert the table to First Normal Form (1NF).
Identify dependencies.
Normalize the table to 2NF and 3NF.
Additional Questions
Which attributes are functionally dependent on the primary key?
What tables will be created after normalization?
Explain how normalization improves data integrity in the hospital system.
4. Online Shopping System
Scenario
An online store records customer orders. Sometimes a customer buys multiple products in one
order, but all products and prices are stored in the same row.
An online store records the following data.
OrderID CustomerName Products Prices OrderDate
O01 Abebe Laptop, Mouse 45000, 500 01-03-2024
O02 Hana Keyboard 1200 02-03-2024
O03 Dawit Laptop, Monitor 45000, 8000 04-03-2024
Problem:
The Products and Prices columns contain multiple values, which violates 1NF.
Tasks
Identify the 1NF violation.
Convert the table into 1NF.
Identify Functional Dependencies.
Normalize the table into 2NF and 3NF.
Additional Questions
What repeating attributes exist in this table?
What tables will be created after normalization?
Why is normalization important for online shopping systems?
5. Employee Project System
Scenario
A company records employees and the projects they work on. Some employees work on multiple
projects, so several project names and managers are stored in the same row.
A company stores the following information.
EmployeeID EmployeeName Projects ProjectManagers
E01 Abebe Website, Database Mr. Samuel, Mr. John
E02 Hana Mobile App Ms. Sara
E03 Dawit Website, Security Mr. Samuel, Mr. Daniel
Problem:
The Projects and ProjectManagers columns contain multiple values, violating 1NF.
Tasks
Explain why the table violates 1NF.
Convert the table into First Normal Form (1NF).
Identify the Primary Key.
Normalize the table into 2NF and 3NF.
Additional Questions
What repeating data exists in the table?
What new tables will be created after normalization?
Explain how normalization improves database efficiency.