SRM INSTITUTE OF SCIENCE AND TECHNOLOGY

RAMAPURAM CHENNAI
FACULTY OF SCIENCE AND HUMANITIES
DEPARTMENT OF COMPUTER SCIENCE AND APPLICATIONS

DATABASE MANAGEMENT SYSTEM

UNIT 3: Defining constraints through SQL – Introduction to SQL Aggregate functions – Built-in
Functions: Numeric, Date and String – SQL Joins – Creating Sub Queries – Transaction Control
Commands: Commit, Rollback and Savepoint – Relational Algebra: Fundamental Operations –
Fundamental Dependency: Trivial and Non-trivial.

NOTES
SQL Constraints

 SQL constraints are used to specify rules for the data in a table. Constraints are used to
limit the type of data that can go into a table. This ensures the accuracy and reliability of
the data in the table. If there is any violation between the constraint and the data action,
the action is aborted.

 Constraints can be column level or table level. Column level constraints apply to a
column, and table level constraints apply to the whole table.

The following constraints are commonly used in SQL:

 NOT NULL - Ensures that a column cannot have a NULL value

 UNIQUE - Ensures that all values in a column are different
 PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies
each row in a table
 FOREIGN KEY - Prevents actions that would destroy links between tables
 CHECK - Ensures that the values in a column satisfies a specific condition
 DEFAULT - Sets a default value for a column if no value is specified

SQL NOT NULL Constraint

 By default, a column can hold NULL values.

 The NOT NULL constraint enforces a column to NOT accept NULL values.

 This enforces a field to always contain a value, which means that you cannot insert a new
record, or update a record without adding a value to this field.

SQL NOT NULL on CREATE TABLE

The following SQL ensures that the "ID", "LastName", and "FirstName" columns will NOT
accept NULL values when the "Persons" table is created:

CREATE TABLE Persons (ID int NOT NULL, LastName varchar(25) NOT NULL, FirstName
varchar(25) NOT NULL, Age int );
SQL NOT NULL on ALTER TABLE

To create a NOT NULL constraint on the "Age" column when the "Persons" table is already
created, use the following SQL:

ALTER TABLE Persons MODIFY Age int NOT NULL;

SQL UNIQUE Constraint

 The UNIQUE constraint ensures that all values in a column are different.
 Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness
for a column or set of columns.
 A PRIMARY KEY constraint automatically has a UNIQUE constraint.
 However, you can have many UNIQUE constraints per table, but only one PRIMARY
KEY constraint per table.

SQL UNIQUE Constraint on CREATE TABLE

The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is
created:

CREATE TABLE Persons (

ID int NOT NULL UNIQUE,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Age int );

SQL UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "ID" column when the table is already created, use the
following SQL:

ALTER TABLE Persons ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

DROP a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:

ALTER TABLE Persons DROP CONSTRAINT UC_Person;

SQL PRIMARY KEY Constraint

 The PRIMARY KEY constraint uniquely identifies each record in a table.
 Primary keys must contain UNIQUE values, and cannot contain NULL values.
 A table can have only ONE primary key; and in the table, this primary key can consist of
single or multiple columns (fields).
SQL PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is
created:

CREATE TABLE Persons (

ID int NOT NULL PRIMARY KEY,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Age int );

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(25) NOT NULL,
FirstName varchar(25),
Age int,
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName));

SQL PRIMARY KEY on ALTER TABLE

To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use
the following SQL:

ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);

DROP a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:

ALTER TABLE Person DROP CONSTRAINT PK_Person;

SQL FOREIGN KEY Constraint
 The FOREIGN KEY constraint is used to prevent actions that would destroy links
between tables.
 A FOREIGN KEY is a field (or collection of fields) in one table that refers to
the PRIMARY KEY in another table.
 The table with the foreign key is called the child table, and the table with the primary key
is called the referenced or parent table.

Look at the following two tables:

Persons Table

PersonID LastName FirstName Age

1 Hansen Ola 30

2 Svendson Tove 23

3 Pettersen Kari 20
 Orders Table

OrderID OrderNumber PersonID

1 77895 3

2 44678 3

3 22456 2

