Module 2

Relational Model and

Relational Algebra
Smita Ganjare
 Introduction to Relational Model
• The relational model stores data in the form of tables. This concept is
introduced by Dr. E. F. Codd, a researcher of IBM.
• The relational database is collectively combination of data structures,
storage and retrieval operations and integrity constraints.
• The relational model is the first choice of commercial data processing
applications for storing data.
• Relational model is most famous of its simplest structure as compared to
other database models like network or hierarchical model.
• The relational data model is the simple model having all the properties and
capabilities required to process the data.
• Most of the Database Management Systems (DBMS) are relational.
Introduction to Relational Model
 The relational model consists of three major components:
1. Data structure: The set of relations and set of domains that defines the way
data can be represented.
2. Data Integrity: Integrity rules that defines the procedure to protect the data.
3. Data Manipulation: The operations that can be performed on data.

 In relational database model, the data is stored in different tables. These

tables are interlinked with each other with the help of common fields
(columns) in between them.
Relational Database System
1. Relational databases are a type of database that store and
organize data points with defined relationships for fast access.
2. With a relational database, data is organized into tables that
hold information about each entity and represent predefined
categories through rows and columns.
3. Relational databases are also built to understand Structured
Query Language (SQL), a standardized programming language
which is used to store, manipulate, and retrieve data.
 Characteristics of Relational Database
1. Table structure: Data is organized into tables with rows representing records and columns representing
attributes, each with unique names and data types.
1. Data consistency: Relational databases enforce data integrity by ensuring that all users see the same consistent
data across the system, adhering to ACID properties (Atomicity, Consistency, Isolation, Durability).
1. Relationships between tables: Relationships between tables are established using primary keys (unique
identifiers in one table) and foreign keys (matching values in another table), allowing for complex data retrieval
through joins.
2. Structured Query Language (SQL):Relational databases are accessed and manipulated using SQL,
a standardized language for querying, inserting, updating, and deleting data.
3. No duplicate rows: Each row in a table is unique, preventing redundant data entry.
4. Attribute domains: Each column has a defined domain of valid values, ensuring data type
consistency.
5. Logical data separation: The logical structure (tables, views, indexes) of a relational database is
distinct from the physical storage structure, allowing for flexibility in data access.

What is relational model?
 The relational model represents how data is stored in Relational Databases. A
relational database consists of a collection of tables each of which is assigned a
unique name.
Relational Model- Key Terms
• Attribute: Attributes are the properties that define an entity. e.g.
ROLL_NO, NAME, ADDRESS.
• Relation Schema: A relation schema defines the structure of the relation
and represents the name of the relation with its attributes. e.g. STUDENT
(ROLL_NO, NAME, ADDRESS, PHONE, and AGE) is the relation
schema for STUDENT. If a schema has more than 1 relation it is called
Relational Schema.
• Tuple: Each row in the relation is known as a tuple. The above relation
contains 4 tuples one of which is shown as:
Relational Model- Key Terms
• Relation Instance: The set of tuples of a relation at a particular
instance of time is called a relation instance. It can change whenever
there is an insertion, deletion or update in the database.
• Degree: The number of attributes in the relation is known as the degree
of the relation. The STUDENT relation defined above has degree 5.
• Cardinality: The number of tuples in a relation is known as
cardinality. The STUDENT relation defined above has cardinality 4.
• Column: The column represents the set of values for a particular
attribute. The column ROLL_NO is extracted from the relation
STUDENT.
• NULL Values: The value which is not known or unavailable is called a
NULL value. It is represented by NULL. e.g. PHONE of STUDENT
having ROLL_NO 4 is NULL.
 Characteristics of the Relational Model
• Data Representation: Data is organized in tables (relations), with rows (tuples)
representing records and columns (attributes) representing data fields.
• Atomic Values: Each attribute in a table contains atomic values, meaning no multi-
valued or nested data is allowed in a single cell.
• Unique Keys: Every table has a primary key to uniquely identify each record, ensuring
no duplicate rows.
• Attribute Domain: Each attribute has a defined domain, specifying the valid data
types and constraints for the values it can hold.
• Tuples as Rows: Rows in a table, called tuples, represent individual records or
instances of real-world entities or relationships.
• Relation Schema: A table’s structure is defined by its schema, which specifies the table
name, attributes, and their domains.

• Data Independence: The model ensures logical and physical data independence, allowing
changes in the database schema without affecting the application layer.
Characteristics of the Relational Model
• Integrity Constraints: The model enforces rules like:
• Domain constraints: Attribute values must match the
specified domain.
• Entity integrity: No primary key can have NULL
values.
• Referential integrity: Foreign keys must match
primary keys in the referenced table or be NULL.
Advantages of Relational Model
 Simple model: Relational Model is simple and easy to
