COLLEGE OF INFORMATION AND COMPUTING SCIENCES

ICS2607 Information Management

MODULE 2

Relational Database Model

 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

At the end of this topic, the learners will be able to:

● demonstrate understanding in different concepts
and principle in relational database (relations,
keys, relationships);
● compare and contrast the different database
keys;
● translate database cases into relational algebra;
and
● compare and contrast the different database join
operations
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Topic Outline
I. Logical View of Data (Table / Relations)
II. Keys
III. Relational Algebra
IV. Data Dictionary
V. Relationships
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

I: Logical View of Data

▪ A database stores and manages both data and
metadata.
▪ The relational data model allows the designer to
focus on the logical representation of the data and its
relationships, rather than on the physical storage
details.
▪ In short, the relational model enables you to view
data logically rather than physically.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Table Characteristics
▪ Table is a two dimensional structure composed of
rows & column. It is also called as relation by Edgar
Codd.
▪ Characteristics of a table (relation)
-A table is perceived as a two-dimensional
structure composed of rows and columns.
-The entries in the table are single valued.
-Each table row (tuple) represents a single entity
occurrence within the entity set.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Table Characteristics
• Each table column represents an attribute, and
each column has a distinct name.
• Each row/column intersection represents a single
data value.
• All values in a column must conform to the same
data format.
• Each column has a specific range of values known
as the attribute domain.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Table Characteristics
• The order of the rows and columns is immaterial to
the DBMS.
• Each table must have an attribute or a combination
of attributes that uniquely identifies each row.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Table Characteristics
▪ Unnormalized Relation
-Entries that contain repeating groups and thus not
single valued.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Table Characteristics
▪ Relational database terminology is very
precise. Unfortunately, file system
terminology sometimes creeps into the
database environment.
- Thus, rows are sometimes referred to as
records and columns are sometimes labeled as
fields.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Table Characteristics

Image: W3Resource
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Table Characteristics
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

II: Keys
▪ In the relational model, keys are important
because they are used to ensure that each row
in a table is uniquely identifiable.
▪ A key consists of one or more attributes that
determine other attributes.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

II: Keys
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Dependencies
▪ Determination is the state in which knowing the
value of one attribute makes it possible to
determine the value of another.
STU_NUM→ STU_NAME
STU_NUM → Determinant
STU_NAME → Dependent
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Dependencies
▪ Full Functional Dependence
-Functional dependencies in which the entire
collection of attributes in the determinant is
necessary for the relationship.

(STU_NUM, STU_LNAME) → STU_GPA

 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Types of Keys

Image: Learn Computer Science

 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Types of Keys
▪ Composite Key
-A key that is composed of more than one
attribute. An attribute that is a part of a key is
called a key attribute.
STU_NUM → STU_GPA
(STU_LNAME, STU_FNAME, STU_INIT)
→ STU_HRS
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Types of Keys
▪ Candidate Key / Alternate Key
▪ The attributes or combination of attributes that
are not used as primary key are known as
candidate key or alternate key.
▪ Characteristics:
- It must contain unique values
- It must not contain null values
- It contains the minimum number of fields to
ensure uniqueness
- It must uniquely identify each record
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Types of Keys
▪ Super Key
-A key that can uniquely identify any row in the
table.
-Remember: Not all keys are super keys
- STU_UNITS → STU_LEVEL
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Types of Keys
▪ Primary Key
-A primary key is a candidate key that is most
appropriate to be the main reference key for
the table.
-Rule of Thumb: Unique & Not Null
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Types of Keys
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Types of Keys
▪ Foreign Key
-A foreign key is an attribute or combination of
attribute in a relation whose value match a
primary key in another relation.
-The table in which foreign key is created
is called as dependent table.
-The table to which foreign key is refers is known
as parent table.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Types of Keys
Employee

Department
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Types of Keys
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

III: Relational Algebra

Relational algebra defines the theoretical way of
manipulating table contents using the eight relational
operators:

− DIFFERENCE
− SELECT
− PRODUCT
− PROJECT
− JOIN
− INTERSECT
− DIVIDE
− UNION
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

SELECT
▪ SELECT, also known as RESTRICT, yields values for
all rows found in a table that satisfy a given
condition.
▪ Denoted by the lowercase Greek letter sigma (σ)
σcus_code = 10010
(customer)
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

SELECT
COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Employee Table
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

PROJECT
▪ PROJECT yields all values for selected attributes. In
other words, PROJECT yields a vertical subset of a
table.
▪ Denoted by the Greek letter pi (π)
π cus_fname, cus_lname (customer)
π cus_fname, cus_lname (σcus_code = 10010
(customer))
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

PROJECT
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
S
T
U
D
E
16
N
T
S

1. Display the roll_no, name and age of the student(s), provided the age is
20
2. Display the records of all students whose address is either ‘Delhi’ or
‘Gurgaon’
3. Display the name and phone whose age is 18
4. Display the name and address of the employee whose age is between 16
to 18.
5. Display only roll_no, and name of students whose roll_no is below 3.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

