In this lecture we look at...

[Section notes PDF 121Kb]

- Information Principle:
- The entire information content of the database is represented in one and only one way, namely as explicit values in column positions in tables
- Implies that two relations cannot have the same meaning
- unless they explicitly have the same design/attributes (including name)

- Reduced redundancy
- Organised data efficiently
- Improves data consistency
- Reduces chance of update anomalies
- Data duplicated, then updated in only one location
- Only duplicate primary key
- All non-key data stored only once
- Data spread across multiple tables, instead of one Universal relation R

- Depends on Application
- OLTP (Transaction processing)
- Lots of small transactions
- Need to execute updates quickly
- OLAP (Analytical processing/DSS)
- Largely Read-only
- Redundant data copies facilitate Business Intellegence applications, e.g. star schema (later)
- 3NF considered ‘normalised’
- save special cases

- First Normal form (1NF)
- Disallows multivalued attributes
- Part of the basic relational model
- Domain must include only atomic values
- simple, indivisible
- Value of attribute-tuple in extension of schema
- t[A
_{i}] ∈ (A_{i})

- Remove fields containing comma separated lists
- Multi-valued attribute (A
_{MV}) of R_{i} - Create new relation (R
_{NEW}) - with FK to R
_{i}[PK] - R
_{NEW}(UID, A_{MV}, FK_{I})

- A relation R
_{i}is in 2NF if: - Every nonprime attribute A in R
_{i}is - fully functionally dependent on 1y key of R
- If all keys are singletons, guaranteed
- If R
_{i}has composite key are - all non-key attributes fully functionally dependent
- on all attributes of composite key?

- Second normal form (2NF)
- Full functional dependency X → Y
- A ∈ X, (X - {A}) ¬→ Y
- If any attribute A is removed from X
- Then X → Y no longer holds
- Partial functional dependency
- A ∈ X, (X - {A}) → Y

- In context
- Not 2NF: AB → C, A → C
- AB → C is not in 2NF, because B can be removed
- Not 2NF: AB → CDE, B → DE
- because attributes D&E are dependent on part of the composite key (B of AB), not all of it

- Boyce-Codd Normal form (BCNF)
- Simpler, stricter 3NF
- BCNF → 3NF
- 3NF does not imply BCNF
- nontrivial functional dependency X → Y
- Then X must be a superkey

- Third Normal form (3NF)
- Derived/based on transitive dependency
- For all nontrivial functional dependencies

X → A - Either X must be a superkey
- Or A is a prime attribute

(member of a key)

- AB → C, C → D, D → A
- In context
- 3NF? Yes
- Because AB is a superkey and
- D and A are prime attributes
- BCNF? No
- Because C and D are not superkeys
- (even though AB is)