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

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 | 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 |
|---|---|
|
|
|
|
|
|
This stores 2 + 3 + 3 = 8 tuples. More importantly, it does not invent pairings between an email and a course.

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_iddept_id -> dept_namecourse_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.

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
StudentEmailrow.Create
email_1,email_2, andemail_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_idand 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:
S101 has 2 independent collection-valued attributes.
Careless flattening creates 4 S101 rows and 5 rows in total.
(student_id, email, course_id)identifies a flattened row under the stated rules.Changing C201's title touches 3 flattened rows.
The cleaner design is in 1NF, but
Enrollmentstill has the partial dependencycourse_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.
Keep learning
Introduction to Normalization and 1NF MCQs: 12 Solved Questions
Solve normalization and 1NF MCQs with a worked relation, clear explanations, and four traps covering atomicity, anomalies, and trade-offs.
DBMS Normalization from 1NF to BCNF: One Complete Worked Example
Follow one university-registration relation from a repeating group to BCNF. Compute both candidate keys, remove each dependency violation, and test the final split.
Normalization in DBMS: 1NF to BCNF, with the exam angle
Normalization in DBMS explained: functional dependencies, the exact 1NF, 2NF, 3NF and BCNF rules, a worked decomposition of one relation, and 3NF vs BCNF.
DBMS Normalization MCQs: 12 solved questions from 1NF to BCNF (GATE/NET)
DBMS normalization MCQs: 12 solved previous-year questions from GATE, UGC NET, DSSSB and TPSC papers on 1NF, 2NF, 3NF, BCNF and lossless join decomposition.





