UNIT – II

Relational Model: Introduction to the Relational Model, Integrity Constraints over Relations,
Enforcing Integrity Constraints, Querying Relational Data, Logical Database Design: ER to
Relational, Introduction to Views, Destroying/Altering Tables and Views

Relational Algebra: Selection and Projection, Set Operations, Renaming, Joins, Division, More
Examples of Algebra Queries, Relational Calculus: Tuple Relational Calculus, Domain
Relational Calculus.

Introduction to the Relational Model

Relational Model was proposed by E.F Codd to model data in the form of relations or
tables. After designing the conceptual model of database using ER diagram, we need to
convert the conceptual model in the relational model which can be implemented using
any RDBMS (Relational Data Base Management System) like SQL, MY SQL etc.

The relational model is very simple and elegant; a database is a collection of one or more
relations, where each relation is a table with rows and columns.

This simple tabular representation enables even new users to understand the contentsof
a database, and it permits the use of simple, high-level languages to query the data.

Relational Model

Relational Model represents how date is stored in relational databases. A Relational database
stores data in the form ofrelations (tables).
Consider a relation STUDENT with attributes ROLL_NO, NAME, ADDRESS,
PHONE and AGE as shown in table.

ROLLNO NAME ADDRESS PHONE AGE

1 Nishma Hyderabad 9455123451 28

2 Sai Guntur 9652431843 27
3 Swetha Nellore 9156253131 26
4 Raji Ongole 9215635311 25

Attribute: Attributes are the properties that define arelation. Ex: ROLL_NO, NAME
Tuple: Each row in a relation is known as tuple.
Ex:
1 Nishma Hyderabad 9455123451 28

Degree: The number of attributes in the relation is known as degree.

Ex: The degree of the given STUDENT table is 5.
Column: Column represent the set of values for a particular attribute. The column ROLL_NO is
extracted from the relation STUDENT.
Ex:

ROLL_N
O
1

Null values: The value which is not known or unavailable is called NULL VALUE.
Itis represented by blank space.
Cardinality: The number of tuples is present in the relation is called as its cardinality.
Ex: The Cardinality of the STUDENT table is 4.

Concept Of Domain

The domain of a database is the set of all allowable values (or) attributes of the database.
Ex: Gender (Male, Female, Others).
Relation
 A relation is defined as a set of tuples and attributes.
 A relation consists of Relation schema and relation instance.
 Relation schema: A relation schema represents the name of the relation with its attributes.
Ex: STUDENT (ROLL_NO, NAME, ADDRESS, PHONE and AGE) is
Relation schema for STUDENT.
 Relation instance: The set of tuples of a relation at a particular instance of a time is called
Relation Instance.
An instance of Employee relation

Emp_code Emp_Name Dept_Name

01234 John HR
12567 Smith Sales
21678 Sai Production
 12456 Jay Design

Importance of Null values:

 SQL supports a special value known as NULL which is used to represent the values
of attributes that may be unknown or not apply to a tuple.
 For example, the apartment number attribute of an address applies only to the address
that is in apartment buildings and not to other types of residences.
 It is important to understand that a NULL value is different from Zero value.
 A Null value is used to represent a missing value, but that is usually has one of the
following interpretations:
 Value unknown (Value exists but it is unknown)
 Value not available (exists but it is purposely withheld)
 Attribute not applicable (undefined for this tuple)
It is often not possible to determine which of the meanings is intended

Entity: An entity is an object in the real world that is distinguishable from other objects.
Entity set: An entity set is a collection of similar entities. The Employees entity
set with attributes ssn, name, and lot is shown in the following figure.

Attribute: An attribute describes a property associated with entities. Attribute

will have a name and a value for each entity.
Domain: A domain defines a set of permitted values for an attribute
Entity Relationship Model: An ERM is a theoretical and conceptual way of
showing data relationships in software development. It is a database modeling
technique that generates an abstract diagram or visual representation of a
system's data that can be helpful in designing a relational database.