use in comparison to other languages.
 Flexible: Relational Model is more flexible than any other
relational model present.
 Secure: Relational Model is more secure than any other
relational model.
 Data Accuracy: Data is more accurate in the relational
data model.
 Data Integrity: The integrity of the data is maintained in
the relational model.
 Operations can be Applied Easily: It is better to perform
operations in the relational model.
Disadvantages of the Relational Model
• Relational Database Model is not very good for large
databases.
• Sometimes, it becomes difficult to find the relation
between tables.
• Because of the complex structure, the response time for
queries is high.


Outline
ER-to-Relational Mapping Algorithm

Step 1: Mapping of Regular Entity Types

Step 2: Mapping of Weak Entity Types
-

Step 3: Mapping of Binary 1:1 RelationTypes

Step 4: Mapping of Binary 1:N Relationship Types.
Step 5: Mapping of Binary M:N Relationship Types.
-

Step 6: Mapping of Multivalued attributes.

Step 7: Mapping of N-ary Relationship Types.
-
The ER conceptual schema diagram for the COMPANY database.

Fname Minit Lname

Bdate Name Address Salary

Ssn Sex Locations

N 1
WORKS_FOR
Name Number

EMPLOYEE Startdate Number_of_employees DEPARTMENT

1 1
MANAGES
CONTROLS

Hours
N
M N
WORKS_ON PROJECT
Supervisor Supervisee 1
1

SUPERVISION N Name
Location
Number
DEPENDENTS_OF

DEPENDENT

Name Sex Birth date Relationship

Codd’s Rules in DBMS

 Codd’s rules are proposed by a computer scientist named

Dr. Edgar F. Codd and he also invent the relational model
for database management. These rules are made to ensure
data integrity, consistency, and usability. This set of rules
basically signifies the characteristics and requirements of a
relational database management system (RDBMS).
 Codd’s Rules in DBMS
 Rule 0: The Foundation Rule:- The database must be in relational form. So that the system
can handle the database through its relational capabilities.
 Rule 2: Guaranteed Access Rule :- Every single or precise data (atomic value) may be
accessed logically from a relational database using the combination of primary key value,
table name, and column name.
 Rule 3: Systematic Treatment of Null Values :-This rule defines the systematic treatment of
Null values in database records. The null value has various meanings in the database, like
missing the data, no value in a cell, inappropriate information, unknown data and the
primary key should not be null.
 Rule 4: Active/Dynamic Online Catalog based on the relational model :- It represents the
entire logical structure of the descriptive database that must be stored online and is known
as a database dictionary. It authorizes users to access the database and implement a similar
query language to access the database.
 Rule 5: Comprehensive Data Sublanguage Rule :- A crucial component of any efficient
database system is its ability to offer an easily understandable data manipulation language
(DML) that facilitates defining, querying, and modifying information within the database.
Codd’s Rules in DBMS
 Rule 6: View Updating Rule :- All views table can be theoretically updated and must be
practically updated by the database systems.
 Rule 7: Relational Level Operation (High-Level Insert, Update and delete) Rule :- A
database system should follow high-level relational operations such as insert, update, and
delete in each level or a single row. It also supports union, intersection and minus
operation in the database system.
 Rule 8: Physical Data Independence Rule :- All stored data in a database or an
application must be physically independent to access the database. Each data should not
depend on other data or an application. If data is updated or the physical structure of the
database is changed, it will not show any effect on external applications that are accessing
the data from the database.
 Rule 9: Logical Data Independence Rule :-It is similar to physical data independence. It
means, if any changes occurred to the logical level (table structures), it should not affect
the user's view (application). For example, suppose a table either split into two tables, or
two table joins to create a single table, these changes should not be impacted on the user
view application.
Codd’s Rules in DBMS
 Rule 10: Integrity Independence Rule:- A database must maintain integrity
independence when inserting data into table's cells using the SQL query language. All
entered values should not be changed or rely on any external factor or application to
maintain integrity. It is also helpful in making the database-independent for each front-
end application.
 Rule 11: Distribution Independence Rule :- The distribution of data across multiple
locations should be invisible to users, and the database system should handle the
distribution transparently.
 Rule 12: Non Subversion Rule :- The non-submersion rule defines RDBMS as
a SQL language to store and manipulate the data in the database. If a system has a low-
level or separate language other than SQL to access the database system, it should not
subvert or bypass integrity to transform data.
 Introduction to Relational Algebra
 Relational Algebra is a procedural query language.
 The main purpose of using Relational Algebra is to define operators that
transform one or more input relations into an output relation.
 Relational algebra consists of a certain set of rules or operations that are
widely used to manipulate and query data from a relational database.
 It can be facilitated by utilizing SQL language and helps users interact with
