Databases
08 June 2021
Time-Controlled Assessment Paper
Answer ALL questions.
Clearly cross out surplus answers.
Time: 4 hours
The maximum mark for this paper is 100.
Any reference material brought into the examination room must be
handed to the invigilator before the start of the examination.
Answer ALL questions
Question 1
a) State/identify FOUR (4) main functions for which a supermarket would use a 4
database.
b) Identify TWO (2) functions of a DBMS. 2
c) Identify FOUR (4) types of records/entities a car dealership might use to store 4
data in.
Total 10 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
ItemID Number (Auto increment)
ItemName
ItemPrice
QtyInStock
SupplierID
b) Identify TWO (2) properties of a Foreign key and draw a diagram to show an 2
appropriate example.
c) Explain the difference between an attribute and an entity. Illustrate your answer 2
with an example.
Total 10 Marks
Question 3
Consider the following scenario shown below:
A patient makes an appointment to see a Doctor. At the time of booking a single
Doctor is assigned to the appointment.
a) Draw an Entity-Relationship (ER) diagram to represent the above scenario 5
b) Identify all of the primary and foreign keys for the ER model in question 3 a) 5
Total 10 Marks
Page 2 of 6
Databases © NCC Education Limited 2021
Marks
Question 4
a) Consider the ER diagram shown below for a holiday accommodation park. 6
0..N 1
Customer Booking Accommodation
1 0..N
Create a CRUD matrix to show the following transactions:
Transaction 1 – add a new booking for a new customer
Transaction 2 – delete an accommodation
Transaction 3 – update an existing booking
Transaction 4 – update the price of all accommodation
Transaction 5 – produce a list of all bookings including Customer and
Accommodation details
Transaction 6 – add a new accommodation
b) If your relational model had a M:M (many to many) relationship, explain what you 2
would need to do to resolve the issue. Draw an example to help explain your
answer.
c) Explain what is meant by a 1:1 (1 to 1) relationship. Draw an example to help 2
explain your answer.
Total 10 Marks
Questions continue on the next page
Page 3 of 6
Databases © NCC Education Limited 2021
Marks
Question 5
Consider the following tables:
tblStock
StockID Name Price CategoryID SupplierID
11 A1 Smart WiFi Plug £14 4 14
28 Ra Smart Speaker £72 4 34
32 NuTech 52” Smart TV £699 1 29
92 DosTec Laptop Hub £65 3 11
111 NuMedia Smart TV £45 4 34
Dongle
118 TopTech 60” Smart TV £799 1 29
tblCategory
CategoryID CategoryType
1 Television
2 Sound Bar
3 Computer Components
4 Smart Home
tblSupplier
SupplierID SupplierName
11 TecTel
14 Armstrad Electricals
22 Trashiba Ltd
29 Sumsang
34 Narla Tech
a) Write the SQL that produces a list of all supplier names in alphabetical order. 2
b) Write the SQL that lists all stock details and the names of the category they 2
belong to.
c) Write the SQL that produces a list of all stock items that are supplied by Narla 3
Tech.
d) Write the SQL that lists the stock name and the supplier name of all products that 3
are in the ‘Smart Home’ category.
Total 10 Marks
Page 4 of 6
Databases © NCC Education Limited 2021
Marks
Question 6
a) Consider the following table 2
tblTariff
TariffID Description MonthlyCost ContractLength
3 Huge Data £24 24
100GB
4 Text/Talk/Social £21 12
20GB
5 Mid Data + inc £16 1
Text/Talk
6 No frills Data 5gb £9 12
Write the SQL statement that will update the monthly cost of all tariffs by 5%
b) Consider the following table 2
tblMenuItems
MenuID ItemDescripton Type Price
3 Breakfast Buffet Breakfast £14
4 American Pancake Stack Breakfast £9.99
5 Afternoon Tea for 2 Lunch £24.99
6 Lunch Special 2 course Lunch £12.99
7 Early Bird Dinner 2 Course Dinner £19.99
8 Set Dinner 2 Course (after 6pm) Dinner £24.99
9 Set Dinner 3 Course Dinner £29.99
Write the SQL statement that will delete all the data for menu items within the
Breakfast type category.
c) Consider the following table: 6
tblHire
HireID Description Duration Price
1 E-Scooter Short 1 hour £4
2 E-Scooter Medium 4 hours £12
3 E-Scooter Daily 10 hours £20
Write the SQL statement that will create the table and add the data into the newly
created table.
Total 10 Marks
Page 5 of 6
Databases © NCC Education Limited 2021
Marks
Question 7
NuBank is a new high street bank. It offers a range of bank accounts to 20
customers. Some accounts may be age restrictive, as they may have credit
facilities (such as an overdraft). Transactions are made on the account.
Examples of transactions are ATM (Cash machine) withdrawal or a debit card
transaction. These transactions are made against a customer account.
Draw an ER-diagram to represent the scenario above, including the cardinalities.
Create a data dictionary for each identified entity
Replicate the data dictionary below for each one of the entities.
Using the information described 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
TABLE NAME
AttributeName Data Type Length/Field Key
Size
Total 20 Marks
Question 8
a) Identify TWO (2) benefits of normalisation. 2
b) Typically, there are THREE (3) stages of normalisation – 1NF, 2NF, 3NF. Explain 8
the process for each of the three stages using appropriate examples.
Total 10 marks
Question 9
a) Explain why a company would decide to implement a DBMS. 5
b) Enforcing data integrity via data constrains helps to ensure the accuracy of the 5
data entered by users.
Give FIVE (5) examples of data integrity types that could be enforced within a
column.
Total 10 Marks
End of paper
Page 6 of 6
Databases © NCC Education Limited 2021