4 24562 1

Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the
"Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key
column, because it has to be one of the values contained in the parent table.

SQL FOREIGN KEY on CREATE TABLE

The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table
is created:

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID));
SQL FOREIGN KEY on ALTER TABLE

To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is
already created, use the following SQL:

ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:

ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;

SQL CHECK Constraint

 The CHECK constraint is used to limit the value range that can be placed in a column.
  If you define a CHECK constraint on a column it will allow only certain values for this
column.
 If you define a CHECK constraint on a table it can limit the values in certain columns
based on values in other columns in the row.

SQL CHECK on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table
is created. The CHECK constraint ensures that the age of a person must be 18, or older:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18));

SQL CHECK on ALTER TABLE

To create a CHECK constraint on the "Age" column when the table is already created, use the
following SQL:

ALTER TABLE Persons

ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint

To drop a CHECK constraint, use the following SQL:

ALTER TABLE Persons DROP CONSTRAINT CHK_PersonAge;

SQL DEFAULT Constraint

 The DEFAULT constraint is used to set a default value for a column.

 The default value will be added to all new records, if no other value is specified.

SQL DEFAULT on CREATE TABLE

The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is
created:

CREATE TABLE Orders (

ID int NOT NULL,
OrderNumber int NOT NULL,
OrderDate date DEFAULT GETDATE());
SQL DEFAULT on ALTER TABLE
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;

DROP a DEFAULT Constraint

ALTER TABLE Persons ALTER COLUMN City DROP DEFAULT;
 Introduction to Aggregate Functions

Common SQL Aggregate Functions:

 COUNT():

• COUNT(*): Returns the total number of rows in a table or a group.

• COUNT(column_name): Returns the number of non-NULL values in the specified column.

• COUNT(DISTINCT column_name): Returns the number of unique non-NULL values in the

specified column.
SYNTAX; count([DISTINCT/ALL] n)
Eg; count(DISTINCT COST)
 SUM(column_name): Calculates the sum of all non-NULL values in a numeric column.
SYNTAX; SUM ([DISTINCT/ALL] n)
Eg; SUM (DISTINCT COST)
 AVG (column_name): Calculates the average of all non-NULL values in a numeric column.
SYNTAX: AVG ([DISTINCT /ALL] n)
Eg; AVG (DISTINCT COST)
 MIN(column_name): Returns the minimum value in a column.
SYNTAX; MIN([DISTINCT/ALL]n)
Eg; MIN(DISTINCT COST)
 MAX(column_name): Returns the maximum value in a column.
SYNTAX; MAX([DISTINCT/ALL]n)
Eg; MAX(DISTINCT COST)

SQL BUILT-IN FUNCTIONS

SQL String Functions

Function Description
ASCII Returns the ASCII value for the specific character
CHAR_LENGTH Returns the length of a string (in characters)
CHARACTER_LENGTH Returns the length of a string (in characters)
CONCAT Adds two or more expressions together
CONCAT_WS Adds two or more expressions together with a separator
FIELD Returns the index position of a value in a list of values
FIND_IN_SET Returns the position of a string within a list of strings
FORMAT Formats a number to a format like "#,###,###.##", rounded to a
specified number of decimal places
INSERT Inserts a string within a string at the specified position and for a
certain number of characters
INSTR Returns the position of the first occurrence of a string in another
 string
LCASE Converts a string to lower-case
LEFT Extracts a number of characters from a string (starting from left)
LENGTH Returns the length of a string (in bytes)
LOCATE Returns the position of the first occurrence of a substring in a string
LOWER Converts a string to lower-case
LPAD Left-pads a string with another string, to a certain length
LTRIM Removes leading spaces from a string
MID Extracts a substring from a string (starting at any position)
POSITION Returns the position of the first occurrence of a substring in a string
REPEAT Repeats a string as many times as specified
REPLACE Replaces all occurrences of a substring within a string, with a new
substring
REVERSE Reverses a string and returns the result
RIGHT Extracts a number of characters from a string (starting from right)
RPAD Right-pads a string with another string, to a certain length
RTRIM Removes trailing spaces from a string
SPACE Returns a string of the specified number of space characters
STRCMP Compares two strings
SUBSTR Extracts a substring from a string (starting at any position)
SUBSTRING Extracts a substring from a string (starting at any position)
SUBSTRING_INDEX Returns a substring of a string before a specified number of
delimiter occurs
TRIM Removes leading and trailing spaces from a string
UCASE Converts a string to upper-case
UPPER Converts a string to upper-case

