Lecture 4B: Normalisation

Question 1

What is normalisation?

Answer 1

improving the efficiency of relational models through minimising redundancy.

creating easier access to users to maintain data
takes up minimal storage

Question 2

List relational keys.

Answer 2

1. superkey
2. candidate key
3. primary key
4. foreign key

Question 3

quickly describe each relational key

Answer 3

1. a superkey is an attribute, or a set of attributes that uniquely identify a tuple within a relation
2. a CK, is a column, or set of columns, in a table that can uniquely identify any database record without referring to any other data.
3. The PK is the candidate key that is selected to uniquely identify rows in a table
4. a FK, is the attribute or set of attributes in table that matches the ck/pk

Question 4

define dependency

Answer 4

essentially, an attribute or set of attributes is reliant on another

Question 5

What is a functional dependency?

Answer 5

In a given table, an attribute (B) is said to be dependent on a set of attributes (A) if and only if each A value is associated with precisely one B value.

e.g. staffNo functionally determines position, but position does not functionally determine staffNo

Question 6

describe full functional dependency

Answer 6

e.g. staffAddress has a functional dependency on (staffNo, staffName), but a full functional dependency between staffNo

Question 7

describe Transitive dependency

Answer 7

there is a transitive dependency between staffNo and bAddress by virtue of branchAddress.

Question 8

describe Partial dependency

Answer 8

so C is partially dependent on AB.

studentNo, subjectCode determines subjectName and Grade. but subjectName is partially dependent on subjectCode

Question 9

describe Multi-valued dependency

Answer 9

ISBN is dependent to subjectCode
and
LectureID is dependent on subjectCode

Question 10

List the goals of normalisation

Answer 10

1. free the collection of tables from undesirable insertion, update, deletion dependencies
2. reduce the need to restructure relations when new data types are introduced
3. make relational model more informative to users
4. make the collection of relations neutral to the query bias

Question 11

What is redundancy?

Answer 11

the major aim of relational database design is to group attributes into relations to minimise data redundancy

Question 12

List three ways to update anonmalies

Answer 12

1. insertion
2. deletion
3. modification

Question 13

List the normal forms list

Answer 13

1. UNF/0NF
2. 1NF
3. 2NF
4. 3NF
5. BCNF
6. 4NF

Question 14

explain 0NF

Answer 14

a relation that contains one more repeating groups (unnormalised form)

Question 15

explain 1NF

Answer 15

a relation where each row and column contains one and only one value

Question 16

explain 2NF

Answer 16

a relation that is in 1NF, and every non-PK are fully functionally dependent on the PK

Question 17

explain 3NF

Answer 17

A relation in 1NF and 2NF, where no “non PK” attribute is transitively dependent on the PK

Question 18

explain BCNF

Answer 18

a relation that is in 3NF and every determinant is a candidate key

Question 19

explain 4NF

Answer 19

a relation that is in BCNF and does not contain nontrivial multi-valued dependencies

Question 20

what does the term “decomposition” mean?

Answer 20

the process of breaking one table into multiple tables.

Question 21

what are two objectives for decomposition?

Answer 21

1. lossless-join decomposition:
   enables us to find any instance of the
   original relation from corresponding instances in the smaller
   relations.
2. Dependency preservation:

enables us to enforce a constraint
on the original relation by enforcing some constraint on each of the
smaller relations.

Question 22

Give an example of lossless-join and dependency preserved

Answer 22

Students (StudentNo, Name, Address, DOB, GPA):

StudentsProfile(StudentNo, Name, Address, DOB)

StudentsAcademic(StudentNo, GPA)

Question 23

what does BCNF stand for?

Answer 23

boyce codd normal form

Question 24

explain BCNF

Answer 24

a relation is in BCNF, of and only if, every determinant is a candidate key (pretty much fully functional dependency is in the table)