ER MODEL

1
E-R MODEL

• Entity-Relationship Data Model A
  detailed, logical representation of the entities,
  associations, and data elements for an
  organization or business.

2
E-R DIAGRAM

• Entity-Relationship Diagram
• A graphical representation of an E-R model
• Basic Symbols
• Entity ?
• Relationship, ?
• Primary Key O-
• Attribute O

3
ENTITY TYPE

• A collection of entities that share common
  properties or characteristics.
• Examples
• employee
• course
• account

4
ENTITY INSTANCE

• A single occurrence of an entity type
• Example Instance of Employee
• John Doe
• 345-567-8925
• 123 Ocean Drive
• Santa Monica, CA 97142

5
ATTRIBUTE

• A named property or characteristics of an entity
  that is of interest to the organization
• Example Student
• Student Number
• Student Name
• Address
• Phone

6
CANDIDATE KEY

• An attribute ( or combination of attributes) that
  uniquely identifies each instance of an entity
  type.
• For the entity STUDENT
• Student No.

7
PRIMARY KEY

• A candidate key that has been selected as the
  identifier for an entity type.
• Also called an identifier.
• Primary key values may not be null.
• Name Address
• SS

8
MULTIVALUED ATTRIBUTES

• An attribute that can have more than one value
  for each entity instance.
• Example
• Employees Skills
• Typing
• Truck Driving
• Welding

9
RELATIONSHIP

• An association between the instances of one or
  more entity types that is of interest to the
  organization
• student - completes - course
• ? - ? - ?

10
DEGREE OF RELATIONSHIP

• The number of entity types that participate in a
  relationship
• Unary Relationship degree one
• Binary Relationship degree two
• Ternary Relationship degree three

11
UNARY RELATIONSHIP

• RECURSIVE RELATIONSHIP
• A relationship between the instances of one
  entity type
• One-to-One ? -?
  Person is married
• One-to-Many ? gt-?
  Manger supervises many employees

12
BINARY RELATIONSHIP

• A relationship between instances of two entity
  classes.
• This is the most common type of relationship
  encountered in data modeling.
• one-to-one
• one-to-many
• many-to many

13
BINARY RELATIONSHIPS

• One-to-one 1-1
• employee - is assigned- parking space
• One-to-many 1-ltM
• product line -contains-lt product
• Many-to-many Mgt-ltM
• studentgt-register for-ltcourse

14
TERNARY RELATIONSHIP

• A simultaneous relationship among instance of
  three entity types
• PART
• V
• Vendorgt---------ships--------ltWarehouse
• I
• Quantity

15
GERUND

• A many-to-many relationship that the data modeler
  chooses to model as an entity type with several
  associated one-to-many relationships with other
  entity types.
• Vendor Part
• Shipment No-----Shipment----Quantity
• Warehouse

16
CARDINALITY

• The number of instances of entity B that can or
  must be associated with each instance of A.
• Movie ----is stocked as ----ltmovie copy

17
CARDINALITIES

• II Mandatory 1 cardinality
• patient has patient history
• Ilt Many (M) cardinality (1,2,.many)
• employees is assigned to project
• OI Optional 0 or 1 cardinality
• person is married
• Olt Optional zero-many cardinality
  (0,1,2,many)

18
EXISTENCE DEPENDENCY

• An instance of one entity can not exist without
  the existence of an instance of some other
  (related) entity.
• Movie Copy cannot exist with a Movie

19
WEAK ENTITY

• An entity type that has an existence dependency
• Movie Copy is a weak entity

20
IDENTIFYING RELATIONSHIP

• A relationship in which the primary key of the
  parent entity is used as part of the primary key
  of the dependent entity.
• Movie Number and Movie
  Copy Number

21
REPEATING GROUP

• A set of two or more multivalued attributes that
  are logically related.
• Patient entity with 3 multivalued attributes
• Date of Visit
• Physician
• Symptom

22
TIME DEPENDENT DATA

• Time Stamp
• A value (such as date and time) that is
  associated with any data value.
• Price --- Effective Date

23
GENERALIZATION

