SubjectScout
ENAR
Sign inGet matched
Get matched

Constructing and normalising relational data

Start questions

AI-assisted English content review; independent teacher approval pending.

Practice scope and review

Original, AI-authored English practice with worked guidance. Selected topics only; independent teacher review is pending. This is not a complete syllabus, official past-paper collection or predicted exam score. Check your own exam-board specification: these concepts are not certified against a particular board or exam year.

151 original questions across 21 selected topic areas. No account or payment required to practise.

Cambridge International AS & A Level Computer Science (9618), examination year 2027: AI-assisted conceptual alignment to official sources, not teacher or exam-board certification. Syllabus mapping and remaining coverage.

5 questions in this topic

Exact English revisions checked in an AI-assisted academic review; independent teacher approval is still pending.

All topics

Original English exercises targeting selected 9618 2027-29 requirements. Conceptual alignment only; independent teacher approval, practical competence and complete teaching coverage are not established.

Subtopics in this topic (3)
  • DDL constraints
  • Two-table joins and aggregates
  • Transitive dependencies

5 of 5 questions shown

Loading saved responses

Question 1Write SQL DDL for Student(StudentID INTEGER primary key, Name VARCHAR(30)) and Score(StudentID INTEGER not null foreign key to Student, Mark INTEGER). Multiple scores per student are allowed. Write a two-table INNER JOIN query returning StudentID, Name and average Mark per student, ordered by StudentID. Students are(1,Ada),(2,Bo),(3,Cy); scores are(1,60),(1,80),(2,90). State query rows and why Cy is absent. Explain why Score.StudentID alone must not be its primary key.6 indicative marks
Construct, trace and explain6 indicative marksNo calculator

Write SQL DDL for Student(StudentID INTEGER primary key, Name VARCHAR(30)) and Score(StudentID INTEGER not null foreign key to Student, Mark INTEGER). Multiple scores per student are allowed. Write a two-table INNER JOIN query returning StudentID, Name and average Mark per student, ordered by StudentID. Students are(1,Ada),(2,Bo),(3,Cy); scores are(1,60),(1,80),(2,90). State query rows and why Cy is absent. Explain why Score.StudentID alone must not be its primary key.

Show indicative guidance

Worked guidance

  1. CREATE TABLE Student (
  StudentID INTEGER PRIMARY KEY,
  Name VARCHAR(30)
);
CREATE TABLE Score (
  StudentID INTEGER NOT NULL,
  Mark INTEGER,
  FOREIGN KEY (StudentID) REFERENCES Student(StudentID)
);
SELECT Student.StudentID, Student.Name, AVG(Score.Mark)
FROM Student INNER JOIN Score ON Student.StudentID = Score.StudentID
GROUP BY Student.StudentID, Student.Name
ORDER BY Student.StudentID;
  2. The join has three matched score rows: Ada60,Ada80,Bo90. Grouping produces Ada's mean(60+80)/2=70 and Bo's90. Cy contributes no matched row, so no invented zero mark or third row is produced.
  3. The foreign key prevents a non-null Score.StudentID referencing a missing student. A primary key on that column alone would reject Ada's second score. A complete design may add a separate ScoreID/composite attempt key, but this task deliberately permits repeated student references and does not require an outside-subset LEFT JOIN.

Self-marking points

View marking points
  1. Create Student with an INTEGER primary key and appropriately sized name field.
  2. Create Score with NOT NULL StudentID and FOREIGN KEY REFERENCES Student(StudentID).
  3. Do not make Score.StudentID unique because several scores share it.
  4. INNER JOIN on matching StudentID and group by both selected nonaggregate fields.
  5. Use AVG(Mark) and order by StudentID.
  6. Output(1,Ada,70),(2,Bo,90); unmatched Cy has no INNER JOIN row.

Examiner tip

Indicative distinct points, not an official Cambridge mark allocation. State bounds, assumptions and intermediate states; practical teacher review remains required.