UNION
▪ UNION combines all rows from two tables,
excluding duplicate rows. The tables must have the
same attribute characteristics (the columns and
domains must be compatible) to be used in the
UNION.
▪ What’s Union Compatible?
− When two or more tables share the same number of
columns, and when their corresponding columns share
the same domains.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
UNION
▪ Denoted by the symbol ∪
supplier ∪ vendor
π supplier_name (supplier) ∪ π vendor_name (vendor)
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Display only the student name of both French and

German table
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

INTERSECT
▪ INTERSECT yields only the rows that appear in both
tables. As was true in the case of UNION, the tables
must be union-compatible to yield valid results.
▪ Denoted by the symbol ∩
supplier ∩ vendor
π supplier_name (supplier) ∩ πvendor_name
(vendor)
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

INTERSECT
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

DIFFERENCE
▪ DIFFERENCE yields all rows in one table that are
not found in the other table; that is, it subtracts
one table from the other.
▪ As was true in the case of UNION, the tables must
be union-compatible to yield valid results.
▪ Denoted by the minus symbol –
supplier - vendor
π supplier_name (supplier) – π
vendor_name
(vendor)
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

DIFFERENCE
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
PRODUCT
▪ PRODUCT yields all possible pairs of rows from two
tables— also known as the Cartesian Product.
▪ Therefore, if one table has six rows and the other
table has three rows, the PRODUCT yields a list
composed of 6 × 3 = 18 rows.
▪ Denoted by the multiplication symbol ×.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

PRODUCT
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

DIVIDE
▪ The DIVIDE operation uses one single-column table
(e.g., column “a”) as the divisor and one 2-column
table (i.e., columns “a” and “b”) as the dividend.
▪ Denoted by the division symbol ÷
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

DIVIDE
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

JOIN
▪ JOIN allows information to be intelligently
combined from two or more tables.
▪ JOIN is the real power behind the relational
database, allowing the use of independent
tables linked by common attributes.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

JOINS
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

JOINS
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

JOIN
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Natural Join
▪ A natural join links tables by selecting only the
rows with common values in their common
attribute(s).
▪ Natural join is normally just referred to as JOIN in
formal treatments.
▪ Denoted by the symbol ⨝
customer ⨝ agent
▪ A natural join is the result of a three-stage process.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Natural Join

▪ Stage 1: First, a
PRODUCT of the
tables is created,
yielding the results
shown.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Natural Join
▪ Stage 2: Second, a SELECT is performed on the
output of Step 1 to yield only the
rows for which the AGENT_CODE values are equal.
The common columns are referred to as the join
columns.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Natural Join
▪ Stage 3: A PROJECT is performed on the results of
Step 2 to yield a single copy of each
attribute, thereby eliminating duplicate columns.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Equi-Join
▪ Another form of join, known as an equijoin, links
tables on the basis of an equality condition that
compares specified columns of each table.
▪ The outcome of the equijoin does not eliminate
duplicate columns, and the condition or criterion
used to join the tables must be explicitly defined.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Outer Joins
▪ Outer join is also an extension of JOIN. Outer joins are the
application of JOIN, DIFFERENCE,
UNION, and PRODUCT.
▪ Denoted by the symbols ⟕ (left) and ⟖ (right)
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Left Outer Join

▪ A left outer join yields all of the rows in the left
(CUSTOMER) table, including those that
do not have a matching value in the right (AGENT)
table.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES
Right Outer Join
▪ A right outer join yields all of the rows in the right
(CUSTOMER) table, including those that
do not have a matching value in the left (AGENT)
table.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

IV: Data Dictionary

▪ The data dictionary provides a detailed description
of all tables found within the user/designer-created
database.
▪ The data dictionary contains at least all of the
attribute names and characteristics for each table
in the system.
▪ The data dictionary contains metadata—data
about data.
▪ “The database designer’s database”
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Data Dictionary
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Homonyms and Synonyms

▪ In a database context, the word homonym
indicates the use of the same attribute name to
label different attributes.
▪ For example, you might use C_NAME to label a
customer name attribute in a CUSTOMER table and
also use C_NAME to label a consultant name
attribute in a CONSULTANT table.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Homonyms and Synonyms

▪ In a database context, a synonym is the opposite of
a homonym and indicates the use of different
names to describe the same attribute.
▪ For example, car and auto refer to the same object.
Synonyms must be avoided.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

V: Relationships
▪ Relationship is the association between tables.
− 1:1 (one is to one) -- rare
− 1:M (one is to many) – ideal
− M:N (many is to many) – cannot be implemented
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

1:M Relationship
▪ The 1:M relationship is the norm for relational
databases.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

1:M Relationship
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

1:M Relationship
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

1:M Relationship
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

1:1 Relationship
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

1:1 Relationship
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

M:N Relationship
▪ A many-to-many (M:N) relationship is not supported directly in the
relational environment.
▪ However, M:N relationships can be implemented by creating a new
entity in 1:M relationships with the original entities.
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

M:N Relationship
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

M:N Relationship
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

M:N Relationship
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Relationships
 COLLEGE OF INFORMATION AND COMPUTING SCIENCES

Reference

Coronel, C. et al. (2017). Database Systems: Design ,

Implementation, and Management (11th ed). USA:
Cengage Learning.