A-LevelComputer ScienceDatabases9618/12 · Oct/Nov 2023 · Q211 Marks

State what is meant by the following terms in a relational database model. Entity Primary key Referential integrity

(a)State what is meant by the following terms in a relational database model. Entity Primary key Referential integrity[3]
(b)Authentication is one method a Database Management System (DBMS) can use to improve the security of a database. Describe other methods that a DBMS can use to improve the security of a database.[4]
(c)The following database table is not normalised. StudentName DateOfBirth TutorGroup Subject SubjectCode Yuwei Chen 01/09/2004 SMH English, Maths, Computer Science EN, MA, CS Claudia Raj 23/02/2005 JMB Maths, Physics, Art MA, PY, AR Aamil Akram 24/01/2005 KMB Art, Design, English language AR, DE, EN Areeba Faraz 21/12/2004 SMH English language, Chemistry, Design EN, CH, DE Explain how to modify the table to put it into First Normal Form (1NF).[4]

Mark scheme

Marking points from the official mark scheme (Oct/Nov 2023). The mark or mark type is shown in brackets.

[3] (a) Entity: An object about which data can be stored Primary key: The unique attribute / combination of attributes used to identify the record / tuple Referential integrity: Makes sure that if data is changed in one place the change is reflected in all related records - cascading update/delete Makes sure that data that does not exist cannot be referenced Ensures that every foreign key has a corresponding primary key // A logical dependency of a foreign key on a primary key Ensures that the data in the database is consistent / up to date Prevents records from being added/deleted/modified incorrectly Makes sure any queries return accurate and complete results
[4] (b) Backup / recovery procedures: automatically takes copies of the database and store off site on a regular basis / weekly, etc., so that the data can be recovered if lost. Use of access rights: some users are given different access permissions to different tables, e.g. read/write, read only, full access, etc. Views: different users are able to see different parts of the database, only see what users need to see // by example. Record and table locking: prevents simultaneous access to data, so updates are not lost // data is not overwritten. Encryption: the data is turned into ciphertext, so it cannot be understood without a decryption key.
[4] (c) Identify repeating groups of attributes, such as Subject and SubjectCode. Ensure each field is atomic. StudentName should be split into e.g. FirstName and LastName. Identify the primary key for the table.