Fundamentals of CS 2: Databases WEEK 6

1
Fundamentals of CS 2DatabasesWEEK 6

• John Barnden
• Professor of Artificial Intelligence
• School of Computer Science
• University of Birmingham, UK

2
REVIEW of Week 5
3
Strong and Weak Relationships

• Also called identifying and non-identifying
  relationships resply.
• A relationship from entity type A to entity type
  B, mediated by having As primary key (PK) as an
  attribute set in B, is strong when Bs PK
  contains that attribute set. So, B items are
  defined in terms of A items.
• Usually the attribute set As PK, so Bs PK
  contains As PK.
• E.g., A People, B Dependents, where each
  entity in People is identified by a PERS_ID (As
  PK) and each entity in Dependents is identified
  by a PERS_ID plus a first name and a
  kinship/friendship attribute.
• So a PK value in B could be (1698674, Mary,
  child) , meaning that this entity is the child
  called Mary of person 1698674 in the People
  table.
• A relationship is weak when it isnt strong.

4
Strong and Weak Entity Types

• A strong entity type is one that is not weak! .
• A weak entity type E is one that satisfies the
  following two conditions
• It is existence-dependent on some other entity
  type F that is, there is a relationship R from F
  to E such that an E entity e can only exist in
  the database if some entity in F bears
  relationship R to e.
• The relationship R is strong.
• So on previous slide, Dependents is weak, because
  there is a strong relationship to it from People
  and Dependents is existence-dependent on People
  via this relationship.
• Marys existence in the database as a member of
  Dependents relies on the existence of person
  1698674 in the database. (Doesnt mean Mary would
  vanish from the planet if person 1698674 left the
  database or even if that person were to vanish
  from the world. And indeed Mary could herself be
  an entity in type People even if 1698674 leaves
  the DB or really vanishes.)

5
NEW for Week 6
6
based on Chapters 2, 3, 4 and 6 of
Entity Relationship (ER) Model(s) and Diagrams

• Rob and Coronel
• Database Systems Design, Implementation, and
  Management,
• (Seventh Edition)
• just chapters 2, 3 and 4 in Sixth ed.

7
The Entity Relationship Model

• Introduced by Chen in 1976
• Most widely used conceptual model of DBs.
• The ER model strictly speaking is just the
  approach of thinking of things as composed of
  entities, attributes and relationships it has
  nothing intrinsically to do with diagrams.
• We also say that applying this approach to a
  particular body of data gives rise to an ER model
  of the specific intended database.
• Diagrams based on the model are a widely accepted
  and adopted graphical approach to data modelling.

8
A Conceptual Model

• Represents global view of the database
• Enterprise-wide representation of data as viewed
  by high-level managers
• Basis for identification and description of main
  data objects and relationships, avoiding
  details

9
Entity Relationship Diagrams (ERDs)

• The ER model of a database forms the basis of an
  ER diagram (ERD) or several ERDs.
• The ERDs represent the database as viewed by end
  users.
• There are several markedly different styles of
  ERD, and for each main style there are several
  variants.
• And the style in the module handouts will differ
  somewhat from that in the textbook and these
  lectures
• An ERD depicts (some of) the ER models entities,
  attributes and relationships, and (depending on
  the diagram style) varying amounts of other info
  such as connectivities, cardinalities, keys,
  weakness,

10
Examples in Two Styles of Diagram
11
The Completed Tiny College ERD
12
Relationships The Basic Chen ERD
13
Relationships The Basic Crows Foot ERD
14
Caution J.A.B.

• In previous two diagrams, each relationship was
  mandatory in both directions.
• But saying 11, 1M or MN does not of
  itself imply mandatoriness in either direction.
  In particular, dont be deceived by the 1 here
  its not a minimum.
• We will see in a minute how to draw optional
  (non-mandatory) relationships.

15
A Conceptual Model for Tiny College
16
11 Relationship Between PROFESSOR and
DEPARTMENT(mandatory in both directions)
17
Tables for that 11 Relationship
18
1M Relationship Between PAINTER and PAINTING
(mandatory in both directions)
19
Tables for that 1M Relationship
20
The MN Relationship

• Can be implemented by breaking it up to produce a
  set of 1M relationships
• Can avoid problems inherent to MN relationship
  by creating a composite entity or bridge entity

21
MN Relationship between STUDENT and CLASS(both
ways mandatory)preliminary ERD
22
Changing that MN Relationship to Two 1M
Relationships
23
A Bridge (or Composite) Entity Type

• Its table is called a linking table
• Its primary key is composed of the primary keys
  of each of the entity types to be connected
• Those keys are also foreign keys in the bridge
  type
• Linking table may contain multiple occurrences of
  each foreign key value
• May also contain additional attributes that play
  no role in the bridging

24
The MN Relationship Between STUDENT and CLASS
25
Converting the MN Relationship into Two 1M
Relationships
26
Connectivity and Cardinality in an ERD
27
Relationship Participation

• Optional in a particular direction, X to Y
• an X entity occurrence does not require a
  corresponding Y entity occurrence
• i.e. the minimum number of Ys per X is 0
• Mandatory in a particular direction, X to Y
• an X entity occurrence requires a corresponding Y
  entity occurrence
• i.e. the minimum number of Ys per X is 1 or more

28
Drawing Optionality
NOTE the dashing of the line is NOT because of
the optionality
29
Connectivity and Cardinality in an ERD
30
Relationship Participation

• Optional in a particular direction, X to Y
• an X entity occurrence does not require a
  corresponding Y entity occurrence
• i.e. the minimum number of Ys per X is 0
• Mandatory in a particular direction, X to Y
• an X entity occurrence requires a corresponding Y
  entity occurrence
• i.e. the minimum number of Ys per X is 1 or more

31
Drawing Optionality
NOTE the dashing of the line is NOT because of
the optionality indicates WEAKNESS
32
1M Relationship, Mandatory Both Ways
NOTE error in top diagram what??
33
An MN Case
NOTE error in top diagram what??
34
The Chen Representation of the Invoicing Problem
35
The Crows Foot Representation of the Invoicing
Problem
36
The Attributes of the STUDENT Entity
37
Derived Attributes

• Attribute whose value may be calculated (derived)
  from other attributes
• Need not be physically stored within the database
• Can be derived by using an algorithm

38
Depiction of a Derived Attribute
39
Attributes
40
Weak Entity Types in ERDs
41
Weak Entity Types

• Weak entity type W meets two conditions (as
  clarified by J.A.B.)
• Existence-dependent on some other entity type X
  via some relationship R
• An entity of type W cannot exist as such without
  being in relationship R to an entity of type X
• Has primary key that is partially or totally
  derived from the primary key of X
• Database designer usually determines whether an
  entity can be described as weak based on the
  business rules

42
A Weak Entity in an ERD
43
A Weak (Non-Identifying) Relationship