Database Normalization: 1NF to BCNF Worked on One Enrollment Table (2026)

Database normalization is the process of splitting one crowded table into smaller, linked tables so that every fact is stored in exactly one place. The rules are older than most of the software you use: Edgar F. Codd described first normal form in 1970 and added second and third normal form in 1971, according to the history summarized on Wikipedia. They still decide whether a database stays clean or slowly fills with contradictions.

Most explanations of database normalization show each normal form with a different toy table, so the steps never connect. This walkthrough uses one table from start to finish: a made-up enrollment register of the kind many training institutes and schools in Pakistan keep in Excel. Work through it and you'll be able to:

• Spot insert, update and delete anomalies in a real spreadsheet
• Write the functional dependencies of a table before you change anything
• Move one table through 1NF, 2NF, 3NF and BCNF and explain each split
• Turn the final design into Oracle tables with primary and foreign keys
• Decide when a design is normalized enough and when to stop

What Is Database Normalization?

Database normalization is a set of tests for table design. Each test is called a normal form. A table that passes the first test is in first normal form (1NF), one that also passes the second is in 2NF, and so on. Each level fixes a specific kind of repetition. University courses usually teach it under the heading normalization in DBMS, and the rules are the same whether you use Oracle, MySQL or SQL Server.

Codd's stated goal for the forms beyond 1NF was to free tables from "undesirable insertion, update and deletion dependencies." In everyday terms, you want a design where adding, changing or removing one fact never forces you to touch unrelated facts.

Microsoft's training module on normalization describes the same aim from a practical angle: each table should hold data about one entity, so that a customer's address, for example, is changed in one row instead of fifty. That is the habit this guide builds.

The Example: An Institute's Enrollment Register

Picture a small computer institute that tracks admissions in a single sheet. This is a sample built for teaching, with invented names and fees, but the shape will look familiar if you have ever helped in an admission office.

Notice two details before moving on. Ayesha's row packs two courses into one cell. Zainab shares Ayesha's phone number, which is normal in Pakistan when sisters or cousins register with a parent's mobile. Keep that second detail in mind, because it affects which column can serve as a key.

Which Problems Is This Sheet Hiding?

A sheet like this works for a month. Then the same three problems start appearing, and they have formal names.

None of these are rare. Database normalization exists to remove them. They're the everyday reason why two staff members quote different fees for the same course, or why nobody can say who taught a batch last year.

Functional Dependencies: Write These Down First

Students often try to normalize by instinct, splitting tables until they "look right." A better method of database normalization starts by listing the functional dependencies. A functional dependency, written A → B, means that if you know A, there is only one possible value of B.

For this register, the rules of the business give us five:

1. F1: student_id → student_name, phone
2. F2: course_id → course_title, fee
3. F3: student_id, course_id → batch_id (a student joins one batch per course)
4. F4: batch_id → course_id, start_month, teacher_id (every batch belongs to one course and one teacher)
5. F5: teacher_id → teacher_name

Phone is missing from the left side on purpose. Because family members share numbers, phone → student_name is false, so phone can't identify a student. Dependencies come from how the business works, not from the sample rows you happen to have.

Step 1: First Normal Form (1NF)

The first step of database normalization is 1NF. A table is in 1NF when every cell holds a single value, there are no repeating groups, and each row can be identified by a key. Ayesha's "PY01 Python, AC02 AutoCAD" cell breaks the first rule.

The fix is one row per student per course. Never solve it with extra columns like course1, course2 and course3. That layout still repeats a group, and it breaks the day someone joins a fourth course.

