First Normal Form

Definition

A relation is in first normal form when:

1. Every attribute value is atomic — it cannot be meaningfully divided.
2. There are no repeating groups — no attribute holds several values.
3. Every tuple is unique, so a primary key exists.
4. Every value of an attribute comes from the same domain.

1NF is not really optional. The relational model defines a relation as having atomic values, so a structure that fails 1NF is not a relation at all. That is why it is the first form.

Violation 1 — a multivalued attribute

  NOT IN 1NF

  roll | name   | phone
  -----+--------+---------------------------
   21  | Meera  | 9840012345, 9840099887
   22  | Ravi   | 9840055512
   23  | Anitha | 9840077123, 9840088234, 9840012000

  Problems this creates:
    "find the student with number 9840099887"
       needs string searching, and matches 98400998876 too
    "how many numbers does Meera have"
       needs counting commas
    a number cannot be validated, indexed or constrained
    adding a number means rewriting the whole string

Wrong fix: repeating columns

  STILL NOT IN 1NF

  roll | name  | phone1     | phone2     | phone3
  -----+-------+------------+------------+-----------
   21  | Meera | 9840012345 | 9840099887 | (null)
   22  | Ravi  | 9840055512 | (null)     | (null)

  This is a REPEATING GROUP wearing different clothes.
    the fourth number has nowhere to go
    most cells are null
    searching all numbers means checking three columns
    the column NUMBER carries meaning, which it must not

Correct fix: a separate relation

  IN 1NF

  STUDENT                    STUDENT_PHONE
  roll | name                roll | phone
  -----+-------              -----+-----------
   21  | Meera                21  | 9840012345
   22  | Ravi                 21  | 9840099887
   23  | Anitha               22  | 9840055512
                              23  | 9840077123
                              23  | 9840088234
                              23  | 9840012000

  primary key ( roll, phone )

  Any number of phones, each searchable, each constrainable,
  and no nulls wasted.

Violation 2 — a composite value

  NOT ATOMIC

  address
  ---------------------------------
  14 Anna Salai, Chennai, 600002

  "all students in Chennai" now depends on string parsing
  and on everyone having typed the city in the same position.

  IN 1NF
  street        | city    | pincode
  --------------+---------+--------
  14 Anna Salai | Chennai | 600002

How atomic is atomic?

Atomicity is judged by how the application uses the value, not by whether it could theoretically be split.

The list case is the only absolute. Everything else is a design judgement, and the honest answer in an interview is "it depends on whether the parts are queried independently".

Converting to 1NF — the method

1. Find every attribute holding more than one value.
2. Remove it from the original relation.
3. Create a new relation containing the original primary key plus that attribute.
4. Make the primary key of the new relation the combination of both.
5. Split composite attributes into their parts, if the parts are used separately.

Worked conversion

  UNNORMALISED

  order_no | customer | items
  ---------+----------+---------------------------------
    5001   | Meera    | (pen, 10, 5), (book, 2, 250)
    5002   | Ravi     | (bag, 1, 900)

  Two problems: items repeats, and each item is composite.

  IN 1NF

  ORDER                     ORDER_ITEM
  order_no | customer       order_no | item | qty | price
  ---------+---------       ---------+------+-----+------
    5001   | Meera            5001   | pen  | 10  |    5
    5002   | Ravi             5001   | book |  2  |  250
                              5002   | bag  |  1  |  900

  ORDER_ITEM primary key ( order_no, item )

  Now every value is atomic, every value is in its own
  column, and both relations have a key.

What 1NF does not fix

  ENROLMENT ( roll, course, name, title, marks )
    key = ( roll, course )

  This IS in 1NF - every value is atomic.
  But name repeats on every row for that student, and
  title repeats on every row for that course.

  1NF removed repeating groups. It did nothing about
  partial dependency, which is what 2NF exists for.

Common mistakes

• Using numbered columns to fix a multivalued attribute. That is still a repeating group.
• Believing 1NF means one table. It usually means more tables.
• Thinking any splittable value breaks 1NF. A date is atomic; a comma separated list is not.
• Storing a delimited list because it is convenient. Every operation on it becomes string handling.
• Forgetting the new relation needs a composite key. Both the original key and the value.

Exam and interview questions

1. Define 1NF and state all four conditions.
2. Why is a comma separated list a 1NF violation, and why is a numbered set of columns also one?
3. Convert a table with a multivalued attribute into 1NF and give both primary keys.
4. What does atomic mean, and how do you decide in a borderline case?
5. Give a relation that is in 1NF but still has redundancy.

Practice

1. Convert BOOK ( isbn, title, authors, publisher ) where authors is a list.
2. Decide whether each is atomic for a payroll system: full name, date of joining, list of skills, bank account number.
3. Convert an invoice with repeating line items into 1NF and state both keys.

Conclusion

1NF requires atomic values and forbids repeating groups, in every disguise including numbered columns. Move the repeating attribute into its own relation keyed by the original key plus the value — and then move on, because 1NF leaves plenty of redundancy for 2NF to remove.