CIS 5163 APPLIED DATABASE MANAGEMENT Section 1 Lecture 3 Database Principles within The Enterprise F

1
CIS 5163 APPLIED DATABASE MANAGEMENT Section 1
Lecture 3Database Principles withinThe
Enterprise FrameworkEntity Relationship
Modeling BPR

• Dr. Raja K. Iyer
• iyer_at_tarleton.edu
• [phone]

2
Outline of Topics

• Overview of Database Modeling BPR
• Entity-Relationship Model
• Constructs
• Relationships
• Constraints
• Entity-Relationship Diagram
• Multi-way Relationships
• Weak Entities
• Hierarchies
• Design Principles

3
Database Modeling

• Analysis of what information the database must
  hold
• Relationships among components of that
  information
• Database schema is specified in one of several
  languages or notations suitable for expressing
  designs
• Next, design is committed to a form which can be
  input to a DBMS ? DB takes on physical existence
• Design notation in this course is
  Entity-Relationship (E/R) model

Object-Oriented DBMS
ODL, UML, etc.
Ideas
Relational DBMS
Relations
E/R
4
Entity-Relationship Model

• Entity Real-world object, concept, or event
• Entity type/set Collection of entities of the
  same type sets dont have to be distinct
• Attribute Values describing some property of an
  entity
• simple vs. composite
• single-valued vs. multi-valued
• stored vs. derived
• Relationship Association between two or more
  entity sets
• Relationship type/set Collection of relationships

5
Examples - Movie Database

• Entity
• Movie, Star
• Attributes
• Name Harrison Ford (simple, single-valued,
  stored)
• Address 123 Main Str., LA, CA (complex)
• Birthdate 1-1-50 (simple)
• Age 50 (could be derived from Birthdate and
  current date)
• Entity Types
• Stars (Name,Address,Birthdate,Age)
• Movies(Title,Year,Length,FilmType)

6
Relationships

• Define associations among entities
• Grouped together into relationships sets (types)
• Example
• Relationship type Stars-in between Movies and
  Stars
• Can be visualized as a table

Relationship Type Stars-in
Movies
Stars
A. Schwarzenegger
Total Recall
Relationships
Sharon Stone
Total Recall
Sharon Stone
Basic Instinct
7
Relationships Contd

• Degree of a relationship type is the number of
  participating entity types
• binary, ternary, n-ary
• Another example
• Entities Stars, Movies, Studios
• Ternary Relationship Set Contracts
• Each entity type that participates in a
  relationship type plays a role in the
  relationship
• Marked by (optional) name
• Entity type may play multiple roles in a
  relationship type (see example later)

8
Relationships Contd

• Relationships can have attributes
• Example Relationship type Contracts between
  Stars and Studios for Movies
• Might want to record salary associated with each
  contract
• Not necessary to place attributes on
  relationships instead, can invent new entity
  type whose entities have the attributes ascribed
  to the relationship

9
Constraints on Relationships

• Limit the possible combinations of entities that
  participate in the relationship type
• Derived from real-world scenario which is being
  modeled
• Cardinality ratio and participation constraints
• Cardinality ratio specifies the number of
  relationships an entity can participate in
• 11, 1N, N1, NM
• Example
• Stars-in between Stars and Movies is NM from
  Stars to Movies

10
Relationship Constraints Contd

• Existence Dependency
• Existence of some entity x from entity type X
  depends on existence of some entity y from entity
  type Y
• x is said to be existence dependent on y
• if y is deleted, so is x
• Example
• Entity type Loans and Payments with relationship
  Loan-payment (1N from Loans to Payments)
• Payment entities are existence dependent on loan
  entities

11
Relationship Constraints Contd

• Participation constraint
• Participation of an entity type E in relationship
  type R total or partial
• Total if every entity in E participates in at
  least one relationship in R
• Partial if only some entities in E participate in
  R
• Closely related to existence dependency
• In previous example, participation of Payment
  entities in the relationship type Loan-payments
  is total

12
ER Diagrams - Notation
...
Entity Set
Composite Attribute
Role Name
Relationship Set
Total participation of E2 in R
R
E1
E2
Attribute
1N Relationship between E1 and E2 in R
Multivalued
N
1
R
E1
E2
Derived
13
Sample Movie Database
name
address
title
year
Stars
Stars-in
M
N
Movies
name
N
length
filmType
Owns
1
Studios
address
street
city
zip
14
Relationship with Roles
Original
1
Sequel-of
Movies
N
Sequel
one original movie may have many sequels
15
Keys

• Attributes or sets of attributes that uniquely
  identify an entity within its entity set
• In E/R diagram notation, underline the attributes
  belonging to a key for an entity type
• In case there are multiple candidate keys,
  designate one key, primary key
• Example