SQL Numeric Functions

Function Description
ABS Returns the absolute value of a number
ACOS Returns the arc cosine of a number
ASIN Returns the arc sine of a number
ATAN Returns the arc tangent of one or two numbers
ATAN2 Returns the arc tangent of two numbers
AVG Returns the average value of an expression
CEIL Returns the smallest integer value that is >= to a number
 CEILING Returns the smallest integer value that is >= to a number
COS Returns the cosine of a number
COT Returns the cotangent of a number
COUNT Returns the number of records returned by a select query
DEGREES Converts a value in radians to degrees
DIV Used for integer division
EXP Returns e raised to the power of a specified number
FLOOR Returns the largest integer value that is <= to a number
GREATEST Returns the greatest value of the list of arguments
LEAST Returns the smallest value of the list of arguments
LN Returns the natural logarithm of a number
LOG Returns the natural logarithm of a number, or the logarithm of a
number to a specified base
LOG10 Returns the natural logarithm of a number to base 10
LOG2 Returns the natural logarithm of a number to base 2
MAX Returns the maximum value in a set of values
MIN Returns the minimum value in a set of values
MOD Returns the remainder of a number divided by another number
PI Returns the value of PI
POW Returns the value of a number raised to the power of another
number
POWER Returns the value of a number raised to the power of another
number
RADIANS Converts a degree value into radians
RAND Returns a random number
ROUND Rounds a number to a specified number of decimal places
SIGN Returns the sign of a number
SIN Returns the sine of a number
SQRT Returns the square root of a number
SUM Calculates the sum of a set of values
TAN Returns the tangent of a number
TRUNCATE Truncates a number to the specified number of decimal places

SQL Date Functions

Function Description
ADDDATE Adds a time/date interval to a date and then returns the date
ADDTIME Adds a time interval to a time/datetime and then returns the
time/datetime
CURDATE Returns the current date
CURRENT_DATE Returns the current date
CURRENT_TIME Returns the current time
CURRENT_TIMESTAMP Returns the current date and time
CURTIME Returns the current time
DATE Extracts the date part from a datetime expression
DATEDIFF Returns the number of days between two date values
DATE_ADD Adds a time/date interval to a date and then returns the date
DATE_FORMAT Formats a date
DATE_SUB Subtracts a time/date interval from a date and then returns the
date
DAY Returns the day of the month for a given date
DAYNAME Returns the weekday name for a given date
DAYOFMONTH Returns the day of the month for a given date
DAYOFWEEK Returns the weekday index for a given date
DAYOFYEAR Returns the day of the year for a given date
EXTRACT Extracts a part from a given date
FROM_DAYS Returns a date from a numeric datevalue
HOUR Returns the hour part for a given date
LAST_DAY Extracts the last day of the month for a given date
LOCALTIME Returns the current date and time
LOCALTIMESTAMP Returns the current date and time
MAKEDATE Creates and returns a date based on a year and a number of days
value
MAKETIME Creates and returns a time based on an hour, minute, and second
value
MICROSECOND Returns the microsecond part of a time/datetime
MINUTE Returns the minute part of a time/datetime
MONTH Returns the month part for a given date
MONTHNAME Returns the name of the month for a given date
NOW Returns the current date and time
PERIOD_ADD Adds a specified number of months to a period
PERIOD_DIFF Returns the difference between two periods
QUARTER Returns the quarter of the year for a given date value
SECOND Returns the seconds part of a time/datetime
 SEC_TO_TIME Returns a time value based on the specified seconds
