Center for Academic Computing:



Alternative Format Statement

This publication is available in alternative media upon request.

Statement of Non-discrimination

The Pennsylvania State University is committed to the policy that all persons shall have equal access to programs, facilities, admission, and employment without regard to personal characteristics not related to ability, performance, or qualifications as determined by University policy or by state or federal authorities. The Pennsylvania State University does not discriminate against any person because of age, ancestry, color, disability or handicap, national origin, race, religious creed, sex, sexual orientation, or veteran status. Direct all inquires regarding the nondiscrimination policy to the Affirmative Action Director, The Pennsylvania State University, 201 Willard Building, University Park, PA 16802-2801; tel. (814) 865-4700; TDD (814)863-1150.

Table of Contents

Objectives 2

Database Normalization Terminology 3

First Normal Form 4

Second Normal Form 5

Third Normal Form………………………. ….. 7

Relating Tables Terminology 8

One-to-One Relationship 9

One-to-Many Relationship 11

Many-to-Many Relationship 12

Implementing Referential Integrity 15

Orphan Records 16

Cascading Deletes 16

Testing Cascading Deletes 17

Cascading Updates 17

Testing Cascading Updates 18

Independent Practice Activity 18

Additional Resources 19

Objectives

• Learn how to normalize tables to reduce data redundancy

• Understand the requirements for first, second, and third normal forms

• Learn how to set one-to-one, one-to-many, and many-to-many relationships between tables

• Implement referential integrity between related tables

• Use cascading deletes and updates to prevent orphan records

Database Normalization Terminology

|Normalization |the process of restructuring data files to 1) reduce data to its simplest structure, 2) minimize|

| |redundancy, and 3) achieve the most efficient and functional yet flexible way to store data |

|First Normal Form (1NF) |eliminates duplicate data |

| |a table should not contain similar information in several fields; eliminate duplicative columns |

| |from the same table or move repeating fields to a related table |

| |a table should not contain fields that can be further broken down into smaller meaningful parts |

| |create separate tables for each group or related data and identify each row with a unique column|

| |(the primary key); each table should describe a single entity (object) class |

|Second Normal Form (2NF) |requires all data in the table apply directly to the subject of the table indicated by the |

| |primary key field |

| |if the table has a single-field primary key and meets the requirements for 1NF, then it also |

| |meets the requirements for 2NF |

| |however, if the table has a composite (multiple-field) primary key, then all the fields in the |

| |table must be related to and/or dependent on ALL the fields of the composite primary key |

| |to convert a table to 2NF, find all the fields that are related to only part of the composite |

| |(multiple-field) primary key, group these fields into another table, then assign a primary key |

| |to the new table |

| |another way of looking at it is to remove ALL subsets of duplicate/redundant data that apply to |

| |multiple rows of a table and place them in new tables, then create relationships between these |

| |new tables and the original tables by using foreign keys |

|Third Normal Form (3NF) |eliminates fields that can be derived from other fields |

| |if a table contains fields that do not relate to and are not fully dependent on the primary key |

| |and completely describe the object that table represents, then it is not in 3NF |

| |to convert a table to 3NF, delete all the fields that do not relate to the primary key |

| | |

|Fourth Normal Form (4NF) |requires that tables not contain fields for two or more independent, multi-varied facts |

| |this rule requires splitting tables that consist of lists of independent attributes |

| |i.e. in a relation table, job skills and language fluency are independent facts about an |

| |employee; therefore, the 3-field relation table must be split into 2 two-field relation tables |

|Fifth Normal Form (5NF) |requires the capability to reconstruct the source data from the tables that have met 1NF, 2NF, |

| |3NF and 4NF |

| |you should be able to regenerate from the database a spreadsheet that contains all the data in |

| |the original version |

| |this is an “acid test” for normalization and requires considerable skill in designing queries to|

| |join related tables to produce a readable output to compare wit the original data |

Before 1NF

[pic]

After 1NF

[pic]

Before 2NF

[pic]

After 2NF

[pic]

[pic]

Before 3NF

[pic]

After 3NF

[pic]

[pic]

