SHIFA TAMEER-E-MILLAT UNIVERSITY
Department of Computing
Course Title: Database Systems (CS2123) Class: BSCyS 3rd
Assignment No: 3 Instructor: Mehwish
Issued: 12/01/2026
Total Marks: 10
Due Date: 15/01/2026
Goal: The purpose of this assignment is to explore the concepts and fundamentals of database normalization.
Through this assignment, we will delve into the principles of table normalization, including but not limited to
normal forms, dependency theory, and the process of converting tables to meet normalization standards. By
covering these essential topics, we aim to establish a solid understanding of the theoretical and practical aspects of
database normalization. This assignment will serve as a foundation for further exploration and application of
normalization techniques in real-world database design and optimization scenarios.
Instructions
Assignments are due at the beginning of class on the due date and deadlines of assignments will not be
changed for students who are absent.
Use standard formatting for assignment writing. You are encouraged to use recommended book, articles, and
internet sources to complete this work. However, any plagiarism or cheating of work from others or the
internet will be marked as zero.
Due Date for submission of Assignment: 15th , January 2026
Q 1: Give an example of a relation in BCNF but not in 4NF. Transform the relation into
relations in 4NF.
Q 2: Perform normalization process on the following un-normalized database.
Identify dependencies or anomalies e.g. Functional dependency, Full Functional dependency, and
Transitive dependency etc. and remove these dependencies to achieve normalize form of database.
Q 3: Perform normalization process on the following un-normalized table depicts the set of
attributes of a Patient Checkup Form for Hospital.
Describe and illustrate the process of normalizing the data shown in above figure to 1NF, 2NF, 3NF,
and BCNF.
Q 4: Perform normalization process on the following un-normalized table depicts the set of
attributes A collection of (simplified) DreamHome leases.
Q 5: Examine the Patient Medication Form for the Wellmeadows Hospital case study shown in
figure below.
a. Identify the functional dependencies represented by the attributes
b. State any assumptions you make about the data and the attributes shown in this form.
c. Describe and illustrate the process of normalizing the attributes shown in figure below to produce
a set of well-designed 3NF relations.
d. Identify the primary, alternate, and foreign keys in your 3NF relations.
Q 6: The table shown in figure below lists sample dentist/patient appointment data.
A patient is given an appointment at a specific time and date with a dentist located at a particular surgery.
On each day of patient appointments, a dentist is allocated to a specific surgery for that day.
a. The table below is susceptible to update anomalies. Provide examples of insertion, deletion, and
update anomalies.
b. Identify the functional dependencies represented by the attributes shown in the table below. State
any assumptions you make about the data and the attributes shown in this table.
c. Describe and illustrate the process of normalizing the table shown in figure to 3NF relations.
d. Identify the primary, alternate, and foreign keys in your 3NF relations.
Q 7: Perform normalization process on the following un-normalized table depicts the set of
attributes A collection of DreamHome Property Inspection
Q 8: Perform normalization process on the following un-normalized database.
Identify dependencies or anomalies e.g. Functional dependency, Full Functional dependency, and
multivalued dependency etc. and remove these dependencies to achieve normalize form of database.
Q 9: An agency called Instant Cover supplies part-time/temporary staff to hotels within Scotland. The
table shown in Figure below displays sample data, which lists the time spent by agency staff working at
various hotels. The National Insurance Number (NIN) is unique for every member of staff.
Figure: Table displaying sample data for the Instant Cover agency.
a. The table shown in figure above is susceptible to update anomalies. Provide examples of
insertion, deletion, and update anomalies.
b. Identify the functional dependencies represented by the attributes. State any assumptions you
make about the data and the attributes shown in the above table.
c. Describe and illustrate the process of normalizing the table shown in Figure above to 3NF.
Identify primary, alternate and foreign keys in your relations