STR_TO_DATE Returns a date based on a string and a format
SUBDATE Subtracts a time/date interval from a date and then returns the
date
SUBTIME Subtracts a time interval from a datetime and then returns the
time/datetime
SYSDATE Returns the current date and time
TIME Extracts the time part from a given time/datetime
TIME_FORMAT Formats a time by a specified format
TIME_TO_SEC Converts a time value into seconds
TIMEDIFF Returns the difference between two time/datetime expressions
TIMESTAMP Returns a datetime value based on a date or datetime value
TO_DAYS Returns the number of days between a date and date "0000-00-
00"
WEEK Returns the week number for a given date
WEEKDAY Returns the weekday number for a given date
WEEKOFYEAR Returns the week number for a given date
YEAR Returns the year part for a given date
YEARWEEK Returns the year and week number for a given date

Examples:

SELECT UPPER(product_name) FROM Products;

SELECT LOWER(customer_name) FROM Customers;
SELECT SUBSTRING(description, 1, 10) FROM Products;
SELECT CONCAT(first_name, ' ', last_name) AS full_name FROM Employees;
SELECT ROUND(price, 2) FROM Products;
SELECT ABS(-100) AS absolute_value;
SELECT SQRT(64) AS square_root;
SELECT GETDATE() AS current_datetime;
SELECT YEAR(order_date) FROM Orders;
SELECT DATEDIFF(day, start_date, end_date) FROM Projects;
 SQL JOIN

 As the name shows, JOIN means to combine something. In case of SQL, JOIN means
"to combine two or more tables".

 In SQL, JOIN clause is used to combine the records from two or more tables in a database.

Different Types of SQL JOINs

 (INNER) JOIN: Returns records that have matching values in both tables
 LEFT (OUTER) JOIN: Returns all records from the left table, and
the matched records from the right table
 RIGHT (OUTER) JOIN: Returns all records from the right table, and
the matched records from the left table
 FULL (OUTER) JOIN: Returns all records when there is a match in either
left or right table

Sample Table

EMPLOYEE

EMP_ID EMP_NAME CITY SALARY AGE

1 Angelina Chicago 200000 30
2 Robert Austin 300000 26
3 Christian Denver 100000 42
4 Kristen Washington 500000 29
5 Russell Los angels 200000 36
6 Marry Canada 600000 48
 PROJECT

PROJECT_NO EMP_ID DEPARTMENT

101 1 Testing
102 2 Development
103 3 Designing
104 4 Development

1. INNER JOIN

In SQL, INNER JOIN selects records that have matching values in both tables
as long as the condition is satisfied. It returns the combination of all rows from
both the tables where the condition satisfies.
Syntax
1. SELECT table1.column1, table1.column2, table2.column1,….
2. FROM table1
3. INNER JOIN table2
4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
2. FROM EMPLOYEE
3. INNER JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;
Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development

2. LEFT JOIN

The SQL left join returns all the values from left table and the matching values
from the right table. If there is no matching join value, it will return NULL.
Syntax
1. SELECT table1.column1, table1.column2, table2.column1,….
2. FROM table1
3. LEFT JOIN table2
4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
2. FROM EMPLOYEE
3. LEFT JOIN PROJECT
 4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL

3. RIGHT JOIN

In SQL, RIGHT JOIN returns all the values from the values from the rows of right table and
the matched values from the left table. If there is no matching in both tables, it will return
NULL.
Syntax
1. SELECT table1.column1, table1.column2, table2.column1,…
2. FROM table1
3. RIGHT JOIN table2
4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
2. FROM EMPLOYEE
3. RIGHT JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development

4. FULL JOIN

In SQL, FULL JOIN is the result of a combination of both left and right outer join. Join tables
have all the records from both tables. It puts NULL on the place of matches not found.
Syntax
1. SELECT table1.column1, table1.column2, table2.column1,....
2. FROM table1
3. FULL JOIN table2
 4. ON table1.matching_column = table2.matching_column;
Query
1. SELECT EMPLOYEE.EMP_NAME, PROJECT.DEPARTMENT
2. FROM EMPLOYEE
3. FULL JOIN PROJECT
4. ON PROJECT.EMP_ID = EMPLOYEE.EMP_ID;

Output

EMP_NAME DEPARTMENT
Angelina Testing
Robert Development
Christian Designing
Kristen Development
Russell NULL
Marry NULL

