A-LevelComputer ScienceDatabases9618/12 · Oct/Nov 2021 · Q615 Marks

A shop sells plants to customers. The shop manager has a relational database to keep track of the sales. The database, PLANTSALES, has the following structure: PLANT(PlantName, QuantityInStock, Cost) CUSTOMER (CustomerID, FirstName, LastName, Address, Email) PURCHASE (PurchaseID, CustomerID) PURCHASE_ITEM(PurchaseID, PlantName, Quantity)

(a)(i)The database is normalised. The table lists the following three stages of normalisation: • The first stage is from a database that is not normalised (0NF) to First Normal Form (1NF). • The second stage is from 1NF to Second Normal Form (2NF). • The third stage is from 2NF to Third Normal Form (3NF). Tick (✔) one box in each row to identify the appropriate stage for each task. Normalisation stage Task 0NF to 1NF 1NF to 2NF 2NF to 3NF Remove any partial key dependencies Remove any repeating groups of attributes Remove any non-key dependencies[2]
(a)(ii)Draw an entity-relationship (E-R) diagram for the database PLANTSALES. [Figure showing empty boxes for PLANT, PURCHASE_ITEM, CUSTOMER, PURCHASE][3]
(b)The shop manager uses a Database Management System (DBMS). Describe the purpose and contents of the data dictionary in the DBMS.[3]
(c)(i)The shop manager uses both Data Definition Language (DDL) and Data Manipulation Language (DML) statements to create and search the database. Complete the DML statements to return the total number of items purchased with the purchase ID of 3011A. SELECT SUM(.. .) FROM WHERE ;[4]
(c)(ii)Write DDL statements to include a field in the table PURCHASE to store the date of the order.[3]

This question uses a diagram from the original paper, which isn't shown here. Open the full paper from the past papers archive to see it.

Mark scheme

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

[1] Correct tick for 'Remove any partial key dependencies' in '1NF to 2NF'
[1] Correct tick for 'Remove any repeating groups of attributes' in '0NF to 1NF'
[1] Correct tick for 'Remove any non-key dependencies' in '2NF to 3NF'
[1] 1 mark for each correct relationship (CUSTOMER to PURCHASE - 1 to Many)
[1] 1 mark for each correct relationship (PURCHASE to PURCHASE_ITEM - 1 to Many)
[1] 1 mark for each correct relationship (PURCHASE_ITEM to PLANT - Many to 1)
[1] 1 mark for description of purpose: Stores metadata about the database
[1] 1 mark for each example of contents to max 2: e.g. field / attribute names
[1] 1 mark for each example of contents to max 2: e.g. table name / validation rules / data types / primary keys // foreign keys / relationships
[1] SUM(Quantity)
[1] FROM PURCHASE_ITEM
[1] WHERE PurchaseID
[1] = "3011A"
[1] ALTER TABLE PURCHASE
[1] ADD OrderDate
[1] Suitable data type, e.g. DATE

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.