ER model allows us to describe the data involved in a real-world enterprise in

terms of objects and their relationships and is widely used to develop an initial
database design.

Representation of Entities and Attributes

ENTITIES: Entities are represented by using rectangular boxes. These are named with the
entity name that they represent.
ATTRIBUTES: Attributes are the properties of entities. Attributes are
represented by means of ellipses. Every ellipse represents one attribute and is
directly connected to its entity.

Types of attributes:

 Simple attribute − Simple attributes are atomic values, which cannot be

divided further. For example, a student's roll number is an atomic value.

 Composite attribute − Composite attributes are made of more than

one simple attribute. For example, a student's complete name may have
first_name and last_name.

 Derived attribute − Derived attributes are the attributes that do not

exist in the physical database, but their values are derived from other attributes
present in the database. For example, average_salary in a department should
not be saved directly in the database, instead it can be derived. For another
example, age can be derived from data_of_birth.
 Single-value attribute − Single-value attributes contain single value.
For example − Social_Security_Number.
 Multi-value attribute − Multi-value attributes may contain more than
one values. For example, a person can have more than one phone number,
email_address, etc.

Relationship and Relationship set

Relationships are represented by diamond-shaped box. Name of the relationship is written

inside the diamond-box. All the entities (rectangles) participating in a relationship, are connected
to it by a line. Types of relationships:

Degree of Relationship is the number of participating entities in a relationship defines the degree
of the relationship. Based on degree the relationships are categorized as

Unary = degree 1
Binary = degree 2
Ternary = degree 3
n-array = degree

Unary Relationship: A relationship with one entity set. It is like a relationship among 2 entities
of same entity set. Example: A professor ( in-charge) reports to another professor (Head Of the
Dept).
Binary Relationship: A relationship among 2 entity sets. Example: A professor teaches a
course and a course is taught by a professor.

Ternary Relationship: A relationship among 3 entity sets. Example: A professor teaches a

course in so and so semester.

n-array Relationship: A relationship among n entity sets.

Cardinality:

Defines the number of entities in one entity set, which can be associated with the number of
entities of other set via relationship set. Cardinality ratios are categorized into 4. They are.

One-to-One relationship: When only one instance of entities is associated with the
relationship, then the relationship is one-to-one relationship. Each entity in A is associated
with at most one entity in B and each entity in B is associated with at most one entity in A.
One-to-many relationship: When more than one instance of an entity is associated with a
relationship, then the relationship is one-to-many relationship. Each entity in A is associated
with zero or more entities in B and each entity in B is associated with at most one entity in A.

Many-to-one relationship: When more than one instance of entity is associated with the
relationship, then the relationship is many-to-one relationship. Each entity in A is associated

with at most one entity in B and each entity in B is associated with 0 (or) more entities in A.

Many-to-Many relationship: If more than one instance of an entity on the left and more
than one instance of an entity on the right can be associated with the relationship, then it depicts
many-to-many relationship. Each entity in A is associated with 0 (or) more entities in B and
each entity in B is associated with 0 (or) more entities in A.
Relationship Set:
A set of relationships of similar type is called a relationship set. Like entities, a relationship too
can have attributes. These attributes are called descriptive attributes.

Integrity Constraints over Relations

In DBMS, constraints refer to limitations placed on data or data processes. This indicates that
only a particular type of data may be entered into the database or that only a particular sort of
operation can be performed on the data inside.

Constraints thereby guarantee data accuracy in a database management system (DBMS).

The following can be guaranteed via constraints

Data Accuracy − Data accuracy is guaranteed by constraints, which make sure that only true
data is entered into a database. For example, a limitation may stop a user from entering a
negative value into a field that only accepts positive numbers.

Data Consistency − The consistency of data in a database can be upheld by using constraints.
These constraints are able to ensure that the primary key value in one table is followed by the
foreign key value in another table.