SUBQUERIES

CORRELATED SUBQUERY
A correlated subquery is evaluated once for each row processed by the parent statement. The
parent statement can be a SELECT, UPDATE, or DELETE statement.

SELECT column1, column2, .... FROM table1 outer

WHERE column1 operator

(SELECT column1, column2 FROM table2 WHERE expr1 = outer.expr2);

A correlated subquery is one way of reading every row in a table and comparing values in each
row against related data. It is used whenever a subquery must return a different result or set of
results for each candidate row considered by the main query. In other words, you can use a
correlated subquery to answer a multipart question whose answer depends on the value in each
row processed by the parent statement.
NESTED SUBQUERIES

A nested query (also called a subquery) is a query embedded within another SQL query. The
result of the inner query is used by the outer query to perform additional operations.

Subqueries can be used in various parts of an SQL query such as SELECT, FROM or WHERE
Clauses.
 The inner query runs first, providing data for the outer query.
 The output query uses the results of the inner query for comparison or as input.
 Nested queries are particularly useful for breaking down complex problems into smaller,
manageable parts, making it easier to retrieve specific results from large datasets.

SELECT last_name, salary, department_id FROM employees outer WHERE salary >
 (SELECT AVG(salary) FROM employees WHERE department_id = outer.department_id
group by department_id);
Using the EXISTS Operator:

The EXISTS operator tests for existence of rows in the results set of the subquery. If a
subquery row value is found the condition is flagged TRUE and the search does not continue
in the inner query, and if it is not found then the condition is flagged FALSE and the search
continues in the inner query.

EXAMPLE of using EXIST operator:

Find employees who have at least one person reporting to them.

SELECT employee_id, last_name, job_id, department_id FROM employees outer WHERE

EXISTS (SELECT ’X’ FROM employees WHERE manager_id = outer.employee_id);

OUTPUT:

EXAMPLE of using NOT EXIST operator:

Find all departments that do not have any employees.

SELECT department_id, epartment_name FROM departments d WHERE NOT EXISTS

(SELECT ’X’ FROM employees WHERE department_id = d.department_id);

OUTPUT:
 A nested query is a query that has another query embedded within it.
The embedded query is called a subquery.
A subquery typically appears within the WHERE clause of a query. It
can sometimes appear in the FROM clause or HAVING clause.

Example

Let’s learn about nested queries with the help of an example. Find the names of employee who
have regno=103. The query is as follows −
select E.ename from employee E where E.eid IN (select S.eid from salary S where
S.regno=103);

The student table is created as follows −

create table student(id number(10), name varchar2(20),classID number(10), marks
varchar2(20));
Insert into student values(1,'pinky',3,2.4);
Insert into student values(2,'bob',3,1.44);
Insert into student values(3,'Jam',1,3.24);
Insert into student values(4,'lucky',2,2.67);
Insert into student values(5,'ram',2,4.56);
select * from student;

Output

You will get the following output −

Id Name ClassID Marks

1 Pinky 3 2.4
2 Bob 3 1.44
3 Jam 1 3.24
4 Lucky 2 2.67
5 Ram 2 4.56
The teacher table is created as follows −

Create table teacher(id number(10), name varchar(20), subject varchar2(10), classID

number(10), salary number(30));
Insert into teacher
values(1,’bhanu’,’computer’,3,5000); Insert into
teacher values(2,'rekha','science',1,5000); Insert into
teacher values(3,'siri','social',NULL,4500); Insert into
teacher values(4,'kittu','mathsr',2,5500); select * from
teacher;
Output

Id Name Subject classID Salary

1 Bhanu Computer 3 5000
2 Rekha Science 1 5000
3 Siri Social NULL 4500
4 Kittu Maths 2 5500

The class table is created as follows −

Example

Create table class(id number(10), grade number(10), teacherID number(10), noofstudents

number(10));
insert into class values(1,8,2,20);
insert into class values(2,9,3,40);
insert into class values(3,10,1,38);
select * from class;

Output

You will get the following output −

Id Grade teacherID No.ofstudents

1 8 2 20
2 9 3 40
3 10 1 38

Now let’s work on nested queries