Question 2Employee(EmpID,EmpName,DeptID,DeptName) has atomic fields and sole candidate key EmpID. Dependencies are EmpID->EmpName,DeptID and DeptID->DeptName; there are no other nontrivial dependencies. Decide whether it is in2NF and3NF, normalise to3NF with keys/foreign keys, and describe the E-R relationship assuming every employee has one department and a department may have many employees. Use rows(7,Dee,2,Lab),(8,Eli,2,Lab) to explain an update anomaly.6 indicative marks
Construct, trace and explain6 indicative marksNo calculator

Employee(EmpID,EmpName,DeptID,DeptName) has atomic fields and sole candidate key EmpID. Dependencies are EmpID->EmpName,DeptID and DeptID->DeptName; there are no other nontrivial dependencies. Decide whether it is in2NF and3NF, normalise to3NF with keys/foreign keys, and describe the E-R relationship assuming every employee has one department and a department may have many employees. Use rows(7,Dee,2,Lab),(8,Eli,2,Lab) to explain an update anomaly.

Show indicative guidance

Worked guidance

  1. EmpID is the only candidate key and is not composite, so there cannot be a dependency on a proper part of it. That supports2NF, not automatic3NF: DeptID is not a superkey but determines nonprime DeptName, violating3NF.
  2. The resulting rows are Employee(7,Dee,2),(8,Eli,2) and Department(2,Lab). EmpName remains employee-specific; DeptName is stored with the key that actually determines it. A foreign key on Employee.DeptID preserves the link, with NOT NULL needed to enforce the stated mandatory department in SQL.
  3. In an E-R sketch, put Department on the one side and Employee on the many side. The design removes this transitive duplication; no broad claim that normalisation eliminates every performance or security problem follows.

Self-marking points

View marking points
  1. Atomic fields establish the stated1NF; the single-attribute key leaves no partial-key dependency, so2NF holds.
  2. DeptName depends transitively on EmpID through nonkey DeptID, so3NF fails.
  3. Create Department(DeptID primary key,DeptName).
  4. Create Employee(EmpID primary key,EmpName,DeptID foreign key referencing Department).
  5. Relationship is Department one-to-many Employee, with each employee assigned exactly one department under the assumption.
  6. Before decomposition a rename must change both Lab copies and could disagree; afterward Department2 is updated once without losing either employee.

Examiner tip

Indicative distinct points, not an official Cambridge mark allocation. State bounds, assumptions and intermediate states; practical teacher review remains required.

Question 3Stock has rows (ID,Qty): (1,4),(2,9),(3,4), all non-null with unique ID. Execute UPDATE Stock SET Qty=Qty+2 WHERE Qty<5; then SELECT SUM(Qty) FROM Stock;. State final rows and aggregate, and explain why WHERE ID=1 would not implement the same update.4 indicative marks
Analyse4 indicative marksNo calculator

Stock has rows (ID,Qty): (1,4),(2,9),(3,4), all non-null with unique ID. Execute UPDATE Stock SET Qty=Qty+2 WHERE Qty<5; then SELECT SUM(Qty) FROM Stock;. State final rows and aggregate, and explain why WHERE ID=1 would not implement the same update.

Cambridge 2027: Direct conceptual match

Independent relational update gave rows1:6/2:9/3:6 and SUM21; ID-only counterexampleSUM19.

AI-assisted placement. Independent teacher approval pending.

Show indicative guidance

Worked guidance

  1. UPDATE Stock SET Qty = Qty + 2 WHERE Qty < 5;
SELECT SUM(Qty) FROM Stock;
  2. Both rows whose original quantity is4 satisfy Qty<5 and rise to6. Row2 remains9; the final sum is6+9+6=21.
  3. Filtering on ID=1 describes identity rather than low quantity. It leaves row3 at4, producing6+9+4=19 and failing the stated condition-based requirement.

Self-marking points

