417 LAB ACTIVITY 8: Developing Normalized Entity Relationship Diagrams

WHY:

This activity is designed to give you practice designing, normalizing and drawing ERDs using System Architect. Normalizing the data attributes for entities in an Entity Relationship Diagram helps establish a clean database design. Entities with repeated groups of attributes or which have many-to-many relationships with other entities will be changed or replaced under normalization by several new entities. Thus, a normalized ERD is often much more complex than an unnormalized one.

LEARNING OBJECTIVES:

1. Discover how to construct a normalized Entity Relationship Diagram using System Architect.
2. Gain a better understanding of the three normal forms.
3. Get practice setting up a normalized record of attributes for entities in System Architect's Encyclopedia.

PERFORMANCE CRITERIA:

1. Quality of the answers to the Critical Thinking Questions.
   • Completeness
   • Clarity
   • Depth
2. Quality of the normalized Entity Relationship Diagram.

RESOURCES:

1. System Architect Help System and Manuals;
2. Class Activity 17;
3. Chapter 11 in Systems Analysis and Design Methods;
4. 60 minutes;

PLAN:

1. Choose roles if you have not already done so.
   
   
2. Draw an Entity Relationship Diagram on paper for the following problem:

   Saint Mary's conducts a variety of fund-raising campaigns. The campaign has a number of pledge categories. Donors are contacted and they either make donations outright or pledge a certain amount to one or more pledge categories. The attributes of the entities campaign, donor, donation, and pledge are on the other side.
   
   

3. Underline the identifier(s) (Primary Key(s)) for each entity.
   
   
4. Put the attribute lists in 1NF -- put repeating groups into separate entities with combination identifiers (concatenated keys).
   
   
5. Put the attribute lists into 2NF -- look for partial key dependencies, remove these items, but make sure they are included in another entity.
   
   
6. Put the attribute lists in 3NF -- make sure no attribute depends on a non-key attribute. Remove those that do. You may have to create new entities for these dependent attributes.
   
   
7. Use System Architect to draw a fully attributed normalized ERD for this problem. Note the role of foreign keys to indicate relationships between entities.
   
   
8. Answer the Critical Thinking Questions.

MODEL:

DONOR            DONATION              CAMPAIGN              PLEDGE
  Donor ID         Donation Number       Campaign Name         Pledge Category
  Donor Name       Donation Code         Campaign Description  1 to many
  Donor Address    Donation Description  Campaign Date           Donor ID
0 or more          Donation Amount       Campaign Goal ($)       Pledge Date
  Pledge Category  Donor ID            1 or more                 Pledge Amt.
  Pledge Date      Pledge Category       Pledge Category
  Pledge Amount    Pledge Date           Category Goal ($)
1 or more 
   Donation Number
   Donation Code
   Donation Amount
   Donation Date
   Pledge Category
   Pledge Date

CRITICAL THINKING QUESTIONS:

1. Why do you know that an ERD which has many-to-many relationships is not normalized?
   
   
   
2. Which attributes are foreign keys in each of the four original entities in the model?
   
   
   
3. For each relationship in the fully-attributed ERD, which is the parent entity and which is the child entity?
   
   
   
4. List the associative or intersection entities in the final ERD.
   
   
   

   ---

   CPSC 417 Lab Activity 8 -- Revised 10/30/98

   Return to the List of Activities