Module - 5: Relational Database Modeling
Business Data Warehousing (MIS 6309)
Module - 5: Relational Database Modeling
Learning Objectives:
You will learn in detail on how to map an ER Diagram into a Relational Schema
Module - 5: Relational Database Modeling
Introduction:
• Relational database model - logical database model that represents a database as a collection of related tables
• Relational schema – a visual depiction of the relational database model
• Most contemporary commercial DBMS software packages are relational DBMS (RDBMS) software packages
• Terminology:
Module - 5: Relational Database Modeling
Relation:
• Relation - table in a relational database
A table containing rows and columns
The main construct in the relational database model
Every relation is a table, not every table is a relation
• For a table to be a relation the following conditions must hold:
Within one table, each column must have a unique name.
Within one table, each row must be unique.
All values in each column must be from the same (predefined) domain.
Within each row, each value in each column must be single-valued (one value from a predefined domain, within
each row in each column).
Module - 5: Relational Database Modeling
Relation: (Cont.)
• Example of relational and non-relational tables
Module - 5: Relational Database Modeling
Relation: (Cont.)
• Two additional properties of each table:
The order of columns is irrelevant
The order of rows is irrelevant
• Example of a relation with rows and columns appearing in a different order:
Module - 5: Relational Database Modeling
Relational Database and Primary Key:
• Relational database – a 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
Relation with the primary key underlined
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema:
• Once an ER diagram is constructed, it is subsequently mapped into a relational schema (collection of relations)
• 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
a) Entity mapped into a relation b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• 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
b) Sample data records for the mapped relation
a) Entity with a composite attribute mapped into a relation c) Mapped relation presented to a user in a front-end application
Module - 5: Relational Database Modeling
Composite Primary Key, Mapping an ER Diagram into a Relational Schema:
• Composite primary key - a primary key that is composed of multiple columns
Column names of a composite primary key are underlined because together they form the primary key
• 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
a) Entity with a unique composite attribute mapped into a relation b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema:
• Mapping entities with optional attributes into relations
An optional attribute of an entity is mapped as an optional column
a) Entity with an optional attribute mapped into a relation b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
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
Entity integrity constraint — compliance and violation example
Module - 5: Relational Database Modeling
Entity Integrity Constraint: (Cont.)
• Entity integrity constraint — another compliance and violation example
Module - 5: Relational Database Modeling
Foreign Key, Mapping an ER Diagram into a Relational Schema:
• 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
• 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.
a) Example - Mapping a 1:M relationship b) Sample data records for the mapped ER Diagram
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping 1:M relationships:
a) Mapping a 1:M relationship (Mandatory participation on both sides)
b) Sample data records for the
mapped ER Diagram
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping 1:M relationships:
a) Mapping a 1:M relationship (Optional participation on the 1 sides)
b) Sample data records for the
mapped ER Diagram
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping 1:M relationships:
a) Mapping a 1:M relationship (Optional participation on the M sides)
b) Sample data records for the
mapped ER Diagram
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping 1:M relationships:
a) Mapping a 1:M relationship (Renaming a foreign key)
b) Sample data records for the
mapped ER Diagram
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• 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.
a) Example - Mapping an M:N relationship b) Sample data records for the mapped ER Diagram
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping M:N relationships:
a) Example - Mapping an M:N relationship (Optional participation on b) Sample data records for the mapped ER Diagram
both sides)
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping M:N relationships:
a) Example - Mapping a M:N relationship with an attribute b) Sample data records for the mapped ER Diagram
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• 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
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping 1:1 relationships
a) Example - Mapping a 1:1 relationship b) Sample data records for the mapped ER Diagram
Module - 5: Relational Database Modeling
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
Referential integrity constraint — compliance example:
Module - 5: Relational Database Modeling
Referential Integrity Constraint:
• Referential integrity constraint —
compliance example
• Referential integrity constraint —
violation example
Module - 5: Relational Database Modeling
Example ER diagram : ZAGI Retail Company Sales Department Database
Module - 5: Relational Database Modeling
Example mapped relational schema: ZAGI Retail Company Sales Department Database
Module - 5: Relational Database Modeling
Example: Sample data records for the ZAGI Retail Company Sales Department Database
Module - 5: Relational Database Modeling
Granularity of the Table:
• The granularity of the table describes what is depicted by one row in the table
Mapping an ER Diagram into a Relational Schema: (Cont.)
• 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
a) Entity with candidate keys mapped into a relation b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping Candidate Keys:
a) Entity with regular and composite
candidate keys mapped into a relation
b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• 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
Both of these columns form a composite primary key for the separate relation
a) Entity with a multivalued attribute mapped into relations b) Sample data records for the mapped relations
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• 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
a) Entity with derived attributes b) Sample data records for the c) The relation shown as presented
mapped into a relation mapped relation to a user in a front-end application
Module - 5: Relational Database Modeling
Example : Entity with various types of attributes mapped into a relation
Module - 5: Relational Database Modeling
Example : Sample data records for the mapped relations
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping Unary Relationships
Unary relationships in ER diagrams are mapped in the same way as binary 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
a) Mapping a 1:M unary relationship b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• 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
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping M:N unary relationships:
a) Mapping an M:N unary relationship b) Sample data records for the mapped relations
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping 1:1 unary relationships
Mapped in the same way as 1:M unary relationships
a) Mapping a 1:1 unary relationship b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
• Mapping multiple relationships between the same entities
Each relationship is mapped
a) Mapping multiple relationships between the same entities b) Sample data records for the mapped relations
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
a) Mapping a weak entity with two owners
b) Sample data records for the mapped relations
Module - 5: Relational Database Modeling
Mapping an ER Diagram into a Relational Schema: (Cont.)
a) Mapping a weak entity with no partial identifier b) Sample data records for the mapped relations
Module - 5: Relational Database Modeling
Example ER diagram: HAFH Realty Company Property Management Database
Module - 5: Relational Database Modeling
Example mapped relational schema: HAFH Realty Company Property Management Database
Module - 5: Relational Database Modeling
Example: Sample data records for the HAFH Realty Company Property Management Database (Part 1)
Module - 5: Relational Database Modeling
Relational Database Constraints:
• Relational database constraints - rules that a relational database has to satisfy in order to be valid
Implicit constraints
o The implicit relational database model rules that a relational database must satisfy in order to be valid
User-defined constraints
o Database constraints that are added by the database designer
• Implicit constraints
Each relation in a relational schema must have a different name
Each relation must satisfy the following conditions:
o Each column must have a different name
o Each row must be unique
o Domain constraint - all values in each column must be from the same predefined domain
o In each row, each value in each column must be single-valued
o The order of columns is irrelevant
o The order of rows is irrelevant
Module - 5: Relational Database Modeling
Relational Database Constraints: (Cont.)
• Implicit constraints: (Cont.)
Primary key constraint - each relation must have a primary key, which is a column (or a set of columns) whose value
is unique for each row
Entity integrity constraint
Referential integrity constraint
• User-defined constraints: Added by the database designers
a) Specific minimum and maximum cardinalities b) Sample data records for the mapped relations
Module - 5: Relational Database Modeling
Relational Database Constraints: (Cont.)
• Business rules
User-defined constraints that specify restrictions on databases that are not a part of the standard notation for
creating ER diagrams
a) Business rule for salary amounts b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Relational Database Constraints: (Cont.)
a) Business rule for salary amounts b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Relational Database Constraints: (Cont.)
a) Business rule for the dates of enrollment and graduation b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Relational Database Constraints: (Cont.)
a) Business rules for gender of students in an organization b) Sample data records for the mapped relation
Module - 5: Relational Database Modeling
Designer-Created Primary keys and Auto number Option
• Auto number data type option - enables automatic generation of consecutive numeric data values in a column
• Designer-created primary key - primary key column, not called for by the original requirements, added to a table by the
database designer
Often used in conjunction with the auto number data type option
a) Entity and the resulting relation b) Entity and the resulting relation with c) Sample data records for the relation
a designer-created primary key column with a designer-created primary key
Module - 5: Relational Database Modeling
ER and Relational Modeling:
• The 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
Unit - 5: Relational Database Modeling
Learning Outcomes:
You are aware of the details of mapping an ER Diagram into a Relational Schema