Normalisation

Question 1

partial dependency

Answer 1

occurs when a non-key attribute is functionally dependent on, or determined by, a single attribute that is part of a composite primary key. Requires composite primary key.

Question 2

transitive dependecy

Answer 2

occurs when a non-key attribute determines another non-key attribute but it can be skipped over because the primary key already uniquely identifies that specific attribute.

Question 3

1NF

Answer 3

First Normal Form. Describes the state of a database where all tables are free from repeating groups

Question 4

repeating groups

Answer 4

cells in the table are left blank because they would be repeats of the line above it. Only the cells in those columns which have values that differ from the row above are filled in. Multiple entries of the same type existing for any single key attribute occurrence.

Question 5

2NF

Answer 5

Second Normal Form. Occurs only when 1NF contains composite primary keys. Achieved when all tables are in 1NF and partial dependencies are removed.

Question 6

3NF

Answer 6

Third Normal Form. Achieved when all tables are in 2NF and transitive dependencies are also removed.

Question 7

BCNF

Answer 7

Boyce Codd Normal Form. This normal form is a special type of 3NF which occurs only when the tables have determinants with more than one candidate key. A table is already in BCNF if it is in 3NF and there are no determinants of key attributes that are non-key attributes.

Question 8

4NF

Answer 8

Fourth Normal Form. Achieved when all multivalued-dependencies are removed. Useful for taking information from spreadsheets and creating a database since a spreadsheet is likely to have multivalued dependencies in it.

Question 9

conversion to 1NF

Answer 9

1. Eliminate repeating groups
   put data in table
   each cell should have single value
   each entity occurrence should have a value for every attribute unless nulls allowed
2. Identify the primary key
   must u n i q u e l y identify every row
3. Identify all dependencies

Question 10

conversion to 2NF

Answer 10

1. Make new table for every primary key attribute in a composite key to eliminate partial dependency. Keep all key-attributes in original table.
2. Reassign corresponding attributes to a new table based on which key-attribute they were dependent on.
3. Name the tables appropriately
4. Make the determinants foreign keys in the original table.

Question 11

conversion to 3NF

Answer 11

1. Repeat process of 2NF but for transitive dependencies. For every transitive dependency create a new table with the determinant as the primary key in the new table and a foreign key in the original table.
2. Remove dependent attributes from the original table

Question 12

conversion to BCNF

Answer 12

Make determinant of prime attribute part of the primary key. Remove dependent from primary key. Leads to partial dependency. Remove partial dependency by normalising to 3NF.

Question 13

conversion to 4NF

Answer 13

Multivalued dependency is eliminated through the creation of new tables for the components of the multivalued dependency.

Question 14

advantages of normalisation

Answer 14

Reduced redundancy leads to fewer anomalies and information is therefore more accurate and reliable.

Question 15

disadvantages of normalisation

Answer 15

Normalisation compromises processing speed to eliminate data redundancy and anomalies. Only necessary if the manner in which the data is likely to be manipulated/queried requires the separation. All choices to normalise are a trade-off between the needs of processing speed and reduced redundancy.

Question 16

denormalisation

Answer 16

Process of amalgamating data into fewer tables to increase speed for the end user at the expense of increased data redundancy.

Question 17

multi-valued dependency

Answer 17

occurs when one key determines multiple values of two other attributes and those attributes are independent of each other.

Question 18

two rules that reduce the likelihood of multi-value dependency occurrence:

Answer 18

1. all attributes must be dependent on the primary key, but they must be independent of each other; and 2. no row may contain more than two or more multi-valued facts about an entity