<aside> ◻️ Review of last lecture: 1NF, 2NF, Functional Dependency

</aside>

<aside> ❕ Outline: 3NF and R Review Examples

</aside>

https://www.notion.so/lavendershuo/Lecture-19-3NF-and-R-Review-882ec061fa174f63a7b50cf241ea3b29 3NF quickly beyonds the scope of PSTAT10. All we need to know is the content mentioned in the powerpoint slides.

Third Normal 3NF

A relation is in 3NF if both of the following conditions hold:

1. the relation is in 2NF.
2. there are no TRANSITIVE FUNCTIONAL DEPENDENCIES of NON-PRIME ATTRIBUTES on the primary key.

Transitive Functional Dependencies

If A--->B and B--->C are two Functional Dependencies, then A--->C is a TRANSITIVE FUNCTIONAL DEPENDENCY.

Recall:

• If a set of attributes A of a relation uniquely identifies a set of attributes B of the same relation, then B is FUNCTIONALLY DEPENDENT on A. Written: A ---> B
• An attribute that is not part of any candidate key is a NON-PRIME ATTRIBUTE.

A simple example of 3NF

Consider the following relation schema: Student_Address (STUDENT_ID, STUDENT_NAME, ZIP, STATE, CITY)

<aside> ❕ It's always important to state your assumptions

</aside>

Assumptions: Each student has a unique STUDENT_ID and only one address, denoted by ZIP, STATE, CITY