Example 1

Select AVG(noofstudents) from class where teacherID IN( Select id from teacher where
subject=’science’ OR subject=’maths’);
 Output
You will get the following output −
20.0

Example 2

SELECT * FROM student WHERE classID = (SELECT id FROM class WHERE

noofstudents = ( SELECT MAX(noofstudents) FROM class));

Output

You will get the following output −

4|lucky |2|2.67
5|ram |2|4.56

TRANSACTION CONTROL LANGUAGE (TCL)

The changes in the database that we made using DML commands are either
performed or roll backed using TCL.

 To persist the changes made by DML commands in database – COMMIT

 To rollback the changes made to the database – ROLLBACK

Transaction Control Language (TCL) commands are used to manage

transactions in the database. These are used to manage the changes made to
the data in a table by DML statements. It also allows statements to be grouped
together into logical transactions.

COMMIT command
 COMMIT command is used to permanently save any transaction into the database.
 When we use any DML command like INSERT, UPDATE or
DELETE, the changes made by these commands are not permanent,
until the current session is closed, the changes made by these
commands can be rolled back. 
 To avoid that, we use the COMMIT command to mark the changes as permanent.
 Following is commit command's syntax,

 COMMIT;

ROLLBACK command
 This command restores the database to last commited state. It is also used with
SAVEPOINT command to jump to a savepoint in an ongoing transaction.
 If we have used the UPDATE command to make some changes into the database, and
realize that those changes were not required, then we can use the ROLLBACK command
to rollback those changes, if they were not committed using the COMMIT command.
 Following is rollback command's syntax,
ROLLBACK TO savepoint_name;
SAVEPOINT command
 SAVEPOINT command is used to temporarily save a transaction so that you can
rollback to that point whenever required.
 Following is savepoint command's syntax,
SAVEPOINT Savepoint_name;

 In short, using this command we can name the different states of our
data in any table and then rollback to that state using the ROLLBACK
command whenever required.

Using Savepoint and Rollback Following is the table class,

Let’s use some SQL queries on the below table and see the results.
Id Name
1 Abhi
2 Adam
4 Alex

INSERT INTO class VALUES(5, 'Rahul');

COMMIT;
UPDATE class SET name = 'Abhijit' WHERE id = '5';
SAVEPOINT A;
INSERT INTO class VALUES(6, 'Chris');
SAVEPOINT B;
INSERT INTO class VALUES(7, 'Bravo');
SAVEPOINT C;

NOTE: SELECT statement is used to show the data stored in the table. The resultant table will
SELECT * FROM class;
look like,
Id Name
1 Abhi
2 Adam
4 Alex
5 Abhijit
6 Chris
7 Bravo

Now let's use the ROLLBACK command to roll back the state of data to the savepoint B.

ROLLBACK TO B;

SELECT * FROM class;

Now our class table will look like,

 Id Name
1 Abhi
2 Adam
4 Alex
5 Abhijit
6 Chris

Now let's again use the ROLLBACK command to roll back the state of data to
the savepoint A

ROLLBACK TO A;

SELECT * FROM class;

Now the table will look like,

Id Name
1 Abhi
2 Adam
4 Alex
5 Abhijit

RELATIONAL ALGEBRA: FUNDAMENTAL OPERATIONS

Relational database systems are expected to be equipped with a query language that can assist
its users to query the database instances. There are two kinds of query languages − relational
algebra and relational calculus.

Relational Algebra

 Relational algebra is a procedural query language, which takes instances of relations as

input and yields instances of relations as output. It uses operators to perform queries.
An operator can be either unary or binary. They accept relations as their input and
yield relations as their output.

 Relational algebra is performed recursively on a relation and intermediate results are

also considered relations.
The fundamental operations of relational algebra are as follows −
 Select
 Project
 Union
 Set different
 Cartesian product
  Rename
We will discuss all these operations in the following sections.

Select Operation (σ)

It selects tuples that satisfy the given predicate from a relation.
Notation − σp(r)
Where σ stands for selection predicate and r stands for relation. p is prepositional logic
formula which may use connectors like and, or, and not. These terms may use relational
operators like − =, ≠, ≥, < , >, ≤.
For example −
σsubject = "database"(Books)
Output − Selects tuples from books where subject is 'database'.
σsubject = "database" and price = "450"(Books)

