Computer Science

1. 1.

   For Student(StudentID, Name) with StudentID declared PRIMARY KEY and Enrolment(StudentID, CourseID) with StudentID declared NOT NULL, explain the primary key and a foreign-key constraint referencing Student.

   [3 marks] · no calculator

   Answer explanation

   Draft walkthroughs are based on marking guidance, not independently verified derivations.

   1. Names may repeat, so a stable identifier distinguishes students. The foreign key together with NOT NULL requires every enrolment to reference an existing student. A nullable foreign key alone would allow a null reference, independently of how names are displayed.

   Marking points

   • StudentID uniquely identifies each Student row.
   • The primary key cannot be null.
   • Enrolment.StudentID references an existing Student.StudentID.

   Examiner tip: A foreign key need not be unique in the referencing table.

2. 2.

   Explain an update anomaly when DepartmentName is repeated in every employee row, and suggest a relational remedy.

   [3 marks] · no calculator

   Answer explanation

   Draft walkthroughs are based on marking guidance, not independently verified derivations.

   1. Repeated descriptive data makes one fact have many storage locations. Separating department facts gives the name one authoritative row while employee records retain a reference to that entity.

   Marking points

   • Renaming a department requires changing multiple rows.
   • Partial updates can leave inconsistent names for one department.
   • Store departments once with a DepartmentID referenced by employees.

   Examiner tip: Normalisation reduces logical redundancy; it is not merely file compression.

3. 3.

   Given Scores(Name, Mark), write SQL to return names with Mark >= 70 sorted by descending Mark then ascending Name. Do not require duplicate removal.

   [3 marks] · no calculator

   Answer explanation

   Draft walkthroughs are based on marking guidance, not independently verified derivations.

   1. Filter rows before presentation ordering: SELECT Name FROM Scores WHERE Mark >= 70 ORDER BY Mark DESC, Name ASC;. The second sort key makes ties deterministic by name.

   Marking points

   • SELECT Name FROM Scores.
   • WHERE Mark >= 70.
   • ORDER BY Mark DESC, Name ASC;

   Examiner tip: Use >= rather than > so exactly 70 is included.

4. 4.

   Orders contains (OrderID, CustomerID): (1,A), (2,A), (3,B). Customer contains IDs A, B and C once each. Determine row counts for an inner join on CustomerID and a customer-left join with Orders.

   [3 marks] · no calculator

   Answer explanation

   Draft walkthroughs are based on marking guidance, not independently verified derivations.

   1. Enumerate matches: A produces two joined rows and B one. C has no order, so an inner join excludes it while a left join preserves one customer row with null order columns.

   Marking points

   • The inner join has 3 rows.
   • Customer C adds one null-extended row in the left join.
   • The customer-left join has 4 rows.

   Examiner tip: A join is not necessarily one output row per customer.

5. 5.

   A relation Enrolment(StudentID, CourseID, StudentName, CourseTitle, Grade) has key (StudentID, CourseID); StudentName depends only on StudentID and CourseTitle only on CourseID. Decompose it to remove the partial dependencies and explain how Grade is retained.

   [4 marks] · no calculator

   Answer explanation

   Draft walkthroughs are based on marking guidance, not independently verified derivations.

   1. Place each fact with the key that determines it. Names and titles describe single entities, while a grade describes one enrolment, so moving Grade into Student would lose course-specific information.

   Marking points

   • Student(StudentID, StudentName).
   • Course(CourseID, CourseTitle).
   • Enrolment(StudentID, CourseID, Grade) retains the composite key.
   • Grade belongs to the student-course pair; foreign keys connect the separated entities.

   Examiner tip: Use the supplied functional dependencies; do not invent a dependency from student to grade.

6. 6.

   A bank transfer debits one account then credits another. Explain how atomicity and isolation address different risks if a crash or concurrent balance update occurs.

   [4 marks] · no calculator

   Answer explanation

   Draft walkthroughs are based on marking guidance, not independently verified derivations.

   1. All-or-nothing treatment solves partial execution within one transfer. Concurrency is a separate issue: two valid transactions can still interfere unless the database enforces an appropriate isolation and update strategy.

   Marking points

   • Atomicity commits both changes or rolls both back.
   • This prevents a crash leaving only a debit recorded.
   • Isolation controls interference from concurrent transactions.
   • Suitable locking/serialisable operations prevent lost updates or inconsistent reads; atomicity alone does not.

   Examiner tip: Name the failure each property addresses rather than using 'ACID' without explanation.

Original, unofficial practice questions from SubjectScout — not official past-paper questions.