

0 / 2 embers
0 / 3000 xp
click for more info
Complete a lesson to start your streak
click for more info
Difficulty: 6
click for more info
Not enough gems
Cost: 6 gems
1: Table Relationships
incomplete
2: One to Many
incomplete
3: Many to Many
incomplete
4: Database Normalization
incomplete
5: Normal Forms
incomplete
6: Primary Keys
incomplete
7: Functional Dependencies
incomplete
8: First Normal Form (1NF)
incomplete
9: Second Normal Form (2NF)
incomplete
10: Third Normal Form (3NF)
incomplete
11: Boyce-Codd Normal Form (BCNF)
incomplete
12: Normalization Review
incomplete
13: Query Practice – Users & Banks
incomplete
Back
ctrl+,
Next
ctrl+.
This lesson's interactive features are locked, please to keep using them
A table in third normal form (3NF) follows all the rules of second normal form, plus the following rule:
All columns that aren't part of a candidate key are dependent solely on the candidate keys. They cannot depend on a non-candidate key column.
Notice that this is only slightly different from second normal form. In second normal form, we can't have a column completely dependent on only part of the candidate keys; in third normal form, we can't have a column that is entirely dependent on anything that isn't in the candidate keys.
In this example, id and email are candidate keys, and we've chosen id as the primary key.
| id | name | first_initial | |
|---|---|---|---|
| 1 | Lane | l | [email protected] |
| 2 | Breanna | b | [email protected] |
| 3 | Lane | l | [email protected] |
This table is in second normal form because both candidate keys are single columns, so there's no partial-key dependency. However, it's not in third normal form because first_initial is dependent on the name column, which isn't a candidate key.
One way to convert the table above to 3NF is to move first_initial to a new table keyed by name. The name → first_initial dependency now lives in a table where name is the primary key. Notice how similar this solution is to 2NF.
| id | name | |
|---|---|---|
| 1 | Lane | [email protected] |
| 2 | Breanna | [email protected] |
| 3 | Lane | [email protected] |
| name | first_initial |
|---|---|
| Lane | l |
| Breanna | b |
We could also calculate the initial from name instead of storing it.
The same exact rule of thumb applies to the second and third normal forms.
Optimize for data integrity and data de-duplication first by adhering to 3NF. If you have speed issues, de-normalize accordingly.
This rollout of business accounts is really causing some headaches for our development team. The companies table has been a disaster. Our database architect pointed out that the idea behind the size field is redundant.
If a company has more than 100 employees, we consider it "large," otherwise it's "small." That's something we can calculate from the num_employees field, rather than storing it separately.
Remember the IIF function and the AS clause.