Lecture 4B: Normalisation Flashcards
What is normalisation?
improving the efficiency of relational models through minimising redundancy.
creating easier access to users to maintain data
takes up minimal storage
List relational keys.
- superkey
- candidate key
- primary key
- foreign key
quickly describe each relational key
- a superkey is an attribute, or a set of attributes that uniquely identify a tuple within a relation
- 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.
- The PK is the candidate key that is selected to uniquely identify rows in a table
- a FK, is the attribute or set of attributes in table that matches the ck/pk
define dependency
essentially, an attribute or set of attributes is reliant on another
What is a functional dependency?
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
describe full functional dependency
e.g. staffAddress has a functional dependency on (staffNo, staffName), but a full functional dependency between staffNo
describe Transitive dependency
there is a transitive dependency between staffNo and bAddress by virtue of branchAddress.
describe Partial dependency
so C is partially dependent on AB.
studentNo, subjectCode determines subjectName and Grade. but subjectName is partially dependent on subjectCode
describe Multi-valued dependency
ISBN is dependent to subjectCode
and
LectureID is dependent on subjectCode
List the goals of normalisation
- free the collection of tables from undesirable insertion, update, deletion dependencies
- reduce the need to restructure relations when new data types are introduced
- make relational model more informative to users
- make the collection of relations neutral to the query bias
What is redundancy?
the major aim of relational database design is to group attributes into relations to minimise data redundancy
List three ways to update anonmalies
- insertion
- deletion
- modification
List the normal forms list
- UNF/0NF
- 1NF
- 2NF
- 3NF
- BCNF
- 4NF
explain 0NF
a relation that contains one more repeating groups (unnormalised form)
explain 1NF
a relation where each row and column contains one and only one value
explain 2NF
a relation that is in 1NF, and every non-PK are fully functionally dependent on the PK
explain 3NF
A relation in 1NF and 2NF, where no “non PK” attribute is transitively dependent on the PK
explain BCNF
a relation that is in 3NF and every determinant is a candidate key
explain 4NF
a relation that is in BCNF and does not contain nontrivial multi-valued dependencies
what does the term “decomposition” mean?
the process of breaking one table into multiple tables.
what are two objectives for decomposition?
- lossless-join decomposition:
enables us to find any instance of the
original relation from corresponding instances in the smaller
relations. - Dependency preservation:
enables us to enforce a constraint
on the original relation by enforcing some constraint on each of the
smaller relations.
Give an example of lossless-join and dependency preserved
Students (StudentNo, Name, Address, DOB, GPA):
StudentsProfile(StudentNo, Name, Address, DOB)
StudentsAcademic(StudentNo, GPA)
what does BCNF stand for?
boyce codd normal form
explain BCNF
a relation is in BCNF, of and only if, every determinant is a candidate key (pretty much fully functional dependency is in the table)