Data integrity − The accuracy and completeness of the data in a database are ensured by
constraints. For example, a constraint can stop a user from putting a null value into a field that
requires one.
Domain Constraints In DBMS
➢ In DBMS table is viewed as a combination of rows and columns
➢ For example, if you are having a column called month and you want only (jan,
feb, march……) as values allowed to be entered for that particular column which is referred
to as domain for that particular column
Definition: Domain constraint ensures two things it makes sure that the data value entered for that
particular column matches with the data type defined by that column.
It shows that the constraints (NOT NULL/UNIQUE/PRIMARY KEY/FOREIGN
KEY/CHECK/DEFAULT)

Domain constraint= data type check for the column +constraints.

Example:, we want to create a table “STUDENT” with “stu_id” field having avalue
greater than 100, can create a domain and table like this.
▪ Create domain id_value int constraint id_test check (value>=100);
▪ CREATE table STUDENT (stu_id id value primary key, stu_name varchar (30), stu_age
int);

Key constraints in DBMS:

➢ Constraints are nothing but the rules that are to be followed while entering data into
columns of the database table.
➢ Constraints ensure that the data entered by the user into columns must be within the
criteriaspecified by the condition.
➢ We have 6 types of key constraints in DBMS
1. Not Null
2. Unique
3. Default
4. Check
5. Primary key
6. Foreign key

15
1. Not Null:
• Null represents a record where data may be missing data or data for that record may
be optional.
• Once not null is applied to a particular column, you cannot enter null values to that
column.
• A not null constraint cannot be applied at table level.

Example:

Create table EMPLOYEE (id int Not null, name varchar Not null, Age int not null,
address char (25), salary decimal (18,2), primary key(id));

➢ In the above example we have applied not null on three columns id, name and age
which means whenever a record is entered using insert statement all three columns
should contain a value other than null.
➢ We have two other columns address and salary, where not null is not applied
which means that you can leave the row as empty.

2. Unique:

Some times we need to maintain only. Unique data in the column of a database table,
this is possible by using a Unique constraint.

➢ Unique constraint ensures that all values in a column are Unique.

Example:

16
 Create table PERSONS (id int unique, last_name varchar (25) not null, First name
varchar (25), age int);

➢ In the above example, as we have used unique constraint on ID column we are

not supposed to enter the data that is already present, simply no two ID values are
same.

3. Default:

Default in SQL is used to add default data to the columns.

➢ When a column is specified as default with same value then all the rows will use the
same value i.e., each and every time while entering the data we need not enter that
value.
➢ But default column value can be customised i.e., it can be over ridden when inserting
a data for that row based on the requirement.
(Row with default values “abc”)

Example:

Create table EMPLOYEE (id int Not null, last_name varchar (25) Not null, first_name
varchar (25), Age int, city varchar (25) Default Hyderabad);

17
 ➢ As a result, whenever you insert a new row each time you need not enter a value
for this default column that is entering a column value for a default column is optional.

4. Check:
➢ Check constraint ensures that the data entered by the user for that column is within the
range of values or possible values specified.

Example: Create table STUDENT (id int, name varchar (25), age int, check(age>=18));

➢ As we have used a check constraint as (age>=18) which means value entered by user
for this age column while inserting the data must be less than or equal to 18.

5. Primary Key:
➢ A primary key is a constraint in a table which uniquely identifies each row record
in a database table by enabling one or more column in the table as primarykey.

Creating a primary key:

➢ A particular column is made as a primary key column by using the primary key
keyword followed by the column name.
Example:
Create table EMP (ID int, name varchar (20), age int, course varchar (10),
Primarykey (ID));
18
 ➢ Here we have used the primary key on ID column then ID column must contain
unique values i.e., one ID cannot be used for another student.

6. Foreign Key:
➢ The foreign key constraint is a column or list of columns which points to the
primary key column of another table.
➢ The main purpose of the foreign key is only those values are allowed in the present
table that will match to the primary key column of another table.

From the above two tables, COURSE_ID is a primary key of the table
STUDENT_MARKS and also behaves as a foreign key as it is same in
STUDENT_DETAILS and STUDENT_MARKS.

