FIT2094 Lecture Notes - Lecture 5: Data Redundancy, Dependency Graph, Functional Dependency
31 May 2018
School
Department
Course
Professor
Normalisation!
Data Normalisation:!
•"Relations should be normalised in order to avoid anomalies which may occur when
inserting, updating and deleting data. !
•"Normalisation is a systematic series of steps for progressively refining the data model. !
•A formal approach to analysing relations based on their primary key (or candidate keys)
and functional dependencies. !
•Used:#
as a design technique "bottom up design", and#
as a way of validating table structures produced via #
"top down design" (ER modelling) #
Creating valid relations, i.e. each relation meets the properties of the relational model. In
particular: !
•Entity integrity !
•Referential integrity !
•"No many-to-many relationship !
•$Each cell contains a single value (is atomic). #
▪ In practical terms:!
•"Each table represents a single subject !
•"No data item will be unnecessarily stored in more than one table. !
•The relationship between tables can be established (pair of PK and FK is identified). !
•"Each table is void of insert, update and delete anomalies. #
Functional Dependency !
For a composite PRIMARY KEY, it is possible to have FULL or PARTIAL dependency. !
FULL DEPENDENCY !
•"– $occurs when an attribute is always dependent on all attributes in the composite PK !
•"– $ORDER-NUMBER, PART-NUMBER ➔ QTY-ORDERED#
Lack of full dependency for multiple attribute key = PARTIAL DEPENDENCY !
•"– $ORDER-NUMBER, PART-NUMBER#
➔ QTY-ORDERED, PART-DESCRIPTION !
•"– $here although qty-ordered is fully dependent on order-number and part-number, only
part-number is required to determine part-description #
•"– $part-description is said to be partially dependent on order-number and part-number #
find more resources at oneclass.com
find more resources at oneclass.com
