Problem Set 6.docx

7 Pages
Unlock Document

York University
Earth, Space Science and Engineering
ESSE 3600

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 2 PIN Owner Address Purchase Area(m ) Zoning Date 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. 1 300 Cohen 30 Montreal 2005 500 2 Commercial Str. 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 Area(m ) Zoning Date 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
More Less

Related notes for ESSE 3600

Log In


Don't have an account?

Join OneClass

Access over 10 million pages of study
documents for 1.3 million courses.

Sign up

Join to view


By registering, I agree to the Terms and Privacy Policies
Already have an account?
Just a few more details

So we can recommend you notes for your school.

Reset Password

Please enter below the email address you registered with and we will send you a link to reset your password.

Add your courses

Get notes from the top students in your class.