10.4: First Normal Form (1NF)

We say a relation is in 1NF if all values stored in the relation are single-valued and atomic. With this rule, we are simplifying the structure of a relation and the kinds of values that are stored in the relation.

Example \(\PageIndex{1}\)

Consider the following EmployeeDegrees relation.

• empNo is the PK
• each employee has one first name and one salary
• each employee has zero or more university degrees ... stored as a single attribute

Table \(\PageIndex{1}\): EmployeeDegrees relation.

EmployeeDegrees
empNo	first name	salary	degrees
111	Joe	29,000	BSc, MSc
200	April	41,000	BA, MA
205	Peter	33,000	BEng
210	Joe	20,000

This relation is not in 1NF because the degrees attribute can have multiple values. Below are two relations formed by splitting EmployeeDegrees into two relations – one relation has attributes empNo, first name, and salary and the other has empNo and degree. We say we have decomposed EmployeeDegrees into two relations and we have populated each with data from EmployeeDegrees. Each of these is in 1NF, and if we join them on empNo we can get back the information shown in the relation above.

Table \(\PageIndex{2}\): Employee table.

Employee
empNo	first name	salary
111	Joe	29,000
200	April	41,000
205	Peter	33,000
210	Joe	20,000

empNo is the PK. Each employee has one name and one salary.

Table \(\PageIndex{3}\): Degree table.

Degree
empNo	degree
111	BSc
111	MSc
200	BA
200	MA
205	BEng

{empNo, degree} is the PK. degree is single-valued.

Example \(\PageIndex{2}\)

Consider the Student relation below. The name attribute comprises both first and last names and so its not atomic. Student is not 1NF.

Table \(\PageIndex{4}\): Student table not in 1NF.

Student – not in 1NF
studentNo	name	gender
444	Jim Smith	m
254	Donna Jones	f
333	Peter Thomas	m
765	Jim Smith	m

If we modify Student so there are two attributes (say, first and last) then Student would be 1NF:

Table \(\PageIndex{5}\): Student table in 1NF.

Student – in 1NF
studentNo	first	last	gender
444	Jim	Smith	m
254	Donna	Jones	f
333	Peter	Thomas	m
765	Jim	Smith	m

If we can say that a relation (or table) is in 1NF then we are saying that every attribute is atomic and every value is single-valued. This simplifies the form of a relation.

It is very common for names to be separated out into two or more attributes. However, attributes such as birth dates, hire dates, etc. are usually left as a single attribute. Dates could be separated out into day, month, and year attributes, but that is usually beyond the needs of the intended system. Some would take the view that separating a date into 3 separate attributes is carrying the concept of normalization a little too far. Database systems do have convenient functions that can be used to obtain a day, month, or year values from a date.

Exercises

Exercise \(\PageIndex{1}\)

Consider the relation below that holds information about courses and sections. Suppose departments have courses and offer these courses during the terms of an academic year. A section has a section number, is offered in a specific term (e.g. Fall 2016, Winter 2017) and a slot (e.g. 1, 2, 3, ...15) within that term. Each time a course is delivered there is a section for that purpose. Each section of a course has a different number. As you can see a course may be delivered many times in one term.

CourseDelivery
deptNo	courseNo	delivery
ACS	1903	001, Fall 2016, 05; 002, Fall 2016, 06; 003, Winter 2017, 06
ACS	1904	001, Fall 2016, 12; 002, Winter 2017, 12
Math	2201	001, Fall 2016, 11; 050, Fall 2016, 15
Math	2202	050, Fall 2016, 15

Modify CourseDelivery to be in 1NF. Show the contents of the rows for the above data.

Exercise \(\PageIndex{2}\)

Chapter 8 covered mapping an ERD to a relational database. Consider the examples from Chapter 8; are the relations in 1NF?