Relation from Database - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Relation from Database - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

Functional

Dependency

What is Functional Dependency?

●An attribute or set of attributes X is said to functionally determine another attribute Y (written X  Y) if and only if each X value is associated with at most one Y value. Customarily we call X determinant set and Y a dependent set. ●A functional dependency is a constraint between two sets of attributes in a relation from a database. ●So if we are given the value of X we can determine the value of Y.

Functional Dependency

Inference Rules

Reflexivity : If B is a subset of A then A functionally determines B For example: {name, location}  {name}

Functional Dependency

Inference Rules(cont.)

Augmentation : If B is a subset of A and C functionally determines D then A and C functionally determine B and D For example: {name, location} and {birthdate, time}  {name} and {age} (as {name} is a subset of {name, location} and {birthdate, time} functionally determines {age})

Functional Dependency

Inference Rules(cont.)

Pseudo transitivity : If A functionally determines B and B and C functionally determine D then A and C functionally determine D

Functional Dependency

Inference Rules(cont.)

Union : If A functionally determines B and A functionally determines C then A functionally determines B and C For example: {name, location, birthdate, time}  {initials, age} (as {name, location, birthdate, time}  {initials} and {name, location, birthdate, time}  {age})

Trivial

Functional Dependencies

Some functional dependencies are said to be trivial because they are satisfied by all relation. For example: A  A X  Y if Y is a subset of X

Keys and

Functional Dependencies

●Keys and, more generally, functional dependencies, are constraints on the database that require relations to satisfy certain properties. Relations that satisfy all such constraints are legal relations.

Keys and Functional

Dependencies(cont.)

Functional dependencies allow us to express constraints that we cannot express with superkeys. Let's consider the schema of the example in the textbook(p.265) Figure 7.

. . . L- . . . loan_number amount . . . 10000 . . . loan . . . 23- 15- 23- . . . customer_id loan_number . . . L- L- L- . . . borrower . . . 23- 15- 23- . . . customer_id loan_number . . . L- L- L- . . . amount . . . 10000 10000 10000 . . . bor_loan Figure 7.2 Partial list of tuples in relations loan , borrower , and bor_loan

Keys and Functional

Dependencies(cont.)

We shall use functional dependencies in two ways:

1. To test relations to see whether they are legal under a given set of functional dependencies. If a relation r is a legal under a set F of functional dependencies, we say that r satisfies F.
2. To specify constraints on the set of legal relations. We shall thus concern ourselves with only those relations that satisfy a given set of functional dependencies. If we wish to constrain ourselves to relations on schema R that satisfy a set F of functional dependencies, we say that F holds on R.

Database Normalization ● Database normalization relates to the level of redundancy in a relational database's structure. The key idea is to reduce the chance of having multiple different versions of the same data , like an address, by storing all potentially duplicated data in different tables and linking to them instead of using a copy. Then updating the address in one place will instantly change all the places where the address is used. ●Well-normalized databases have a schema that reflects the true dependencies between tracked quantities. This means that updates can be quickly performed with little risk of data becoming inconsistent. ● Database normalization relates to the level of redundancy in a relational database's structure. The key idea is to reduce the chance of having multiple different versions of the same data , like an address, by storing all potentially duplicated data in different tables and linking to them instead of using a copy. Then updating the address in one place will instantly change all the places where the address is used. ●Well-normalized databases have a schema that reflects the true dependencies between tracked quantities. This means that updates can be quickly performed with little risk of data becoming inconsistent.

Normal Forms ●In the relational model, formal methods exist for quantifying "how normalized" a database is. These classifications are called normal forms , and there are algorithms for converting a given database between them. ●Edgar F. Codd originally established three normal forms: 1NF , 2NF and 3NF. There are now others that are generally accepted, but 3NF is widely considered to be sufficient for many practical applications. Most tables when reaching 3NF are also in BCNF. Normal Forms

1NF(First Normal Form) In the relational model, we formalize this idea that attributes do not have any substructure. A domain is atomic if elements of the domain are considered to be indivisible units. We say that a relation schema R is in first normal form if the domains of all attributes of R are atomic. In other words, a relation schema R is in 1NF if there are no muntivalued attributes. 1NF(First Normal Form) In the relational model, we formalize this idea that attributes do not have any substructure. A domain is atomic if elements of the domain are considered to be indivisible units. We say that a relation schema R is in first normal form if the domains of all attributes of R are atomic.