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
Connectivity and Cardinality in an ERD
4
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

5
Drawing Optionality
NOTE the dashing of the line is NOT because of
the optionality indicates WEAKNESS
6
NEW for Week 6
7
1M Relationship, Mandatory Both Ways
NOTE error in top diagram what??
8
An MN Case
NOTE error in top diagram what??
9
The Chen Representation of the Invoicing Problem
10
The Crows Foot Representation of the Invoicing
Problem
11
The Attributes of the STUDENT Entity
12
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

13
Depiction of a Derived Attribute
14
Attributes
15
Weak Entity Types in ERDs
16
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

17
A Weak Entity in an ERD
18
A Weak (Non-Identifying) Relationship
19
Multivalued Attributes in ERMs and ERDs
20
A Multivalued Attribute in an EntityCAR_COLOR
gives multiple colours
21
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, split
  the attribute into several attributes (see next
  slide)
• Can lead to major structural problems in the
  table

22
Splitting the Multivalued Attribute into New
Namable Component Attributes
23
Multivalued Attributes, contd

• 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

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

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

27
Generalization Hierarchies in ERMs and ERDs
28
Entity Supertypes and Subtypes

• Generalization hierarchy
• Depicts relationships each of which is between a
  higher-level supertype entity (e.g. EMPLOYEE)
  and a lower-level subtype entity (e.g.,
  PROFESSOR)
• Supertype
• Contains attributes shared by all its subtypes
• Subtype
• Contains special attributes ones that some other
  sister subtype doesnt have

29
Why Consider Supertypes and Subtypes?

• We would not want to store the special attributes
  for all the sister subtypes in the table for
  their supertype, because of the resulting poor
  table structure.

30
Disjoint Subtypes

• Also known as non-overlapping subtypes
• Each entity instance of the supertype can appear
  in only one of the subtypes

31
Disjoint Subtypes
32
A Generalization Hierarchy with Overlapping
Subtypes
33
Supertypes/Subtypes in ERDs
A supertype maintains a 11 relationship with
each subtype optional in the super-to-sub
direction and mandatory in the other
34
The EMPLOYEE/PILOT Supertype/Subtype Relationship
35
Non-Binary and Recursive Relationships in ERMs
and ERDs
36
Relationship Degree

• CAUTION The standard terminology definitions
  relating to relationship degree in the book are
  mathematically misleading. Definitions below are
  mine. J.A.B.
• The degree of a relationship is the number of
  entity occurrences that are associated by each
  instance of the relationship
• (Unary relationship ignore thisJ.A.B.
  consider recursive relationships below instead)
• Binary relationship
• Two entity occurrences are associated, as in all
  previous examples
• Ternary relationship
• Three entity occurrences are associated

37
Different Degrees
38
Tables for a Ternary Relationship
CFR is just like a bridging entity type for a
binary MN relationship, but has 3 foreign keys
instead of 2