Lecture 19-21

From ERD to Relational Designs (translation)

Outline
Build and Validate Logical Data Model
Derive relations for logical data model(From Entity sets to relations, From ER
Relationships to Relations)
Check integrity constraints
Introduction
In the previous chapter we introduced a methodology that describes the steps that
make up the three phases of database design and then presented the first step i.e.
methodology for conceptual database design.
In the second step is to translates the conceptual model produced in Step 1 into a
logical data model.
Step 2: Build and Validate Logical Data Model
To translate the conceptual data model into a logical data model and then to
validate this model to check that it is structurally correct and able to support the
required transactions. This objective is achieved by following these activities:
Step 2.1 Derive relations for logical data model
Step 2.2 Validate relations using normalization
Step 2.3 Validate relations against user transactions
Step 2.4 Check integrity constraints
Step 2.5 Review logical data model with user
Step 2.6 Merge logical data models into global data model (optional step)
Step 2.7 Check for future growth
Conceptual data model for Staff view showing all attributes
Derive relations for logical data model
To create relations for logical data model to represent entities, relationships, and
attributes that have been identified
First specify the name of the relation followed by a list of the relation’s simple
attributes enclosed in brackets. We then identify the primary key and any alternate
and/or foreign key(s) of the relation.
The relationship that an entity has with another entity is represented by the primary
key/ foreign key mechanism.
In deciding where to post (or place) the foreign key attribute(s),we must first identify
the ‘parent’ and ‘child’ entities involved in the relationship. The parent entity refers
to the entity that posts a copy of its primary key into the relation that represents the
child entity, to act as the foreign key.
We describe how relations are derived for the following structures that may occur in a
conceptual data model:
(1) strong entity types;
(2) weak entity types;
(3) one-to-many (1:*) binary relationship types;
(4) one-to-one (1:1) binary relationship types;
(5) one-to-one (1:1) recursive relationship types;
(6) superclass/subclass relationship types;
(7) many-to-many (*:*) binary relationship types;
(8) complex relationship types;
(9) multi-valued attributes.
Derive relations for logical data model
(1) Strong entity types
For each strong entity in data model,
Create relation that includes all simple attributes of entity
For composite attributes, include only constituent simple attributes
Example:
Staff (staffNo, fName, lName, position, sex, DOB)
Primary Key staffNo

(2) Weak entity types

For each weak entity in data model,
Create relation that includes all simple attributes of entity
Primary key of weak entity is partially or fully derived from each owner entity
Example:
Preference (prefType, maxRent)
Primary Key None (at present)
Derive relations for logical data model
(3) One-to-many (1:*) binary relationship types
For each 1:* binary relationship,
Entity on ‘one side’ of relationship designated as parent entity
Entity on ‘many side’ designated as child entity

Represent relationship by posting copy of primary key attribute(s) of parent

entity into relation representing child entity (Acts as foreign key)
If relationship has attributes
Post attributes to child entity along with copy of parent entity primary key

Example: The Staff Registers Client relationship

(4) One-to-one (1:1) binary relationship types
Cardinality cannot be used to identify parent and child entities
Participation constraints used
Options:
(a) mandatory participation on both sides of 1:1 relationship
(b) mandatory participation on one side of 1:1 relationship
(c) optional participation on both sides of 1:1 relationship
(a) Mandatory participation on both sides of 1:1 relationship
Combine entities involved into 1 relation
Choose one primary key of original entities to be primary key of new relation
Other primary key (if one exists) used as alternate key
If relationship has attributes → include in merged relation

(b) Mandatory participation on one side of a 1:1 relationship

