Conceptual Data Model

1
Things are bad. Children no longer obey their
parents and everyone is writing a book. --
Marcus Tillius Cicero
2
Entity Relationship DiagramWhat Is It?

• A graphical diagram representing the data
  relationships in a problem area
• Often referred to as E/RD

3
Entity Relation Diagram
Student
Class
Enrolls in
S_No S_Name
C_No C_Name
4
IDEF1X Diagram
5
Entity-Relationship Diagram

• Consists of
• Entities
• Relationships
• Keys
• Attributes
• Is not necessarily Normalized

6
Entity

• Any distinguishable person, place, thing, event,
  or concept about which information is kept.
• Entity Instance A single occurrence of an
  entity.

7
Entities

• Entities are represented as boxes
• Square cornered boxes represent independent
  entities
• Rounded cornered boxes represent dependent
  entities
• More on this later

8
Example of Entities
Entity Title
Student
Primary Key
Student Number
Entity Box
Student Name Street Address City State Zip
Code Phone Number Birth Date
Attributes
9
Relationship

• A connection between two entities

10
Relationships

• Relationships are represented as lines
• They connect two entities
• They have names
• The connection between entities and relationships
  can be stated as sentences

11
Types of Relationships

• Identifying Relationships
• Non-Identifying Relationships
• Recursive Relationships

12
More about Entities

• Entities can exist in two forms
• Independent Entity
• Either Entity can exist without a relationship to
  the other
• Dependent Entity
• A Dependent Entity must be tied to one or more
  Independent Entities

13
More About Relationships

• Relationships can be
• Identifying Relationships
• Ties an Independent Entity and a Dependent Entity
  together
• Non-Identifying Relationships
• Ties two (or more) Independent Entities together

14
E/R examples

• A TEAM HAS many PLAYERS

15
E/R Examples

• A Plane-Flight transports many Passengers

16
E/R Examples

• A House is Owned by 1 Owner

17
E/R Examples

• Many Salespeople Sell many Products.

18
Attribute

• A property of an Entity

19
Attributes

• Attributes are fields
• They describe the entity
• They can be data attributes (non-key) or key
  attributes

20
Keys

• Primary Key An attribute or group of attributes
  that has been chosen as the unique identifier of
  the entity
• Primary Key Attribute A attribute that, either
  by itself or in combination with other primary
  key attributes will form the primary key
• Non-key Attribute An attribute that has not
  been chosen as a part of the primary key of the
  entity
• Candidate Key An attribute or group of
  attributes that might be chosen as a primary key
• Foreign Key Primary Key from another entity,
  contributed by a relationship.

21
Key Selection

• Employee
• Employee Number
• Employee Name
• Employee Sex
• Employee Hire Date
• Social Security Number
• Employee Birth Date
• Employee Bonus Amount

22
Key Selection Rules

• Find an attribute that will not change its value
  of the life of each entity instance
• Look for a reasonably small key
• Avoid Intelligent keys where the structure of
  the key indicates groupings, locations,
  classification, dates, etc.
• Consider substituting a single attribute
  surrogate key for a large composite key

23
Entities with Attributes
Employee
Primary Key Area
Employee Number
Data Attribute Area
Employee Name Employee Sex Employee Hire
Date Social Security Number Employee Birth
Date Employee Bonus Amount
24
Identity Relationship

• A relationship in which the primary key of the
  parent entity becomes part of the primary key of
  the child entity

25
Identity Relationships
26
Non-Identity Relationship

• A relationship where the primary key of the
  parent entity is placed in the data area of the
  child entity.
• In non-identifying relationships the child may be
  existence-dependent on the parent, but is not
  identification-dependent on the parent.

27
Non Identifying Relationship
28
Entities

• Independent Entity An entity that does not
  depend on any other for its identification.
  Represented by a square cornered box.
• Dependent Entity An entity that depends on one
  or more other entities for its identification.
  Represented by a rounded corner box.

29
11 Relationships
Employee
Car
Assigned
Kurt
Yugo Brian Mustang Tonya Ranger Scott
Jeep Nancy Camry
30
1n Relationships
Customer
Video
Rents
Jack Janet Tim
Star Wars Monty Python and the Holy
Grail Speed Sleepless in Seattle Cassablanca Silen
ce of the Lambs
31
NM Relationships
Student
Course
Enrolls In
Sara Jesse Sam
Java Programming Accounting I English 101 Physics
for Dummies
32
Excercises

• Try and draw the E/RDs for the following
  exercises
• Some exercises may build on what was done in
  previous exercises.

33
Exercise 1

• Customers come into the video store and rent
  movies. Customers must be in the store system.
  This system assigns them a unique identifier.
  When we sign them up we get their name, address,
  and phone number.

34
Exercise 2

• We have many movies in the store. We try and
  keep a large number of the most popular movies
  and a smaller number of less popular movies.
  Each VHS or DVD copy is individually identified.
  We will rent these copies many times through out
  the year.

35
Exercise 3

• When a movie is rented out, it is due back in
  three days. We need to know who has the movie
  rented out.

36
Exercise 4

• For each movie, we need to know the name, rating,
  and rental rate.
• For each copy of the movie we need to know its
  general condition and the remaining rentals we
  will use that copy for.

37
Exercise 5

• We have employees at each store. We know the
  employees name, address, hire date and salary.
• These employees are involved in the rental
  process. We want to know which employee is
  involved in each rental event.

38
Exercise 6

• When a movie is rented, we need to track when the
  movie was rented, which copy of the movie was
  rented, what customer rented it, and what
  employee is involved.

39
What You Should Know

• What the different kinds of entities are.
• What the different relationships are.
• What a primary key is and how to select it.
• How to draw simple E/RDs.

40
Assignment

• Review Chapter 5.
• Make sure you understand how to create the E/RD
  for the exercises.