Textbook Notes (270,000)
CA (160,000)
York (10,000)
ESSE (10)
Sohn (2)
Chapter

ESSE 3600 Chapter Notes -Second Normal Form, Print Screen, Unique Key

by

Department
Earth, Space Science and Engineering
Course Code
ESSE 3600
Professor
Sohn

This preview shows pages 1-2. to view the full 7 pages of the document.
Problem Set 6
Objective
The objective of this exercise is to learn how to prepare data tables of a relational
database and then create queries and get results from a relational database using Microsoft
Access.
Part 1-Table Normalization
In order to prepare a relational database, the tables need to be normalized so that
redundant data is eliminated. To create consistent data, repeatability must be minimized. After
the normalization process, which creates single entries in attribute fields and minimizes
repeatability, the data tables are related using keys to index and link the tables. The keys
identify rows in the table.
Tables are normalized following the guidelines of the first, second, and third normal
forms, known as 1NF, 2NF and 3NF. In 1NF, all attributes need a single value and duplicate
columns must be eliminated. In 2NF, the requirements of the primary key need to be met and
in 3NF, the requirements of 2NF need to be met. In 3NF, all non-key departments do not have a
transitive dependence.
The task that was given was to create a relational database through the process of
normalization and determine the primary keys to generate the relational tables. The original
table is in 1F normalization, and is shown in Table 1. In this normalization, all attributes have a
single value and duplicate columns are eliminated.
Table 1-1NF normalization
PIN
Owner
Address
Purchase
Date
Area(m2)
Zoning
100
Brown
50 Main Str.
1995
200
1 Residential
Chang
65 Centre Rd
200
Smith
25 Banff Str.
2000
600
2 Commercial
Parks
15 Green Ave.

Only pages 1-2 are available for preview. Some parts have been intentionally blurred.

1
300
Cohen
30 Montreal
Str.
2005
500
2 Commercial
400
Smith
25 Banff Str.
1998
200
1 Residential
2NF Normalization
This section shows Table 1 modified via the process of 2F normalization, and
subsets of data that apply to multiple rows of the table have been placed in separate tables. For
example, the PIN has been stored twice. The result of 2NF normalization is shown below.
Table 2-2NF Normalization
PIN
Purchase
Date
Area(m2)
Zoning
100
1995
200
1 Residential
200
2000
600
2 Commercial
300
2005
500
2 Commercial
400
1998
200
1 Residential
Table 3-2NF Normalization
PIN
Owner
Address
100
Brown
50 Main Str.
Chang
65 Centre Rd
200
Smith
25 Banff Str.
Parks
15 Green Ave.
300
Cohen
30 Montreal
Str.
400
Smith
25 Banff Str.
3NF Normalization
You're Reading a Preview

Unlock to view full version