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.
a. Define the term record.[1]
b. 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.[3]
c.i. Identify the steps to create a non-persistent derived field called Totalprice, which would hold the total value of wine stored for each record.[2]
c.ii.Outline why the inclusion of a derived field will not affect the normalization of a database.[2]
d. Construct the 3rd Normal Form (3NF) of the unnormalized Wine file.[6]
e. Outline why a single-field primary key is not always an appropriate solution for normalized databases.[2]
▶️Answer/Explanation
Ans:
a.)
A record is one row in a table;
All of the fields relating to item of information;
b. )
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”;
c.i. )
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;
c.ii.)
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;
d. )
Example of solution (accept different names for tables)
Vineyard (Vineyard, Region)
Wine (WinelD, Wine, Vineyard ${ }^{(\mathrm{fk})}$, Year, Flavour, APV, UnitPrice)
Stock (StorelD, WinelD ${ }^{(f k)}$, StockQty)
Vineyard table e.g. Vineyard (Vineyard, Region)
Award [1] for primary key. Either Vineyard or VineyardID.
Wine table e.g. Wine (WinelD , Wine, Vineyard $(\stackrel{(\mathrm{fk})}{ }$, Year, Flavour, APV, UnitPrice)
Award [1] for primary key WinelD 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 (StorelD, WinelD ${ }^{\text {(fk) }}$, StockQty)
Award [1] for composite key StorelD/WinelD or Store/Wine/Vineyard
Award [1] for WinelD or equivalent key shown as foreign key
Note: Should candidates provide other reasonable solutions, please contact your team leader.
e.)
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;
Question
Billetmania is an online company that sells tickets for theatre performances and music concerts. After a customer has chosen their seats, they can pay for the tickets through a secure online payment system. Once the transaction has been completed, the customer receives an email receipt.
An information system and a database are used for Billetmania’s day-to-day operations.
a. Define the term database transaction.[1]
b. Outline how the Billetmania information system would utilize a database.[2]
c. Explain the importance of transaction durability to Billetmania when clients book tickets.[3]
d. Explain how the Billetmania database management system ensures that a seat is not booked by two people simultaneously.[4]
▶️Answer/Explanation
Ans:
a. )
A logical unit of work that is executed in full or not at all;
A transaction is a single logical operation that comprises of a sequence of database operations;
A transation satisfies the ACID (Atomicity, Consistency, Isolation, Durability) properties;
b. )
The database would store details of concerts, tickets sold, customers;
An information system would access the database to present data it in a way that aids informs managers / aids decision making (e.g. charts of ticket sales);
Accept any other reasonable example.
c. )
Durability ensures that transactions are saved permanently and do not accidentally disappear or get erased;
Billetmania does not need to worry the transaction being lost even in the event of power loss, crashes, or errors;
A seat in a theatre is guaranteed even if the database crashes;
d. )
Concurrency allows multiple users try to book tickets at the same time / complete a transaction at the same time;
Concurrency prevents access by more than one user to the same row/record;
Concurrency uses row locking;
Select seats are locked for a short period of time to ensure that they aren’t double booked/overwritten;