Introduction to Normalization and 1NF in DBMS: Worked Table and Exam Traps

Follow Asha and Ravi's fictional StudentRecord data from collection-valued cells to two valid 1NF designs. See where cross products, anomalies, keys, and dependencies remain.

KnowledgeGate Team

Exam prep & CS education

Updated 19 Nov 20266 min read
Introduction to Normalization and 1NF in DBMS: Worked Table and Exam Traps

A table can look tidy and still hide lists inside cells, repeated column groups, and facts that must be updated in several places. That is why "put every value in one cell" is too weak a rule for judging First Normal Form. Asha's 2 emails and 2 course records create 2 x 2 = 4 rows when the two unnormalised StudentRecord records are flattened carelessly.

Why normalization begins with anomalies, not table splitting

Normalization is a dependency-led way to organise facts so that each fact has a clear home. It asks three practical questions: what must be repeated when one fact changes, what fact cannot be stored on its own, and what useful fact disappears when a row is deleted?

Consider STUDENT_RECORD(student_id, student_name, dept_id, dept_name, email_addresses, course_records) with these source records:

  • (S101, Asha, D10, CSE, {asha.home@example.test, asha.college@example.test}, {(C201, DBMS, 84), (C202, OS, 76)})

  • (S102, Ravi, D10, CSE, {ravi.home@example.test}, {(C201, DBMS, 91)})

All names, identifiers, addresses, and marks here are fictional tutorial values. CSE repeats for D10, DBMS repeats for C201, and two attributes contain collections. These are immediate warning signs, not yet a solution.

What First Normal Form requires, and what it does not

The exam-safe core of 1NF is precise: every row-column intersection contains one value from the attribute's declared domain. It must not contain a list, set, nested table, or repeating group such as email_1, email_2, email_3.

Atomicity depends on the modelled domain. Asha can be one student_name value, but {home email, college email} is not one email value. Replacing braces with the text asha.home@example.test, asha.college@example.test does not fix the design if every useful query must split that text. Adding a surrogate row_id does not repair multi-valued cells either.

1NF removes nested and repeating structures. It does not, by itself, remove partial dependencies, transitive dependencies, update anomalies, or every duplicated value. That boundary matters whenever an option calls a scalar table "fully normalized" without examining its dependencies.

Worked example: flatten the two records and count every row

S101 violates 1NF twice because email_addresses has 2 values and course_records has 2 nested records. S102 also uses collection-valued attributes, although each collection currently has only 1 member. Today's member count does not change the attribute's design.

A careless scalar flattening produces STUDENT_COURSE_EMAIL_1NF(student_id, student_name, dept_id, dept_name, email, course_id, course_title, marks):

student_id

student_name

dept_id

dept_name

email

course_id

course_title

marks

S101

Asha

D10

CSE

asha.home@example.test

C201

DBMS

84

S101

Asha

D10

CSE

asha.home@example.test

C202

OS

76

S101

Asha

D10

CSE

asha.college@example.test

C201

DBMS

84

S101

Asha

D10

CSE

asha.college@example.test

C202

OS

76

S102

Ravi

D10

CSE

ravi.home@example.test

C201

DBMS

91

The count is 2 emails x 2 courses = 4 rows for S101 and 1 x 1 = 1 row for S102. Therefore the total is 4 + 1 = 5 rows. Under the stated rules, (student_id, email, course_id) identifies a row. Every cell is scalar, so the table is in 1NF, but it invents email-course pairings through a cross product.

A cleaner 1NF design separates the independent facts:

Relation

Exact rows

Student

(S101,Asha,D10,CSE), (S102,Ravi,D10,CSE)

StudentEmail

(S101,asha.home@example.test), (S101,asha.college@example.test), (S102,ravi.home@example.test)

Enrollment

(S101,C201,DBMS,84), (S101,C202,OS,76), (S102,C201,DBMS,91)

This stores 2 + 3 + 3 = 8 tuples. More importantly, it does not invent pairings between an email and a course.

Unnormalised STUDENT_RECORD records, the careless five-row flattening with its 2 x 2 cross product, and the resulting three 1NF tables.

Prove why a table can be in 1NF and still be badly designed

The five-row table makes the anomalies countable. Renaming Asha to Asha Rao needs 4 row updates. Renaming C201 from DBMS to Database Systems needs 3 updates: two S101 rows, one for each email, and one S102 row. If only 2 of those 3 cells change, C201 has conflicting titles.

