UNIT-II

What is Relational Model?

Relational model can represent as a table with columns and rows. Each row is known as a tuple.
Each table of the column has a name or attribute.

Relational Model (RM) represents the database as a collection of relations. A relation is nothing but
a table of values. Every row in the table represents a collection of related data values. These rows in
the table denote a real-world entity or relationship.

The table name and column names are helpful to interpret the meaning of values in each row. The
data are represented as a set of relations. In the relational model, data are stored as tables. However,
the physical storage of the data is independent of the way the data are logically organized.

Relational Model Concepts

1. Attribute: Each column in a Table. Attributes are the properties which define a relation. e.g.,
Student_Rollno, NAME,etc.
2. Tables – In the Relational model the, relations are saved in the table format. It is stored along
with its entities. A table has two properties rows and columns. Rows represent records and
columns represent attributes.
3. Tuple – It is nothing but a single row of a table, which contains a single record.
4. Relation Schema: A relation schema represents the name of the relation with its attributes.
5. Degree: The total number of attributes which in the relation is called the degree of the
relation.
6. Cardinality: Total number of rows present in the Table.
7. Column: The column represents the set of values for a specific attribute.
8. Relation instance – Relation instance is a finite set of tuples in the RDBMS system. Relation
instances never have duplicate tuples.
9. Relation key - Every row has one, two or multiple attributes, which is called relation key.
10. Attribute domain – Every attribute has some pre-defined value and scope which is known
as attribute domain
Integrity Constraints:
o Integrity constraints are a set of rules. It is used to maintain the quality of information.
o Integrity constraints ensure that the data insertion, updating, and other processes have to be
performed in such a way that data integrity is not affected.
o Thus, integrity constraint is used to guard against accidental damage to the database.

Types of Integrity Constraint

1. Domain constraints
o Domain constraints can be defined as the definition of a valid set of values for an attribute.
o The data type of domain includes string, character, integer, time, date, currency, etc. The
value of the attribute must be available in the corresponding domain.
Example:

2. Entity integrity constraints

o The entity integrity constraint states that primary key value can't be null.
o This is because the primary key value is used to identify individual rows in relation and if the
primary key has a null value, then we can't identify those rows.
o A table can contain a null value other than the primary key field.

Example:

3. Referential Integrity Constraints

o A referential integrity constraint is specified between two tables.

o In the Referential integrity constraints, if a foreign key in Table 1 refers to the Primary Key
of Table 2, then every value of the Foreign Key in Table 1 must be null or be available in
Table 2.
Example:

4. Key constraints
o Keys are the entity set that is used to identify an entity within its entity set uniquely.
o An entity set can have multiple keys, but out of which one key will be the primary key. A
primary key can contain a unique and null value in the relational table.

Example:
Enforcing integrity constraints:

Data integrity refers to the correctness and completeness of data within a database. To enforce data
integrity, you can constrain or restrict the data values that users can insert, delete, or update in the
database.

For example, the integrity of data in the pubs2 and pubs3 databases requires that a book title in the
titles table must have a publisher in the publishers table. You cannot insert books that do not have a
valid publisher into titles, because it violates the data integrity of pubs2 or pubs3.

Transact-SQL provides several mechanisms for integrity enforcement in a database such as rules,
defaults, indexes, and triggers. These mechanisms allow you to maintain these types of data
integrity:

• Requirement – requires that a table column must contain a valid value in every row; it cannot
allow null values. The create table statement allows you to restrict null values for a column.

• Check or validity – limits or restricts the data values inserted into a table column. You can
use triggers or rules to enforce this type of integrity.

• Uniqueness – no two table rows can have the same non-null values for one or more table
columns. You can use indexes to enforce this integrity.

• Referential – data inserted into a table column must already have matching data in another
table column or another column in the same table. A single table can have up to 192 references.

Types of SQL

Here are five types of widely used SQL queries.

 Data Definition Language (DDL)

 Data Manipulation Language (DML)
 Data Control Language(DCL)
 Transaction Control Language(TCL)
 Data Query Language (DQL)
 Types of SQL

What is DDL?

Data Definition Language helps you to define the database structure or schema. Let's learn about
DDL commands with syntax.

