Databases
SAMPLE TIME CONSTRAINED ASSESSMENT
M ARK I N G SCH EM E
This marking scheme has been prepared as a guide only to markers. This is not a set of
model answers, or the exclusive answers to the questions, and there will frequently be
alternative responses which will provide a valid answer. Markers are advised that, unless a
question specifies that an answer be provided in a particular form, then an answer that is
correct (factually or in practical terms) must be given the available marks.
If there is doubt as to the correctness of an answer, the relevant NCC Education materials
should be the first authority.
Throughout the marking, please credit any valid alternative point.
Where markers award half marks in any part of a question, they should ensure
that the total mark recorded for the question is rounded up to a whole mark.
Answer ALL questions
Question 1
a) Explain TWO (2) functions of a DBMS, with an example and why this is 4
important.
Mark Scheme
Concurrency (1) ensures multiple users can use the database at the same
time (1)
Security (1) Allows authorised users to gain access to relevant parts of the
database (1) keep unauthorised users out of the database(1)
Backup and recovery (1) Ensures that the database can be recovered in
the event of a failure (1)
Integrity (1)
Data descriptions (1) Describes the purpose of each field (1)
Stores data in linked tables (1) To avoid redundancy (1)
1 mark each point, valid alternatives accepted with 1 mark for explanation
of why important max 2 points for each function and example max 4
b) Identify FOUR (4) types of records/entities a car sales showroom might store 4
data about.
Mark Scheme
Cars (1)/ Customer (1) / Staff (1) / Sales (1) / Customer Finance (1)
Any suitable alternative / Fields alone do not count / maximum 4 marks
c) If an ERD has a many-to-many relationship describe what could be done to fix 2
it.
Mark Scheme
break the many-to-many relationship into two one-to-many relationships (1)
create a third (join) table that has the primary keys of each of the new
tables (1)
Total 10 Marks
Page 2 of 13
Databases © NCC Education Limited 2021
Marks
Question 2
a) The following table is in Third Normal Form (3NF). Suggest appropriate data 6
types and keys (if applicable) for the attributes listed.
Attribute Name Data Type Key
ServiceID Number (Auto increment) Primary Key
CustomerID
CarRegNo
ServiceDate
StaffID
Mark Scheme
Attribute Name Data Type Key
ServiceID Number (Auto Primary Key
increment)
CustomerID Number/Integer (1) Foreign Key (1)
CarRegNo VarChar (8)
ServiceDate Date/Time
StaffID Number/Integer (1) Foreign Key (1)
1 mark for each correct / appropriate data type used (character length is
not required for the mark to be awarded). Customer and Staff IDs should be
identified as a foreign key (1 mark for each one) Correct alternatives are to
be marked, to a maximum of 6 marks.
b) Identify TWO (2) properties of a Candidate key. 2
Mark Scheme
Any two of the following:
• It must contain unique values
• May have multiple attributes
• Must not contain null values
• Should contain minimum fields to ensure uniqueness
• Uniquely identify each record in a table.
c) Explain the term Optionality (in the context of an ERD) and provide a suitable 2
example.
Mark Scheme
Specifies if entities on one side must be joined to an entity on the other
side (1) any valid example (1)
Total 10 Marks
Page 3 of 13
Databases © NCC Education Limited 2021
Marks
Question 3
Consider the following scenario shown below:
A college student studies a number of modules and is issued with a transcript of
results at the end of the year.
a) Draw an Entity-Relationship (ER) diagram to represent the above scenario. 5
Mark Scheme
0..N 1
Student StudentResult Module
1 0..N
• 1 mark for each correct entity, to a maximum of 3 marks.
• 1 mark for each correct relationship, to a maximum of 2 marks (0.5
for each correct notation).
b) Identify all of the primary and foreign keys for the ER model in question 3 a). 5
Mark Scheme
Student:
StudentID (PK) – or any appropriate field name
Module:
ModuleID (PK)
Student Result (or suitable alternative):
StudentID (PK) (FK)
ModuleID (PK) (FK)
Or
ResultID (PK)
StudentID (FK)
ModuleID (FK)
• Award 1 mark for each (PK), to a maximum of 3 marks.
• Award 1 mark for each (FK) – to a maximum of 2 marks.
• Alternative naming allowed.
Total 10 Marks
Page 4 of 13
Databases © NCC Education Limited 2021
Marks
Question 4
a) Consider the ER diagram shown below for a Doctor’s surgery system through 6
which patients can book appointments with a Doctor.
0..N 1
Doctor Appointment Patient
1 0..N
i) Create a CRUD matrix to show the following transactions:
Transaction 1 – add a new appointment for an existing patient
Transaction 2 – delete a Doctor
Transaction 3 – update a Patient’s details
Transaction 4 – change the time of an appointment
Transaction 5 – produce a list of all appointments including Doctor & Patient details
Transaction 6 – add a new patient
Mark Scheme
Transaction Doctor Appointment Patient
T1 R C R
T2 D
T3 U
T4 R U R
T5 R R R
T6 C
1 mark for each correct row in a table, similar to the one shown above.
Maximum 6 marks (award part marks for part correct solutions, round
up final mark for question).
b) Identify the CRUD operations in a transaction. 4
Mark Scheme
CREATE (1)
READ (1)
UPDATE (1)
DELETE (1)
Award any valid alternate explanatory point, to a maximum of 4 marks.
Total 10 Marks
Page 5 of 13
Databases © NCC Education Limited 2021
Marks
Question 5
a) What does the acronym SQL stand for? 1
Mark Scheme
Structured Query Language – award only one (1) point for full answer – no
half points allowed.
b) Explain and summarise the purpose of SQL. 2
Mark Scheme
SQL is a database language (1) that allows the database to be manipulated
(1) or similar. Award 1 for a summary and 1 for the purpose, to a maximum
of 2 marks.
c) Consider the following tables:
tblAnimal
AnimalID AnimalName DOB Type
117 Leo 12/2/2017 Lion
218 Keeno 12/3/2015 Tiger
342 Darli 9/1/2012 Gorilla
912 Lou-Lou 18/7/2014 Elephant
1116 Doogle 25/4/2016 Elephant
1187 Prince 19/10/2015 Lion
tblKeeper
KeeperID KeeperName
1 Cecil Armstrong
2 Miranda Narzala
3 Derek Longbottom
4 Hermoine Garnett
tblAnimalKeeper
KeeperID AnimalID
1 117
1 1187
2 912
2 1116
3 218
4 342
Page 6 of 13
Databases © NCC Education Limited 2021
Marks
i) Write the SQL that produces a list of the animals, type and date of birth in 2
date order.
Mark Scheme
Select * from tblAnimal (1 mark)
Orderby DOB (1 mark)
Alternative Select AnimalName, DOB, Type from tblAnimal (1 mark)
d) Write the SQL that lists only the names and dates of birth of the lions. 2
Mark Scheme
Select AnimalName, DOB From tblAnimal (1 mark)
Where Type=”Lion” (1 mark)
Alternative Select * from tblAnimal (1 mark)
e) Write the SQL that produces a list of all of the animal names, their type and their 3
keeper names.
Mark Scheme
Select AnimalName, Type, KeeperName From tblAnimal, tblKeeper (1 mark)
WHERE tblAnimal.AnimalID = tblAnimalKeeper.AnimalID (1 mark)
AND tblKeeper.KeeperID = tblAnimalKeeper.KeeperID (1 mark)
Total 10 Marks
Question 6
a) Consider the following table:
tblFood
FoodID Description Cost Type
3 Chocolate Doughnut £1.12 Sweet
4 Meat Pie £0.89 Pies
5 Cheese Pie £1.02 Pies
6 Danish Pastry £1.05 Sweet
i) Write the SQL statement that will update the Pies type to Savoury. 3
Mark Scheme
UPDATE tblFood (1 mark)
SET Type “Savoury” (1 mark)
WHERE Type = “Pies” (1 Mark)
Page 7 of 13
Databases © NCC Education Limited 2021
Marks
b) Consider the following table:
tblItems
ItemID ItemDescripton Type Price
3 White 6 Seat Dining Table Table £399.95
4 Small Oak Coffee Table Table £129.99
5 Small Children Wardrobe Pine Bedroom £124.99
6 Toddler Bed White Bedroom £89.99
i) Write the SQL statement that will delete all of the data held for items where 2
the price is over £300.
Mark Scheme
DELETE from tblItems (1 mark)
WHERE Price > 300 (1 mark)
c) Consider the following table:
tblCar
CarID Manufacturer Model Price
1 Quintec CityXP £12,800
2 Smord Sprint £15,750
3 Nisax Family £21,995
i) Write the SQL statement that will create the table and add the data into the 5
newly created table.
Mark Scheme
CREATE TABLE tblCar(
CarID int NOT NULL AUTO_INCREMENT,
Manufacturer char (30),
Model char (20)
Price decimal (5,2)
PRIMARY KEY (CarID)
);
3 marks maximum, 1 mark correct syntax for create table, 1 mark for
appropriate correct fields/data types (use discretion), 1 mark for
primary key.
INSERT INTO tblCar (CarID, Manufacturer, Model, Price)
VALUES (1 mark for correct syntax)
(‘Quintec’,’CityXP’,12800.00),
(‘Smord’,’Sprint’,15750.00),
(‘Nisax’,’Family’,21995.00)
(1 mark for correct data ensure consistency between fields created
and data format / types)
Total 10 Marks
Page 8 of 13
Databases © NCC Education Limited 2021
Marks
Question 7
Woof Cuts is a grooming salon for dogs. It has several groomers. The customer
contacts the salon to make an appointment for their dog. The customer contact
details are recorded on the system together with the name and breed of the dog (so
that it can be determined if the dog is a small / medium or large breed – and can be
charged accordingly). An appointment is made for the dog at an agreed date and time
and a member of staff is assigned to that appointment. Special comments are noted
on the appointment (e.g. Dog must be muzzled etc.)
A suggested ER model is below:
0..N 1
Customer Appointment Staff
1 0..N
a) Replicate the data dictionary below for each one of the entities. 20
Using the information provided above, populate it with a range of suitable
attributes and associated meta data for each entity.
NB: For clarity it is expected that there will be multiple entries for each entity.
tblCustomer
AttributeName Data Type Length/Field Size Key
tblAppointment
AttributeName Data Type Length/Field Size Key
tblStaff
AttributeName Data Type Length/Field Size Key
Page 9 of 13
Databases © NCC Education Limited 2021
Marks
Mark Scheme
tblCustomer
AttributeName Data Type Length/Field Size Key
CustomerID AutoNumber Primary
CustomerName String 25
ContactNo String 15
NameofDog String 20
Breedofdog/Size String 20
tblAppointment
AttributeName Data Type Length/Field Size Key
AppointmentID AutoNumber Primary
CustomerID Number/Integer Foreign Key
StaffID Number/Integer Foreign Key
Price Double /
currency
Comments String 50
tblStaff
AttributeName Data Type Length/Field Size Key
StaffID Autonumber Primary
StaffFirstName String 20
StaffLastName String 25
Expectations and grading guidance:
KEYS: Would expect to see all relevant primary / foreign keys
(maximum 5 marks)
Each appropriate attribute (0.5 marks to maximum of 6)
Each appropriate data type (0.5 marks to maximum of 6)
Each appropriate length/field size – use discretion; field sizes shown are
just guidance (0.5 mark to maximum 3)
Total 20 Marks
Page 10 of 13
Databases © NCC Education Limited 2021
Marks
Question 8
a) Explain the purpose of normalisation 2
Mark Scheme
Reduce / eliminate data redundancy (1) create interlinking/related tables
(1)
b) What conditions need to be met if a table is said to be in second normal form 3
(2NF)
Mark Scheme
Any of the following up to a maximum of 3 marks
Primary key contains only one attribute (1)
No non-key attributes exist in the relation (1)
In 1NF (1 mark) AND
Every non-key attribute is functional dependent on the full set of primary
key attributes (1)
c) The following table is in first normal form (1NF) you should normalise it to 5
second normal form
Students
FirstName Surname Subject
Adam George Art and Design
Melissa Brown Art and Design
Shabana Atif Maths
Louise Jenkins Maths
Mark Scheme
Students
ID FirstName Surname Subject
1 Adam George 1
2 Melissa Brown 1
3 Shabana Atif 2
4 Louise Jenkins 2
Subject
ID Subject
1 Art and Design
2 Maths
1 mark each for identification of each table (max 2)
1 Mark for each for each ID in each table (max 2)
1 mark for identification of subject ID in student table
Total 10 Marks
Page 11 of 13
Databases © NCC Education Limited 2021
Marks
Question 9
a) Explain each of the following DBMS terms. 5
i) Tuple
Mark Scheme
Tuple: is a row or a record in a table (1 mark)
ii) Attribute
Mark Scheme
Attribute: is a column / field / data item in a table (1 mark)
iii) Data type
Mark Scheme
A data type, is a type of data / it specifies which type of value an
attribute has (1 mark)
iv) Optionality
Mark Scheme
Optionality: Specifies if entities on one side must be joined to an
entity on the other side (1 mark)
v) Cardinality
Mark Scheme
Cardinality: Shows the type of relationship i.e. 1:M, M:1, M:M (1 mark)
b) Explain the term referential integrity and provide an example. 5
Mark Scheme
Referential integrity refers to the accuracy (1) and consistency (1) of data
within a relationship. In relationships, data is linked between two or more
tables (1). ... Referential integrity requires that, whenever a foreign key
value (1) is used it must reference a valid, existing primary key in the
parent table. (1)
Alternative - 1 mark for each suitable point, to a maximum of 5.
Total 10 Marks
End of paper
Page 12 of 13
Databases © NCC Education Limited 2021
Marks
Learning Outcomes matrix
Question Learning Outcomes Marker can differentiate
assessed between varying levels of
achievement
1 1,3 Yes
2 3,4 Yes
3 2,3 Yes
4 3 Yes
5 3 Yes
6 5 Yes
7 4,5 Yes
8 2,3 Yes
9 1,2,3,5 Yes
Grade descriptors
Learning Outcome Pass Merit Distinction
Understand the Demonstrate Demonstrate Demonstrate highly
concepts adequate level of robust level of comprehensive level
associated with understanding understanding of understanding
database systems
Understand the Demonstrate Demonstrate Demonstrate highly
concepts adequate level of robust level of comprehensive level
associated with the understanding understanding of understanding
relational model
Understand how to Demonstrate Demonstrate Demonstrate highly
design and develop adequate level of robust level of comprehensive level
a database system understanding understanding of understanding
Be able to develop Show adequate Show sound and Show innovative and
a logical database development appropriate highly appropriate
design development development
Be able to develop Show adequate Show sound and Show innovative and
a database system development appropriate highly appropriate
using SQL development development
Page 13 of 13
Databases © NCC Education Limited 2021