(The phone, start_month and teacher_id columns are still there; they're hidden here to keep the table readable.) The key is now the pair student_id + course_id. Every cell is atomic, so the table is in 1NF. The anomalies are all still present, though. "Python" and "30,000" already appear twice.

Step 2: Second Normal Form (2NF)

A table is in 2NF when it is in 1NF and no non-key column depends on only part of a candidate key. This rule only matters when a key has more than one column, which is exactly our situation.

Check each dependency against the key student_id + course_id:

1. student_name and phone depend on student_id alone (F1). That's a partial dependency.
2. course_title and fee depend on course_id alone (F2). Another partial dependency.
3. There's a second candidate key hiding here. Because a batch belongs to one course (F4), student_id + batch_id also identifies each row. Measured against that key, start_month and the teacher columns depend on batch_id alone.

Move each group into a table keyed by the column it really depends on:

• STUDENT (student_id, student_name, phone)
• COURSE (course_id, course_title, fee)
• BATCH (batch_id, course_id, start_month, teacher_id, teacher_name)
• ENROLLMENT (student_id, course_id, batch_id)

The fee now lives in one row of COURSE. The update anomaly for fees is gone, and you can add the new Power BI course before anyone enrolls.

Step 3: Third Normal Form (3NF)

A table is in 3NF when it is in 2NF and no non-key column depends on another non-key column. Microsoft's module phrases it as every non-key column being "nontransitively dependent" on the primary key, and it calls 3NF the usual target for transaction databases.

Look at BATCH. Its key is batch_id. It determines teacher_id (F4), and teacher_id determines teacher_name (F5). That chain, batch_id → teacher_id → teacher_name, is a transitive dependency. If Mr. Fahad corrects the spelling of his name, every batch he has ever taught must be edited.

Split the chain into its own table:

• TEACHER (teacher_id, teacher_name)
• BATCH (batch_id, course_id, start_month, teacher_id)

Now check the deletion anomaly from earlier. If Zainab withdraws, you delete her row from ENROLLMENT only. The OR-DEC batch and its teacher stay safely in BATCH.

Step 4: Boyce-Codd Normal Form (BCNF)

Codd and Raymond F. Boyce defined BCNF in 1974. The test is short: for every functional dependency A → B in a table, A must be a superkey, meaning A on its own identifies the row.

Every table so far passes, except one. ENROLLMENT (student_id, course_id, batch_id) has two candidate keys: student_id + course_id, and student_id + batch_id. It also carries F4, batch_id → course_id. batch_id alone doesn't identify an enrollment row, so this dependency breaks BCNF.

Why does the table still pass 3NF? Because course_id is part of a candidate key, and 3NF allows that case. That's the classic gap between the two forms, and it's a favorite exam and viva question.

The BCNF fix is to drop course_id from ENROLLMENT. The course is reachable through BATCH anyway:

• ENROLLMENT (student_id, batch_id)

There's an honest trade-off here. Rule F3, one batch per course for each student, can no longer be enforced by a key, because student_id and course_id no longer sit in the same table. You'd enforce it with a trigger or in the application. Some teams keep the 3NF version for exactly this reason, and that's a defensible choice.

The Normal Forms Side by Side

Here is the whole database normalization path for the register in one table.

Fourth and fifth normal form exist too. Ronald Fagin introduced them in 1977 and 1979. They deal with independent multi-valued facts and complex join rules, and you'll rarely need them in a first project.

Building the Final Design in Oracle SQL

Database normalization on paper is only useful if the database enforces it. Oracle's Data Integrity chapter in the 19c Concepts guide lists the tools: NOT NULL, unique, primary key, foreign key and check constraints. Here are the five tables with those constraints in place:

CREATE TABLE students (
  student_id   NUMBER(6)    CONSTRAINT students_pk PRIMARY KEY,
  student_name VARCHAR2(60) NOT NULL,
  phone        VARCHAR2(15)
);

CREATE TABLE teachers (
  teacher_id   NUMBER(4)    CONSTRAINT teachers_pk PRIMARY KEY,
  teacher_name VARCHAR2(60) NOT NULL
);

CREATE TABLE courses (
  course_id    VARCHAR2(6)  CONSTRAINT courses_pk PRIMARY KEY,
  course_title VARCHAR2(80) NOT NULL,
  fee          NUMBER(8)    CONSTRAINT courses_fee_ck CHECK (fee >= 0)
);

CREATE TABLE batches (
  batch_id     VARCHAR2(10) CONSTRAINT batches_pk PRIMARY KEY,
  course_id    VARCHAR2(6)  NOT NULL
               CONSTRAINT batches_course_fk REFERENCES courses (course_id),
  start_month  DATE         NOT NULL,
  teacher_id   NUMBER(4)
               CONSTRAINT batches_teacher_fk REFERENCES teachers (teacher_id)
);

CREATE TABLE enrollments (
  student_id   NUMBER(6)
               CONSTRAINT enr_student_fk REFERENCES students (student_id)
               ON DELETE CASCADE,
  batch_id     VARCHAR2(10)
               CONSTRAINT enr_batch_fk REFERENCES batches (batch_id),
  enrolled_on  DATE DEFAULT SYSDATE,
  CONSTRAINT enrollments_pk PRIMARY KEY (student_id, batch_id)
);

A few choices are worth explaining:

• No unique constraint on phone. Shared family numbers would make it reject real admissions.
• The composite key is declared out of line. The Oracle 19c SQL Language Reference requires that, and it caps a composite primary key at 32 columns.
• ON DELETE CASCADE on student_id. Deleting a student removes their enrollments too. Leave it out and Oracle blocks the delete while child rows exist, which some offices prefer for audit reasons.
• teacher_id may be NULL. A batch can be planned before a teacher is assigned. Oracle allows nulls in a foreign key column.

To see the original register again, join the tables back together. If joins are new to you, the SQL joins guide with Oracle examples covers each type.

SELECT s.student_name, c.course_title, c.fee, b.start_month, t.teacher_name
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN batches  b ON b.batch_id   = e.batch_id
JOIN courses  c ON c.course_id  = b.course_id
LEFT JOIN teachers t ON t.teacher_id = b.teacher_id
ORDER BY s.student_name;

When Should You Stop Normalizing?

For the database behind an admission form, a billing system or a point-of-sale app, 3NF or BCNF is the normal target. These systems write data all day, and anomalies hurt them most.

Reporting databases are different. Microsoft's module explains that normalized data can need many joins, while denormalized data "can be more efficient to query," especially for read-heavy data warehouses. That's why data warehouses usually use a star schema: one fact table of events, such as fee payments, surrounded by wide dimension tables for students, courses and dates.

So the real skill in database normalization isn't "always normalize." It's knowing which kind of system you're designing. Normalize where data is entered. Denormalize deliberately, and only where data is read for reports. If you're heading toward analytics, our DAX for beginners guide works through a complete sales model.

Five Database Normalization Mistakes Students Make

1. Normalizing from sample rows. Three rows can suggest a dependency that the business doesn't have. Ask how the institute or shop actually works.
2. Using a name or phone as a key. Names repeat and phones are shared. Use a generated ID.
3. Splitting too far. A table with only an ID and one column, created without a dependency reason, just adds joins.
4. Forgetting second candidate keys. Our register had two, and missing the second hides both a 2NF and a BCNF problem.
5. Stopping at the diagram. If the foreign keys aren't declared in SQL, nothing stops bad data from coming back.

Where Normalization Fits in PSTA's Oracle Course

Table design is the first module of the Oracle Database 19c Masterclass at PSTA, under "SQL Fundamentals & Data Design." Students go on to joins, subqueries and DDL, then PL/SQL triggers, which is where a rule like F3 above can be enforced after a BCNF split.

The course page lists it as a 12-week, beginner-level program, available on campus in Abbottabad, online or as a hybrid. The fee shown there is Rs. 33,000 (listed down from Rs. 45,000), and a new batch starts every month. Later modules cover database administration, RMAN backup and recovery, and performance tuning.

If you're still choosing a database, the Oracle vs MySQL comparison may help. Before a viva or interview, the SQL interview questions for freshers make a good revision list. To compare every program, see all PSTA courses, and when you're ready, the admission page explains how to apply. For quick questions, WhatsApp the office at +92 314 5424399.

Redraw One Sheet Before Your Next Lab

Pick any spreadsheet you already use: a class attendance list, a shop's sales log or your society's fund record. Write its functional dependencies on paper, then take it to 3NF using the steps above. If you can explain why each table exists, you understand database normalization better than a memorized definition ever could.