Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-1
EXTENDED LEARNING MODULE C
DESIGNING DATABASES AND ENTITY-RELATIONSHIP DIAGRAMMING
JUMP TO THE SUPPORT YOU WANT
STUDENT LEARNING OUTCOMES
2. List and describe the four steps in designing and building a relational database.
4. Given a small operating environment, build an entity-relationship (E-R) diagram.
6. Describe the process of creating an intersection relation to remove a many–to-many
relationship.
MODULE SUMMARY
This Extended Learning Module teaches your students how to appropriately design a relational
database.
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-2
LECTURE OUTLINE
INTRODUCTION (p. 371)
DESIGNING AND BUILDING A RELATIONAL DATABASE (p. 371)
1. Step 1: Define Entity Classes and Primary Keys
END OF MODULE (p. 383)
1. Summary: Student Learning Outcomes Revisited
Back to Jump List
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-3
MODULES, PROJECTS, AND DATA FILES
Group Projects
• Building Value Chains: Helping Customers Define Value
DATA FILES
XLM/C Running Case (Word document) – for Assignment and Exercise #6 on p. 385
Mod C-4
• These are the Student Learning Outcomes for the module.
• These are the Student Learning Outcomes for the module.
• Use them as a road map to inform your students of what you will be
• This slide presents some key information concerning the logic of the
• This slide discusses the differences between and similarities of
spreadsheets and databases (Student Learning Outcome #1).
• This slide provides the definitions for database and relational
database.
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-5
• This slide presents the four steps to follow in designing and building a
relational database (Student Learning Outcome #2).
• In this module, we cover the first three steps.
• These next 11 slides review Solomon Enterprises as it was discussed in
Chapter 3.
• This slide presents Figure 3.2 on page 67 of Chapter 3.
• It provides a review of Solomon Enterprises for your students.
• These are entity classes.
• This slide lists the 5 database relations or tables in Solomon’s
• This slide presents the five types of concrete that Solomon provides.
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-6
• This slide presents Figure C.1 on page 372.
• It is a report of the concrete types that Solomon provides and
• This slide presents the 6 different raw materials that go into making
the concrete types.
• This slide presents some observations for how Solomon Enterprises
works.
• This slide presents some observations for how Solomon Enterprises
works.
• This slide presents some observations for how Solomon Enterprises
works.
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
• These two slides present the four important business rules for
Solomon Enterprises.
• This slide continues with the four important business rules for
Solomon Enterprises.
• This slide presents the definition for an entity class and identifies the
three entity classes in the initial database design (Student Learning
Outcome #3).
• This slide presents the definitions for primary key and instance.
• This is mostly a review of Chapter 3.
• This slide presents the entity classes under consideration and their
primary keys.
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-8
• This slide presents Figure C.1 on page 372.
• It points out some entity classes, instances, and primary keys.
E-R diagrams and going through the process of normalization.
• In this section, your students will spend a great deal of time building
wish; however, be careful not to confuse your students.
• This slide presents the five basic symbols used in an E-R diagram.
• You may choose to use different notations (there are many) if you
• This slide presents Figure C.2 on page 375.
• It provides the initial E-R diagram for the SCM side of Solomon’s
database.
• This slide provides how to read and interpret the E-R diagram in Figure
C.2 on page 375.
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-9
• This slide presents the notion that E-R diagrams also denote the
numerical nature of relationships among entity classes.
• This slide presents Figure C.3 on page 376.
• It illustrates how to build cardinality into an E-R diagram and how to
• This slide presents Figure C.3 on page 376.
• This slide begins the process of introducing your students to
normalization (Student Learning Outcome #5).
• This slide presents the three rules of normalization followed in this
text.
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-10
• The first rule of normalization is to eliminate many–to-many
relationships.
• This slide presents Figure C.4 on page 377.
• It graphically illustrates how to eliminate a many–to-many relationship
• This slide provides the definitions for an intersection (composite)
relation and a composite primary key.
• This slide presents Figure C.5 on page 378.
• These final three slides of this section present the steps involved in
creating an intersection relation to eliminate a many-to-many
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-11
• This slide continues from the previous slide that presents the steps
involved in creating an intersection relation to eliminate a many–to–
many relationship.
• This slide continues from the previous slide that presents the steps
involved in creating an intersection relation to eliminate a many–to–
• In this section, your students will focus on steps 2 and 3 of
• This slide discusses ensuring that each piece of information is in the
correct relation.
• If you review Figure C.6 on page 381, you can see that Supplier Name
• Therefore, Supplier Name does not belong in the Raw Material
relation but rather the Supplier relation.
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-12
• This slide discusses removing derived fields.
• You do not need derived fields in a database because you can
• This slide illustrates that Raw Material Total is a derived field and can
therefore be removed.
• This slide summarizes this section by discussing that Figure C.7 on
• These final two slides discuss implementing a database with a DBMS.
• We cover how to do this in Extended Learning Module J.
• These final two slides discuss implementing a database with a DBMS.
• We cover how to do this in Extended Learning Module J.
Back to Jump List
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-13
SHORT-ANSWER QUESTIONS (p. 384)
1. How are relational databases and spreadsheets both similar and different?
ANSWER: Relational databases and spreadsheets are similar in that they both store
2. What is a database?
3. What are the four steps in designing and building a relational database?
4. What are some examples of entity classes at your school?
5. What is the role of a primary key?
6. What is an entity-relationship diagram?
7. How do business rules help you define minimum and maximum cardinality?
8. What is normalization?
Mod C-14
9. What are the three major rules of normalization?
10. What is an intersection relation? Why is it important in designing a relational database?
11. Why must you remove derived information from a database?
12. What is a database management system (DBMS)?
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-15
ASSIGNMENTS & EXERCISES (p. 384)
1. DEFINING ENTITY CLASSES FOR THE MUSIC INDUSTRY The music industry tracks and uses
all sorts of information related to numerous entity classes. Find a music CD and carefully
review the entire contents of the jacket. List as many entity classes as you can find (for just
that CD). Now, go to a music store and pick out a CD for a completely different music genre
and read its jacket. Did you find any new entity classes? If so, what are they?
2. DEFINING BUSINESS RULES FOR A VIDEO RENTAL STORE Think about how your local video
rental store works. There many customers, renting many videos, and many videos site on
the shelves unrented. Customers can rent many videos at one time. And some videos are
so popular that the video rental store keeps many copies. Write down all the various
business rules that define how a video rental store works with respect to entity classes and
their relationships
DISCUSSION
• You may want to perform this exercise in class and create a partial E-R diagram.
• Below, we’ve recorded a few rules.
Mod C-16
3. CREATING AN E-R DIAGRAM FOR A VIDEO RENTAL STORE After completing assignment 2
above, draw the initial E-R diagram based on the rules you defined. Don’t worry about
going through the process of normalization at this point. Simply identify the appropriate
relationships among the entity classes and define the minimum and maximum cardinality of
each relationship. By the way, how many many-to-many relationships did you define?
DISCUSSION
• Answers here will vary according to the business rules your students defined in the
4. ELIMINATING A MANY–TO-MANY RELATIONSHIP Consider the following situation. At a
small auto parts store, customers can buy many parts. And the same part can be bought by
many different customers. That’s an example of a many–to-many relationship. How would
you eliminate it? What would you call the intersection relation? This one is particularly
tough: You’ll have to actually create two intersection relations to model this correctly.
DISCUSSION
• This is the classic order/invoice problem.
5. DEFINING THE CARDINALITY AMONG TWO ENTITY CLASSES Consider the two entity
classes of Student and Advisor at your school. How would you build an E-R diagram to show
the relationship between these two entity classes? What is the minimum and maximum
cardinality of the relationship?
DISCUSSION
• Answers here will vary according to how your school performs the advising function.
Mod C-17
6. BUILDING A DATABASE OF STUDENTS, SEMINARS, AND TEACHERS On the Web site for
this text (www.mhhe.com/haag, select XLM/C), you’ll find a robust running case study for
this module. In Phase #1, you’ll find a description of a school offering multiple sections of
two different weekend seminars. You are charged with defining the entity classes and their
primary keys for a supporting database. In Phase #2, you must define the relationships
among the various entity classes. In Phase #3, you must define the cardinality for the
relationships among the entity classes. In Phase #4, you’ll have to create an intersection
relation to take care of a many-to-many relationship. Finally, you must define all of the
information (fields) for each relation. Tackle this case study to fully integrate everything
you’ve learned in this module.
DISCUSSION
• For those of you who have used this book in the past, this running case study is a
Phase #1
ENTITY CLASSES
PRIMARY KEYS
Seminar
3-character identifier and number
Seminar Section
and section number
Qualified Teacher
Social security Number
Student
Student ID
BUSINESS RULES
A Seminar Section must have a Qualified Teacher.
Seminar 3-character identifier and number
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-18
Phase #2
Phase #3
Seminar
has
Seminar
Student
Seminar Section
has
Student
Seminar Section
teaches
enrolls in
Extended Learning Module C – Designing Databases and Entity-Relationship Diagramming
Mod C-19
Phase #4
Back to Jump List