View marking points
  1. Updates IDs1 and3 only.
  2. Final quantities are6,9,6.
  3. SUM(Qty)=21.
  4. ID=1 misses qualifying row3 and instead gives total19.

Examiner tip

Apply the predicate to every row; a coincidentally qualifying ID is not the rule.

Question 4Team has primary-key IDs10 and20. Member(MemberID PRIMARY KEY, TeamID NOT NULL REFERENCES Team) starts empty. Inserts (1,10),(2,30),(1,20) are attempted in order; a failed statement leaves prior successful statements unchanged. Determine each success/failure and final Member rows. Explain why NOT NULL alone cannot reject team30.4 indicative marks
Analyse4 indicative marksNo calculator

Team has primary-key IDs10 and20. Member(MemberID PRIMARY KEY, TeamID NOT NULL REFERENCES Team) starts empty. Inserts (1,10),(2,30),(1,20) are attempted in order; a failed statement leaves prior successful statements unchanged. Determine each success/failure and final Member rows. Explain why NOT NULL alone cannot reject team30.

Cambridge 2027: Direct conceptual match

Traced statement-level failure semantics explicitly supplied: success/FK-failure/PK-failure, one retained row; no DB-engine default rollback assumed.

AI-assisted placement. Independent teacher approval pending.

Show indicative guidance

Worked guidance

  1. The first insert has a new member key and an existing parent team, so Member becomes [(1,10)].
  2. The second references nonexistent team30 and fails without undoing row1. The third references valid team20 but duplicates MemberID1, so it also fails.
  3. The final table still contains only (1,10). A non-null value30 passes a presence check; the foreign key is the distinct rule requiring an actual parent key.

Self-marking points

View marking points
  1. First insert succeeds.
  2. Second fails the foreign key because team30 is absent.
  3. Third fails duplicate primary key1; final sole row is(1,10).
  4. NOT NULL rejects missing values, not non-null nonexistent references.

Examiner tip

Check uniqueness and referential integrity separately.

Question 5A table stores PersonID PRIMARY KEY and Phones as a list-valued field: (7,['111','222']),(8,['333']). Under the syllabus requirement of atomic attributes, explain the1NF problem and design PersonPhone with keys. State its rows and the result of SELECT PersonID FROM PersonPhone WHERE Phone='222';. Assume each person lists each phone at most once but different people may share a phone.4 indicative marks
Analyse4 indicative marksNo calculator

A table stores PersonID PRIMARY KEY and Phones as a list-valued field: (7,['111','222']),(8,['333']). Under the syllabus requirement of atomic attributes, explain the1NF problem and design PersonPhone with keys. State its rows and the result of SELECT PersonID FROM PersonPhone WHERE Phone='222';. Assume each person lists each phone at most once but different people may share a phone.

Cambridge 2027: Direct conceptual match

Expanded exactly three associations; verified query7 and composite-key necessity under both many-phone and shared-phone cases.

AI-assisted placement. Independent teacher approval pending.

Show indicative guidance

Worked guidance

  1. The original field bundles multiple phone values into one attribute, contrary to the atomic-attribute requirement specified here.
  2. CREATE TABLE PersonPhone (
   PersonID INTEGER,
   Phone VARCHAR(20),
   PRIMARY KEY (PersonID, Phone)
);
  3. Expanding gives (7,'111'),(7,'222'),(8,'333'). The '222' selection returns7. PersonID alone would reject a second phone and Phone alone would reject a permitted shared phone; the pair identifies a listed association.

Self-marking points

View marking points
  1. List-valued Phones is a repeating/non-atomic group under the stated model.
  2. Uses one phone per row with composite key(PersonID,Phone).
  3. Rows are(7,'111'),(7,'222'),(8,'333'); query returns7.
  4. Phone alone is not a key because sharing is allowed.

Examiner tip

Key choice must follow the supplied sharing and uniqueness rules.

All AS & A Level subjects