Identify parent and child entities using participation constraints
Entity with optional participation in relationship designated as parent
Entity with mandatory participation designated as child
Place copy of primary key of parent in relation representing child
If relationship has one or more attributes → post to child relation following primary key
Preference example
(c) Optional participation on both sides of a 1:1 relationship
Designation of parent and child entities arbitrary unless more about relationship
known
If one entity closer to being mandatory → choose as child
‘Staff Uses Car’ Example
Conceptual data model for Staff view showing all attributes
(5) One-to-one (1:1) recursive relationships
Follow rules for participation for 1:1 relationship
Mandatory participation on both sides
Represent recursive relationship as single relation with two copies of primary key
One copy => primary key; other copy => foreign key, should be renamed
Mandatory participation on only one side
Options:
1. Create single relation with two copies of primary key
2. Create new relation to represent relationship
New relation would only have two attributes => both copies of primary key
Copy of primary keys act as foreign keys, should be renamed to indicate purpose
Optional participation on both sides
Create new relation as described above
(6) Superclass/subclass relationship types
Identify superclass entity as parent entity and subclass entity as child entity
Options on representation of relationship
Selection of most appropriate option dependent on factors:
1. Disjointness and participation constraints on superclass/subclass relationship
2. Whether subclasses involved in distinct relationships
3. Number of participants in superclass/subclass relationship
Guidelines for representation of superclass /
subclass relationship
Representation of superclass / subclass relationship based on participation and
disjointness
(7) Many-to-many (*:*) binary relationship types
Create relation to represent relationship and include any attributes that are part
of relationship
Post copy of primary key attribute(s) of entities that participate in relationship
into new relation - act as foreign keys
Foreign keys also form primary key of new relation
Possibly in combination with other attributes of relationship
See ‘Client Views PropertyForRent’ relationship
(8) Complex relationship types
Create relation to represent relationship and include any attributes
Post copy of primary key attribute(s) of entities that participate in complex
relationship into new relation - act as foreign keys
(9) Multi-valued attributes
Create new relation to represent multi-valued attribute
Include primary key of entity in new relation - acts as foreign key
If multi-valued attribute itself not an alternate key of entity
Primary key of relation is combination of multi-valued attribute and primary key of entity
Branch & Tel. No Example
Summary of how to map entities and relationships to relations
Relations for the Staff user views of DreamHome
Check integrity constraints
Integrity constraints are the constraints that we wish to impose in order to protect
the database from becoming incomplete, inaccurate, or inconsistent.
Identify:
Required data
Attribute domain constraints
Multiplicity
Entity integrity
Referential integrity
General constraints
Check integrity constraints
Required data
Not null attributes contain valid value
For example, every member of staff must have an associated job position (such
as Supervisor or Assistant).
Attribute domain constraints
Every attribute has domain
Multiplicity
Constraints on relationships valid
Examples of such constraints include the requirements that a branch has many
staff and a member of staff works at a single branch
Check integrity constraints
Entity integrity
Primary key not null
Referential integrity
Foreign key must reference existing value in parent relation
Foreign key null if participation optional
Existence constraints
General constraints
For example, DreamHome has a rule that prevents a member of
staff from managing more than 100 properties at the same time.
Existence constraints example
Insert tuple into child relation (PropertyForRent)
tuple is set to null or to a value of an existing Staff tuple
Delete tuple from child relation (PropertyForRent)
referential integrity is unaffected
Update foreign key of child tuple (PropertyForRent)
tuple is set to null or to a value of an existing Staff tuple.
Insert tuple into parent relation (Staff)
does not affect referential integrity
Update primary key of parent tuple (Staff)
If the primary key value of a parent relation tuple is updated, referential integrity is lost
Delete tuple from parent relation (Staff)
referential integrity is lost if there exists a child tuple referencing the deleted parent tuple
There are several strategies we can consider:

NO ACTION Prevent a deletion from the parent relation if there are any referenced child tuples.
CASCADE When the parent tuple is deleted automatically delete any referenced child
tuples.
SET NULL When a parent tuple is deleted, the foreign key values in all corresponding
child tuples are automatically set to null
SET DEFAULT When a parent tuple is deleted, the foreign key values in all corresponding child tuples
should automatically be set to their default values
NO CHECK When a parent tuple is deleted, do nothing to ensure that referential
integrity is maintained
Referential integrity constraints for relations in Staff user
views of DreamHome
Relations for the Branch user views of DreamHome
Suggested Reading
Chapter 17 of Database Systems: A Practical Approach to Design, Implementation,
and Management by Thomas Conolly 6th Edition
Chapter 7.6 of Database System Concepts by Abraham Silberschatz
References
https://www.db-book.com/db6/slide-dir/index.html
https://
www.pearson.com/us/higher-education/product/Connolly-Powerpoint-Slides-for-Dat
abase-Systems