Example:

(Reference Table)

Create table CUSTOMER1 (id int, name varchar (25), course varchar (10), primary key
(ID));

(Child table)

Create table CUSTOMER2 (id int, marks int, references customer1(ID));

Integrity Constraints in DBMS:

There are two types of integrity constraints
1. Entity Integrity Constraints
2.Referential Integrity Constraints

19
Entity Integrity constraints:
➢ These constraints are used to ensure the uniqueness of each record or row inthe data table.
➢ Entity Integrity constraints says that no primary key can take NULL VALUE, since using
primary key we identify each tuple uniquely in arelation.
Example:

Explanation:

➢ In the above relation, EID is made primary key, and the primary key can‟t take NULL values but
in the 3rd tuple, the primary key is NULL, so it is violating Entity integrity constraints.

Referential Integrity constraints:

➢ The referential integrity constraint is specified between two relations or tables and used to
maintain the consistency among the tuples in two relations.
➢ This constraint is enforced through foreign key, when an attribute in the foreign key of relation
R1 have the same domain as primary key of relation R2, then the foreign key of R1 is said
to reference or refer to the primary key of relation R2.
➢ The values of the foreign key in a tuple of relation R1 can either take the values of the primary
key for some tuple in Relation R2, or can take NULL values, butcan‟t be empty.

20
Explanation:

➢ In the above, DNO of the first relation is the foreign key and DNO in the second relation
is the primary key
➢ DNO=22 in the foreign key of the first relation is not available in the second relation so, since
DNO=22 is not defined in the primary key of the second relation therefore Referential
integrity constraints is violated here.

Logical Database Design: ER to Relational

ER Model, when conceptualized into diagrams, gives a good overview of entity-relationship, which is
easier to understand. ER diagrams can be mapped to relational schema, that is, it is possible to create
relational schema using ER diagram. We cannot import all the ER constraints into relational model, but
an approximate schema can be generated.

There are several processes and algorithms available to convert ER Diagrams into Relational Schema.
Some of them are automated and some of them are manual. We may focus here on the mapping diagram
contents to relational basics.

ER diagrams mainly comprise of −

• Entity and its attributes

• Relationship, which is association among entities.

Mapping Entity

An entity is a real-world object with some attributes.

Mapping Process (Algorithm)

• Create table for each entity.
• Entity's attributes should become fields of tables with their respective data types.
• Declare primary key.

Mapping Relationship

A relationship is an association among entities.

21
Mapping Process
• Create table for a relationship.
• Add the primary keys of all participating Entities as fields of table with their respective data
types.
• If relationship has any attribute, add each attribute as field of table.
• Declare a primary key composing all the primary keys of participating entities.
• Declare all foreign key constraints.

Mapping Weak Entity Sets

A weak entity set is one which does not have any primary key associated with it.

Mapping Process
• Create table for weak entity set.
• Add all its attributes to table as field.
• Add the primary key of identifying entity set.
• Declare all foreign key constraints.

Mapping Hierarchical Entities

ER specialization or generalization comes in the form of hierarchical entity sets.

22
Mapping Process
• Create tables for all higher-level entities.
• Create tables for lower-level entities.
• Add primary keys of higher-level entities in the table of lower-level entities.
• In lower-level tables, add all other attributes of lower-level entities.
• Declare primary key of higher-level table and the primary key for lower-level table.
• Declare foreign key constraints.

Views

o Views in SQL are considered as a virtual table. A view also contains rows and columns.
o To create the view, we can select the fields from one or more tables present in the database.
o A view can either have specific rows based on certain condition or all the rows of a table.

Advantages of View:

