Preview

05 - Normalisation

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

  the volume

  the errors and non essential attributes

  redundancy and dependency

  isolation and incrementation

 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 simplified relationships

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

  creation of a single table

  creation of multiple tables

 3. Typically, normalisation divides ________________________________________

  larger tables to smaller tables and links them using relationships

  relationships into sub relationships

  tables into fields and rows, using relationships as links

  smaller tables into larger tables in order to keep them consistent

 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 ________________

  centered index

  queried SQL

  primary key

  normal form

 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

  All of the mentioned answers are valid reasons

  To simplify the process of creating queries

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

  There are no issues at all as this has been normalised

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

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

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

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

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

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

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

  All of the mentioned answers are valid anomalies

 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

  nothing but the key

  every other 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

  find errors and remove integrity

  minimise redundancy and improve integrity

  maximise duplication and reduce integrity

  minimise integrity and improve duplication

 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.

  3NF

  Not in normal form

  2NF

  1NF

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

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

  and has at least two primary keys

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

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

 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 is only one primary key

  there are no secondary keys defined

  

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

 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

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

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

  it has no non-key dependencies

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

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

  [Purchase Location].

  [Customer ID]

  [Store ID]

  None of the listed options are correct

 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

  FALSE

  TRUE

 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

  There are no fields that are dependent on any keys

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

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

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

  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

  Because the CustomerID field is not unique

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

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

  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

  All of the listed answers are valid answers to the question

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


Report an error