| Date | November 2020 | Marks available | 2 | Reference code | 20N.2.SL.TZ0.3 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | Outline | Question number | 3 | Adapted from | N/A |
Question
WineForAll is a retailer that sells wine in its stores. Each store sells wine from a number of vineyards.
The following extract from the Wine file contains unnormalized data.
Define the term record.
Identify the steps to create a query to find the vineyards and names of fruity wines where the quantity in stock is between 25 and 35 bottles.
Identify the steps to create a non-persistent derived field called TotalPrice, which would hold the total value of wine stored for each record.
Outline why the inclusion of a derived field will not affect the normalization of a database.
Construct the 3rd Normal Form (3NF) of the unnormalized Wine file.
Outline why a single-field primary key is not always an appropriate solution for normalized databases.
Markscheme
Award [1 max].
A record is one row in a table;
All of the fields relating to item of information;
Award [3 max].
Fields and Table: Vineyard and NameOfWine FROM Wine;
Criteria: StockQty >= 25 AND StockQty <= 35; note: allow >25, <35
Description: Fruit is extracted from the field.
(Do not award the mark if Description = Fruity)
SELECT Vineyard, NameOfWine FROM Wine
WHERE (StockQty >= 25 AND StockQty <= 35)
AND Description LIKE ‘*Fruity*’;
Accept other versions including:
SELECT Vineyard, NameOfWine FROM Wine
WHERE (StockQty BETWEEN 25 AND 35)
AND Description LIKE ‘*Fruity*’;
Award [2 max].
All three points needed for [2]. Two points needed for [1].
UnitPrice * StockQty;
Totalprice named;
WINE table named;
Accept versions similar to the one shown below:
SELECT UnitPrice * StockQty AS totalprice
FROM Wine;
Award [2 max].
A derived field is created / does not exist in the table/ is temporary;
So the rules of normalization do not apply / is not affected by duplication / redundancy;
No new dependencies are created;
Award [6 max].
Example of solution (accept different names for tables)
Vineyard (Vineyard, Region)
Wine (WineID, Wine, Vineyard(fk), Year, Flavour, APV, UnitPrice)
Stock (StoreID, WineID(fk), StockQty)
Vineyard table e.g. Vineyard (Vineyard, Region)
Award [1] for primary key. Either Vineyard or VineyardID.
Wine table e.g. Wine (WineID, Wine, Vineyard(fk), Year, Flavour, APV, UnitPrice)
Award [1] for primary key WineID or composite Wine/Vineyard
Award [1] for identifying the foreign key (Vineyard)
Award [1] for splitting the three description fields
Stock table e.g. Stock (StoreID, WineID(fk), StockQty)
Award [1] for composite key StoreID/WineID or Store/Wine/Vineyard
Award [1] for WineID or equivalent key shown as foreign key
Note: Should candidates provide other reasonable solutions, please contact your team leader.
Award [2 max].
Relationship 1-1 might not exist;
Which means a single-field PK might not uniquely identify a record;
So a composite key is needed made up of 2 or more fields;
Allow any suitable example;
Examiners report
This was a very straight-forward question.
Candidates were well-prepared with many providing actual queries. However, many did not achieve full marks for incorrectly identifying the adequate step to filter fruity wines.
This question was generally well-answered as many candidates were able to identify the steps for creating the derived field.
In the second part of this question, many students failed to relate derived fields to normalization.
Most students were able to split the tables but only a few identified the foreign keys and correctly relate the tables to each other. Many students also identified that data needed to be atomic and were able to include this in the response. Candidates were not required to re-write the table.
Some responses were reasonable, but many candidates were not aware of the use of composite keys/limitations of primary keys in normalized databases.