1. Complexity: Views help to reduce the complexity. Different views can be created on the same
base table for different users.
2. Security: It increases the security by excluding the sensitive information from the view.
3. Query Simplicity: It helps to simplify commands from the user. A view can draw data from
several different tables and present it as a single table.
4. Consistency: A view can present a consistent, unchanged image of the structure of the database.
Views can be used to rename the columns without affecting the base table.
5. Data Integrity: If data is accessed and entered through a view, the DBMS can automatically
check the data to ensure that it meets the specified integrity constraints.
6. Storage Capacity: Views take very little space to store the data.
7. Logical Data Independence: View can make the application and database tables to a certain
extent independent.

23
Disadvantages of View:

The DML statements which can be performed on a view created using single base table have certain
restrictions are:

1. You cannot INSERT if the base table has any not null column that do not appear in view.
2. You cannot INSERT or UPDATE if any of the column referenced in the INSERT or UPDATE
contains group functions or columns defined by expression.
3. You can't execute INSERT, UPDATE, DELETE statements on a view if with read only option is
enabled.
4. You can't be created view on temporary tables.
5. You cannot INSERT, UPDATE, DELETE if the view contains group functions GROUP BY,
DISTINCT or a reference to a psuedocolumn rownum.
6. You can't pass parameters to the SQL server views.
7. You can't associate rules and defaults with views.

Sample table:

Student_Detail

STU_ID NAME ADDRESS

1 Stephan Delhi

2 Kathrin Noida
3 David Ghaziabad
4 Alina Gurugram

Student_Marks

STU_ID NAME MARKS AGE

1 Stephan 97 19

2 Kathrin 86 21
3 David 74 18
4 Alina 90 20
5 John 96 18

1. Creating view

A view can be created using the CREATE VIEW statement. We can create a view from a single table or
multiple tables.

24
Syntax:

CREATE VIEW view_name AS SELECT column1, column2..... FROM table_name

WHERE condition;

2. Creating View from a single table

In this example, we create a View named DetailsView from the table Student_Detail.

Query:

1. CREATE VIEW DetailsView AS SELECT NAME, ADDRESS FROM Student_Details

WHERE STU_ID < 4;

Just like table query, we can query the view to view the data.

SELECT * FROM DetailsView;

Output:

NAME ADDRESS

Stephan Delhi

Kathrin Noida
David Ghaziabad

3. Creating View from multiple tables

View from multiple tables can be created by simply include multiple tables in the SELECT statement.

In the given example, a view is created named MarksView from two tables Student_Detail and
Student_Marks.

Query:

1. CREATE VIEW MarksView AS

2. SELECT Student_Detail.NAME, Student_Detail.ADDRESS, Student_Marks.MARKS
3. FROM Student_Detail, Student_Mark
4. WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView:

SELECT * FROM MarksView;

25
 NAME ADDRESS MARKS

Stephan Delhi 97

Kathrin Noida 86
David Ghaziabad 74
Alina Gurugram 90

4. Deleting View

A view can be deleted using the Drop View statement.

Syntax

DROP VIEW view_name;

26
Relational Algebra

Selection and Projection

SELECT: Select operation chooses the subset of tuples from the relation that satisfies the
given condition mentioned in the syntax of selection. The selection operation is also known
as horizontal partitioning since it partitions the table or relation horizontally.
Notation:
σ c(R)
where ‘c’ is selection condition which is a boolean expression(condition), we can have a
single condition like Roll= 3 or combination of condition like X>2 AND Y<1, symbol
‘σ (sigma)’ is used to denote select(choose) operator.
Roll Name Department Fees Team
1 Bikash CSE 22000 A
2 Josh CSE 34000 A
3 Kevin ECE 36000 C
4 Ben ECE 56000 D
Select all the student of Team A : σ Team = 'A' (Student)
Roll Name Department Fees Team
1 Bikash CSE 22000 A
2 Josh CSE 34000 A

PROJECT:It displays the specific column of a table. It is denoted by pie (∏). It is a vertical
subset of the original relation. It eliminates duplicate tuples.

Syntax
The syntax is as follows − πA1,A2,A3,...,Ak(r)

Where A1 and A2 are attribute names and r is a relation name.

Regno Branch Section

