10. NORMALIZATION
10.2.1: 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 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
-
|
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.
EXAMPLE 2.
Consider the Student relation below. The name attribute comprises both first and last names, and so it’s not atomic. Student is 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 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 have convenient functions that can be used to obtain a day, month, or year values from a date.
Exercises
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 is assigned a slot (e.g. 1, 2, 3, …15) for the 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. The delivery attribute is multi-valued and is composite.
|
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.
2) Chapter 8 covered mapping an ERD to a relational database. Consider the examples from Chapter 8; are the relations in 1NF?
