| Date | May 2018 | Marks available | 3 | Reference code | 18M.2.SL.TZ0.3 |
| Level | SL | Paper | 2 | Time zone | no time zone |
| Command term | Explain | Question number | 3 | Adapted from | N/A |
Question
Jackson City University has a Music Department that provides music lessons to students in a number of high schools in the city.
The Jackson City University Music Department teachers visit the different schools in the city to teach students a range of musical instruments.
The following diagram shows an unnormalized table of student data.
Explain one benefit of normalizing a database.
Identify three ways that incorrect data could be prevented from being added into the School_phone_no field.
Outline what would be necessary to make the above unnormalized table conform to 1st Normal Form (1NF).
Construct the 3rd Normal Form (3NF) of the unnormalized relation shown above.
Explain the difference between 2nd Normal Form (2NF) and 3rd Normal Form (3NF).
Markscheme
Award up to [3 max].
Normalization leads to a reduction in the chance of data redundancy occurring;
Because each item of data only occurs on one location in the database;
Therefore this can reduce the possibility of update anomalies occurring;
More efficient use of memory;
Normalization reduces the likelihood of update anomalies occurring;
Because each item of data only occurs on one location in the database;
Therefore if the data is edited there is no chance that the data may exist in its original form anywhere else in the database;
Normalization leads to smaller tables with less information in each row;
This leads to a reduction of input/output transfers;
Which means that the likelihood of CPU activities being suspended are reduced/the CPU is able to work at full capacity;
Note: Do not award marks between clusters.
Use a text field for the School_phone_no with a length of 9 characters (Accept limit check);
Use an input mask such as 000000000 to ensure only numbers can be entered;
Use a validation rule to ensure the first three entries can only be “065”
Accept format check;
Award up to [2 max].
Some values are not atomic in the Student_Choice and School_phone_no fields/attributes;
Repeat rows for students with multiple choices / phone numbers with one choice per row;
Each record must have a unique key;
For Student_Choice have three columns, Stud_Choice1, Stud_Choice2 and Stud_Choice3, with null values (or default values set to “none”) as appropriate or two columns for School_phone_no;
Award up to [8 max].
Alternative 1
For a three table solution:
Student table
Award [1] for primary key: student_ID // Do not accept other suggestions for the primary key.
Award [1] for attributes relating to student e.g.
First_Name
Family_Name
Date_of_birth
Note: Do not penalize additional attributes such as Student_choice.
School table
Award [1] for primary key. Either school_Name or user defined key such as SchoolID // Do not accept a composite key.
Award [1] for attributes relating to school such as:
School(School_Name)
School_Post_code,
School_phone_no.
Note: Do not penalize additional attributes such as Student_choice.
Student choice Table/Instrument table
Award [1] for primary key. Either user-defined or a composite key.
Award [1] for attributes relating to choice such as Student_choice/Instrument choice
Award [1] for each foreign key up to a maximum of [2 max].
Example of three table solution
STUDENT (Student ID, First_Name, Family_Name, Date_of_Birth)
SCHOOL (School_Name, School_PostCode, School_Phone_no)
STUDENT CHOICE (Choice ID, Student_ID*, School_Name*, Choice)
Alternative 2
For a four table solution
Student table
Award [1] for primary key: student_ID // Do not accept other primary keys
Award [1] for attributes relating to student e.g.
First_Name
Family_Name
Date_of_birth
Note: Do not penalize additional attributes such as Student_choice.
School table
Award [1] for primary key. Either school_Name or user defined key such as SchoolID // Do not accept a composite key
Award [1] for attributes relating to school such as:
School(School_Name)
School_Post_code
School_phone_no
Note: Do not penalize for additional second School_Phone_no field.
Student choice Table/Instrument table
Award [1] for primary key. Either Lesson_ID or Instrument_ID // Do not accept a composite key.
Award [1] for attributes relating to choice such as Student_choice/Instrument choice.
Details Table/Lessons Table
Award [1] for primary key. Any ID or composite.
Award [1] for the correct foreign key in this table.
Note: This is the only table in which marks can be awarded for the use of a composite [primary] key.
Example of four table solution
STUDENT (Student_ID, First_Name, Family_Name, Date_of_Birth)
SCHOOL (School_Name, School_PostCode, School_Phone_no)
STUDENTCHOICE(Lesson_ID,Instrument)
DETAILS (Lesson_ID*, Student_ID*, School_Name*)
2NF allows non-prime attributes to be functionally dependent on non-prime attributes which means there are transitive dependencies at 2NF;
While 3NF allows non-prime attributes to be functionally dependent only on the primary / super key and is already in 2NF;
Therefore it is not possible for update anomalies to occur when a database is in 3NF;