Relating Tables Terminology

|Relationships |used to extract data from several tables at the same time |

|Primary key |unique identifier for each record in a table |

|Foreign key |the primary key in the related table |

|Referential integrity |ensures that changes made to one table will be reflected in the related table |

|One-to-one relationship |when one complete record in the primary table is related to just one record in the related table|

| |and vice versa; both tables are equally dependent on each other |

|One-to-many relationship |when one record in the primary table is related to several records in the related table; |

| |however, a record in the related table has only one related record in the primary table |

|Many-to-many relationship |when several records in one table are related to several records in another table. A |

| |many-to-many relationship cannot be created directly in MS Access. To relate such tables, you |

| |must first create a junction table that contains the primary keys of both tables |

|Junction table |a table which has a primary key that’s actually a combination of at least the primary keys from |

| |two or more tables; a junction table acts as a bridge between the two tables in a many-to-many |

| |relationship |

One-to-One Relationship

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

One-to-Many Relationship

[pic]

[pic]

[pic]

[pic]

[pic]

Many-to-Many Relationship

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

[pic]

Implementing Referential Integrity

[pic]

[pic]

[pic]

Orphan Records

If you set a relationship between two tables, then the related table should always have a corresponding record in the primary table. If data is modified or deleted in the primary table, then the related table should also be updated. If you accidentally delete a record without deleting the related records in the other table, then that table will contain orphan records. Orphan records are no longer related to anything in the primary table.

Cascading Deletes

You can enforce referential integrity to cascade deletes on records in related tables. When you use cascade deletes to delete a record from a primary table, MS Access automatically deletes all related records from other related tables. This ensures that there are no orphan records.

[pic]

Testing Cascading Deletes

[pic]

When you delete a record in the primary table, a message box will come up stating that cascading deletes will cause records in related tables to also be deleted.

Cascading Updates

You can enforce referential integrity to cascade updates to related records in related tables. This means that whenever you change a primary key in a table, the change is updated in all of the related tables.

[pic]

Testing Cascading Updates

Try changing the primary key in the primary table, then check to see if it was updated in the related tables.

Independent Practice Activity

Open Relational_databases

Open the Relationships window

Create a one-to-many relationship between the Retailer and Transaction tables. The relationship should include all of the records from Retailer and only those records from Transaction where the joined fields are equal

Create a many-to-many relationship between the Retailer and Product tables by creating a one-to-many relationship between the Product and Transaction tables

Save the relationships

Compare the Relationships window to the diagram below:

[pic]

Enforce cascading deletes between the Retailer and Transaction tables

Update the relationship

Test cascading deletes between the Retailer and Transaction tables

Enforce cascading updates between the Retailer and Transaction tables

Update the relationship

Test cascading updates between the Retailer and Transaction tables

Close the database

Additional Resources

MS Access 2002 Help

Help menu (Contents, Answer Wizard, Index tabs)

F1

Ask a Question box

Office Assistant

Books

Special Edition Using Microsoft Access 2002 by Roger Jennings

The Complete Reference: Access 2002 by Virginia Andersen

Mastering Access 2002 by Alan Simpson and Celeste Robinson

Teach Yourself Microsoft Access 2002 Visually by Ruth Maran

Instant Access Databases by Greg Buczek

Microsoft Access 2002 Bible by Cary N. Prague and Michael R. Irwin

Sams Teach Yourself Microsoft Access 2002 in 10 Minutes by Faithe Wempen

Microsoft Access 2002: At A Glance by Perspection

Microsoft Access 2002: Step By Step by Catapult, Inc.

Running Microsoft Access 2002 by John Viescas

Microsoft Pocket Guide to Microsoft Access 2002 by Stephen L. Nelson

Websites























Phone Numbers

Help Desks (215 Computer Bldg 863-2494 and 2 Willard Bldg 863-1035)

Seminar Line 863-9522

-----------------------

ITS@PennState

224B Computer Building

University Park, PA 16802

seminars@psu.edu



ITS@PennState

MS Access: Creating Relational Databases

Observe all of the Project number fields. These fields can be combined into one field called Project_number. As it stands now, it is not in 1NF.

