When we talk about analyzing a database for normal form compliance, we mean checking out the structure of tables, their relationships, and dependencies between attributes. The main goal of this analysis is to find normalization issues and see how they affect performance, integrity, and how easy it is to work with the data.
Simply put, it’s like auditing your accounting: you want to make sure the money isn’t just lying around randomly, but is strictly allocated to the right expense categories.
Practical Approach to Database Analysis
In any database, we start by asking three key questions, each matching one of the first three normal forms.
Let’s say we have a warehouse database with a table like this:
| product_id | product_name | supplier_name | supplier_phone | stock_quantity |
|---|---|---|---|---|
| 1 | Nails | StroyKomplekt | +12301112233 | 150 |
| 2 | Screws | KrepezhPro | +12306667788 | 200 |
| 3 | Nuts | StroyKomplekt | +12301112233 | 100 |
How do we check if this table meets the normal forms?
Remember, a table is in 1NF if:
- Every cell contains a single value.
- There are no repeating columns for the same type of data.
In our example, there’s no 1NF violation: every cell is atomic. That means the table is in 1NF. Nice! Let’s move on.
A table is in 2NF if:
- It’s already in 1NF.
- All non-key attributes depend only on the whole primary key (not just part of it).
In this table, supplier_name and supplier_phone depend only on product_id — the primary key. But there’s data duplication: for the same supplier, we store their name and phone in multiple rows.
To bring the table to 2NF, we can split it into two tables:
Products table:
| product_id | product_name | supplier_id | stock_quantity |
|---|---|---|---|
| 1 | Nails | 1 | 150 |
| 2 | Screws | 2 | 200 |
| 3 | Nuts | 1 | 100 |
Suppliers table:
| supplier_id | supplier_name | supplier_phone |
|---|---|---|
| 1 | StroyKomplekt | +78901112233 |
| 2 | KrepezhPro | +78906667788 |
Now each supplier is listed just once, and the link between tables is via the foreign key supplier_id.
A table is in 3NF if:
- It’s in 2NF.
- All non-key attributes depend only on the primary key, not on other non-key attributes.
With the normalized Products and Suppliers tables, we don’t see any transitive dependencies. That means the tables are in 3NF.
Practical Exercise
Let’s say we have the original "University" table
| student_id | student_name | course_name | professor_name | professor_email |
|---|---|---|---|---|
| 101 | Otto Lin | Mathematics | Peter Pen | pen@university.com |
| 102 | Anna Song | Physics | Alex Sid | sid@university.com |
| 103 | Otto Lin | Physics | Alex Sid | sid@university.com |
- Check the table for normal form compliance.
- Bring it to 1NF, 2NF, and 3NF if needed.
Solution
STEP 1: Check for 1NF
The table is already in 1NF: every cell has a single value.
STEP 2: Check for 2NF
The table breaks 2NF: professor info (name and email) is repeated. We can move it to a separate table:
Students table:
| student_id | student_name |
|---|---|
| 101 | Otto Lin |
| 102 | Anna Song |
Courses table:
| course_id | course_name | professor_id |
|---|---|---|
| 1 | Mathematics | 1 |
| 2 | Physics | 2 |
Professors table:
| professor_id | professor_name | professor_email |
|---|---|---|
| 1 | Peter Pen | pen@university.com |
| 2 | Alex Sid | sid@university.com |
Enrollments table:
| enrollment_id | student_id | course_id |
|---|---|---|
| 1 | 101 | 1 |
| 2 | 102 | 2 |
| 3 | 103 | 2 |
STEP 3: Check for 3NF
In the new structure, there are no transitive dependencies. The tables are in 3NF.
Practical Tips
- Don’t chase perfection if you don’t need to. Sometimes over-normalizing just makes your queries harder.
- Analyze like a detective. Look for duplicates, extra dependencies, and other "anomalies."
- Don’t forget about performance. Normalization is all about balancing clean data and fast processing.
Now that you know how to spot and fix database issues, you could honestly audit a database of any size. Remember: a good database isn’t just functional, it’s also beautiful (or, well, normalized).
GO TO FULL VERSION