Normalization in Relational Database Design

Flashcards about Normalization in Relational Database Design

What is Normalization or Schema Refinement?

A technique of organizing the data in a Database to eliminate data redundancy and undesirable characteristics.

What are the two main purposes of Normalization?

Eliminate redundant data and ensure Data dependencies are logically stored.

What are the desirable properties of Decomposition?

Lossless join decomposition and dependency preserving.

What is lossless join decomposition property?

It should be possible to reconstruct the original data.

What is dependency preserving property?

No functional dependency or constraints should be violated.

What is a Normal Form?

A normal form specifies a set of conditions that the relational schema must satisfy in terms of its constraints.

What are the most common normal forms?

1NF, 2NF, 3NF, and BCNF.

What are some additional Normal Forms?

BCNF, Multivalued dependencies and 4NF, Joined dependencies and 5NF, Sixth Normal Form, and DCNF (Domain Key Normal Form).

What are the goals of Normalization?

Decide whether a relation schema R is in good form and, if not, decompose it into a set of relation schemes that are each in good form, with a lossless and preferably dependency-preserving decomposition.

What is First Normal Form (1NF)?

A relational schema R is in first normal form if the domains of all attributes of R are atomic.

What is an atomic Domain?

Domain is atomic if its elements are considered to be indivisible units.

What are the possible redundancy drawbacks of First Normal Form?

Deletion Anomaly, Insertion Anomaly, and Updation Anomaly.

For a Relation R to be in second normal form (2NF), what conditions must it meet?

R should be in 1NF and should not contain any partial dependency.

When is K a super key for relation schema R?

If and only if K → R

When is K a candidate key (minimal super key) for R?

K → R, and for no   K,  → R

Let R be a relational Schema and (X,Y,A) be the attribute sets over R where X: Any candidate key , Y : Any proper subset of X and A : non key attribute then ?

Y->A is a partial dependency

Based on the partial dependancy of of the insdep schema (Where one Instructor works in many departments ) Insdep (ID, name, salary, dept_name, building, budget ). How do you split the relation?

Instructor (ID,name,salary) and department(deptname,building,budget) Inst-Dept(ID,deptname)