CREATE

CREATE statements is used to define the database structure schema:

Syntax:

CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);

For example:

Create database university;

Create table students;
Create view for_students;

DROP

Drops commands remove tables and databases from RDBMS.

Syntax

DROP TABLE ;

For example:

Drop object_typeobject_name;
Drop database university;
Drop table student;
ALTER

Alters command allows you to alter the structure of the database.

Syntax:

To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

To modify an existing column in the table:

ALTER TABLE MODIFY(COLUMN DEFINITION....);

TRUNCATE:

This command used to delete all the rows from the table and free the space containing the table.

Syntax:

TRUNCATE TABLE table_name;

Example:

TRUNCATE table students;

What is Data Manipulation Language?

Data Manipulation Language (DML) allows you to modify the database instance by inserting,
modifying, and deleting its data. It is responsible for performing all types of data modification in a
database.

There are three basic constructs which allow database program and user to enter data and
information are:

Here are some important DML commands in SQL:

 INSERT
 UPDATE
 DELETE

INSERT:

This is a statement is a SQL query. This command is used to insert data into the row of a table.

Syntax:
INSERT INTO TABLE_NAME (col1, col2, col3,.... col N) VALUES (value1, value2, value3, .... v
alueN);
Or
INSERT INTO TABLE_NAME VALUES (value1, value2, value3, ....valueN);

For example:

INSERT INTO students (RollNo, FIrstName, LastName) VALUES ('60', 'Tom', Erichsen');

UPDATE:

This command is used to update or modify the value of a column in the table.

Syntax:

UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDI

TION] ;

For example:

UPDATE students SET FirstName = 'Jhon', LastName= 'Wick' WHEREStudID = 3;

DELETE:

This command is used to remove one or more rows from a table.

Syntax:

DELETE FROM table_name [WHERE condition];

For example:

DELETE FROM students WHERE FirstName = 'Jhon';

What is DCL?

DCL (Data Control Language) includes commands like GRANT and REVOKE, which are useful to
give "rights & permissions." Other permission controls parameters of the database system.

Examples of DCL commands:

Commands that come under DCL:

 Grant
 Revoke
Grant:

This command is use to give user access privileges to a database.

Syntax:

GRANT SELECT, UPDATE ONMY_TABLE TO SOME_USER, ANOTHER_USER;

For example:

GRANT SELECT ON Users TO'Tom'@'localhost;

Revoke:

It is useful to back permissions from the user.

Syntax:

REVOKE privilege_nameON object_nameFROM {user_name |PUBLIC |role_name}

For example:

REVOKE SELECT, UPDATE ON student FROM BCA, MCA;

What is TCL?

Transaction control language or TCL commands deal with the transaction within the database.

Commit

This command is used to save all the transactions to the database.

Syntax:

Commit;

For example:

DELETE FROMStudents WHERERollNo =25;

COMMIT;

Rollback

Rollback command allows you to undo transactions that have not already been saved to the database.
Syntax:

ROLLBACK;

Example:

DELETE FROM Students WHERERollNo =25;

SAVEPOINT

This command helps you to sets a savepoint within a transaction.

Syntax:

SAVEPOINT SAVEPOINT_NAME;

Example:

SAVEPOINTRollNo;

What is DQL?

Data Query Language (DQL) is used to fetch the data from the database. It uses only one command:

SELECT:

This command helps you to select the attribute based on the condition described by the WHERE
clause.

Syntax:

SELECT expressions FROM TABLES WHERE conditions;

For example:

SELECT FirstName FROM Student WHERE RollNo> 15;

Logical Database Design:
ER Diagram to Relational Model Conversion

First step of any relational database design is to make ER Diagram for it and then convert it into
relational Model.
What is relational model ?
Relational Model represents how data is stored in database in the form of table.

Lets learn step by step how to convert ER diagram into relational model

1.Entity Set:

Consider we have entity STUDENT in ER diagram with attributes Roll Number, Student Name and
Class.

To convert this entity set into relational schema

1.Entity is mapped as relation in Relational schema
2.Attributes of Entity set are mapped as attributes for that Relation.
3.Key attribute of Entity becomes Primary key for that Relation.

