How often Normalisation and dependencies is asked

Where it was asked

• Oct/Nov 202418 marks
• Oct/Nov 202322 marks
• Oct/Nov 202224 marks

The questions

1. Oct/Nov 2024, Q3.a4 marks

   The University of Botswana's UniBo database keeps records of students, the courses they take, and their enrolments. At present all of this information is kept in one single table, shown as Table 3.1 (University Table), with columns StudentID, StudentName, CourseID, CourseName, Instructor and EnrolmentDate. The rows recorded are: StudentID 1, StudentName Thato, CourseID 101, CourseName Math, Instructor Dr. Moape, EnrolmentDate 2024-01-15; StudentID 2, StudentName Lucas, CourseID 102, CourseName History, Instructor Dr. Mtsweni, EnrolmentDate 2024-01-16; StudentID 1, StudentName Thato, CourseID 103, CourseName Physics, Instructor Dr. Chipa, EnrolmentDate 2024-01-17; StudentID 3, StudentName Sam, CourseID 101, CourseName Math, Instructor Dr. Moape, EnrolmentDate 2024-01-18. Using this information, answer the following. Starting from the unnormalised University Table described above, convert it to first normal form (1NF) if this is possible, showing the resulting relation(s).

2. Oct/Nov 2024, Q3.b6 marks

   The University of Botswana's UniBo database keeps records of students, the courses they take, and their enrolments. At present all of this information is kept in one single table, shown as Table 3.1 (University Table), with columns StudentID, StudentName, CourseID, CourseName, Instructor and EnrolmentDate. The rows recorded are: StudentID 1, StudentName Thato, CourseID 101, CourseName Math, Instructor Dr. Moape, EnrolmentDate 2024-01-15; StudentID 2, StudentName Lucas, CourseID 102, CourseName History, Instructor Dr. Mtsweni, EnrolmentDate 2024-01-16; StudentID 1, StudentName Thato, CourseID 103, CourseName Physics, Instructor Dr. Chipa, EnrolmentDate 2024-01-17; StudentID 3, StudentName Sam, CourseID 101, CourseName Math, Instructor Dr. Moape, EnrolmentDate 2024-01-18. Using this information, answer the following. Taking the 1NF relation you produced for the University Table in part (a) as your starting point, identify and remove any partial dependencies that exist, and transform the relation into second normal form (2NF).

3. Oct/Nov 2024, Q3.c8 marks

   The University of Botswana's UniBo database keeps records of students, the courses they take, and their enrolments. At present all of this information is kept in one single table, shown as Table 3.1 (University Table), with columns StudentID, StudentName, CourseID, CourseName, Instructor and EnrolmentDate. The rows recorded are: StudentID 1, StudentName Thato, CourseID 101, CourseName Math, Instructor Dr. Moape, EnrolmentDate 2024-01-15; StudentID 2, StudentName Lucas, CourseID 102, CourseName History, Instructor Dr. Mtsweni, EnrolmentDate 2024-01-16; StudentID 1, StudentName Thato, CourseID 103, CourseName Physics, Instructor Dr. Chipa, EnrolmentDate 2024-01-17; StudentID 3, StudentName Sam, CourseID 101, CourseName Math, Instructor Dr. Moape, EnrolmentDate 2024-01-18. Using this information, answer the following. Continuing from the 2NF version of the University Table relations, identify and remove any transitive dependencies, and transform the relations from second normal form (2NF) into third normal form (3NF).

4. Oct/Nov 2023, Q1.102 marks · multiple choice

   Determine whether it is true or false that, if attribute (A) is functionally dependent on a composite key (B) but not on any subset of that composite key, attribute (A) is fully functionally dependent on (B).

5. Oct/Nov 2023, Q5.110 marks

   A dependency diagram labelled Figure 5.1 (the initial dependency diagram for question 5) is given, showing eight attributes positioned in a row: B, C, D, F, G, H and J, with B and D shown underlined (indicating they form part of a composite primary key). Arrows above the row show that B and D together determine C, F, G, H and J (the composite key B,D determines these attributes). Arrows below the row show additional partial and transitive dependencies: B alone determines C; C alone determines F and G; D alone determines G; and J determines H. Using this dependency diagram, answer the following. Using the dependency diagram in Figure 5.1, design a database in which every resulting table is in at least Second Normal Form (2NF). For each table you create, draw its own dependency diagram showing the determinant and dependent attributes.