Observe the Name field. It contains both first and last names. This is not in 1NF.

The Name field has been divided into two separate fields

The Project number fields have been combined into one field

Together, the Employee_code and the Project_number make up a composite (multiple-field) primary key

Observe the First_name, Last_name and Region fields. These fields have repetitive values. You can divide the data into two tables.

The table has Employee_code, First_name, Last_name and Region fields. All the fields in the table relate to the Employee_code field which is the primary key.

This table contains Employee_code, Project_number and Earnings fields. The Earnings field relates to both the Employee_code and Project_number fields which together form the composite (multiple-field) primary key.

The table contains a Dept_name field which relates to the Dept_code field.

And, the Dept_code field relates to the Employee_code field (primary key).

However, the Dept_name field does not directly relate to the Employee_code field (primary key). It is dependent on the Dept_code field.

The table contains Employee_code, First_name, Last_name, Region and Dept_code fields. All of these directly relate to the Employee_code field (primary key).

The table contains Dept_code and Dept_name fields. Dept_name directly relates to Dept_code (primary key).

First, go to the Tools menu and choose Relationships.

Then, click on the Show Table icon on the toolbar.

Click on the Tables tab.

Select Sales_employees.

Click the Add button.

Select Sales_payroll.

Click the Add button, then click the Close button.

Drag the Employee_code field (primary key) from the Sales_employee table and drop it on the Employee_code field in the Sales_payroll table.

Notice the Relationship Type at the bottom of the window says, “One-To_One.”

Click on the Join Type button.

The first option creates a one-to-one relationship and is selected by default.

Click OK

Check Enforce Referential Integrity, then click on the Create button.

Observe the line connecting the two tables and the number 1 at each end of the line indicating this is a one-to-one relationship.

Click on the Show Table icon on the toolbar, then add the Departments and Employees tables.

Drag the Dept_code field (primary key) from the Departments table and drop it on the Dept_code field (foreign key) in the Employees table.

Notice the Relationship Type at the bottom of the window says, “One-To_Many.”

Click on the Join Type button.

Select the second option. This will create a one-to-many relationship between the tables and will ensure that 1) each record in the Employees table has a corresponding record in the Departments table and 2) that each record in the Departments table can have multiple records in the Employees table. Click OK.

Check Enforce Referential Integrity.

Click the Create button.

Observe the line connecting the two tables and the number 1 near the Departments table and the infinity symbol near the Employees table indicating this is a one-to-many relationship.

Open the Transaction database

Click on the Relationships icon on the toolbar

Click on the Show Table icon on the toolbar and add the Customer, Order_details and Products tables to the Relationship window.

Drag the Customer_ID field from the Customers table to the Customer_ID field in the Order_details table.

In the Edit Relationships window, click the Join Type button.

Select the second option for a one-to-many relationship between the Customers and Order_details tables.

Check Enforce Referential Integrity.

Click the Create button.

Now, repeat this procedure and create a one-to-many relationship between the Product and Order_details tables.

There is a one-to-many relationship between Customers and Order_details and a one-to-many relationship between Products and Order_details. The Products and Customers tables have a many-to-many relationship that is defined by both tables have a one-to-many relationship with Order_details which is the junction table.

Observe the Order_details table.

It is a junction table because it acts as a bridge between which customers bought each type of product in each order transaction.

Open the Customers table, then go to the Window menu and choose 1Transactions: Database (Access 2000 file format)

Open the Order_details table, then go to the Window menu and choose Tile Horizontally.

In the Order_details table, observe the Customer_ID and the Order_ID. Notice that a customer can place several orders.

However, for each Order_ID there is only one Customer_ID. This means that only one customer can place an order. There can’t be multiple customers for each individual order.

Enforcing referential integrity means that since there is a relationship between the Customer table and the Order_details table, if a customer is deleted out of the Customer table, then all the orders placed by that same customer will also be deleted.

Check Enforce Referential Integrity and Cascade Delete Related Records

Click OK

Check Enforce Referential Integrity and Cascade Update Related Fields

Click OK

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download