Database Final Ch 8

Legal relations

instance of a relation that satisfies all such real-world constraints is called a legal instance of the relation; a legal instance of a database is one where all the relation instances are legal instances.

Universal relation

states, that one can place all data attributes into a (possibly very wide) table, which may then be decomposed into smaller tables as needed.[

Extraneous attributes

An attribute of a functional dependency is said to be extraneous if we can remove it without changing the closure of the set of functional dependencies.

Denormalization

Denormalization is a database optimization technique in which we add redundant data to one or more tables. This can help us avoid costly joins in a relational database. Note that denormalization does not mean not doing normalization. It is an optimization technique that is applied after doing normalization.

Third normal form (3NF)

> Third normal form (3NF) relaxes this constraint slightly by allowing certain nontrivial functional dependencies whose left side is not a superkey. >

E-R model and normalization

> When we define an E-R diagram carefully, identifying all entities correctly, the relation schemas generated from the E-R diagram should not need much further normalization.However, there can be functional dependencies between attributes of an entity. >Functional dependencies can help us detect poor E-R design. > normalization can be left to the designer's intuition during E-R modeling, and can be done formally on the relation schemas generated from the E-R model. > Indeed, the process of creating an E-R design tends to generate 4NF designs

If the decomposition is _________________________________, given a database update, all functional dependencies can be verifiable from individual relations, without computing a join of relations in the decomposition.

> dependency preserving

Decomposition

> dept), the amount of a budgetmay have to be repeated. Observations such as these and the rules (functional dependencies in particular) that result from them allow the database designer to recognize situations where a schema ought to be split, or decomposed, into two or more schemas > Finding the right decomposition is much harder for schemaswith a large number of attributes and several functional dependencies > Not all decompositions of schemas are helpful

We showed ____________ in database design, and how to systematically design a database schema that avoids the pitfalls. The ________ included repeated information and inability to represent some information.

> pitfalls

We showed the development of a relational database design from an E-R design, when __________ may be combined safely, and when a schema should be decomposed. All valid ____________________ must be lossless.

> schemas > decompositions

Canonical cover

A canonical cover of a set of functional dependencies F such that ALL the following properties are satisfied: > F logically implies all dependencies in Fc. Fc logically implies all dependencies in F. No functional dependency in Fc contains an extraneous attribute. Each left side of a functional dependency in Fc is unique. That is, there are no two dependencies and in such that .

Atomic domains

A domain is atomic if elements of the domain are considered to be indivisible units like numbers, names would be nonatomic

Project-join normal form (PJNF)

A relation is in 5NF if it is in 4NF and not contains any join dependency and joining should be lossless. 5NF is satisfied when all the tables are broken into as many tables as possible in order to avoid redundancy. 5NF is also known as Project-join normal form (PJ/NF).

Dependency preservation

Because our design makes it computationally hard to enforce this functional dependency, we say our design is not dependency preserving.4 Because dependency preservation is usually considered desirable, we consider another normal form,weaker than BCNF, that will allow us to preserve dependencies. That normal form is called third normal form

Multivalued dependencies

Multivalued dependency occurs in the situation where there are multiple independent multivalued attributes in a single table. A multivalued dependency is a complete constraint between two sets of attributes in a relation. It requires that certain tuples be present in a relation.

Fourth normal form (4NF)

Fourth normal form (4NF) is a level of database normalization where there are no non-trivial multivalued dependencies other than a candidate key.It builds on the first three normal forms (1NF, 2NF and 3NF) and the Boyce-Codd Normal Form (BCNF). It states that, in addition to a database meeting the requirements of BCNF, it must not contain more than one multivalued dependency.

R satisfies F

Given an instance of r (R), we say that the instance satisfies the functional dependency → if for all pairs of tuples t1 and t2 in the instance such that t1[] = t2[], it is also the case that t1[] = t2[].

F holds on R

If we wish to constrain ourselves to relations on schema r (R) that satisfy a set F of functional dependencies, we say that F holds on r (R).

Lossless decomposition

Let r (R) be a relation schema, and let F be a set of functional dependencies on r (R). Let R1 and R2 form a decomposition of R.We say that the decomposition is a lossless decomposition if there is no loss of information by replacing r (R) with two relation schemas r1(R1) andr2(R2). More precisely, we say the decomposition is lossless if, for all legal database instances (that is, database instances that satisfy the specified functional dependencies and other constraints), relation r contains the same set of tuples

Boyce-Codd normal form (BCNF)

One of the more desirable normal forms that we can obtain is Boyce-Codd normal form (BCNF). It eliminates all redundancy that can be discovered based on functional dependencies, though, as we shall see in Section 8.6, there may be other types of redundancy remaining.Arelation schema R is in BCNF with respect to a set F of functional dependencies if, for all functional dependencies in F+ of the form → , where ⊆ R and ⊆ R, at least one of the following holds: → is a trivial functional dependency (that is, ⊆ ). is a superkey for schema R.

Closure of a set of functional dependencies

The Closure Of Functional Dependency means the complete set of all possible attributes that can be functionally derived from given functional dependency using the inference rules known as Armstrong's Rules. > Three Steps: Step-1: Add the attributes which are present on Left Hand Side in the original functional dependency. Step-2 : Now, add the attributes present on the Right Hand Side of the functional dependency. Step-3 : With the help of attributes present on Right Hand Side, check the other attributes that can be derived from the other given functional dependencies. Repeat this process until all the possible attributes which can be derived are added in the closure.

Domain-key normal form (DKNF)

The basic idea behind the DKNF is to specify the normal form that takes into account all the possible dependencies and constraints.In simple words, we can say that DKNF is a normal form used in database normalization which requires that the database contains no constraints other than domain constraints and key constraints. In other words, a relation schema is said to be in DKNF only if all the constraints and dependencies that should hold on the valid relation state can be enforced simply by enforcing the domain constraints and the key constraints on the relation.

Trivial functional dependencies

The dependency of an attribute on a set of attributes is known as trivial functional dependency if the set of attributes includes that attribute. > A ->B is trivial functional dependency if B is a subset of A. The following dependencies are also trivial: A->A & B->B

Armstrong's axioms

The term Armstrong axioms refer to the sound and complete set of inference rules or axioms, introduced by William W. Armstrong, that is used to test the logical implication of functional dependencies. > Axiom of reflexivity - If is a set of attributes and is subset of , then holds . If then This property is trivial property. > Axiom of augmentation - If holds and is attribute set, then also holds. That is adding attributes in dependencies, does not change the basic dependencies. If , then for any . > Axiom of transitivity - Same as the transitive rule in algebra, if holds and holds, then also holds. is called as functionally that determines . If and , then

Functional dependencies

Therefore, we need to allow the database designer to specify rules such as "each specific value for dept name corresponds to atmost one budget" even in cases where dept name is not the primary key for the schema in question. In other words, we need to write a rule that says "if there were a schema (dept name, budget), then dept name is able to serve as the primary key." This rule is specified as a functional dependency

First normal form (1NF)

a relation schema R is in first normal form (1NF) if the domains of all attributes of R are atomic

Unique-role assumption

attribute names must be unique in the universal relation