Relational Languages and Relational Model

 

Introduction to SQL

- SQL stands for Structured Query Language.
- SQL is a standard language for storing, manipulating and retrieving data in databases.
- SQL lets you access and manipulate databases
- SQL is an ANSI (American National Standards Institute) standard

---

Features of SQL

- SQL can execute queries against a database
- SQL can retrieve data from a database
- SQL can insert records in a database
- SQL can update records in a database
- SQL can delete records from a database
- SQL can create new databases
- SQL can create new tables in a database
- SQL can create stored procedures in a database
- SQL can create views in a database
- SQL can set permissions on tables, procedures, and views

---

Query and Sub Query

Query

A query is an inquiry into the database using the SELECT statement. A query is used to extract data from the database in a readable format according to the user's request. For instance, if you have an employee table, you might issue a SQL statement that returns the employee who is paid the most. This request to the database for usable employee information is a typical query that can be performed in a relational database.

---

Sub Query

- A Subquery or Inner query or a Nested query is a query within another SQL query and embedded within the WHERE clause.
- A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
- Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.

---

Set Operations

Union

UNION is used to combine the results of two or more Select statements. However it will eliminate duplicate rows from its result set. In case of union, number of columns and datatype must be same in both the tables.

---

Intersect

Intersect operation is used to combine two SELECT statements, but it only retuns the records which are common from both SELECT statements. In case of Intersect the number of columns and datatype must be same. MySQL does not support INTERSECT operator.

---

Minus

Minus operation combines result of two Select statements and return only those result which belongs to first set of result.

---

Joined and Derived Relations

Join

Join operations take two relations and return another relation as a result. In general, these additional operations are used as subquery expressions in the from clause. In relational databases, a join operation matches records in two tables. The two tables must be joined by at least one common field. That is, the join field is a member of both tables.Join condition defines which tuples in the two relations match, and what attributes are present in the result of the join.Join type defines how tuples in each relation are treated that do not match any tuple in the other relation (based on the join condition).

Equi-join: It is a joint in which the joining condition is based on equality between values in the common columns. Common columns appear redundantly in the resulting table.

Natural join: Natural join can be understood as an equi-join but with an elimination of the duplicate columns in the result table.

Inner join: An inner join is a join in which the database management system selects records from two tables only when the records have the same value in the common field that links the tables.

Outer join: An outer join is a join which returns all rows that satisfy the join condition and those rows from one table for which no rows from the other satisfy the join conditions. Such rows are not returned by a simple join.

---

Derived Relation

Derived relation is a type of a relation in which SQL allows a subquery expression to be used in the from clause. Example for the derived relation is given below:

Find the average account balance of those branches where the average account balance is greater than $1200.

select branch_name, avg_balance
from (select branch_name, avg (balance)
from account
group by branch_name )
as branch_avg ( branch_name, avg_balance )
where avg_balance > 1200;

---

Example of SQL Query

Q) Consider database schema
Account (acc_no ,branch_name ,balance)
Branch ( branch_name , branch_city ,assets)
Customer ( customer_name , customer_street ,customer_city)
Loan( loan_no , branch_name ,amount)
Depositor( customer_name , acc_no )
Borrower ( customer_name , loan_no)

1. Create database and list existing databases
Create database bank ;
Show databases

2. Use database bank for above schema
Use bank ;

3. Create tables
Create table account
(acc_no int not null ,
Branch_name varchar(30),
Balance real ,
Primary key (acc_no)) ;

4. Create table branch
Branch_name varchar(30) not null,
Branch_city varchar(30),
Assets real ,
Primary key (branch_name));

5. To list all tables
Show tables;

6. List account no of all account whose branch is kathmandu
Select acc_no from Customer
where
branch_name = kathmandu

---

Relational Algebra

- A relation algebra defines the set of operations on relations with the use of algebraic operations . it operates on one or two relations and return a relation as output.
- The fundamental operations used are:-
1. Selection
2. Projection
3. Union
4. Set difference
5, Cartesian Product
6. Rename
7. Set Intersection
8. Natural join
9. Assignment

---

Selection Operation:-

- It selects tuples that satisfy a given predicate.
- it is denoted by sigma .
- predicates appear as a subscript to sigma
- Argument relations is in parenthesis after sigma
- Syntax: σpredicate ( Argument relation)
- for example:
To select all the tuples from instructor table whose salary is greater than 10000;
σsalary > 10000 (instructor)

---

Project Operation

- It allows to produce the relation.
- it is unary operation that returns argument relation with certain attributes left out.
- Duplicate rows are eliminated.
- It is denoted by ‘π’
- Those attributes that we wish to appear is listed as subscript.
- Argument relation is written in paranthesis.
- Syntax: π attributes (Argument relation)

---

Union Operation

- It allows union of two relations as in set theory.
- It is denoted by ‘U’
- Duplicate values are eliminated.
- The operation r U s is valid if it holds
- The relations r and s must have same number of attributes.
- The domain of ith attribute of r and ith attribute of s must be same for all i.

---

Set Difference Operation:

- It is denoted by ‘-‘ .
- It also allows to find tuples that are in one relation but not in another.

---

Cartesian product operation:

- It is denoted by ‘X’.
- it allows to combine informations from any two relations.
- it associates every tuple of one relation with every tuple of another relation.

---

Rename Operation

- It gives a name to the result of relational algebra expressions.
- it is denoted by ‘P’ .
- Synatax: Px(E) = result of E with name x
- Px (A1 ,A2 ….An)(E) = result of name x and attribute renamed as A1,A2,A3 … An .

---

Set Intersection operation

- It is denoted by “∩’
- It can be replaced by r-(r-s) for r∩s

---

Natural join operation:

- It combines certain selections and Cartesians product operation.
- It is denoted by ⋈

---

Assignment Operation

- It is denoted by ‘ ’
- It assigns part of relational algebra expression to the relation variables.

---

Example---

Q) employee (e_no ,name,address)
Project (p_no ,p_name)
work (e_no,p_no)
part (part_no,part_name,qty,size)
use(e_no,p_no,part_no,number)