A-LevelComputer ScienceDatabases9618/12 · May/June 2024 · Q411 Marks

An assessment board wants to store the marks students achieved in exams in a database named RECORDS. Part of the database design includes these two tables: EXAM(ExamID, Subject, Level, TotalMarks) EXAM_QUESTION(ExamQuestionID, ExamID, QuestionNumber, Question, MaxMark)

(a)Identify the relationship between EXAM and EXAM_QUESTION.[1]
(b)Sample data for the table EXAM is shown: ExamID Subject Level TotalMarks 00956124 Computer Science 2 75 00956125 Computer Science 3 120 00956126 Mathematics 2 100 00956127 Mathematics 3 150 00956128 Physics 2 70 00956129 Physics 3 80 Write a Structured Query Language (SQL) script to define the table EXAM.[3]
(c)The table EXAM_QUESTION has been created but the foreign key has not been linked. Write an SQL script to update EXAM_QUESTION and link the foreign key to EXAM.[2]
(d)The database also needs to store data about the students, the exams the students have taken and the marks the students achieved in each question of each exam. Describe the additional tables that will need to be included in the database and explain how all the tables in the database will be linked.[5]

Mark scheme

Marking points from the official mark scheme (May/June 2024). The mark or mark type is shown in brackets.

[1] (a) 1-to-many
[1] (b) Creating table EXAM with opening and closing brackets
[1] (b) All fields with appropriate data types and commas at end of lines
[1] (b) ExamID as primary key
[1] (c) Altering table EXAM_QUESTION
[1] (c) Linking ExamID to ExamID in EXAM
[1] (d) STUDENT table identified with suitable Primary Key
[1] (d) A linking table between STUDENT and EXAM with suitable Primary Key and appropriate name
[1] (d) that includes the Primary Key of the STUDENT table as a Foreign Key to join with STUDENT
[1] (d) and includes the Primary Key of the EXAM table as a Foreign Key to join with EXAM
[1] (d) A linking table between STUDENT and EXAM_QUESTION with suitable Primary Key and appropriate name
[1] (d) that includes the Primary Key of Table 2 as a Foreign Key to join with Table 2
[1] (d) that stores the ExamQuestionID and the mark for that question

On Oracle Prep you can write or photograph your own answer and have it marked by AI against this mark scheme, with feedback on what you missed.