title
year
Movies
length
filmType
16
Sample Movie Database with Keys
name
address
title
year
N
Stars-in
M
Stars
Movies
name
length
N
filmType
1
Owns
Studios
address
street
city
zip
17
Multi-way Relationships
Movies
Stars
N
Contracts
N
1 means that if we select one entity from each
of the other entity sets in the relationship,
those entities are related to a unique entity in
Studios
Studios
1
Contracts Relationship
Movies
Stars
Studios
Schwarzenegger
Total Recall
MGM
Sharon Stone
Total Recall
MGM
Sharon Stone
Basic Instinct
Universal
H. Ford
Star Wars
Fox
18
Higher-Order Relationships
quantity
Supply
N
N
Suppliers
Projects
N
pName
sName
Parts
pNum
19
Equivalent to Three Binary Relationships?
pName
sName
Supply
M
N
Suppliers
Projects
M
M
Can Supply
Uses
N
N
Parts
pNum
20
Converting Multi-way Relationships to Binary
quantity
Supply
N
N
Suppliers
Projects
N
Parts
pName
sName
pNum

• Convert to a collection of binary, many-one
  relationships
• Introduce new entity set whose entities we may
  think of as tuples of the relationship set
• for the multi-way relationship

21
Converting Multi-way Relationships to Binary
Suppliers
Parts
1
1
Supply- Suppliers
Supply- Parts
N
N
Supply
N
Supply- Project
1
Projects
22
Weak Entity Sets

• Def. Entity types key is composed of attributes
  some or all of which belong to another entity
  type
• weak entity type vs. strong entity type
• Example

name
always total participation
strong entity
Unit-of
N
1
Studios
Crews
weak entity
address
number
Many-to-1 relationship connecting weak entity to
strong entity
street
city
zip
23
Example
salary
contractNo
Contracts
N
N
N
1
1
1
filmType
Movies
Studios
Stars
length
address
name
title
year
name
address
24
Subclass/Superclass

• Often, class contains certain objects that have
  special properties not associated with all
  members
• Useful to organize class into subclasses
• Each having its own special attributes and/or
  relationships
• In addition to those of the superclass
• Example
• Superclass Movies
• Subclasses Cartoons, Mystery, Science-Fiction
• Members in subclasses must also be members of the
  corresponding superclass (unlike ODL)
• Opposite not true

25
Inheritance

• Entity in subclass represents same real-world
  entity as in the superclass
• Should possess values for its specific attributes
• As well as values for the attributes it inherits
  from its superclass
• Entity inherits all attributes defined on
  superclass and the relationships in which
  superclass participates

26
Specialization / Generalization

• Processes of defining a set of subclasses/supercla
  sses
• Functionally considered inverses of each other
• Special E/R notation to indicate
  specialization/generalization
• No notational distinction between specialization
  and generalization
• Subclass/superclass relationship resembles 11
  relationship at the entity level
• Entity can participate in more then one entity set

27
Example 1
title
year
length
filmType
Movies
M
Indicates direction of superclass/subclass relati
onship
Voices
ISA
N
weapon
overlap
contents may overlap
Cartoons
Murder Mysteries

• How do we represent a cartoon-murder-mystery?

28
Example 2
balance
account-number
Accounts
ISA
overdraft-amount
interest-rate
disjoint
contents are disjoint
Savings Accounts
Checking Accounts
29
Constraints

• Predicate-defined subclasses
• Placing a condition on the value of some
  attribute of the superclass (defining predicate)
• Attribute-defined specialization if all
  subclasses in a specialization have membership
  condition on same attribute
• User-defined
• Membership in subclass determined by user
• Disjointness constraint
• disjoint if not disjoint, overlap
• Completeness constraint total or partial
• Total every entity in the superclass must be a
  member of some subclass in specialization

30
Example 3
ss
name
address
jobType
Employees
attribute-defined
jobType
ISA
disjoint
ISA
ISA
disjoint
Secretary
Engineer
Technician
Secretaries
Technicians
Engineers
Managers
Hourly Emps
Salaried Emps
typingSpeed
tGrade
engType
Manages
payScale
salary
31
More on Weak Entity Sets

• Two sources for weak entity sets
• Hierarchies (subunits without identity)
• Connecting entity sets (used to eliminate
  multi-way relationships)
• If E is a weak entity type then each of the
  entity sets F that supplies one or more keys to E
  must be related to E by a relationship type R
• The following must also hold
• R must be binary, N1 relationship from E to F
• Attributes that F supplies for the key of E must
  be key attributes
• If there are several N1 relationships from E to
  F, each may be used to supply a copy of the key
  attributes of F

32
Supply as Weak Entity
quantity
pName
sName
N
1
1
N
Suppliers
Projects
N
1
pNum
Parts
33
Not Covered

• Union
• Multiple superclasses, single subclass
• Aggregation
• Abstraction concept for building composite
  objects from their component objects

34
Design Principles

• Faithfulness
• Avoid redundancy
• Relationship Owns between Movies and Studios
  adding attribute studioName to Movies would be
  redundant
• Simplicity counts
• Picking the right kind of element
• Many of the choices are between using attributes
  and using entities
• E.g., Should we have eliminated Studios and made
  name and address attributes of Movies instead?
• If something has more info than just a name, it
  probably needs to be an entity type