FIT1004 Lecture Notes - Lecture 11: Unique Key, Dependency Graph, Second Normal Form
2 Jul 2018
School
Department
Course
Professor
Lecture 11 Logical Database Design
Types of Entities
● Strong Entity
○ independent existence unless there's a relationship with a strong entity
● Weak Entity
○ does not exist unless
● Associative (composite) entity
○ formed from many-to-many relationship between other entity types
ER diagrams → Logical Model
● For 1:1
○ Place PK into other entity as FK
○ TOTAL relationship (both mandatory) should be consolidated into one entity
● For 1:M
○ Place PK from the 1 end into each m side as FK
● For N:M
○ create intersection record based on composite entity
○ end with 3 entities in logical
Normalisation
The process of reducing data redundancy
Done to avoid certain anomalies (insert/update/delete) which may occur when updating data
Functional Dependency
● Attribute b is functionally dependent on another attribute A, if a value of A determines a
single value of B at any one time
● Total Dependency
○ attribute A determines B and attribute B determines A
● Full Dependency
○ occurs when an attribute is always dependant on at least two or other attributes
● Partial Dependency
○ lack of full dependency for multiple attribute key
● Transitive Dependency
○ occurs when Y depends on X, and Z depends on Y
■ therefore Z depends on X
UNF → 1NF
● identify primary keys
● remove repeating groups (if there is any)
○ take the repeating group and make it into a table and take the primary key as
well
● can draw a dependency diagram at this point
Document Summary
Independent existence unless there"s a relationship with a strong entity. Formed from many-to-many relationship between other entity types. Place pk into other entity as fk. Total relationship (both mandatory) should be consolidated into one entity. Place pk from the 1 end into each m side as fk. Create intersection record based on composite entity. Done to avoid certain anomalies (insert/update/delete) which may occur when updating data. Attribute b is functionally dependent on another attribute a, if a value of a determines a single value of b at any one time. Attribute a determines b and attribute b determines a. Occurs when an attribute is always dependant on at least two or other attributes. Lack of full dependency for multiple attribute key. Occurs when y depends on x, and z depends on y. Remove repeating groups (if there is any) Take the repeating group and make it into a table and take the primary key as well.
