Normalisation

BCNF

Boyce-Codd Normal Form - Also referred to as the "third and half (3.5) normal form in which every determinant is a candidate key

Candidate Key

- Attribute that uniquely identifies a row in a relation - Could be a combination of (non-redundant) attributes - Each non-key field is functionally dependent on every candidate key

3NF Hints

- If a nonkey attribute directly depends on another nonkey attribute, then there is transitive dependency - if attributes do not contribute to a description on the key, remove them to separate table

2NF requirements

- be in 1NF - eliminate partial primary key dependencies - nonkey columns must depend on all of the composite primary key

1NF requirements

- identify primary key - have one set of values per column - have just one value per cell must eliminate repeating data must have atomic attributes

2NF hints

- if the primary key is a single column, it is in 2NF - if nonkey column is related to just one of the columns of the composite key, it is partially depends on the whole key and needs to be in a separate table

Determinant

A determinant in a database table is any attribute that you can use to determine the values assigned to other attribute(s) in the same row.

Transitive dependency?

A functional dependency between the primary key and one or more non-key attributes that are dependent on the primary key via another non-key attribute

Denormalization

A process by which a table is changed from a higher-level normal form to a lower-level normal form, usually to increase processing speed. Denormalization potentially yields data anomalies.

2NF

A table is said to be in second normal form if each record in the table is in its 1st normal form and each column in the record is dependent on its primary key.

Normalization

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency. Normalization usually involves dividing large tables into smaller (and less redundant) tables and defining relationships between them. The objective is to isolate data so that additions, deletions, and modifications of a field can be made in just one table and then propagated through the rest of the database using the defined relationships.

Saying for Normalisation

Every non key attribute must be dependent on the key, the whole key and nothing but the key so help me Codd.

Functional Dependency?

Functional Dependency: The value of one attribute (the determinant) determines the value of another attribute. A particular relationship between two attributes. For a given relation, attribute B is functionally dependent on attribute A if, for every valid value of A, that value of A uniquely determines the value of B. The functional dependence of B on A is represented by A->B.

3NF

The third step in the normalization process, 3NF must be in 1NF and 2NF and in which transitive dependencies are removed from database records.

BCNF more..

When a relation has more than one candidate key, anomalies may result even though the relation is in 3NF. 3NF does not deal satisfactorily with the case of a relation with overlapping candidate keys i.e. composite candidate keys with at least one attribute in common. BCNF is based on the concept of a determinant. A determinant is any attribute (simple or composite) on which some other attribute is fully functionally dependent. A relation is in BCNF is, and only if, every determinant is a candidate key.

1NF

table that has all key attributes defined, has no repeating groups, and all its attributes are dependent on the primary key