2.Entity set with multi valued attribute:

Consider we have entity set Employee with attributes Employee ID, Name and Contact number.
Here contact number is multivalued attribute as it has multiple values. as an employee can have
more than one contact number for that we have to repeat all attributes for every new contact number.
This will lead to data redundancy in table.
Hence to convert entity with multivalued attribute into relational schema
separate relation is created for multivalued attribute in which
1.Key attribute and multivalued attribute of entity set becomes primary key of relation.
2.Separate relation employee is created with remaining attributes.
Due to this instead of repeating all attributes of entity now only one attribute is need to repeat.

3.Entity set with Composite attribute:

Consider entity set student with attributes Roll Number, Student Name and Class. here student name
is composite attribute as it has further divided into First name, last name.
In this case to convert entity into relational schema,
composite attribute student name should not be include in relation but all parts of composite attribute
are mapped as simple attributes for relation.
4. 1:M (one to many) Relationship:

Consider 1:M relationship set enrolled exist between entity sets student and course as follow,

Attributes of entity set student are Roll no which is primary key, student name and class
Attributes of entity set course are Course code which is primary key, Course name and duration
And date of enroll is attribute of relationship set enroll.
Here Enroll is 1:M relationship exist between entity set student and course which means that one
student can enroll in multiple courses
In this case to convert this relationship into relational schema,
1.Separate relation is created for all participating entity sets (student and course)
2.Key attribute of Many’s side entity set (course) is mapped as foreign key in one’s side
relation(Student)
3.All attributes of relationship set are mapped as attributes for relation of one’s side entity set
(student)
5. M:1 (many to one) Relationship:

Consider same relationship set enroll exist between entity sets student and course .but here student is
many side entity set while course is one side entity set. Which means many student can enroll in one
course.

To convert this relationship set into relational schema,

1.Separate relation is created for all participating entity sets.
2.Key attribute of Many’s side entity set student is mapped as foreign key in one’s side relation
3.All attributes of relationship set are mapped as attributes for one’s side relation course.
6. M:N (many to many) Relationship:

Consider same relationship set enrolled exist between entity sets student and course ,which means
multiple student can enroll in multiple courses.

To convert this Relationship set into relational schema,

1. Relationship set is mapped as separate relation

2. Key attributes of participating entity sets are mapped as primary key for that relation
3. Attribute of relationship set becomes simple attributes for that relation
4. And separate relation is created for other participating entities
7. 1:1 (one to one) Relationship:

Consider same relationship set enroll exist between entity sets student and course ,which means one
student can enroll in only one courses

To convert this Relationship set into relational schema,

1. Separate relation is created for all participating entity sets.

2. Primary Key of Relation Student can be act as foreign key for relation Course OR Primary
Key of Relation Course act as foreign key for relation Student.
EXAMPLE:
Problem Definition

In this example problem, you will create a database for an organization with many departments.
Each department has employees and employees have dependents. To create a database for the
company, read the description and identify all the entities from the description of the company.

 The company organized into departments and departments have employees

working in it.
 Attributes of Department are dno, dname. Attributes of Employee include eno,
name, dob,gender, doj, designation, basic_pay, panno, skills . Skills are mult i-
valued attribute.
 The Department has a manager managing it. There are also supervisors in
Department who supervises a set of employees.
 Each Department enrolls a number of projects. Attributes of Project are pcode,
pname. A project is enrolled by a department. An employee can work on any
number of projects on a given day. The Date of employee work in -time and out-
time has to keep track.
 The Company also maintains details of the dependents of each employees.
Attributes of dependent include dname, dob, gender and relationship with the
employee.
Model an entity-relationship diagram for the above scenario.

Solution – Converting ER model into Relational model

From the real world description of the organization, we were able to identify the following entities.
These entities will become the basis for an entity-relationship diagram or model.
 ENTITY-RELATIONSHIP DIAGRAM OR MODEL.

Convert to Relational Model

The next step in the database design is to convert the ER Model intothe Relational Model. In the
Relational Model, we will define the schema for relations and their relationships. The attributes from
the entity-relationship diagram will become fields for a relationship and one of them is a primary
field or primary key. It is usually underlined in the entity-relationship diagram.

