3rd Normal Form

Creating PostgreSQL Databases

Darryl Reeves

Industry Assistant Professor, New York University

Defining 3rd Normal Form

Requirements

  • 2NF is satisfied
  • No "transitive dependencies" exist
    • i.e., All non-key columns are only dependent on the PRIMARY KEY
Creating PostgreSQL Databases

Transitive dependencies

  • Involve 3 columns in table
  • Columns X, Y, Z
  • column X -> column Y
  • column Y -> column Z
  • column X -> column Z
Creating PostgreSQL Databases

Example: course room assignments

id name teacher num
157 Algebra Maggie Winters 244
162 Physics Maggie Winters 244
321 Spanish I Jeremy Smith 309
497 History I Sarah Williams 313
613 Spanish II Jeremy Smith 309
  • course name -> teacher
  • teacher -> room number
  • course name -> room number
Creating PostgreSQL Databases

Example: course room assignments

id name teacher num
157 Algebra Maggie Winters 244
162 Physics Maggie Winters 244
321 Spanish I Jeremy Smith 309
497 History I Sarah Williams 313
613 Spanish II Jeremy Smith 309
  • course name -> teacher
  • teacher -> room number
  • course name -> room number
           (transitive dependency)
Creating PostgreSQL Databases

Example: course room assignments

id name teacher num
157 Algebra Maggie Winters 244
162 Physics Maggie Winters 244
321 Spanish I Jeremy Smith 309
497 History I Sarah Williams 313
613 Spanish II Jeremy Smith 309
  1. Updating room number
Creating PostgreSQL Databases

Example: course room assignments

id name teacher num
157 Algebra Maggie Winters 244
162 Physics Maggie Winters 244
321 Spanish I Jeremy Smith 309
497 History I Sarah Williams 313
613 Spanish II Jeremy Smith 309
  1. Updating room number
  2. Adding new teachers
Creating PostgreSQL Databases

Example: course room assignments

id name teacher num
157 Algebra Maggie Winters 244
162 Physics Maggie Winters 244
321 Spanish I Jeremy Smith 309
497 History I Sarah Williams 313
613 Spanish II Jeremy Smith 309
  1. Updating room number
  2. Adding new teachers
  3. Deleting all courses for a teacher
Creating PostgreSQL Databases

Example: course room assignments

How do we change the structure of our data in order to alleviate these potential problems?

Creating PostgreSQL Databases

Example: course room assignments

teacher table

id name room_num
1 Maggie Winters 244
2 Jeremy Smith 309
3 Sarah Williams 313
Creating PostgreSQL Databases

Example: course room assignments

teacher table

id name room_num
1 Maggie Winters 244
2 Jeremy Smith 309
3 Sarah Williams 313

course_assignment table

id name teacher_id
157 Algebra 1
162 Physics 1
321 Spanish I 2
497 History I 3
613 Spanish II 2
Creating PostgreSQL Databases

Let's practice!

Creating PostgreSQL Databases

Preparing Video For Download...