Preview

05 - Normalisation

 1. Normalization is a database design technique which organizes tables in a manner that reduces _______________________ of data

  redundancy and dependency

  the volume

  isolation and incrementation

  the errors and non essential attributes

 2. Another definition of normalisation is as follows. Fill in the blanks
Normalization is the process of efficiently organizing data in a database. 

There are two goals of the normalization process: 

1. eliminating redundant data (which often requires the ______
___________________________) 


and 

2. ensuring data dependencies make sense (only storing 
related data in a table)

  creation of a single table

  creation of multiple tables

  creation of simplified relationships

  creation of many to many relationships, when it is possible to do so

 3. Typically, normalisation divides ________________________________________

  smaller tables into larger tables in order to keep them consistent

  relationships into sub relationships

  tables into fields and rows, using relationships as links

  larger tables to smaller tables and links them using relationships

 4. The inventor of the relational model Edgar Codd proposed the theory of normalization with the introduction of First ____________, and he continued to extend theory with Second and Third ________________

  primary key

  centered index

  normal form

  queried SQL

 5. Which of the following are valid reasons for normalising a database?

  To minimise duplication of data

  To minimise or avoid data modification (update) issues

  To simplify the process of creating queries

  All of the mentioned answers are valid reasons

 6. Can you identify any problems in the following database table design?
normalisation_1.png

  Data for the fields 'branch' and 'hod' is duplicated

  The 'name' field is not directly linked to the primary key

  the 'office_tel' field should or could be integrated with the branch field instead of being separate

  There are no issues at all as this has been normalised

 7. Which of the following are also problems/anamolies that can occur due to the lack of normalisation? (refer to above image)

  All of the mentioned answers are valid anomalies

  Updation anomaly: If Mr. X leaves the college, all records would have to be updated.

  Deletion anomaly: If student data is deleted, branch data is also lost with those records

  Insertion anomaly: If we need to insert a 1000 students with the same branch, the branch will be repeated

 8. The "normalisation oath" is used as a little tip to help remember the concept: "Each attribute is dependent on the key, the whole key, and _______________________"

  every primary key

  every other key

  nothing but the key

  only a secondary key

 9. The process of refining the structure of a database to ______________________________________ is called normalisation. When a database has been normalised, it is said to be in normal form

  minimise redundancy and improve integrity

  maximise duplication and reduce integrity

  minimise integrity and improve duplication

  find errors and remove integrity

 10. A database is in _______if there are no repeated fields. That means that there must only be one field for each item of data you want to score.

  1NF

  3NF

  2NF

  Not in normal form

 11. A database is said to be in second normal form if it: is already in first normal form ________________________________

  and has at least two primary keys

  and has no fields that aren't dependent on the whole of the key

  and only has fields that are dependent on more than one key

  and all its fields depend on not just the key but each other

 12. If you had a table with the following fields, assume 'Name' was the key: It isn't in 2NF beacuse:
tblStudentAwardst>> 
-------------
Name
Tutor group
Teacher
Subject
Date
Reason
Head of year
Tutor

  there are fields that are not dependent on the key (e.g. subject is related to the teacher, not student)

  there are no secondary keys defined

  there is only one primary key

  

 13. A database is in 3NF if: it is in 2NF and ______________________________________________
tblStudentAwardst>> 
-------------
Name (key field)
Tutor group
Teacher
Subject
Date
Reason
Head of year
Tutor

  in this example, the non key dependency would be dealt with (e.g. subject is related to teacher and not to the student)

  All of the above options are valid ways of answering the question

  it has no non-key dependencies

  it has no fields that are dependent on other fields that are not part of the key

 14. This table has a composite primary key [Customer ID, Store ID]. The non-key attribute is __________________
normalisation_2.png

  [Customer ID]

  None of the listed options are correct

  [Store ID]

  [Purchase Location].

 15. In the above example, [Purchase Location] only depends on [Store ID], which is only part of the primary key. Therefore, this table does perfectly satisfy second normal form.

  FALSE

  TRUE

 16. In this example there are no partial functional dependencies. This means that the column [Purchase Location] is fully dependent on the primary key of that table, which is [Store ID]
Note: Compare this to the previous example
normalisation_3.png

  TRUE

  FALSE

 17. The following database appears to be in 3NF
All non-key attributes are fully functional dependent only on 
the primary key. In [TABLE_BOOK], both [Genre ID] and [Price] 
are only dependent on [Book ID]. 

In [TABLE_GENRE], [Genre Type] is only dependent on [Genre ID].
normalisation_4.png

  FALSE

  TRUE

 18. The following is not in 1NF because _______________________________________
normalisation_5.png

  The values in the 'Course' column are not dependent on the primary key

  The 'Content' column as well as the 'Course' column has multiple instances of duplication

  The values in the 'Content' column are not atomic (indivisible)

  There are no fields that are dependent on any keys

 19. In the example below, state why the database is not normalised.
normalisation_6.png

  Because the CustomerID field is not unique

  Branch name depends on Sort Code (i.e. there is a transitive relationship).

  Because the 'Sort-code' field is non-atomic and can be divided further

  Because the Branch name should depend directly on the Acc No and not the Sort code

 20. For the question and example above, how could the database be put into Third Normal Form?

  All of the listed answers are valid answers to the question

  Create another table for Branches which should include sort-code and branch name

  Make sort code the primary key of the BRANCH table/ Add a primary key to BRANCH

  Two tables CUSTOMER and BRANCH (or similar names) Link from CUSTOMER to BRANCHES is Many to One