1 CSE A
2 ECE B
3 CIVIL B
4 IT A
Examples: ∏regno(student) ∏branch,section(student) ∏regno,section(σbranch=ECE(student))

Output Branch Section Regno Section

RegNo CSE A 2 B
1 ECE B
CIVIL B
2 IT A
 3
4

Set Operations

The SQL Set operation is used to combine the two or more SQL SELECT statements.

Types of Set Operation

1. Union
2. UnionAll
3. Intersect
4. Minus
5. Union

1. Union

o The SQL Union operation is used to combine the result of two or more SQL SELECT
queries.
o In the union operation, all the number of datatype and columns must be same in both the
tables on which UNION operation is being applied.
o The union operation eliminates the duplicate rows from its resultset.

Syntax

SELECT column_name FROM table1 UNION SELECT column_name FROM table2;

Example:

The First table

ID NAME
1 Jack
2 Harry
3 Jackson

The Second table

ID NAME
3 Jackson
4 Stephan
5 David

Union SQL query will be:

SELECT * FROM First UNION SELECT * FROM Second;

The resultset table will look like:

ID NAME
1 Jack
2 Harry
3 Jackson
4 Stephan
5 David

2. Union All

Union All operation is equal to the Union operation. It returns the set without removing
duplication and sorting the data.

Syntax:

SELECT column_name FROM table1 UNION ALL SELECT column_name FROM table2;

Example: Using the above First and Second table.

Union All query will be like:

SELECT * FROM First UNION ALL SELECT * FROM Second;

The result set table will look like:

ID NAME
1 Jack
2 Harry
3 Jackson
3 Jackson
4 Stephan
5 David
3. Intersect
o It is used to combine two SELECT statements. The Intersect operation returns the
common rows from both the SELECT statements.
o In the Intersect operation, the number of datatype and columns must be the same.
o It has no duplicates and it arranges the data in ascending order by default.

Syntax

SELECT column_name FROM table1 INTERSECT SELECT column_name FROM table2;

Example:

Using the above First and Second table.

Intersect query will be:

SELECT * FROM First INTERSECT SELECT * FROM Second;

The resultset table will look like:

ID NAME
3 Jackson

4. Minus
o It combines the result of two SELECT statements. Minus operator is used to display the
rows which are present in the first query but absent in the second query.
o It has no duplicates and data arranged in ascending order by default.

Syntax:

SELECT column_name FROM table1 MINUS SELECT column_name FROM table2;

Example

Using the above First and Second table.

Minus query will be:

SELECT * FROM First MINUS SELECT * FROM Second;

The result set table will look like:

ID NAME
1 Jack
2 Harry

Rename operation

It is used to assign a new name to a relation and is denoted by ρ (rho).

Syntax
ρ newname ( tablename or expression)

Consider the student table given below −

Regno Branch Section

1 CSE A
2 ECE B
3 CIVIL B
4 IT A

Example 1

The student table is renamed with newstudent with the help of the following command −

ρnewstudent (student)

Example 2

The name, branch column of student table are renamed and newbranch respectively

ρ newname, newbranch(∏name,branch( student))

Joins

SQL JOIN clause is used to query and access data from multiple tables by establishing logical
relationships between them. It can access data from multiple tables simultaneously using
common key values shared across different tables.

We can use SQL JOIN with multiple tables. It can also be paired with other clauses, the most
popular use will be using JOIN with WHERE clause to filter data retrieval.

Consider two tables: students and courses.

Students
Courses
course_id course_name
student_id name course_id
1 Alice 101 101 Math
2 Bob 102 102 Science
3 Carol 103 103 History
4 Dave 104 105 Art

CROSS JOIN OR NATURAL JOIN

Returns the Cartesian product of the two tables, i.e., every row from the first table is joined with
every row of the second table.
Example:
SELECT students.name, courses.course_name
FROM students
CROSS JOIN courses;
Result:

