These seven original questions combine the four relational modelling skills. Try each before opening the answer.
This set belongs to relational data modelling. A teacher can go through your designs in the one-hour trial class (from RM50), a taught lesson on the Computer Science topic you choose.
Questions
Question 1. A Pupil table has two rows named “Lim Wei” in Form 4 and one “Lim Wei” in Form 5. Suggest a primary key and explain.
Answer
Add PupilID as the primary key. Name repeats, and Name plus Form could repeat if two Lim Wei join the same form. An assigned ID is unique, never empty and stable.
Question 2. Rows of a Member table: M01, M02, M03. Rows of a Loan table have MemberID values M01, M03, M04. Find the orphan.
Answer
M04 is the orphan. It does not appear in Member, so the loan points to a member who does not exist.
Question 3. Employees work on many projects, and each project has many employees. Design the tables.
Answer
Employee (EmployeeID, Name), Project (ProjectID, Title) and an intermediate table Assignment (EmployeeID, ProjectID). The primary key of Assignment is the pair (EmployeeID, ProjectID), and each column is a foreign key.
Question 4. A table stores each student’s class teacher in every row. Name the anomaly if the teacher changes and only some rows are updated.
Answer
An update anomaly. The same fact is stored in several rows, so a partial edit leaves contradictory data.
Question 5. After the last student leaves a class, the row is deleted and the class teacher’s name vanishes. Name this anomaly.
Answer
A deletion anomaly. Removing one fact, the student, also removes a separate fact, the class and its teacher.
Question 6. A student writes ClubID = “C1, C2” in one cell. State the problem and the fix.
Answer
The cell holds two values, which breaks the one-value-per-cell rule and stops clean searching or counting. Create an intermediate table with one row per student and club pair.
Question 7. Class table has ClassID 4A, 4B. Student rows show ClassID 4A, 4B, 4B, 4C. State whether the foreign key is valid and give one fix.
Answer
It is not valid, because 4C is not in Class. Fix it by adding class 4C to Class or correcting the student’s ClassID to an existing class.
If you got these wrong
For question 1, read choosing a primary key when names are not unique. For questions 3 and 6, see resolving a many-to-many relationship.
For questions 4 and 5, use explaining update anomalies. For questions 2 and 7, return to checking a foreign key against sample rows.
To repair a weak area with a teacher, see online one-to-one Computer Science tuition.