DATABASE SYSTEMS
Relational Database Modeling
Slide 1
INTRODUCTION
▪ Relational database model - logical database model that
represents a database as a collection of related tables
▪ Relational schema - visual depiction of the relational
database model
▪ Most contemporary commercial DBMS software packages,
are relational DBMS (RDBMS) software packages
Slide 2
INTRODUCTION
▪ Relation - table in a relational database
• A table containing rows and columns
• The main construct in the relational database model
Slide 3
INTRODUCTION
▪ Relation vs table
• Every relation is a table, not every table is a relation
• In order for a table to be a relation the following conditions must hold:
1. Within one table, each column must have a unique name.
2. Within one table, each row must be unique.
3. All values in each column must be from the same (predefined)
domain.
4. Within each row, each value in each column must be single valued
(one value from a predefined domain, within each row in each column).
• Two additional properties of each table:
5. Order of columns is irrelevant.
6. Order of rows is irrelevant.
Slide 4
INTRODUCTION
Example of relational and non-relational tables
Slide 5
INTRODUCTION
Example of a relation with rows and columns appearing in a
different order
Slide 6
INTRODUCTION
▪ Relational database - collection of related relations within
which each relation has a unique name
▪ Primary key - column (or a set of columns) whose value is
unique for each row
• Each relation must have a primary key
• The name of the primary key column is underlined in order to
distinguish it from the other columns in the relation
Slide 7
INTRODUCTION
Relation with the primary key underlined
Slide 8
MAPPING ER DIAGRAMS INTO RELATIONAL
SCHEMAS
▪ Once an ER diagram is constructed, it is subsequently mapped
into a relational schema (collection of relations)
ERD → RS
Slide 9
MAPPING ENTITIES
▪ Mapping entities into relations
• Each regular entity becomes a relation
• Each regular attribute of a regular entity becomes a column of the
newly created relation
• If an entity has a single unique attribute, then that attribute becomes
the primary key in the resulting mapped relation
Slide 10
MAPPING ENTITIES
Entity mapped
into a relation
Sample data
records for the
mapped relation
Slide 11
MAPPING ENTITIES WITH COMPOSITE
ATTRIBUTES
▪ Mapping entities with composite attributes into
relations
• Each component of a composite attribute is mapped as a column of a
relation
• The composite attribute itself does not appear in the mapped relation
Slide 12
MAPPING ENTITIES WITH COMPOSITE
ATTRIBUTES
Entity with a
composite
attribute
mapped into a
relation
Sample data
records for the
mapped relation
Slide 13
MAPPING ENTITIES WITH COMPOSITE
ATTRIBUTES
The mapped
relation as
presented to a
user in a front-
end application
Slide 14
MAPPING ENTITIES WITH UNIQUE COMPOSITE
ATTRIBUTES
▪ Mapping entities with unique composite attributes into
relations
• An entity whose only unique attribute is a composite attribute is
mapped as a relation with a composite primary key
▪ Composite primary key - a primary key that is composed
of multiple columns
• Column names of a composite primary key are underlined, because
combined together they form the primary key
Slide 15
MAPPING ENTITIES WITH UNIQUE COMPOSITE
ATTRIBUTES
Entity with a
unique
composite
attribute
mapped into a
relation
Sample data
records for the
mapped relation
Slide 16
MAPPING ENTITIES WITH OPTIONAL
ATTRIBUTES
▪ Mapping entities with optional attributes into relations
• Optional attribute of an entity is mapped as an optional column
Slide 17
MAPPING ENTITIES WITH OPTIONAL
ATTRIBUTES
Entity with an
optional attribute
mapped into a
relation
Sample data null
records for the
mapped relation
Slide 18
ENTITY INTEGRITY CONSTRAINT
▪ Entity integrity constraint - in a relational table, no primary
key column can have null (empty) values
• A rule stating that no primary key column can be optional
• Every RDBMS enforces this rule
Slide 19
ENTITY INTEGRITY CONSTRAINT
Entity integrity constraint — compliance and violation example
Slide 20
ENTITY INTEGRITY CONSTRAINT
Entity integrity constraint — another compliance and violation
example
Slide 21
MAPPING RELATIONSHIPS
▪ During the process of mapping ER diagrams into relational
schemas, in addition to mapping entities, relationships also
have to be mapped.
▪ Foreign key - column in a relation that refers to a primary key
column in another (referred) relation
• A mechanism that is used to depict relationships in the relational
database model
• For every occurrence of a foreign key, the relational schema contains a
line pointing from the foreign key to the corresponding primary key
Slide 22
MAPPING RELATIONSHIPS
▪ Mapping 1:M relationships
• The relation mapped from the entity on the M side of the 1:M
relationship has a foreign key that corresponds to the primary key of
the relation mapped from the 1 side of the 1:M relationship.
Slide 23
MAPPING RELATIONSHIPS
Example -
Mapping a
1:M
relationship
Sample data
records for the
mapped ER
diagram
Slide 24
MAPPING RELATIONSHIPS
Example -
Mapping a
1:M
relationship
Mandatory
participation
on both sides
Sample data
records for the
mapped ER
diagram
Slide 25
MAPPING RELATIONSHIPS
Example -
Mapping a
1:M
relationship
Optional
participation
on the 1 side
Sample data
records for the
mapped ER
diagram
Slide 26
MAPPING RELATIONSHIPS
Example -
Mapping a
1:M
relationship
Optional
participation
on the M side
Sample data
records for the
mapped ER
diagram
Slide 27
MAPPING RELATIONSHIPS
Example -
Mapping a
1:M
relationship
Renaming a
foreign key
Sample data
records for the
mapped ER
diagram
Slide 28
MAPPING RELATIONSHIPS
▪ Mapping M:N relationships
• In addition to the two relations representing the two entities involved in
the M:N relationship, another relation is created to represent the
M:N relationship itself
• This new relation has two foreign keys, corresponding to the primary
keys of the two relations representing the two entities involved in the
M:N relationship
• The two foreign keys form the composite primary key of the new
relation
Slide 29
MAPPING RELATIONSHIPS
Example -
Mapping an
M:N
relationship
bridge relation
Sample data
records for
the mapped
ER diagram
Slide 30
MAPPING RELATIONSHIPS
Example -
Mapping an
M:N
relationship
Optional
participation
on both sides
Sample data
records for
the mapped
ER diagram
Slide 31
MAPPING RELATIONSHIPS
Example -
Mapping a
M:N
relationship
with an
attribute
Sample data
records for
the mapped
ER diagram
Slide 32
MAPPING RELATIONSHIPS
▪ Mapping 1:1 relationships
• 1:1 relationships are mapped in the same way as 1:M relationships
• One of the resulting relations will have a foreign key pointing to the
primary key of another resulting relation
• One of the mapped relations is chosen to have a foreign key referring
to the primary key of the other mapped relation
o In cases when there is no particular advantage in choosing which resulting
relation will include a foreign key, the choice can be arbitrary
o In other cases one choice can be more efficient than the other
Slide 33
MAPPING RELATIONSHIPS
Example -
Mapping a 1:1
relationship
Sample data
records for the
mapped ER
diagram
Slide 34
REFERENTIAL INTEGRITY CONSTRAINT
▪ Referential integrity constraint - In each row of a relation
containing a foreign key, the value of the foreign key EITHER
matches one of the values in the primary key column of the
referred relation OR the value of the foreign key is null
(empty).
• A rule that defines values that are valid for use in foreign keys
• In a relational schema lines pointing from the foreign key to the
corresponding primary key are referred to as referential integrity
constraint lines
Slide 35
REFERENTIAL INTEGRITY CONSTRAINT
Referential integrity
constraint —
compliance example
Slide 36
REFERENTIAL INTEGRITY CONSTRAINT
Referential integrity
constraint —
compliance example
Slide 37
REFERENTIAL INTEGRITY CONSTRAINT
Referential integrity
constraint — INVALID
violation example
Slide 38
Example ER diagram : ZAGI Retail Company Sales Department Database
Slide 39
Example mapped relational schema: ZAGI Retail Company Sales Department
Database
Slide 40
Example: Sample data records for the ZAGI Retail Company Sales Department
Database
Slide 41
GRANULARITY OF THE TABLE
▪ Granularity of the table
• Describes what is depicted by one row in the table
Slide 42
MAPPING CANDIDATE KEYS
▪ Mapping entities with candidate keys (multiple unique
attributes) into relations
• One of the candidate keys is chosen by the database designer as the
primary key during the mapping process
• Other candidate keys are mapped as non-primary key columns
Slide 43
MAPPING CANDIDATE KEYS
Entity with
candidate keys
mapped
into a relation
Sample data
records for the
mapped relation
Slide 44
MAPPING CANDIDATE KEYS
Entity with
regular and
composite
candidate keys
mapped into a
relation
Sample data
records for the
mapped relation
Slide 45
MAPPING MULTIVALUED ATTRIBUTES
▪ Mapping entities with multivalued attributes into
relational database constructs
• An entity containing the multivalued attribute is mapped without the
multi-valued attribute
• The multi-valued attribute is mapped as a separate relation that has a
column representing the multivalued attribute and a foreign key
column referring to the primary key of the relation resulting from the
entity itself
o Both of these columns form a composite primary key for the separate
relation
Slide 46
MAPPING MULTIVALUED ATTRIBUTES
Entity with a
multivalued
attribute mapped
into relations
Sample data
records for the
mapped relations
Slide 47
MAPPING DERIVED ATTRIBUTES
▪ Mapping derived attributes
• Derived attributes are not mapped as a part of the relational schema
• They are implemented as a part of the database front-end application
Slide 48
MAPPING DERIVED ATTRIBUTES
Entity with
derived attributes
mapped into a
relation
Sample data The relation
records for shown as
the presented to
mapped a user in a
relation front-end
application
Slide 49
Example : Entity with various types of attributes mapped into a relation
Slide 50
Example : Sample data records for the mapped relations
Slide 51
MAPPING UNARY RELATIONSHIPS
▪ Mapping unary relationships
• Unary relationships in ER diagrams are mapped in the same way as
binary relationships
Slide 52
MAPPING UNARY RELATIONSHIPS
▪ Mapping 1:M unary relationships
• The relation mapped from an entity involved in a 1:M unary relationship
contains a foreign key that corresponds to its own primary key
Slide 53
MAPPING UNARY RELATIONSHIPS
Mapping a 1:M
unary relationship
Sample data
records for the
mapped relation
Slide 54
MAPPING UNARY RELATIONSHIPS
▪ Mapping M:N unary relationships
• In addition to the relation representing the entity involved in a unary M:N
relationship, another relation is created to represent the M:N
relationship itself
• This new relation has two foreign keys, both of them corresponding
to the primary key of the relation representing the entity involved in the
unary M:N relationship
• Each of the foreign keys is used as a part of the composite
primary key of the new relation
Slide 55
MAPPING UNARY RELATIONSHIPS
Mapping a M:N
unary relationship
Sample data
records for the
mapped relations
Slide 56
MAPPING UNARY RELATIONSHIPS
▪ Mapping 1:1 unary relationships
• Mapped in the same way as 1:M unary relationships
Slide 57
MAPPING UNARY RELATIONSHIPS
Mapping a 1:1
unary relationship
Sample data
records for the
mapped relation
Slide 58
MAPPING MULTIPLE RELATIONSHIPS BETWEEN
THE SAME ENTITIES
▪ Mapping multiple relationships between the same
entities
• Each relationship is mapped
Slide 59
MAPPING MULTIPLE RELATIONSHIPS BETWEEN
THE SAME ENTITIES
Mapping multiple
relationships
between the
same entities
Sample data
records for the
mapped relations
Slide 60
MAPPING WEAK ENTITIES
▪ Mapping weak entities
• Weak entities are mapped in a same way as regular entities with one
addition:
o The resulting relation has a composite primary key that is composed of
the partial identifier and the foreign key corresponding to the primary
key of the owner entity
Slide 61
MAPPING WEAK ENTITIES
Mapping a
weak entity
Sample data
records for the
mapped relations
Slide 62
MAPPING WEAK ENTITIES
Mapping a
weak entity
with two
owners
Sample
data
records for
the mapped
relations
Slide 63
MAPPING WEAK ENTITIES
Mapping a weak
entity with no
partial identifier
Sample data
records for the
mapped relations
Slide 64
Example ER diagram : HAFH Realty Company Property Management Database
Slide 65
Example mapped relational schema: HAFH Realty Company Property
Management Database
Slide 66
Example: Sample data records for the HAFH Realty Company Property
Management Database
Slide 67
ER AND RELATIONAL MODELING
▪ Process of requirements collection should be accompanied by
the ER modeling and then followed by mapping the ER model
into a subsequent relational schema
▪ Some practitioners prefer to create relational schemas
straight from the requirements
• In such cases, the ER modeling phase is simply omitted
▪ Creating relational schemas straight from the requirements is
not advisable for the following reasons:
• ER modeling is more suited for visualization of the requirements
• Certain concepts can be visualized graphically only in ER diagrams
• Every attribute is mentioned only once in the ER diagram
• An ER model is a better communication and documentation device
Slide 68
DATABASE SYSTEMS
Next: SQL
Slide 69