WorksheetsL3 IT Unit 2 Databases: Normalisation
Total questions: 18
Worksheet time: 9mins
Normalisation is:
Filling in empty fields so that all rows / tuples contain data.
Organising a database to remove duplication of data, and maintain the accuracy / integrity of the data.
Putting fields from different tables into one big database.
An advantage of normalisation is:
The storage space needed for a normalised database is likely to be smaller
The process of normalising a database is very easy and doesn’t require any skill
It is possible to see previous details that have been changed, such as a customer’s address, as we can look back at an older entry
What is an atomic field?
A field that contains multiple items of data
A field that is repeated
A field that contains only one item of data
What is a foreign key?
A unique identifier in a database
A primary key of one table that appears in another table
A field that should not be in a table and needs removing
A relation is in 1NF if it doesn't contain any ____________?
Determinants
Repeating groups
Null values in primary key fields
Functional dependencies
A table is in 2NF if the table is in 1NF and what other condition is met?
There are no functional dependencies.
There are no null values in primary key fields.
There are no repeating groups.
There are no attributes that are not functionally dependent on the relation's primary (compound) key.
When you normalize a relation by breaking it into two smaller relations, what must you do to maintain data integrity?
Remove any functional dependencies from both relations
Assign both relations the same primary key
Ensure that both smaller relations have primary keys
A table in ____ contains no transitive dependencies.
1NF
2NF
3NF
none of the above
Identification of the ____ will let you know where you are in the normalization process.
normal form
primary key
attributes
repeating groups
When is a table is said to be 3NF when...
performance is improved by increasing redundancy.
it is in 2NF and every non key attribute is non-transitively dependent on the primary key
It involves introduction of redundancy in data
None of the above
Which of the 2 following steps must be performed to move data into the first normal form?
Ensure all attributes contain only atomic values.
Any attribute in our entities that is not functionally dependent on all parts of the primary key is moved into a new entity.
All repeated groups of data are removed and put into new entities so that each record within the primary entity is the same length.
Any attribute that is not entirely functionally dependent on the primary key is moved into a new entity.
What does atomic data mean?
Related data must be grouped together.
Data on scientific readings.
Related data must be split into its smallest form.
Repeated groups of data are removed and put into new entities.
To move this data into the first normal form, what 2 actions must we do?
Split Student_Name into separate First_Name & Last_Name fields
Move Student_Name into its own table.
Move Course_Name & Course_Room into its own table and create a copy of the Course_Name in the original table to create a relationship
Delete Course_Room from the table
The second normal form only concerns the tables in our database that have a composite key.
True
False
To move this data into the second normal form, what must we do?
Move Qty into a new table.
Move Item_Price into a new table and a copy of Item
Move Item into a new table
Move Qty & Item_Price into a new table.
When a user cannot find a suitable field to become a primary key, they use a ________ Key
House
Database
Foreign
Composite
What does functional dependency mean?
Data is split into its smallest form.
One attribute uniquely determines another attribute.
Related data is grouped together.
All attributes are free from anomalies.
Third normal form requires, any attribute that is not entirely functionally dependent on the primary key, is moved into a new entity.
True
False
