Lesson 4	First normal form
Objective	Explain the requirements for first normal form.

First Normal Form (1NF): Values, Repeating Groups, and Keys

In the conventional relational model used in this course, first normal form means that each attribute contains a single value from its domain and the table has no repeating groups. Represent multiple related items as separate rows instead of numbered sets of columns or lists packed into a cell.

Relations have distinct tuples. In a SQL implementation, identify candidate keys and enforce an appropriate primary key so that each row can be distinguished reliably. A generated row ID alone does not fix a repeating group.

This lesson uses fictional book data and illustrative employee records. The examples show how to preserve individual values and their relationships while removing repeating groups.

Example 1: numbered author columns

Fictional books in a design limited to two authors
BookID	Title	Author1	Author2
1	Practical Database Design	Alex Morgan	Sam Patel
2	Working with SQL	Sam Patel	Lee Chen

Author1 and Author2 are positions in a list of contributors, rather than independently defined roles. A third author requires another column, searching for an author requires checking several columns, and a single-author book leaves an unused slot.

In this course's practical normalization convention, these interchangeable numbered columns form a repeating group. Technically, scalar values in separately named columns do not by themselves settle every formal interpretation of 1NF; the underlying modeling problem is the arbitrary fixed limit on a multivalued relationship.

Do not apply the rule merely because two columns have the same data type. StartDate and EndDate, for example, represent distinct roles and are not a repeating group.

What does “atomic” mean?

An atomic value is treated as one value in the model's domain. It does not have to be physically indivisible. A date has year, month, and day components, yet a DATE value is suitable for a single date attribute. A person's name can also be one value if that matches the application's requirements.

By contrast, a comma-separated list of AuthorIDs represents multiple author references when the application must search, validate, or link each author independently. Store those references in separate rows. SQL systems can support arrays and JSON, but their availability does not make such collections a substitute for the normalized relationships taught here.

Replace author slots with an association table

One book may have several authors, and one author may contribute to several books. This is a many-to-many relationship, resolved by BookAuthor. Each BookAuthor row records one association. AuthorID distinguishes people even when their names match.

CREATE TABLE Book (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255) NOT NULL
);
CREATE TABLE Author (
    AuthorID INTEGER PRIMARY KEY,
    AuthorName VARCHAR(200) NOT NULL
);
CREATE TABLE BookAuthor (
    BookID INTEGER NOT NULL,
    AuthorID INTEGER NOT NULL,
    AuthorOrder INTEGER NOT NULL,
    PRIMARY KEY (BookID, AuthorID),
    UNIQUE (BookID, AuthorOrder),
    CHECK (AuthorOrder > 0),
    FOREIGN KEY (BookID) REFERENCES Book(BookID),
    FOREIGN KEY (AuthorID) REFERENCES Author(AuthorID)
);

The pair key prevents the same author being listed twice for the same book. The unique constraint prevents two authors occupying the same position within a book; the check requires a positive position. These constraints do not require consecutive positions or enforce that every book has an author.

INSERT INTO Book VALUES
    (1, 'Practical Database Design'), (2, 'Working with SQL');
INSERT INTO Author VALUES
    (10, 'Alex Morgan'), (20, 'Sam Patel'), (30, 'Lee Chen');
INSERT INTO BookAuthor VALUES
    (1, 10, 1), (1, 20, 2), (2, 20, 1), (2, 30, 2);

SELECT b.Title, a.AuthorName, ba.AuthorOrder
FROM Book AS b
JOIN BookAuthor AS ba ON ba.BookID = b.BookID
JOIN Author AS a ON a.AuthorID = ba.AuthorID
ORDER BY b.BookID, ba.AuthorOrder;

This preserves both the associations and the intended author order. Separating Author also avoids maintaining a person's name in every book association. That additional benefit goes beyond simply removing the numbered columns.

Example 2: parallel lists of children and birthdates

The following legacy sample stores children's names and dates as parallel lists. It assumes the first name matches the first date, the second name matches the second date, and so on. That pairing is encoded only by position inside text.

Legacy employee sample: parallel lists of names and birthdates
EmpNum	First Name	Last Name	Child Names	Child Birthdates
1001	Jane	Doe	Mary, Sam	1/9/02, 5/15/04
1002	John	Doe	Lisa, David	1/9/00, 5/15/01
1003	Jane	Smith	John, Pat, Lee, Mary	10/5/04, 10/12/00, 6/6/2006, 8/21/04
1004	John	Smith	Michael	7/4/06
1005	Jane	Jones	Edward, Martha	10/21/05, 10/15/99

Inserting or removing one list entry without changing the other can pair a child with the wrong birthdate. Finding children born before a date also requires parsing strings instead of comparing typed date values. It is not impossible to query the lists, but validation and updates become unnecessarily complicated.

The source uses month/day/year notation, including two-digit years. Confirm those date interpretations before migration. The sample conversion below explicitly interprets Jane Doe's dates as January 9, 2002 and May 15, 2004.

Store one child record per row

Give each child record its own identifier. A child's name is not a safe key: siblings can share a name, names can change, and different people can have identical names and birthdates.

CREATE TABLE Employee (
    EmpNum INTEGER PRIMARY KEY,
    FirstName VARCHAR(80) NOT NULL,
    LastName VARCHAR(80) NOT NULL
);
CREATE TABLE EmployeeChild (
    ChildRecordID INTEGER PRIMARY KEY,
    EmpNum INTEGER NOT NULL,
    ChildName VARCHAR(120) NOT NULL,
    BirthDate DATE NOT NULL,
    FOREIGN KEY (EmpNum) REFERENCES Employee(EmpNum)
);

INSERT INTO Employee VALUES (1001, 'Jane', 'Doe');
INSERT INTO EmployeeChild VALUES
    (1, 1001, 'Mary', '2002-01-09'),
    (2, 1001, 'Sam', '2004-05-15');

SELECT e.EmpNum, c.ChildName, c.BirthDate
FROM Employee AS e
JOIN EmployeeChild AS c ON c.EmpNum = e.EmpNum
WHERE c.BirthDate < '2005-01-01';

These examples use SQL constructs supported by common relational databases; verify date-literal handling in the system you use. Applications should normally bind date values as typed parameters.

This simplified design treats a child record as belonging to one employee. If the same child may be associated with two employees, use a separate Child entity and an EmployeeChild association table. A surrogate key distinguishes records but does not, on its own, prevent duplicate real-world people.

Check a conversion to first normal form

Identify what one row represents and how it is uniquely identified.
Move independently meaningful collection members into rows with appropriate parent references.
Preserve pairings, such as each child's name with the correct birthdate.
Preserve ordering only when it has business meaning, using an explicit attribute.
Check missing or malformed values before loading the new tables.
Reconstruct the original associations and compare them with the source.

First normal form is a foundation, not proof that all redundancy has been removed. A table can contain scalar values and still repeat facts because of partial or transitive dependencies. The next lesson explains how to achieve first normal form; later lessons examine those remaining dependencies.

Atomicity and 1NF: interpret each attribute according to its domain and the facts the application must manage.