FundamentalsCS 2 Databases WEEK 4

1
Fundamentals/CS 2DatabasesWEEK 4

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

2
Reminder of Week 3
3
Next until noted are from 07/08 Weeks 5 6
4
MN Connectivity between Tables

• If we represented MN connectivity in a similar
  way to 1M, then we can expect that
• in the People table some people will have each
  several employers listed
• or in the Organizations table an organization
  will have several employees listed
• or both.
• This is a problem. Why?

5
MN Connectivity between Tables

• Because of this problem, an MN relationship is
  usually broken up into two 1M relationships.
• This means introducing an extra bridging or
  linking or composite entity type (hence
  table) to stand between the two original ones.

6
MN -- a person may be employed by more than one
organization and an organization may
employ more than one person

PEOPLE
EMPLOYMENTS each person-id
organzn-id possibly plus other attributes
do not have an EMPL ID attrib.
ORGANIZATIONS
do not have a PERS ID attrib
7
MN Connectivity between Tablesusing a Bridging
Entity Type
People
Employments
Organizations
8
New for Week 4
9
Strong (or Identifying) Relationships

• A relationship from entity type A to entity type
  B, mediated by having As primary key (PK) as a
  subset of Bs attributes, is strong when Bs PK
  contains As PK. (incl. the case of Bs PK just
  being As PK).
• So, B entities are defined in terms of A
  entities.
• E.g., A People, B Dependents, where
• As PK consists of PERS_ID
• Bs PK consists of PERS_ID, FIRST_NAME,
  CONNECTION.
• 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.

10
A Strong Relationship
People (the A type)
Strong relationship going from A to B (we could
say B is strongly dependent on A)
Dependents (the B type)
11
Weak (or Non-Identifying) Relationships

• A relationship is weak when it isnt strong.
• So, most relationships are weak.
• Note that strength/weakness is directional the
  People to Dependents relationship (above) is
  strong, but the Dependents to People relationship
  is weak.
• Can a relationship be weak in both directions?
• Can a relationship be strong in both directions?

12
Weak Entity Types

• 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.

13
Weak Entity Types, contd.

• So on a 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.

14
Strong Entity Types

• A strong entity type is one that is not weak! .
• So, in particular, any entity that receives only
  weak relationships from other entity types is
  strong.
• So the usual case is for an entity type to be
  strong.
• And any entity type that is not
  existence-dependent on anything is strong.

15
Mental Exercises for You

• What about the Employments bridging type we
  introduced?
• Although it might look at first as though a
  strong relationship necessarily implies existence
  dependence, it doesnt. Why not?
• Satisfy yourself that one entity type can be
  existence-dependent on another without therefore
  being weak.

16
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.

17
See Chapters 2, 3, 5 and 6 of

• Rob, Coronel Crockett
• Database Systems Design, Implementation, and
  Management,
• (2008)
• Uses different diagrams from previous book and my
  slides!
• More like the notation in the Additional Notes

18
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.

19
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

20
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 textbooks and these
  lectures
• An ERD depicts (some of) the ER models entity
  types, attributes and relationships, and
  (depending on the diagram style) varying amounts
  of other info such as connectivities,
  cardinalities, keys, weakness,

21
Examples in Two Styles of Diagram
22
The Completed Tiny College ERD
23
Relationships The Basic Chen ERD
24
Relationships The Basic Crows Foot ERD
25
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.

26
A Model for Tiny College
27
11 Relationship Between PROFESSOR and
DEPARTMENT(mandatory in both directions)
28
Tables for that 11 Relationship
29
1M Relationship Between PAINTER and PAINTING
(mandatory in both directions)
30
Tables for that 1M Relationship
31
The MN Relationship

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

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

• Its table is called a linking table (or bridging
  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 as such

35
The MN Relationship Between STUDENT and CLASS
36
Converting the MN Relationship into Two 1M
Relationships
37
Connectivity and Cardinality in an ERD
38
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

39
Drawing Optionality
NOTE the dashing of the line is NOT because of
the optionality
40
1M Relationship, Mandatory Both Ways
NOTE error in top diagram what??
41
An MN Case
NOTE error in top diagram what??
42
The Chen Representation of the Invoicing Problem
43
The Crows Foot Representation of the Invoicing
Problem
44
The Attributes of the STUDENT Entity
45
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

46
Depiction of a Derived Attribute
47
Attributes
48
Weak Entity Types in ERDs
49
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

50
A Weak Entity in an ERD
51
A Weak (Non-Identifying) Relationship
52
Next until end are from 07/08 Week 7
53
Multivalued Attributes in ERMs and ERDs
54
A Multivalued Attribute in an EntityCAR_COLOR
gives multiple colours
55
Resolving Multivalued Attribute Problems

• You should not implement them in the relational
  DBMS rather, you should re-represent them in a
  special way J.A.B.
• One possibility Within original entity type,
  split the attribute into several different
  attributes (see next slide)
• Can have disadvantages, e.g. if the attribute
  needs to be split differently in different cases.

56
Splitting the Multivalued Attribute into New
Namable Component Attributes
57
Multivalued Attributes, contd

• Often Better Replace the attribute by a new 1M
  relationship to a new entity type holding the
  original attributes data.
• If the components of the original attribute are
  conceptually distinguishable in a natural way,
  the new entity can have an attribute whose values
  identify those components.

58
So trim colour is one of the components of the
original multivalued attribute
59
So, Replace the Attribute by a New Entity Set
with an Attribute identifying the Original
Attributes Separate Components
60
Multivalued Attributes, contd

• Exercise What would you do if the components of
  the original multivalued attribute were not
  conceptually distinguishable?
• Imagine blotches of colour all over the body of a
  car.