| Date | May 2019 | Marks available | 4 | Reference code | 19M.2.SL.TZ0.3 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | Outline | Question number | 3 | Adapted from | N/A |
Question
Armour Hardware Company has the following data about salespersons and the quantities of items sold.
Each salesperson can sell many different products.
SALES_PERSON
Outline two reasons why databases are normalized.
Outline why the SALES_PERSON table is not in 1st Normal Form (1NF).
Construct the 3rd Normal Form (3NF) of the unnormalized relation shown above.
Outline why it is necessary to ensure that referential integrity is maintained in databases.
Outline why a primary key may consist of more than one attribute.
Markscheme
Award [4 max].
Applications interacting with the database are minimally affected;
When a fully normalized database structure is extended, to accommodate new types of data, database structure can remain largely or entirely unchanged;
So, the applications interacting with the database are minimally affected;
Key dependent;
Every non-key column in every table is directly dependent on the key, the whole key and nothing but the key;
Thus making reduced redundancies, lesser anomalies and better efficiencies;
Mark as [2] and [2].
Award [2 max].
For First Normal Form, each attribute value should be atomic;
In the given example product_Num, Pro_Name, Unit_Price and Qty is multivalued / All 4 tuples shown have multiple values in their first 4 attributes;
Award [8 max].
Award [1] for each correct table up to [4 max].
Award [1] for each correct primary key up to [4 max].
3NF
Product_Number
Product_Number, Unit_Price, Product_Name
Sales_Person
Sales_Person_Number, Sales_Person_Name, Manager_Number
Manager
Manager_Number, Manager_Name
Purchases
Purchase_ID, Date_And_Time, Product_Number, Sales_Person_Number
Award [2 max].
You can enter foreign keys that do not match the corresponding primary key in the related table;
This could cause a lot of problems such as mismatched customer data and mismatched transaction records;
Cascading update;
If the primary key for a record in the Managers table changes, all corresponding records in the Employees table are modified;
Cascading delete;
If a record in the Managers table is deleted, all corresponding records in the Employees table are deleted;
Award [2 max].
The combination of the two provides unique identifiers for the records of the table;
And that there is no single attribute that is able to uniquely identify a record;
In a manner that will not lead to potential duplication of records;
It identifies exactly one record of the table, then that record shows the single value of each of the non-key attributes;
That is associated with the unique combination of the key attributes;
Examiners report
Most students were clear about normalisation in removing redundancy but only a few could identify reasons of key dependencies and interaction of the applications with the database.
Most students were able to identify why the data was not in First Normal Form but some of the students could not use the terms ‘atomic’ or ‘multi-valued’.
Most students are able to correctly split the tables but only a few identified the foreign keys. The primary keys were in most cases identified.
Many students could not explain the referential integrity.
Most students understood the composite keys and the need for them.