database tables based on querying data from the database more efficiently
and effectively.
 This ” algebra ” is the foundation for most database queries, and it enables
us to extract the required information from the databases by using SQL
query language.
Fundamental Operators
Relational algebra consists of various operators that help
us fetch and manipulate data from relational tables in the
database to perform certain operations on relational data.
1. Selection(σ)
2. Projection(π)
3. Union(U)
4. Set Difference(-)
5. Set Intersection(∩)
6. Rename(ρ)
7. Cartesian Product(X)
1. Select Operation:
• The select operation selects tuples that satisfy a given predicate.
• It is denoted by sigma (σ).

 Notation: σ p(r)
 Where:
 σ is used for selection prediction
r is used for relation
p is used as a propositional logic formula which may use connectors like: AND OR
and NOT. These relational can use as relational operators like =, ≠, ≥, <, >, ≤.
 For example: LOAN Relation
BRANCH_NAME LOAN_NO AMOUNT
Downtown L-17 1000
Redwood L-23 2000
Perryride L-15 1500
Downtown L-14 1500
Mianus L-13 500
Roundhill L-11 900
Perryride L-16 1300

Input:
1.σ BRANCH_NAME="perryride" (LOAN)

Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500
Perryride L-16 1300
 2. Project Operation:

• This operation shows the list of those attributes that we wish to

appear in the result. Rest of the attributes are eliminated from the
table.
• It is denoted by ∏.

• Notation: ∏ A1, A2, An (r)

 Where, A1, A2, A3 is used as an attribute name of relation r

Example: CUSTOMER RELATION

NAME STREET CITY

Jones Main Harrison
Smith North Rye
Hays Main Harrison
Curry North Rye
Johnson Alma Brooklyn
Brooks Senator Brooklyn NAME CITY

Jones Harrison
 Input: ∏ NAME, CITY (CUSTOMER)
Smith Rye
 Output:
Hays Harrison
Curry Rye
Johnson Brooklyn
Brooks Brooklyn
 3. Union Operation:
• Suppose there are two tables R and S. The union operation contains
all the tuples that are either in R or S or both in R & S.
• It eliminates the duplicate tuples. It is denoted by ∪.
Example: DEPOSITOR RELATION BORROW RELATION

CUSTOMER_NAME ACCOUNT_NO CUSTOMER_NAME LOAN_NO

Johnson A-101 Jones L-17
Smith A-121 Smith L-23
Mayes A-321 Hayes L-15
Turner A-176 Jackson L-14
Johnson A-273 Curry L-93
Jones A-472 Smith L-11
Lindsay A-284 Williams L-17

INPUT: ∏ CUSTOMER_NAME (BORROW) ∪ ∏ CUSTOMER_NAME (DEPOSITOR)

Output: CUSTOMER_NAME
Johnson
Smith
Hayes
Turner
Jones
Lindsay
Jackson
Curry
Williams
Mayes
4. Set Intersection:
• Suppose there are two tables R and S. The set intersection operation contains all
tuples that are in both R & S.
• It is denoted by intersection ∩.
• Notation: R ∩ S

 Example: Using the above DEPOSITOR table and BORROW table

 Input: ∏ CUSTOMER_NAME (BORROW) ∩ ∏ CUSTOMER_NAME (DEPOSITOR)

 Output:
CUSTOMER_NAME

Smith
Jones
 5. Set Difference:
• Suppose there are two tables R and S. The set intersection operation contains all tuples that are
in R but not in S.
• It is denoted by intersection minus (-).
 Notation: R - S
 Example: Using the above DEPOSITOR table and BORROW table
 Input:
1. ∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)
 Output:

CUSTOMER_NAME
Jackson
Hayes
Willians
Curry
 6. Cartesian product
• The Cartesian product is used to combine each row in one table with each row in the
other table. It is also known as a cross product.
• It is denoted by X.
1. Notation: E X D
 Example: EMPLOYEE DEPARTMENT

EMP_ID EMP_NAME EMP_DEPT EPT_NO DEPT_NAME

1 Smith A A Marketing
2 Harry C B Sales
3 John B C Legal
Input:
[Link] X DEPARTMENT
 Output:
EMP_ID EMP_NAME EMP_DEPT DEPT_NO DEPT_NAME

1 Smith A A Marketing
1 Smith A B Sales
1 Smith A C Legal
2 Harry C A Marketing
2 Harry C B Sales
2 Harry C C Legal
3 John B A Marketing
3 John B B Sales
3 John B C Legal
7. Rename Operation:
 The rename operation is used to rename the output relation. It is
denoted by rho (ρ).
 Example: We can use the rename operator to rename STUDENT
relation to STUDENT1.
1. ρ(STUDENT1, STUDENT)