Functional Dependencies

Guideline 1 for Relation Schemas

1. Design relation schema so that it is easy to explain its meaning 2. Do not combine attributes from multiple entity types and relationship types into a single relation

Normalization Steps

1. Propagate data to eliminate any repeating groups or nested tables 2. Show functional dependencies 3. Identify candidate key(s) 4. Any attributes not FD on Key? If so, decompose (normalize) to 1NF 5. Any attributes FD on part of the Key? If so, decompose (normalize) to 2NF 6. Any non-key attributes FD on non-key? If so, decompose (normalize) to 3NF

Full Functional Dependency

1. X -> Y i.e. ,Y depends on X 2. Let Z be a proper subset of X, i.e. Z ( X, and there does NOT exist a Z such that Z -> Y.(i.e., if Y depends on X, then Y depends on Z, too)

Normal Forms Definitions 1-3

1NF: All domain values in R are atomic 2NF : R is in 1NF and every non-key attribute is fully dependent on the key 3NF: R is 2NF and every non-key attribute is non-transitively dependent on the key The Key, The Whole Key, and Nothing But The Key

4NF Definition

A relation schema R is in 4NF with respect to a set of dependencies F (that includes functional dependencies and multivalued dependencies) if, for every nontrivial multivalued dependency X→→Y in F^+17 is a superkey for R

Third Normal Form

A relation, R, is in 3NF if it satisfies 2NF and no nonprime attribute of R is transitively dependent on Primary Key

SuperKeys

A subset of the relation attributes where all tuple values must be distinct. 1. SK is a set of attributes 2. t1 and t2 are tuples 3. Then, t1 [SK] != t2 [SK]

Alternate key

AKA secondary key, another unique identifier

Nonprime Attribute

An attribute of R that is not a prime attribute.

Prime Attribute

An attribute that is a member of any candidate key of R.

Any relation can be non-loss decomposed into what?

An equivalent collection of 4NF relations.

Foreign key

Attribute(s) in an entity type that relates to a primary key in another relation

2NF Normalization

Decompose and set up a new relation for each partial key with its dependent attribute(s). Make sure to keep a relation with the original primary key and any attributes that are fully functionally dependent on it.

3NF Normalization

Decompose and set up a relation that includes the nonkey attribute(s) that functionally determine(s) other nonkey attribute(s).

The Challenge with Validating Normalization

Decompose relation R into relations, R1,..., Rk, with associated functional dependencies, F1,..., Fk, such that R1,..., Rk are in a more desirable form. Preserve the dependencies without losing information.

Fifth Normal Form (5NF)

Defined as a relation that has no join dependency. Joining two or more decomposed table should not lose records nor create new records

Functional Dependency

Denoted by X -> Y between two sets of attributes X and Y that are subsets of R specifies a constraint on the possible tuples the can form a relation state r of R. The constraint is that, for any two tuples t1 and t2 in r that have t1[X] = t2[X], they must also have t1[Y] = t2[Y]

Guideline 2 for Relation Schemas

Design base relation schemas so that: NO Redundant Information exists in Tuples NO Update anomalies will occur

Y is functionally dependent on X means

Each X-value in R is associated with precisely one Y-value in R. Or, equivalently, if two tuples have the same X-value, they must have the same Y-value.

5NF is often done in practice. T/F

False

BCNF Decomposition Theorem

For any relation R, there exists a non-loss decomposition of R into a set of BCNF relations.

2NF Test

For relations where primary key contains multiple attributes, no nonkey attribute should be functionally dependent on part of the primary key

1NF Normalization

Form new relations for each multivalued attribute or nested relation

Second Normal Form

Full functional dependency on the key Table is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. Put simply, a table is in 2NF if it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key.

In 3NF every nonprime attribute is what?

Fully functionally dependent on every candidate key. Nontransitively dependent on every candidate key.

Multivalued Dependency (MVD)

Given R(A, B, C) and R.A →→ R.B, attribute R.B is multidependent on attribute R.A. R.A multidetermines attribute R.B if the set of B-values matching a given (A-value, C-value) pair in R depends only on the A-value and is independent of the C-value. As usual A, B, and C may be composite.

The Problem with Validating Normalization

Given a relation R and functional dependencies, F, on R But what if R is not in a desirable form for enforcing F?

Boyce-Codd Normal Form (BCNF)

If X -> A holds in R, then X is a superkey of R

Types of update anomalies

Insertion Deletion Modification

Informal Design Guidelines for Relation Schemas- Measure of Quality

Making sure attribute semantics are clear Reducing redundant information in tuples Reducing NULL values in tuples Disallowing possibility of generating spurious tuples

Key

Minimal superkey

Join Dependencies and Fifth Normal Form

Multiway decomposition into fifth normal form (5NF) to reduce redundancy in recording multi-valued facts These are situations in which a complex real-world constraint governing the valid combinations of attribute values in the 4NF table is not implicit in the structure of that table.

Natural or Surrogate Key?

Natural keys exist already Natural keys are coupled to business requirements Use Natural keys, especially if the key values won't change If requirements change, you may need to change the key: For example, if clients change CustomerNumber from numeric to alphanumeric then you would have to change every single table where CustomerNumber is used as a foreign key.

What is dependency is not preserved when decomposing from 3NF to BCNF?

New Goal: Eliminate Redundancy while maintaining loss-less decomposition AND preserving dependencies Can't always satisfy both goals.

Does having a surrogate key eliminate the need to consider all candidate keys and functional dependencies?

No, having a surrogate key does not eliminate the need to consider all candidate keys and functional dependencies.

Candidate key

One of several keys

2NF is only a concern when?

Only a concern in the presence of composite candidate keys.

First Normal Form

Part of the formal definition of a relation in the basic (flat) relational model Only attribute values permitted are single atomic (or indivisible) values Does not allow nested relations Tuple with a relation within it

Approaches for relational schema design

Perform a conceptual schema design using a conceptual model then map conceptual design into a set of relations Design relations based on external knowledge derived from existing implementation of files or forms or reports i.e. Functional dependancies

4NF Theorem

R.A →→ R.B if and only if R.A →→ R.C.

1NF Test

Relation should have no multivalued attributes or nested relations

3NF Test

Relation should not have a nonkey attribute functionally determined by another nonkey attribute (or by a set of nonkey attributes). That is, there should be no transitive dependency of a nonkey attribute on the primary key

Techniques to achieve first normal form

Remove nested relation and non-atomic attributes into a new relation Propagate the primary key into it

artificial key or surrogate key

Sometimes row-ids or sequential numbers are assigned as keys to identify the rows in a table; key with no business meaning

Group attributes into relation schemas have a significant effect on what?

Storage space

A table is in 2NF if what?

Table is in 2NF if it is in 1NF and every non-prime attribute of the table is dependent on the whole of every candidate key.

Normalization of Relations

Takes a relation schema through a series of normal form tests Certify whether it satisfies a certain normal form Proceeds in a top-down fashion

normal form

The normal form of a relation refers to the highest normal form condition that it meets, and hence indicates the degree to which it have been normalized

For every nontrivial multivalued dependency A →→ B , A is a superkey for R. T/F

True

If R is in 4NF, then it is also in BCNF. T/F

True

What is required to determine which FDs hold and which do not?

We must know the meaning of, and relationships among, the attributes

The relation R(A, B, C) can be non-loss decomposed into its two projections R1(A,B) and R2(A, C) iff

the multivalued dependency A →→ B|C holds in R.

X →→Y

for any x and all xyz combinations, x will be associated with the same y entries regardless of z

A table is in Boyce-Codd normal form iff

for every X → Y, X is a superkey that is, X is either a candidate key or a superset thereof.

Composite key

key of two or more attributes

Natural key

key that is formed of attributes that already exist in the real world. For example, SSN

Candidate key(s)

minimal super key, before surrogate key is created

Primary key

preferred candidate key for an entity type

X -> Y means

Y is functionally dependent on X X (functionally) determines Y

Fourth normal form (4NF) is violated when

a relation has undesirable multivalued dependencies

Superkey

data attribute(s) that uniquely identify a tuple

A table is said to be in the 5NF or Project-Join Normal Form (PJNF) IFF

every join dependency in it is implied by the candidate keys. it cannot have a lossless decomposition into any number of smaller tables.