An entity in a relational model is a relation. For example, the entity Dependent is a relation in the
relational model with all the attributes as fields – eno, dname, dob, gender, and relationship.
Relational Model of Database
Views in SQL
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.

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.

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:

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:

CREATE VIEW MarksView AS SELECT Student_Detail.NAME, Student_Detail.ADDRESS,

Student_Marks.MARKS FROM Student_Detail, Student_Mark
WHERE Student_Detail.NAME = Student_Marks.NAME;

To display data of View MarksView:

SELECT * FROM MarksView;

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;

Example:

If we want to delete the View MarksView, we can do this as:

DROP VIEW MarksView;

Executed Queries on Views:

mysql> create table student_detail(std_idint,name varchar(20),address varchar(20));

Query OK, 0 rows affected (0.33 sec)

mysql> insert into student_detail(std_id,name,address) values

(1,'stephan','delhi'),(2,'kathrin','noida'),(3,'david','ghaziabad'),(4,'alina','gurugram');

Query OK, 4 rows affected (0.11 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> create table student_marks(std_idint,name varchar(20),marks int,ageint);

Query OK, 0 rows affected (0.11 sec)

mysql> insert into student_marks(std_id,name,marks,age) values

(1,'stephan',97,19),(2,'kathrin',86,21),(3,'david',74,18),(4,'alina',90,19),(5,’john’,96,18);

Query OK, 5 rows affected (0.02 sec)

Records: 5 Duplicates: 0 Warnings: 0

mysql> create view detailsview as select name,address from student_detail where std_id<4;

Query OK, 0 rows affected (0.17 sec)

mysql> select * from detailsview;

+---------+-----------+

| name | address |

+---------+-----------+

| stephan | delhi |

| kathrin | noida |

| david | ghaziabad |

+---------+-----------+

3 rows in set (0.08 sec)

mysql> create view marksviewas select

student_detail.name,student_detail.address,student_marks.marks from student_detail,student_marks
where student_detail.name=student_marks.name;

Query OK, 0 rows affected (0.09 sec)

mysql> select * from marksview;

+---------+-----------+-------+

| name | address | marks |

+---------+-----------+-------+

| stephan | delhi | 97 |

| kathrin | noida | 86 |

| david | ghaziabad | 74 |

| alina | gurugram | 90 |

+---------+-----------+-------+

4 rows in set (0.00 sec)

mysql> drop view marksview;

Query OK, 0 rows affected (0.00 sec)

mysql> create view marksview1 as select student_detail.name,

student_marks.marks,student_marks.age from student_detail, student_marks where
student_detail.std_id=student_marks.std_id;

Query OK, 0 rows affected (0.20 sec)

mysql> select * from marksview1;

+------+-------+------+

| name | marks | age |

+------+-------+------+

| xyz | 98 | 18 |

| pqr | 95 | 19 |

| abc | 68 | 17 |

| uvw | 78 | 18 |

+------+-------+------+

4 rows in set (0.09 sec)

Update SQL View:

The SQL view created can also be modified. We can do the following operations with the SQL
VIEW.

But, all views are not updatable. A SQL view can be updated if the following conditions are
satisfied.

1. The view is defined based on only one table.

2. The view should not have any field which is made of an aggregate function.
3. The view must not have GROUP BY, HAVING or DISTINCT clause in its definition.
4. The view should not be created using any nested query.
 5. The selected output fields of the view must not use constants, string or value expressions.
6. If you want to update a view based on another view then that view should be updatable.

Updating a SQL View

We can use the CREATE OR REPLACE VIEW statement to modify the SQL view.
Syntax

CREATE OR REPLACE VIEW view_name ASSELECT column1,coulmn2,..FROM table_name

WHERE condition;

Example: If we want to update the view marksview1and remove the attribute "Age" from in the
view then the query would be:

Query

mysql> create or replace view marksview1 as select student_detail.name,

student_marks.marks from student_detail, student_marks where
student_detail.std_id=student_marks.std_id;

Query OK, 0 rows affected (0.03 sec)

The above CREATE OR REPLACE VIEW statement would create a virtual table based on the
result of the SELECT statement. Now, you can query the SQL VIEW as follows to see the output:
Output

mysql> select * from marksview1;

+---------+-------+

| name | marks |

+---------+-------+

| stephan | 97 |

| kathrin | 86 |

| david | 74 |

| alina | 90 |

+---------+-------+

4 rows in set (0.03 sec)

Relational Algebra

Relational algebra is a procedural query language. It gives a step by step process to obtain the result
of the query. It uses operators to perform queries.

Types of Relational operation

1. Select Operation:
o The select operation selects tuples that satisfy a given predicate.
o 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:

σ BRANCH_NAME="perryride" (LOAN)

Output:

BRANCH_NAME LOAN_NO AMOUNT

Perryride L-15 1500

Perryride L-16 1300

2. Project Operation:
o 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.
o 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

Input:

∏ NAME, CITY (CUSTOMER)

Output:

NAME CITY

Jones Harrison

Smith Rye

Hays Harrison

Curry Rye

Johnson Brooklyn

Brooks Brooklyn

3. Union Operation:
o Suppose there are two tuples R and S. The union operation contains all the tuples that are
either in R or S or both in R & S.
o It eliminates the duplicate tuples. It is denoted by ∪.

Notation: R ∪ S

A union operation must hold the following condition:

o R and S must have the attribute of the same number.

o Duplicate tuples are eliminated automatically.
Example:

DEPOSITOR RELATION

CUSTOMER_NAME ACCOUNT_NO

Johnson A-101

Smith A-121

Mayes A-321

Turner A-176

Johnson A-273

Jones A-472

Lindsay A-284

BORROW RELATION

CUSTOMER_NAME LOAN_NO

Jones L-17

Smith L-23

Hayes L-15

Jackson L-14
 Curry L-93

Smith L-11

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:
o Suppose there are two tuples R and S. The set intersection operation contains all tuples that
are in both R & S.
o 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:
o Suppose there are two tuples R and S. The set intersection operation contains all tuples that
are in R but not in S.
o It is denoted by intersection minus (-).

Notation: R - S

Example: Using the above DEPOSITOR table and BORROW table

Input:

∏ CUSTOMER_NAME (BORROW) - ∏ CUSTOMER_NAME (DEPOSITOR)

Output:

CUSTOMER_NAME

Jackson

Hayes

Willians

Curry

6. Cartesian product
o 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.
o It is denoted by X.

Notation: E X D

Example:

EMPLOYEE

EMP_ID EMP_NAME EMP_DEPT

1 Smith A

2 Harry C

3 John B
DEPARTMENT

DEPT_NO DEPT_NAME

A Marketing

B Sales

C Legal

Input:

EMPLOYEE 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.

ρ(STUDENT1, STUDENT)
Join Operations:

A Join operation combines related tuples from different relations, if and only if a given join
condition is satisfied. It is denoted by ⋈.

Example:

EMPLOYEE

EMP_CODE EMP_NAME

101 Stephan

102 Jack

103 Harry

SALARY

EMP_CODE SALARY

101 50000

102 30000

103 25000
Operation: (EMPLOYEE ⋈ SALARY)

Result:

EMP_CODE EMP_NAME SALARY

101 Stephan 50000

102 Jack 30000

103 Harry 25000

Types of Join operations:

1. Natural Join:
o A natural join is the set of tuples of all combinations in R and S that are equal on their
common attribute names.
o It is denoted by ⋈.

Example: Let's use the above EMPLOYEE table and SALARY table:
Input:

∏EMP_NAME, SALARY (EMPLOYEE ⋈ SALARY)

Output:

EMP_NAME SALARY

Stephan 50000

Jack 30000

Harry 25000

2. Outer Join:

The outer join operation is an extension of the join operation. It is used to deal with missing
information.

Example:

EMPLOYEE

EMP_NAME STREET CITY

Ram Civil line Mumbai

Shyam Park street Kolkata

Ravi M.G. Street Delhi

Hari Nehru nagar Hyderabad

FACT_WORKERS

EMP_NAME BRANCH SALARY

Ram Infosys 10000

Shyam Wipro 20000

Kuber HCL 30000

Hari TCS 50000

Input:

(EMPLOYEE ⋈ FACT_WORKERS)

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru nagar Hyderabad TCS 50000

An outer join is basically of three types:

a. Left outer join

b. Right outer join
c. Full outer join
a. Left outer join:
o Left outer join contains the set of tuples of all combinations in R and S that are equal on their
common attribute names.
o In the left outer join, tuples in R have no matching tuples in S.
o It is denoted by ⟕.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

EMPLOYEE ⟕ FACT_WORKERS
EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

Ravi M.G. Street Delhi NULL NULL

b. Right outer join:

o Right outer join contains the set of tuples of all combinations in R and S that are equal on
their common attribute names.
o In right outer join, tuples in S have no matching tuples in R.
o It is denoted by ⟖.

Example: Using the above EMPLOYEE table and FACT_WORKERS Relation

Input:

EMPLOYEE ⟖ FACT_WORKERS
Output:

EMP_NAME BRANCH SALARY STREET CITY

Ram Infosys 10000 Civil line Mumbai

Shyam Wipro 20000 Park street Kolkata

Hari TCS 50000 Nehru street Hyderabad

Kuber HCL 30000 NULL NULL

c. Full outer join:

o Full outer join is like a left or right join except that it contains all rows from both tables.
o In full outer join, tuples in R that have no matching tuples in S and tuples in S that have no
matching tuples in R in their common attribute name.
o It is denoted by ⟗.

Example: Using the above EMPLOYEE table and FACT_WORKERS table

Input:

EMPLOYEE ⟗ FACT_WORKERS

Output:

EMP_NAME STREET CITY BRANCH SALARY

Ram Civil line Mumbai Infosys 10000

Shyam Park street Kolkata Wipro 20000

Hari Nehru street Hyderabad TCS 50000

 Ravi M.G. Street Delhi NULL NULL

Kuber NULL NULL HCL 30000

3. Equi join:

It is also known as an inner join. It is the most common join. It is based on matched data as per the
equality condition. The equi join uses the comparison operator(=).

Example:

CUSTOMER RELATION

CLASS_ID NAME

1 John

2 Harry

3 Jackson

PRODUCT

PRODUCT_ID CITY

1 Delhi

2 Mumbai

3 Noida
Input:

CUSTOMER ⋈ PRODUCT

Output:

CLASS_ID NAME PRODUCT_ID CITY

1 John 1 Delhi

2 Harry 2 Mumbai

3 Harry 3 Noida

Conditional Join(⋈c): Conditional Join is used when you want to join two or more
relation based on some conditions.
Example: Select students whose ROLL_NO is greater than EMP_NO of employees
STUDENT

ROLL_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

2 RAMESH GURGAON 9652431543 18

3 SUJIT ROHTAK 9156253131 20

4 SURESH DELHI 9156768971 18

EMPLOYEE

EMP_NO NAME ADDRESS PHONE AGE

1 RAM DELHI 9455123451 18

5 NARESH HISAR 9782918192 22

6 SWETA RANCHI 9852617621 21

4 SURESH DELHI 9156768971 18

STUDENT⋈c STUDENT.ROLL_NO>EMPLOYEE.EMP_NOEMPLOYEE

In terms of basic operators (cross product and selection) :

σ (STUDENT.ROLL_NO>EMPLOYEE.EMP_NO)(STUDENT×EMPLOYEE)
RESULT:
ROLL_NO NAME ADDRESS PHONE AGE EMP_NO NAME ADDRESS PHONE AGE

2 RAMESH GURGAON 9652431543 18 1 RAM DELHI 9455123451 18

3 SUJIT ROHTAK 9156253131 20 1 RAM DELHI 9455123451 18

9455123451
4 SURESH DELHI 9156768971 18 1 RAM DELHI 18

Division Operator (÷): Division operator A÷B can be applied if and only if:
 Attributes of B is proper subset of Attributes of A.
 The relation returned by division operator will have attributes = (All attributes of A – All
Attributes of B)
 The relation returned by division operator will return those tuples from relation A which are
associated to every B’s tuple.
Table 1

STUDENT_SPORTS
ROLL_NO SPORTS

1 Badminton

2 Cricket

2 Badminton

4 Badminton

Table 2
ALL_SPORTS

SPORTS

Badminton

Cricket
Consider the relation STUDENT_SPORTS and ALL_SPORTS given in Table 1 and Table 2
above.
To apply division operator as
STUDENT_SPORTS÷ ALL_SPORTS

 The operation is valid as attributes in ALL_SPORTS is a proper subset of attributes in

STUDENT_SPORTS.
 The attributes in resulting relation will have attributes {ROLL_NO,SPORTS}-
{SPORTS}=ROLL_NO
 The tuples in resulting relation will have those ROLL_NO which are associated with all B’s
tuple {Badminton, Cricket}. ROLL_NO 1 and 4 are associated to Badminton only. ROLL_NO
2 is associated to all tuples of B. So the resulting relation will be:

ROLL_NO

2
Relational Calculus:

Relational calculus is a non-procedural query language that tells the system what data to be retrieved
but doesn’t tell how to retrieve it.

Types of Relational Calculus

1. Tuple Relational Calculus (TRC)

In tuple relational calculus, we work on filtering tuples based on the given condition.
Syntax: { T | Condition }
In this form of relational calculus, we define a tuple variable, specify the
table(relation) name in which the tuple is to be searched for, along with a condition.
We can also specify column name using a . dot operator, with the tuple variable to
only get a certain attribute(column) in result.
A lot of informtion, right! Give it some time to sink in.
A tuple variable is nothing but a name, can be anything, generally we use a single
alphabet for this, so let's say T is a tuple variable.
To specify the name of the relation(table) in which we want to look for data, we do the
following:
Relation(T), where T is our tuple variable.
For example if our table is Student, we would put it as Student(T)
Then comes the condition part, to specify a condition applicable for a
particluarattribute(column), we can use the . dot variable with the tuple variable to
specify it, like in table Student, if we want to get data for students with age greater
than 17, then, we can write it as,
T.age> 17, where T is our tuple variable.
Putting it all together, if we want to use Tuple Relational Calculus to fetch names of
students, from table Student, with age greater than 17, then, for T being our tuple
variable,
T.name | Student(T) AND T.age> 17

Tuple relational calculus is used for selecting those tuples that satisfy the given condition.
Table: Student

First_NameLast_NameAge

---------- -------------

Ajeet Singh 30

Chaitanya Singh 31

Rajeev Bhatia 27

Carl Pratap 28

write relational calculus queries.

Query to display the last name of those students where age is greater than 30

{t.Last_Name|Student(t) AND t.age>30}

In the above query you can see two parts separated by | symbol. The second part is where we define
the condition and in the first part we specify the fields which we want to display for the selected
tuples.

The result of the above query would be:

Last_Name

---------

Singh
Query to display all the details of students where Last name is ‘Singh’

{ t|Student(t) AND t.Last_Name='Singh'}

Output:

First_NameLast_NameAge

---------- -------------

Ajeet Singh 30

Chaitanya Singh 31

2. Domain Relational Calculus (DRC)

In domain relational calculus the records are filtered based on the domains.
Again we take the same table to understand how DRC works.
Table: Student

First_NameLast_NameAge

---------- -------------

Ajeet Singh 30

Chaitanya Singh 31

Rajeev Bhatia 27

Carl Pratap 28

Query to find the first name and age of students where student age is greater than 27

{<First_Name,Age>|∈Student∧Age>27}

Note:
The symbols used for logical operators are: ∧ for AND, ∨ for OR and ┓ for NOT.

Output:

First_NameAge
In domain relational calculus, filtering is done based on the domain of the attributes
and not based on the tuple values.
Syntax: { c1, c2, c3, ..., cn | F(c1, c2, c3, ... ,cn)}
where, c1, c2... etc represents domain of attributes(columns) and F defines the
formula including the condition for fetching the data.
For example,
{< name, age > | ∈ Student ∧ age > 17}
Again, the above query will return the names and ages of the students in the
table Student who are older than 17.

---------- ----

Ajeet 30

Chaitanya 31

Carl 28