• The Concept that some things (entities) are
  subtypes of other, more general things.
• Passenger
• First Class Passenger
• Business Class Passenger
• Economy Class Passenger

24
CATEGORIZATION

• The concept that an entity comes in various
  subtypes.
• Ice cream
• vanila
• chocolate
• strawberry

25
SUPERTYPE

• A generic entity type that is subdivided into
  subtypes
• Employee
• Hourly
• Salaried
• Commissioned
• Consultant

26
SUBTYPE

• A subset of a supertype that shares common
  attributes or relationships distinct from other
  subsets.
• Employee
• Hourly
• Salaried
• Commissioned

27
ISA RELATIONSHIP

• The relationship between each sub-type and its
  supertype.
• Hourly Employee ISA Employee
• ?

28
EXCLUSIVE RELATIONSHIP

• The subtypes of a supertype are mutually
  exclusive, and each instance of the supertype is
  categorized as exactly one subtype.
• Employee must be
• hourly, salary, commissioned, or consultant

29
INHERITANCE

• The property that, when entity types or object
  classes are arranged in a hierarchy, each entity
  type or object class assumes the attributes and
  methods of its ancestors (that is, those higher
  up in the hierarchy).
• Name, Adress, Date Hired, are inherited for
  EMPLOYEE

30
EXHAUSTIVE SUBTYPE

• All subtypes are defined for a supertype.
• Each Patient must be
• Outpatient
• Resident Patient

31
NONEXHAUSTIVE SUBTYPE

• Some (but not all) of the subtypes have been
  defined for a supertype.
• Vehicle
• Exclusive
• Automobile
• Truck
• Motorcycle

32
NONEXCLUSIVE SUBTYPE

• Subtypes may overlap, and an instance of the
  supertype may simultaneously belong to more than
  one subtype.
• Vehicle
• Automobile
• Truck
• 4-Wheel-Drive, Bicycle

33
BUSINESS RULES

• Specifications that preserve the integrity of the
  logical data model.
• There are four basic types of business rules
• Entity Integrity
• Referential Integrity Constraints
• Domains
• Triggering Operations

34
ENTITY INTEGRITY

• Each instance of an entity type must have a
  unique identifier ( or primary key value) that is
  not null.

35
REFERENTIAL INTEGRITY CONSTRAINTS

• Rules concerning the relationships between entity
  types.

36
DOMAIN

• The set of all data types and ranges of values
  that an attribute may assume.
• Data type, length, format, range allowable
  values, meaning, uniqueness, and null support.

37
TRIGER

• Triggering Operation
• An assertion or rule that governs the validity of
  data manipulation operations such as insert,
  update, and delete.

38
TRIGGERING COMPONENTS

• User Rule
• Event
• Entity Name
• Condition
• Action

39
TRIGGERING COMPONENTS

• User Rule
• A concise statement of the business rule to be
  enforced by the triggering operation.

40
TRIGGERING COMPONENTS

• Event
• The data manipulation operation (insert, delete,
  or update) that initiates the operation.

41
TRIGGERING COMPONENTS

• Entity Name
• The name of the entity being accessed and/or
  modified

42
TRIGGERING COMPONENTS

• Condition
• Condition that causes the operation to be
  triggered.

43
TRIGGERING COMPONENTS

• Action
• Action taken when the operation is triggered.

44
REVIEW QUESTIONSCHAPTER 1

• Modern Database Management
• McFadden Hoffer
• Page 31 - 1
• Page 32 - 6
• Page 32 - 10

45
REVIEW QUESTIONSCHAPTER 2

• Modern Database Management
• McFadden Hoffer
• Page 70 - 1
• Page 70 - 4
• Page 70 - 5

46
REVIEW QUESTIONSCHAPTER 3

• Modern Database Management
• McFadden Hoffer
• Page 115 - 1
• Page 115 - 12
• Page 115 - 15

47
REVIEW QUESTIONSCHAPTER 4

• Modern Database Management
• McFadden Hoffer
• Page 163 - 1
• Page 163 - 2
• Page 163 - 6
• Page 163 - 8