is6145 database analysis and design lecture 3: conceptual data modelling 2: er modelling and beyond...
Post on 05-Jan-2016
216 Views
Preview:
TRANSCRIPT
IS6145 Database Analysis and DesignLecture 3: Conceptual Data Modelling 2: ER Modelling and Beyond the Presentation Layer
Rob Gleasure
R.Gleasure@ucc.iewww.robgleasure.com
IS6145
Today’s session Applying the ER Modelling Grammar to the Conceptual Modelling
Process The Presentation Layer ER Model The Coarse-Granular Design-Specific ER Model The Fine-Granular Design-Specific ER Model An exercise
Applying the ER Modelling Grammar to the Conceptual Modelling Process An ER model can be used for two main reasons
To act as a tool for communication between designers and end-users
To guide the design of the eventual database
These roles are met by different types of ERDs and constraint specifications The communication role is met by the Presentation Layer The database design role is met by the Design-Specific ER
model
The Presentation Layer Model is Technology-independent Human-focused in terms of communication Simple, where possible Heuristic-based rather than scientific Iterative/recursive
The Presentation Layer ER Model
Applying the ER Modelling Grammar to the Conceptual Modelling Process
ER modelling grammar
Presentation Layer ER
model/schema
Design-Specific ER
Model/schema
Coarse-granular ER
Model/schema
Fine-granular ER
Model/schema
Last week we spoke about several aspects of ER Modelling at the presentation layer
Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell
The Presentation Layer ER Model
The Presentation Layer ER Model Also cardinality - note that there are several forms of notation, the
one we have been using is Chen’s ‘look across’ numbered notation
Exactly 1
0 or 1
1 or more
0 or many
1
1
n
n
The Presentation Layer ER Model Chen’s notation uses the diamond for relationships and can be a
very intuitive way to create ERDs
BuildingGround-floor
entrance
Is entered through
1 n
The Presentation Layer ER Model We can also describe co-dependencies between relationships using
three types of arcs Exclusive (one can only exist when the other does not)
Rental car
Customer Valet service
Reserves
n 1
n n
Reserves
The Presentation Layer ER Model
Inclusive (one can only exist when the other does also)
Rental car
Staff member
Valet service
1 1
1 n
ReservesBooks valet
The Presentation Layer ER Model
Noninclusive (one or both must exist)
Rental car
Staff member Customer
1 n
1 n
Providesmap
Providesmap
The Synthesis Approach to Generate an Initial Set of Entity Types and Attributes List all discernable data elements and treat them as attributes
Group these attributes based on apparent commonalities
Designate each cluster of attributes as an entity type
Review leftover data elements and investigate the possibility that some of them serve as links among the entity types previously identified
Designate these links as relationship types
The Analysis Approach to Generate an Initial Set of Entity Types and Attributes Begin by searching for things that can be labelled by singular
nouns and call these things entity types
Gather properties that appear to belong to individual entity types and label them as attributes of a particular entity type
Be sensitive throughout the process to the identification of relationships among the various entity types
Rounding Off Initial Modelling When this is done
Gather business rules that can’t be captured in the ERD into a list of Semantic Integrity Constraints, a supplement to the ERD
Note assumptions for further clarification
Example narrative: Mary’s Playschool A parent registers their child or children at the school with a special
registration form, which records the parent, the child, a contact phone number, and any special needs, allergies, etc. the child has. A parent submits one registration form for each child but they may have more than one child at the school. Each room in the school is assigned an age group. Children under 4 years of age go in different rooms from children over 4. A child is assigned to a room based on their age and the capacity of the room. This capacity depends on the room’s size and the experience of the teacher (which may be low, intermediate, or high) but total room capacity capped at 30. A room may be assigned one or more teachers but a teacher must only be assigned to one room. The school also employs assistants to help the teachers (they tend to have favourites they pair up with repeatedly), who must also be assigned to exactly one room. At all times, each room must have an assistant and/or a teacher associated with it. Some part-time teachers also act as assistants on free days.
Example narrative: Mary’s Playschool Draw a Presentation Layer ERD using Chen’s notation
List any semantic integrity constraints at An attribute level An entity level A business level
The Coarse-Granular Design-Specific ER Model Once a design is negotiated with the user community, more details
will have to be added to actually implement the database
These include Collection of more characteristics for attributes (i.e., data type,
size, and range) Use of the technically more precise (min, max) notation for the
specification of relationships Mapping deletion rules to the ER diagram
The (min, max) notation
The min-max notation swaps the standard ‘look across’ notation
For a ‘look here’ notation that
BuildingGround-floor
entrance
Is entered through
(0, n) (1, 1)
BuildingGround-floor
entrance
Is entered through
1 n
Data types/ranges for attributes:updated integrity constraints Many attribute-level semantic integrity constraints are resolved at
this layer by tabularising them according to entity name, attribute name, data type, size, and domain constraint
E.g. at the Presentation Level for Mary’s Playschool, we may have had the following attribute-level semantic integrity constraint Teachers’ experience ranges from low to intermediate to high
This could now become
Entity Attribute Data type Size Domain constraint
Teacher Experience Numeric 1 1=low, 2=intermediate, 3=high
Deletion constraints
Restrict Rule (R): If a parent entity in a relationship is deleted and if all child entities related to this parent in this relationship should not be deleted, then the deletion of the parent should be disallowed
Cascade Rule (C): If a parent entity in a relationship is deleted and if all child entities related to this parent in this relationship should also be deleted, the cascade rule applies
Note: Conventionally, when a deletion constraint is not specified, the restrict rule is implied by default
Deletion constraints
Set Null Rule (N): If a parent entity in a relationship is deleted and if all child entities related to this parent in this relationship should be retained but no longer referenced to this parent, the ‘set null’ rule applies
Set Default Rule (D): If a parent entity in a relationship is deleted and if all child entities related to this parent in this relationship should be retained but no longer referenced to this parent but should be referenced to a predefined default parent, the ‘set default’ rule applies
Deletion constraints
Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell
Image from Data Modeling and Database Design, By Narayan Umanath, Richard Scamell
Deletion constraints
Exercise: Draw a Coarse-Granular Design-Specific ER Model for Mary’s Playschool Draw a Coarse-Granular Design-Specific
Create an updated set of semantic integrity constraints with tabularised attribute-level constraints for entity name, attribute name, data type, size, and domain constraint
top related