name course_name
Alice Math
Alice Science
Alice History
Alice Art
Bob Math
Bob Science
Bob History
Bob Art
Carol Math
Carol Science
Carol History
Carol Art
Dave Math
Dave Science
Dave History
Dave Art

Self Join
A self JOIN is a regular join, but the table is joined with itself.
Syntax:
select column_name(s) from table1 t1, table1 t2 where condition;
Example
SELECT s1.name AS student1, s2.name AS student2, s1.course_id
FROM students s1
INNER JOIN students s2 ON s1.course_id = s2.course_id
WHERE s1.student_id < s2.student_id;
In this query:

Result:

student1 student2 course_id

Alice Eve 101

INNER JOIN
The INNER JOIN keyword selects records that have matching values in both tables
Syntax:
select column_name(s) from table1 inner join table2
on table1.column_name = table2.column_name;

Example:
SELECT students.name, courses.course_name FROM students
INNER JOIN courses ON students.course_id = courses.course_id;

Result:

name course_name
Alice Math
Bob Science
Carol History

OUTER JOIN
In a relational DBMS, we follow the principles of normalization that allows us to minimize the
large tables into small tables. By using a select statement in Joins, we can retrieve the big table
back. Outer joins are of following three types.
1. Left outer join
2. Right outer join
3. Full outer join

LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table (table1), and the matched
records from the right table (table2). The result is NULL from the right side, if there is no match.

Syntax:

select column_name(s)from table1left join table2

on table1.column_name = table2.column_name;
Example:
SELECT students.name, courses.course_name FROM students
LEFT JOIN courses ON students.course_id = courses.course_id;

Result:

name course_name
Alice Math
Bob Science
Carol History
Dave NULL

RIGHT JOIN (or RIGHT OUTER JOIN)

Returns all records from the right table and the matched records from the left table. If no match,
NULL values are returned for columns from the left table.

Syntax:

select column_name(s)from table1right join table2

on table1.column_name table2.column_name;

Example

SELECT students.name, courses.course_name

FROM students
RIGHT JOIN courses ON students.course_id = courses.course_id;
Result:

name course_name
Alice Math
Bob Science
Carol History
NULL Art

FULL JOIN (or FULL OUTER JOIN)

Returns all records when there is a match in either left or right table. If there is no match, NULL
values are returned for columns from the table that lacks the match.
Syntax:
select column_name(s)from table1full outer join table2
on table1.column_name = table2.column_name;
Example:
SELECT students.name, courses.course_name
FROM students
FULL JOIN courses ON students.course_id = courses.course_id;
Result:

name course_name
Alice Math
Bob Science
Carol History
Dave NULL
NULL Art

Division

Division Operation is represented by "division"(÷ or /) operator and is used in queries that

involve keywords "every", "all", etc.

Notation: R(X,Y)/S(Y)

Here, R is the first relation from which data is retrieved.

S is the second relation that will help to retrieve the data.

X and Y are the attributes/columns present in relation. We can have multiple attributes in
relation, but keep in mind that attributes of S must be a proper subset of attributes of R.

Let's have two relations, ENROLLED and COURSE. ENROLLED consist of two attributes
STUDENT_ID and COURSE_ID. It denotes the map of students who are enrolled in given
courses.

ENROLLED

STUDENT_ID COURSE_ID
Student_1 DBMS
 Student_2 DBMS
Student_1 OS
Student_3 OS

COURSE

COURSE_ID
DBMS
OS

Now the query is to return the STUDENT_ID of students who are enrolled in every course.

SQL>ENROLLED(STUDENT_ID, COURSE_ID)/COURSE(COURSE_ID)

Output:

STUDENT_ID
Student_1

Relational Calculus in DBMS

Relational Calculus is a non-procedural query language used in database management systems

(DBMS). It focuses on what to retrieve from the database rather than how to retrieve it (which is
the focus of procedural languages like SQL). It allows you to describe the desired result by
specifying conditions and constraints, and it is used as a foundation for relational query
languages like SQL.

Two Types of Relational Calculus:

