First Normal Form

What 1NF means: Each row–column intersection holds a single, indivisible value; there are no repeating groups. A stable key uniquely identifies each row. These rules keep data consistent and make queries predictable.

Why you care: Designs that violate 1NF are hard to search, update, and validate. Values get crammed into one cell (lists, CSVs), or columns get cloned to simulate “more of the same thing” (Author1, Author2, …). Normalization fixes this without losing information.

• Multiple occurrences of the same type of item
  Many domains allow “zero to many” values (a book can have many authors; a person can have many phone numbers). Modeling these as multiple columns or comma-separated lists violates 1NF.

What’s wrong here?

1. Author2 wastes space for single-author books and caps authors at two.
2. Both Author1 and Author2 represent the same attribute (author) → a repeating group, which violates 1NF.

1NF correction pattern: move the repeating attribute to its own table (one row per author per book), preserving order if needed.

1. Each attribute (column) is atomic in your model’s context (no lists or nested structures).
2. No repeating groups (don’t create Thing1, Thing2, … to store multiple values).

Identify a primary key so each row is unique. Atomicity + no repeating groups + a clear key give you 1NF.

A repeating group occurs when a single row can hold multiple values for the same attribute (e.g., multiple authors, multiple phone numbers). In ER terms, it corresponds to a multivalued attribute. The fix is to represent each value as a separate row in a related table.

When lists live in cells, you lose referential integrity and searchability. Consider the employee table below with children’s names and birthdates stored as comma-separated lists.

1. Ambiguity: There’s no enforced pairing between each child and birthdate.
2. Poor queryability: Filtering by a child’s birth year requires string parsing, which is slow and error-prone.

Solution: eliminate repeating groups by moving them to a related table keyed by the parent entity.

-- Entities
CREATE TABLE Book(
  BookID       INT PRIMARY KEY,
  ISBN         VARCHAR(20) UNIQUE,
  Title        VARCHAR(255),
  PublishedYear INT,
  Pages        INT,
  Publisher    VARCHAR(120),
  City         VARCHAR(120)
);

CREATE TABLE Author(
  AuthorID     INT PRIMARY KEY,
  AuthorName   VARCHAR(200)
);

-- Relationship (1 book : many authors), preserves author order
CREATE TABLE BookAuthor(
  BookID       INT NOT NULL,
  AuthorID     INT NOT NULL,
  AuthorOrder  INT NOT NULL,
  PRIMARY KEY(BookID, AuthorID),
  FOREIGN KEY(BookID)  REFERENCES Book(BookID),
  FOREIGN KEY(AuthorID) REFERENCES Author(AuthorID)
);

-- Example: querying authors in the intended order
SELECT b.Title, a.AuthorName
FROM Book b
JOIN BookAuthor ba ON ba.BookID = b.BookID
JOIN Author a     ON a.AuthorID = ba.AuthorID
ORDER BY b.BookID, ba.AuthorOrder;

CREATE TABLE Employee(
  EmpNum    INT PRIMARY KEY,
  FirstName VARCHAR(80),
  LastName  VARCHAR(80)
);

CREATE TABLE EmployeeChild(
  EmpNum     INT NOT NULL,
  ChildName  VARCHAR(120) NOT NULL,
  BirthDate  DATE NOT NULL,
  PRIMARY KEY(EmpNum, ChildName),
  FOREIGN KEY(EmpNum) REFERENCES Employee(EmpNum)
);

-- Now filtering is simple and indexable:
SELECT e.EmpNum, e.FirstName, e.LastName, c.ChildName, c.BirthDate
FROM Employee e
JOIN EmployeeChild c ON c.EmpNum = e.EmpNum
WHERE c.BirthDate < DATE '2005-01-01';

[1]first normal form (1NF): A table is in first normal form if it contains no repeating groups.