There is an insertion anomaly too. A new course (C203, Computer Networks) cannot be stored until a student, email, and mark exist because the flattened key needs all three identifiers. For deletion, removing both S101 rows for C202 also removes the only facts saying C202 is titled OS. Deleting an enrolment should not erase a course definition.

The cleaner three-table design removes the artificial email-course cross product, but it is not the final design. Enrollment repeats (C201, DBMS) for Asha and Ravi, while Student repeats (D10, CSE). Reaching 1NF is a first checkpoint.

Read the keys and dependencies that remain after 1NF

In the cleaner design, the keys are Student.student_id, (student_id, email) in StudentEmail, and (student_id, course_id) in Enrollment, assuming one current mark per student-course pair. If attempts or semesters must be retained, the key must include that business identifier instead of silently overwriting a result.

The dependencies are:

  • student_id -> student_name, dept_id

  • dept_id -> dept_name

  • course_id -> course_title

  • (student_id, course_id) -> marks

Inside Enrollment, course_id -> course_title depends on only part of its composite key. Inside Student, student_id -> dept_id -> dept_name is a transitive chain.

The next decomposition would use Department(D10,CSE), Course(C201,DBMS) and (C202,OS), Student(S101,Asha,D10) and (S102,Ravi,D10), the same three StudentEmail rows, and Enrollment(S101,C201,84), (S101,C202,76), (S102,C201,91). First Normal Form stops before those partial and transitive dependencies; Normalization in DBMS: 1NF to BCNF carries one relation through the full 1NF-to-BCNF ladder. Independent email and course collections pose a narrower 1NF choice: separate their relations instead of manufacturing email-course pairs.

Correct 1NF dependency map and relation preview with composite StudentEmail key and no Department-to-Course link.

Common 1NF traps and the correction for each

  • Store comma-separated emails: The database must parse one cell to compare or constrain individual emails. Store one email per StudentEmail row.

  • Create email_1, email_2, and email_3: This repeats a column group and sets an arbitrary limit. Add rows, not numbered columns.

  • Call the five-row table fully normalized: Scalar cells prove 1NF, but Asha and C201 facts still repeat. Test partial and transitive dependencies separately.

  • Treat atomic as indivisible characters: The useful unit depends on the domain and query rules. Model one value at the granularity those rules constrain.

  • Add row_id and ignore natural determinants: The anomalies survive behind the new key. Write the business dependencies before choosing a surrogate key.

How exams test introduction to normalization and 1NF

Questions can ask you to identify a non-atomic attribute, choose a decomposition that reaches 1NF, count rows created by flattening repeating groups, identify an update, insertion, or deletion anomaly, distinguish 1NF from 2NF or 3NF, or infer a key after collections become rows.

Use these answer-first checks from the worked data:

  1. S101 has 2 independent collection-valued attributes.

  2. Careless flattening creates 4 S101 rows and 5 rows in total.

  3. (student_id, email, course_id) identifies a flattened row under the stated rules.

  4. Changing C201's title touches 3 flattened rows.

  5. The cleaner design is in 1NF, but Enrollment still has the partial dependency course_id -> course_title.

For any new question, circle multi-valued cells or repeating columns, state the candidate key after values become rows, verify one value per cell, and write the functional dependencies. Do not call the design fully normalized until the higher-form checks pass. Review the atomicity rule in First Normal Form, then use DBMS Normalization MCQs: 12 Solved as a worked follow-up.

The short version and the next step

The source has 2 StudentRecord rows. Careless flattening creates 5 rows; separating student, email, and enrolment facts creates 3 relations in 1NF. 1NF removes collection-valued cells and repeating groups, not every dependency or anomaly.

Now add S103 Meera with emails {meera.home@example.test, meera.college@example.test} and courses {(C201,DBMS,88),(C202,OS,79),(C203,Computer Networks,85)}. Predict 2 x 3 = 6 careless rows, then add exactly 1 Student row, 2 StudentEmail rows, and 3 Enrollment rows.

For semester study, use the Zero to Hero Complete CS Course. For placements, use CS Fundamentals for Placements by Sanchit Sir. Before naming the normal form, write repeating groups, candidate key, and dependencies.