1. Tuple Relational Calculus (TRC)

2. Domain Relational Calculus (DRC)

Let’s look at both in more detail.

1. Tuple Relational Calculus (TRC)

In Tuple Relational Calculus, a query is expressed as a set of tuples. A tuple variable represents a
row (or a record) in the table, and the conditions specified in the query refer to the attributes
(columns) of that tuple. The basic syntax follows this form:

{T | condition(T)}
Where:

 T is a tuple variable.

 condition(T) is a predicate that the tuple must satisfy.

Example of Tuple Relational Calculus:

Suppose we have the following database schema:

Employee(EmpID, Name, Age, Department)

Let's write a TRC query to find the names of employees who are older than 30:

{ T.Name | ∃T (Employee(T) ∧ T.Age > 30) }

Here:

 T is a tuple variable representing a row in the Employee table.

 Employee(T) specifies that T is a tuple from the Employee table.

 T.Age > 30 is the condition specifying that we only want employees whose age is greater
than 30.

 T.Name is the result, which is the name of the employee.

This query reads: "Find all names of employees (T.Name) such that there exists a tuple T in the
Employee table where T.Age > 30."

2. Domain Relational Calculus (DRC)

In Domain Relational Calculus, the query is expressed in terms of the values (or domains) of
attributes, rather than tuples. A domain variable represents a value in a specific domain (like a
column value), and the conditions refer to these domain variables.

The basic syntax of DRC is:

{ <D1, D2, ..., Dn> | condition(D1, D2, ..., Dn) }

Where:

 D1, D2, ..., Dn are domain variables representing the values of different columns.

 condition(D1, D2, ..., Dn) is the condition that the domain variables must satisfy.

Example of Domain Relational Calculus:

Using the same Employee table, let's write a DRC query to find the names of employees who are
older than 30:

{ D | ∃E (Employee(E) ∧ E.Age > 30 ∧ E.Name = D) }

Here:

 D is a domain variable representing the name of an employee.

 E is a tuple variable for rows in the Employee table.

 Employee(E) specifies that E is a tuple from the Employee table.

 E.Age > 30 is the condition that the employee’s age must be greater than 30.

 E.Name = D states that we want the name of the employee, represented by the domain
variable D.

This query reads: "Find all values D (names) such that there exists a tuple E in the Employee
table where E.Age > 30 and E.Name = D."

Differences Between Tuple Relational Calculus and Domain Relational Calculus

1. Tuple Relational Calculus (TRC):

o Works with entire tuples (rows).

o Uses tuple variables to represent whole records.

o Query specifies the conditions on attributes of a tuple.

2. Domain Relational Calculus (DRC):

o Works with individual attribute values (domains).

o Uses domain variables to represent individual attribute values.

o Query specifies conditions on the values of attributes rather than on entire tuples.

Relational Calculus vs SQL

 Relational Calculus is a theoretical query language, primarily used for specifying what
we want from the database, without specifying how to retrieve it.

 SQL, on the other hand, is a practical implementation of relational query languages,

which is more procedural in nature and specifies how to get the result.

For instance, the SQL equivalent of the above queries could be:
SQL Equivalent for TRC Example:

SELECT Name

FROM Employee

WHERE Age > 30;

SQL Equivalent for DRC Example:

SELECT Name

FROM Employee

WHERE Age > 30;

In SQL, the query specifies both the what (which column, Name, to retrieve) and the how (using
SELECT and WHERE clauses). While relational calculus is focused on expressing conditions
and results, SQL tells the DBMS how to execute the query to get the result.

Summary

 Relational Calculus is a non-procedural query language used to express what data to

retrieve, not how to retrieve it.

 There are two forms: Tuple Relational Calculus (TRC) and Domain Relational Calculus
(DRC).

 TRC uses tuples (whole rows) as variables, while DRC uses domain variables (individual
attribute values).

 Relational calculus queries are more abstract, while SQL is more procedural and
optimized for practical use in databases.