Postgres Schemas Refine: 1NF, 2NF, 3NF

introduction to 1nf, 2nf, 3nf

Schema Refinement

1, What is schema?
Schemas contain named objects including data types, functions, and operators. It helps organize database objects into logical groups to make them more manageable.

2, Check schema

SELECT *
FROM information_schema.columns

3, Why refine
Schemas from ER model can have redundancy and might have other problems, so we need to refine it.

4, Data Redundancy and Anomalies
- Redundant storage : Some information is stored repeatedly.
- Update anomalies : If one copy of repeated data is updated, an inconsistency is created unless all copy are updated.
- Insert anomalies : It may not be possible to store certain information unless some other, unrelated, information is stored as well.
- Delete anomalies : It may not be possible to delete certain information without losing some other, unrelated. Information as well.

5, How refine?
Decomposition: replacing a relation, R with two or more collection of smaller relations that each contain a subset of the attributes of R and together include all attributes in R. We use functional dependency and integrity constraints to help us refine.

6, Functional Dependency
- What is FD?
If column A of a table uniquely identifies the column B of same table, Attribute B is functionally dependent on attribute A (A->B).
- What is closure of F?
The set of all FDs implied by a given set F of FDs is called the closure of F, denoted as F+.
- Armstrong’s Axiom
Reflectivity:IfY⊆X,thenX⇒Y.
Augmentation:IfX⇒Y,thenXZ⇒YZforanyZ. ◦ Transitivity:IfX⇒YandY⇒Z,thenX⇒Z.

7, Refine principles:
- Lossless-join: We can recover the original relation from the decomposed relations.
- Dependency-preservation: If R is decomposed into X, Y and Z, and we enforce the FDs that hold on X, on Y and on Z, then all FDs that were given to hold on R must also hold.

8, Normal Forms:
- 1NF
form:
has a primary key
has no repeating attributes or groups attributes
problem:
still have insert/delete/update anomalies.
- 2NF
form:
1NF
every non-prime attribute is dependent on the whole of every candidate key
- 3NF
form:
2NF
every non-prime attribute is non-transitively dependent on every key