Databases
Lecture 5 – Conceptual Design – Part III Principles and EER diagrams
Bernhard Reus
1
Conceptual Design – Tips and Traps
In this lecture :
Special relationships (n-ary, recursive) Enhanced E/R models (specialisation) Methodology for Conceptual Design? Validation and finding traps
[C&B: Ch 11.7, 12, 15]
2
© Bernhard Reus, University of Sussex, 2004-14
Non-binary Relationships
3 participating entity types
Staff registers client at branch.
Staff 1 Registers 1 Branch
M
Client
A member of staff registers a client at one branch.
At a branch a member of staff registers many clients.
A client at a branch is registered by one member of staff
Fix two entities to compute cardinality (multiplicity) of third.
3
Non-binary Relationships
3 participating entity types Staff 1 Registers 1 Branch
M
Client
A member of staff registers a client at one branch.
At a branch a member of staff registers many clients.
A client at a branch is registered by one member of staff
Does this mean a client can be registered at only one branch and be registered by only one member of staff?
Staff registers client at branch.
4
© Bernhard Reus, University of Sussex, 2004-14
Non-binary Relationships Staff registers client at branch.
Staff 1 Registers 1 Branch M
Client
Mrs Jones registers Client A at Hove branch. ✗ Mrs Jones registers Client A at Kemptown branch.
Mrs Jones registers Client A at Hove branch.
Mrs Robinson registers Client A at Kemptown branch.
If you don’t want that you must not use ternary rel’ship !!!
5
✓
Non-binary Relationships
Staff registers client at branch.
Staff 1 Registers 1
M
Client
3 participating entity types
Branch
Participation constraints?
A member of staff may register clients (at branches). At a branch clients may be registered by staff.
Each client must be registered by staff.
6
More about this in Exercises 3!
© Bernhard Reus, University of Sussex, 2004-14
Recursive Relationship
• … is a relationship type where the same entity type participates more than once in different roles.
• Role names must be given explicitly to entity types.
• Example: Staff supervises staff. Supervises
Supervisor
Supervisee
Staff
7
Enhanced ER diagrams (EER)
• Add spezialisation/generalisation to entity types (similar to subclassess and superclasses for object oriented modelling).
Subclass entity types inherit attributes from superclass entity type.
The relationship between a superclass and any of its subclasses is called a superclass/subclass relationship.
Notation:
Avoid specifying same attributes twice
Branch 1 Employs 1
Managed_by
M Staff 1
8
© Bernhard Reus, University of Sussex, 2004-14
Constraints for Specialisation/Generalisation
• Participation constraint: Determines whether every member in the supertype must participate as a member of a subtype
• Mandatory: every member of supertype must also be a member of a subtype. Notation: {Mandatory}
• Optional: not mandatory. Notation: {Optional} 9
Constraints for Specialisation/Generalisation
• Disjoint constraint: indicates whether it is possible for a member of a supertype to be a member of one, or more than one, subtype.
• Only applied when more than one subtype;
• Notation: {Or} if subtypes are disjoint;
• Notation: {And} if member of supertype can be member of more than one subtype (overlapping subclasses)
10
© Bernhard Reus, University of Sussex, 2004-14
Constraints for Specialisation/Generalisation
• Please observe:
Super and sub-entity types need to share the same key, otherwise the sub-entity type cannot be also an entity of the super-entity type.
11
Example with Specialisation Constraints
Branch 1 Employs 1
Managed_by
M Staff
{Mandatory, And}
1
Manager Managed
• {Mandatory,And} means here that a staff entity, i.e. a member of staff, must be either a Manager or Managed and that they can be both at the same time.
12
© Bernhard Reus, University of Sussex, 2004-14
Conceptual Design
• To make an E/R model one needs to identify Entity Types
Relationships
Attributes
Cardinality/Participation (Multiplicity) constraints Generalization/Specialization Constraints
from a description.
• How do we get this description?
13
Conceptual Design Guidance
14
© Bernhard Reus, University of Sussex, 2004-14
Conceptual Design…
• starts off with describing in natural language the reality that is to be modeled.
• Needs research (requirements analysis):
• observations
• forms and documents used in the enterprise
• questionnaires
• interviews
[Fact finding: C&B, Ch. 10]
Conceptual Design Stages
1. Identify entity types
2. Identify relationship types and constraints
3. Identify and associate attributes
4. Determine attribute domains
5. Determine key attributes
6. Check model for redundancy (consider 4th dimension: time)
7. Validate model against (sample) transactions
(find and correct so-called traps) 8. Review model with user
Repeat any of this if user requests changes
16
© Bernhard Reus, University of Sussex, 2004-14
Conceptual Design
• These steps ensure that Conceptual Modelling is
– Complete: all relevant features represented
– Correct: conforming to the informal requirements
– Expressive: represent the required concepts naturally and understandably
– Minimal: each aspect of the requirements appear once and only once.
17
Validation
• Check whether the model supports the required transactions as specified, eg.
Staff Works_For Division Operates Branch
• Example:“Foreachbranchweneedtoprintoff reports about the numbers and total salaries of the staff at this branch. For each staff we need to know at which branch they work.”
18
© Bernhard Reus, University of Sussex, 2004-14
Problems with E/R models
• Connection traps appear when misinterpreting the meaning of certain relationships.
• To identify those traps you must understand the meaning of your relationship via validation.
Fan trap Chasm trap
To “cover” a trap you will need to add an extra relationship
19
Fan Trap
• Where a model represents a relationship between entity types, but the pathway between certain entity types is ambiguous.
• Example: M11M
Staff Works_For Division Operates Branch At which branch does a particular member of staff
work?
We may only determine several possible branches.
20
© Bernhard Reus, University of Sussex, 2004-14
Chasm Trap
• Where a model suggests the existence of a relationship between entity types, but the pathway does not exist between certain entity occurrences.
• Example M Staff 1
Branch
Oversee M Property 1s
Which properties are available at a branch?
We do not find those which are not overseen.
Employs
(partial participation)
21
Check redundancy • Re-examine 1:1 relationships
– Do both entity types describe the same kind of entity?
– Is one a composite attribute of the other?
– In either case, merge the entity types thus removing the redundancy.
22
© Bernhard Reus, University of Sussex, 2004-14
Conceptual Models
• Provide a useful non-technical, non-ambiguous
interface between user and designer.
• Allow design to be independent of any
particular DBMS (and kind of data model).
• Allow choice of DBMS be postponed until design requirements are clarified.
• Provide a standard context within which design issues may be discussed and resolved.
• Are based on the principle of data abstraction. 23
Document the Design
• An E/R diagram should be accompanied by a document indicating and justifying for each relationship type:
– Role of the entities in the relationship
– Participation constraints
– Cardinality constraints or alternatively, if applicable, (min,max) multiplicity constraints
• and for each entity type: domains for attributes, information about derived attributes
24
© Bernhard Reus, University of Sussex, 2004-14
Simple Example
Description of the reality “Store”:
Each product has a product number, description, a price and a supplier. For each supplier we store an address, phone number, and name. Each address is made up of a street name, a house number, a city, and a postcode. Each product is provided by a supplier.
25
•
Entity Types
Product Supplier
Address
Design (1-2)
• Relationships
Does a product have only one supplier?
Product
M1
Provided_By Supplier
Do we store suppliers who do not supply one of the products?
Supplier 1 Has_an 1 Address
Is Address an entity type, weak entity type, composite attribute?
26
© Bernhard Reus, University of Sussex, 2004-14
“E/R model considered harmful”(?)
• Suggests that there is just one approach to the problem
• Is not a formal model
• Only few constraints
expressed in E/R diagrams
• Distinction between relationships and entity types?
[C.J. Date]
“Audiatur et altera pars” the opposite side needs to be heard too
Others e.g. Object Role Modeling (ORM) [Halpin]
only successful because it is too simple?
30
© Bernhard Reus, University of Sussex, 2004-14