Database Concepts Ch 4 – Data Modeling and Entity-Relationship Model
Start class with Traffic Signs
Why do they use signs instead of words? No language issues; faster to understand
Are there groups of signs which carry the same meaning?
Warning are diamond shaped; Traffic issues are Rectangles; Roads are unique
What other things in our world use symbols instead of writing it out?
Short-hand; sports referees and umpires; computer desktops
Most system modeling use symbols – Flow Charting; Data Flow Diagrams
Many symbol methods are available for Database Design. In this class, we will learn Entity-
Relationship Models.
In this method, symbols carry the following meanings…
Rectangles represent Entities (also known as tables, files, or sheets)
Diamonds represent relationships between Entities (tables, files, or sheets)
Another method shown in the text is Unified Modeling Language (UML), symbols are
Rectangles represent Entities (also known as tables, files, or sheets)
Lines represent relationships between Entities (tables, files, or sheets)
In Traffic Signs, when you see a diamond, you know it is a Warning, but the differences in
the symbol tell you what kind of Warning. In Traffic signs, color is used to show the
difference between general warnings (yellow) and construction warnings (orange).
In most database modeling technique, color is not available, so slight changes in shape or
added symbols are used.
Database modeling is uses to symbolize User Requirements for a system.
User Requirements are obtained thru user interviews and existing systems.
With the person next to you, write down as many User Requirements (business rules) you
can come up with for the Glory Garden system to date. See page 88 for examples.
User Requirements define a lot about the system to be developed. Most things have to do
with code that needs to be written. Or rules for Attributes (fields/columns). Some are
regarding how Entities (tables/files/sheets) are related.
Terminology regarding Entities (as well as Object modeling) – Classes vs Instances
The Entity as a whole is known as Entity Class. Customer class (all occurrences/records)
An individual record is known as an Instance of the Class. Beth Smith is an Instance.
With the person next to you, create a list of Entity Classes for the Dorm system here.
(Add the list to the board)
Page 1 of 4
Before we can add Instances to each Class, we must first discuss Attributes (also known as
fields or columns).
Add 3 Attributes to each Class (be sure the Attributes follow good relations rules; each Entity
should have a Primary Key. Try to use a meaningful key not a Surrogate.)
Add 2 Instances to each Class.
Per above, User Requirements / Business Rules define Relationships between Entities.
Relationships are defined between Entity Classes and Entity Instances.
Relationships typically exist between 2 Entity Classes – Student lives in a Dorm room.
But they can exist with 1 Entity Class – Students live with other Students
And they can exist with more than 2 Entity Classes – the best illustration of this is…
Mother; Father; Child
Relationships with 1 class is Recursive; 2 classes are Binary; more than 2 is Ternary
This is known as Degree of the relationship.
Relationships also describe Entity Instances. To understand this lets look at data (after all a
record of data is one Instance of the Entity).
A particular student (John Smith) lives in how many Dorm rooms.
A particular building (Terry) has how many Floors.
A particular Floor has how many Dorm rooms.
When looking at Entity Instances in Relationships, we are interested in Max and Min
Lets look at basic modeling techniques for these Business Rules regarding Instances.
(Connect the Entities together but include only the Rules discussed.)
Most Business Rules do not discuss both sides of the relationship.
Write the Entity Instance business rule for the missing relationships.
Ie. A particular Dorm room can have how many students living in it?
This is known as Type of the Relationships; One-to-One, One-to-Many, Many-to-Many
Be careful of One-to-One relationships. Mandatory One-to-One relationships are most often
just a sign of an additional attribute. This is always true when the one-to-one relationship
between them is the only relationship between the 2 entitities.
Relational Databases can not support Many-to-Many relationships.
As we showed in class on Employees being certified on many Items.
A Correlation table (sheet) was created to represent the data. A new sheet with only the
Primary Key from Employee and Service.
Sometimes the Correlation table actually has additional information. Ie. What if we wanted
to store the data an Employee got certified on the Item.
Page 2 of 4
The term changes from a Correlation table to an Associative Object. No longer does the
table simply correlate the 2 Entities, it actually provides additional information.
Each side of a Relationship has a Maximum and Minimum value known as Cardinality.
Minimums are evaluated as Optional and Mandatory. Does an Instance have to participate in
the Relationship? Ie. Does a Dorm room have to have a student living in it?
Finish your Business Rules regarding Entity Instances, then add them to the diagram.
Some Entities Instances are completely dependant on the existence of other Entity Instances.
Ie. A particular Dorm room can not exist without a Building.
Entities completely dependant on other Entities are known as Weak Entities.
Weak Entities are Rectangles with rounded corners.
There are many different data modeling techniques out there. Your place of business might
supply you with their own just as another country has their own Traffic Signs. They are all
similar in their meaning (control speed; warnings; give directions) but they very in the exact
way they communicate things.
Unified Modeling Language is represented in the text and in many ways is similar but it also
has its differences.
For the exam, students need to be able to construct ERDs and read UML.
Terminology –
System Modeling
Flow Charting
Data Flow Diagrams
Entity-Relationship Diagrams (ERD)
Unified Modeling Language (UML)
Business Rules
Entity vs Attribute
Classes vs Instances
Binary / Recursive / Ternary Relationships
Cardinality
Correlation Table
Associative Object
Weak Entities
Homework #8 –
Answer the following questions from the end of the chapter page 107
2,3,6,7,8,9,15,16,17,21 (estimated time 20 minutes)
James River Jewelry Project Page 109 (estimated time 40 minutes)
(Answer all questions in a word document. To include ERDs use the Drawing toolbar.
View…Toolbars…Drawing. If you prefer working in Paint, that will be accepted as well.)
Page 3 of 4
Bring a copy of your ERD for James River Jewelry to the next class session. We will
distribute and discuss the answer according to the textbook.
Page 4 of 4