Normalisation & SQL Join - W5

Flashcards covering the key vocabulary and definitions related to database normalization and SQL joins, as discussed in the lecture.

Normalisation

The process of correcting table structures to minimise data redundancies.

Insertion Anomaly

Adding new rows forces user to create duplicate data.

Deletion Anomaly

Deleting rows may cause a loss of data that would be needed for other future rows.

Modification (Update) Anomaly

Changing data in a row forces changes to other rows because of duplication.

Normal Form

A state of a relation determined by applying rules on Functional Dependency.

Functional Dependency (FD)

Semantic restriction that some values for a relation are not possible in reality

Determinant

Any attribute(s) whose value determines other values within a row.

Dependant

Attribute whose value is determined by a determinant.

Partial dependency

Determinant is only part of primary key or candidate key, and dependant is a non-prime attribute.

Transitive dependency

X -> Y; Y -> Z; X is primary key. X determines the value of Z via Y, i.e., X -> Z

First Normal Form (1NF)

Table format, no repeating groups, and PK identified.

Second normal form (2NF)

1NF and no partial functional dependencies.

Third normal form (3NF)

2NF and no transitive functional dependencies.

Boyce-Codd normal form (BCNF)

3NF and every determinant is a candidate key (special case of 3NF).

Cross Join

Select all possible combinations of tuples in R with tuples in S, “𝑅 ∗ 𝑆”

Inner Join

Returns combined tuples from two relations that have the same value for a defined attribute (match on the attribute).

Natural Join

Joins tuples based on all attributes with identical names in the two relations, i.e., all common columns, and find matching values in each pair of common columns.

Full Outer Join

Joins tuples from two tables that match on a defined attribute. If no match, the combined row will still appear with missing attributes as NULL (both tables are preserved)

Left Outer Join

Left table is completely preserved. If no match, the attributes from the right side will contain NULL values.

Right Outer Join

Right table is completely preserved. If no match, the attributes from the left side will contain NULL values.