Output − Selects tuples from books where subject is 'database' and 'price' is 450.
σsubject = "database" and price = "450" or year > "2010"(Books)
Output − Selects tuples from books where subject is 'database' and 'price' is 450
or those books published after 2010.

Project Operation (∏)

It projects column(s) that satisfy a given predicate.
Notation − ∏A1, A2, An (r)
Where A1, A2 , An are attribute names of relation r.
Duplicate rows are automatically eliminated, as relation is a set.
For example −
∏subject, author (Books)

Selects and projects columns named as subject and author from the relation
Books.

Union Operation (∪)

It performs binary union between two given relations and is defined as −

r ∪ s = { t | t ∈ r or t ∈ s}
Notation − r U s
Where r and s are either database relations or relation result set (temporary relation).
For a union operation to be valid, the following conditions must hold −

 r, and s must have the same number of attributes.

 Attribute domains must be compatible.
 Duplicate tuples are automatically eliminated.
∏ author (Books) ∪ ∏ author (Articles)
Output − Projects the names of the authors who have either written a book or an article or
 both.

Set Difference (−)

The result of set difference operation is tuples, which are present in one relation
but are not in the second relation.
Notation − r − s
Finds all the tuples that are present in r but not in s.
∏ author (Books) − ∏ author (Articles)
Output − Provides the name of authors who have written books but not articles.

Cartesian Product (Χ)

Combines information of two different relations into one.

Notation − r Χ s
Where r and s are relations and their output will be defined as −

r Χ s = { q t | q ∈ r and t ∈ s}
σauthor = 'tutorialspoint'(Books Χ Articles)
Output − Yields a relation, which shows all the books and articles written by tutorialspoint.

Rename Operation (ρ)

The results of relational algebra are also relations but without any name. The
rename operation allows us to rename the output relation. 'Rename' operation
is denoted with small Greek letter rho ρ.
Notation − ρ x (E)

Where the result of expression E is saved with name of x.

Additional operations are −
 Set intersection
 Assignment
 Natural join

FUNCTIONAL DEPENDENCY

A functional dependency occurs when the value of one attribute (or a set of attributes) uniquely
determines the value of another attribute. This relationship is denoted as:
X→Y
Here, X is the determinant, and Y is the dependent attribute. This means that for each unique
value of X, there is precisely one corresponding value of Y.

• The functional dependency is a relationship that exists between two attributes. It

typically exists between the primary key and non-key attribute within a table.
• X → Y
• The left side of FD is known as a determinant, the right side of the
production is known as a dependent.
 For example:
• Assume we have an employee table with attributes: Emp_Id, Emp_Name,
• Here Emp_Id attribute can uniquely identify the Emp_Name attribute of employee
table because if we know the Emp_Id, we can tell that employee name associated
with it.
• Functional dependency can be written as:
• Emp_Id → Emp_Name
• We can say that Emp_Name is functionally dependent on Emp_Id.

1. Trivial Functional Dependency

In Trivial Functional Dependency, a dependent is always a subset of the determinant. i.e. If X
→ Y and Y is the subset of X, then it is called trivial functional dependency
Example:

roll_no Name age

42 Abc 17

43 Pqr 18

44 Xyz 18

Here, {roll_no, name} → name is a trivial functional dependency, since the

dependent name is a subset of determinant set {roll_no, name}. Similarly, roll_no →
roll_no is also an example of trivial functional dependency.

2. Non-trivial Functional Dependency

In Non-trivial functional dependency, the dependent is strictly not a subset of the determinant.
i.e. If X → Y and Y is not a subset of X, then it is called Non-trivial functional dependency.

Example:

roll_no name Age

42 Abc 17

43 Pqr 18

44 Xyz 18

Here, roll_no → name is a non-trivial functional dependency, since the dependent name is not
a subset of determinant roll_no. Similarly, {roll_no, name} → age is also a non-trivial
functional dependency, since age is not a subset of {roll_no, name}