CMIS 450 Database Design Dr. Bijoy Bordoloi

1
CMIS 450 Database DesignDr. Bijoy Bordoloi

• Entity Relationship (E-R) Model

2
E-R Modeling and Logical Database Design

• The Entity Relationship Model
• The Relational Model
• Transforming E-R Diagrams into Relations
• Normalization

3
E-R Model Constructs

• Entities
• Relationships
• Cardinality
• Attributes
• Sub-entities (EER)
• Examples

4
Definition of Entity

• An entity type is a set of entity instances
  sharing the same common properties
  (attributes), characterized by
• Name - a noun, e.g., EMPLOYEE
• Description - defines which instances belong to
  the entity type E
• Identifier - a property (or a joint set of
  properties) which uniquely identify entity
  instances
• An entity instance is a single identifiable
  real-world thing, e.g., Jack Spratt

5
Entity Types
DEPARTMENT
EMPLOYEE
SUPPLIER
PROJECT
6
Attributes

• Any property of an owner entity
• Attribute type
• A set of attribute instances expressing the same
  property of an entity type, e.g., Gender
• Attribute instance
• A particular property of an individual entity
  instance, e.g., Male

7
Attributes
EMPLOYEE
EMP-ID SS-NUM EMP- NAME PHONE
8
Identifier

• An attribute (or a joint set of attributes)
  which uniquely identify the entity instances of
  an entity
• An identifier should be
• - Unique and known (must not be NULL)
• - Short (preferably)
• - Stable (preferably)

9
What would you choose as the Identifier of the
entity type EMPLOYEE?
EMPLOYEE
EMP-ID SS-NUM EMP- NAME PHONE
10
What Should an Entity Be?

• SHOULD BE
• An object that will have many instances in the
  database
• An object that will be composed of multiple
  attributes
• An object that we are trying to model
• Must have an identifier
• SHOULD NOT BE
• A user of the database system
• An output of the database system (e.g. a report)

11
Figure 3-4
12
Dependent and Independent Entities

• Entity B is existence-dependent on entity A when
• Some instance of A must exist before B can exist
• If A ceases to exist, B must also cease to exist
• An independent entity is not existence-dependent
  on any other entity
• Independent Entity Strong Entity
• Dependent Entity Weak Entity
• Diagramming convention

13
Dependent and Independent Entities

• Typically the identifier of a weak entity is a
  composite identifier which includes the
  identifier of the entity which the weak entity is
  existence-dependent upon
• Diagramming convention

14
Dependent and Independent Entities

• Examples

DEPENDS ON
OFFERING
COURSE
DEPENDS ON
TASK
PROJECT
15
Relationships

• An association between two or more entities, of
  significance to the enterprise
• Relationships are named with a verb an depicted
  as lines between entities

PART
SHIP
SUPPLIER
COMPANY
EMPLOYEE
WORK FOR
16
Degree

• The degree of a relationship is the number of
  associated entities
• Relationships are usually of degree 2 (binary) -
  but sometimes can be unary or of higher degree
  (N-ary)

17
Binary Relationships

• The relationship involves at the most only two
  entities

SHIP
SUPPLIER
PART
WORK FOR
EMPLOYEE
COMPANY
18
Unary Relationships

• Relationships may associate an entity with
  itself, i.e., when an instance of an entity is
  related to some other instance(s) of the same
  entity.

EMPLOYEE
Married_to
19
Ternary Relationship

• The relationship involves three entities

20
Relationships

• Several relationships may exist between the same
  entities
• The name and description distinguishes different
  relationships between the same entities

21
Cardinality Constraints

• A binary relationship (E1,E2) has two directions,
  left and right
• Each direction has cardinality constraints,
  described as maximum and mimimu.

22
Cardinality Constraints

• Cardinality Constraints - the number of instances
  of one entity that can or must be associated with
  each instance of another entity.
• Maximum Cardinality (can be)
• The maximum number
• Minimum Cardinality (must be)
• If zero, then optional
• If one or more, then mandatory

23
Maximum Cardinality

• The maximum cardinality of a direction E1 to E2
  indicates how many e2s can match a given e1 (at
  most)
• Cardinality is denoted as MaxLeft - MaxRight
• 1-1
• 1-N
• N-1
• M-N

24
Cardinality
WORK-IN
?
EMPLOYEE
DEPARTMENT
ONE
EACH EMPLOYEE
DEPARTMENT
MANY
EMPLOYEES
EACH DEPARTMENT
MANY
ONE
25
Examples of Cardinality
EMPLOYEE
DEPARTMENT
N
1
WORKS-IN
D1
E1
D2
E2
D3
E3
D4
E4
26
Examples of Cardinality
1
1
MANAGE
D1
E1
D2
E2
D3
E3
D4
E4
M
N
FORMERLY WORKED-IN
D1
E1
D2
E2
D3
E3
D4
E4
27
Minimum Cardinality

• Minimum cardinality is specified as zero to one

WORK IN
?
EMPLOYEE
DEPARTMENT
ONE
EACH EMPLOYEE
DEPARTMENT
ZERO
EMPLOYEES
EACH DEPARTMENT
ZERO
ONE
28
Minimum Cardinality

• For the purpose of our course, we will use

?
One to many (maximum cardinality)
Mandatory Optional (minimum cardinality)
teaches
?
Instructor
29
Summary of Cardinality

• Minimum and maximum cardinality is specified for
  each direction of a relationship
• Maximum cardinality is either one or many for
  each direction
• Minimum cardinality is either zero or one for
  each direction

30
Terminology

• A single-valued relationship (from entity A to
  entity B) has maximum cardinality of one (from A
  to B)
• A multi-valued relationship has maximum
  cardinality of many
• When we speak of single-valued and multi-valued
  relationships, a direction is implicit