Functional Dependencies

University of Toronto CSC343 Winter 2021 In-class Exercises: Functional Dependencies Suppose we have a relation R with attributes ABCD 1. What an FD means. Suppose the functional dependency BC → D holds in R. Create an instance of R that violates this FD. Solution: In order to violate this FD, we need two tuples with […]

University of Toronto CSC343 Winter 2021 In-class Exercises: The Chase Test Throughout these solutions, I use boldface letters to show values whose presence we can infer from the structure of the decomposition, and non-boldface letters to show values whose presence we can then infer from the FDs. 1. Suppose we have a relation on attributes

University of Toronto CSC343 Winter 2021 In-class Exercises: Projection and Minimal Basis 1.SupposewehavetheseFDs::S={ABE→CF, DF→BD, C→DF, E→A, AF→B} Project the FDs onto: L = CDEF Attributes to take all subsets X of: Closure of the subset C D Solution: E F X+ Functional dependencies inferred C D E F closure FDs 􏰀 C+ =CDFB D+ =

University of Toronto CSC343 Finding all keys Example Consider a relation schema R with attributes ABCDEF with functional dependencies S: S={AB→C, BF→E, C→BE, AC→F} Suppose we need to find all keys for this relation. What does it mean to be a key? Now that we know about functional dependencies, we know that if a set

Recall DB Design Week 9: Worksheet CSC 343 Winter 2021 University of Toronto Mississauga March 25/26, 2021 1 Task I Consider a relation R with a set of attributes ¦Á = {A, B, C, D, E, F } and the set of Functional Dependencies F = { A ¡ú BC, B ¡ú E, C ¡ú

Recall DB Design 1. Functional Dependencies Week 9: Worksheet CSC 343 Winter 2021 University of Toronto Mississauga March 25/26, 2021 • How do you identify FDs? – Domain knowledge! Note: DBMSs can’t identify (nor optimize) FDs for you! • Trivial FDs, Splitting/Combining • Armstrong’s Axioms (Reflexivity, Augmentation, Transitivity, Union, Decomposition) • Closure, Minimal Basis 2.

MONASH INFORMATION TECHNOLOGY 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