DTA(M) Database Theory & Applications
Lab 3 Tasks
Task 1: Operational Anomalies
You are given the following relation: Student
1.1: Identify an anomaly after updating the address of student with SID = 41; provide an explanation.
1.2: Identify an anomaly after dropping the subject ‘Maths’ from student with SID = 42; provide an
explanation.
1.3: Identify an anomaly on inserting a student with SID = 44, who has not selected yet some
Subjects; provide an explanation.
1.4: Consider the following table INFORMATION. Identify the NF of this table. Normalize this table in
1NF (if not already) and identify any possible problems after this normalization.
Table 1: INFORMATION
Student Age Subject
Chris 15 Biology, Maths
Philip 14 Maths
Stella 17 Maths
1.5: Identify the NF of the relation Tournament Winners. Can you identify any possible anomalies?
The PK is the pair: {Tournament, Year}.
Tournament Winners
Tournament Year Winner Winner Date of Birth
Des Moines Masters 1998 Chip Masterson 14 March 1977
Indiana Invitational 1998 Al Fredrickson 21 July 1975
Cleveland Open 1999 Bob Albertson 28 September 1968
Des Moines Masters 1999 Al Fredrickson 21 July 1975
Indiana Invitational 1999 Chip Masterson 14 March 1977
SID Name Address Subject
41 Adam Athole Gardens CS
42 Alex Hyndland Ave Maths
43 Stuart University Ave Maths
41 Adam Athole Gardens Physics
DTA(M) Database Theory & Applications
Task 2: Normalisation to 2NF
2.1: Consider the FD: Product_ID → Product_Description in the relation Order with PK = {Order_No,
Product_ID}. Identify the NF of the relation Order and normalize it to avoid potential anomalies.
Order
Product_ID Order_No Product_Description
2.2: Consider the following relation Student with PK = {Stud_ID, Course_ID} and the FDs: FD1:
Stud_ID → Name and FD2: Course_ID → Units. Identify the NF of the relation Student and
normalize it to avoid potential anomalies.
2.3: Consider a relation:
Student_Grade_Report (StudentNo, StudentName, CourseNo, CourseName, Grade)
with the following FDs: StudentNo → StudentName, CourseNo → CourseName, {StudentNo,
CourseNo} → Grade,
The PK is the pair {StudentNo, CourseNo}. Split this relation into a set of 2NF relations.
2.4: Consider the relation R(ABCDE) below:
What kind of functional dependencies can we observe among the attributes in the relation
R(ABCDE)? Which is the possible candidate key?