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:

SQL Statements
Show databases;
Create database db_name;
Use db_name;
Select database();
Creating Table
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
);

CREATE TABLE Persons (

PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Select * from table_nm
Insert into tn_name values(v1,v2,v3,v4,v5)

SELECT column1, column2, ...

FROM table_name
WHERE condition;
SELECT * FROM Customers
WHERE Country='Mexico';
SQL Create Constraints
Syntax
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);

CREATE TABLE Orders (

OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
create table pp (mks int,rno int,foreign key(rno) references mm(rno));
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);

CREATE INDEX idx_lastname

ON Persons (LastName);
Select * from where condn and
Select f1,f2,f3 from where condn and
Update table nm
Delete from tn;
Drop table tn;
Truncate table tn;

SELECT * from tutorials_tbl -> WHERE tutorial_author LIKE '%jay';

select * from mm where sname like 'a%';
select * from mm where sname like 'a*';
SELECT * from tutorials_tbl ORDER BY tutorial_author ASC
UPDATE tutorials_tbl -> SET tutorial_title = 'Learning JAVA' -> WHERE tutorial_id = 3;
ALTER TABLE testalter_tbl DROP i;
ALTER TABLE testalter_tbl ADD i INT;
ALTER TABLE testalter_tbl RENAME TO alter_tbl;
SELECT * FROM tcount_tbl WHERE tutorial_count = NULL;
SQL Commands
SQL commands are instructions. It is used to communicate with the database. It is also used to perform
specific tasks, functions, and queries of data.
SQL can perform various tasks like create a table, add data to tables, drop the table, modify the table, set
permission for users.

Types of SQL Commands

There are five types of SQL commands: DDL, DML, DCL, TCL, and DQL.
 1. Data Definition Language (DDL)
DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
All the command of DDL are auto-committed that means it permanently save all the changes in the
database.
Here are some commands that come under DDL:
CREATE
ALTER
DROP
TRUNCATE

• Create table tname(c1 dt1,c2,dt2,c3 dt3);

• ALTER TABLE Customers
ADD Email varchar(255);
• ALTER TABLE table_name
DROP COLUMN column_name;
• ALTER TABLE table_name
ALTER COLUMN column_name datatype;
• ALTER TABLE Persons
ADD DateOfBirth date;

DROP TABLE table_name;

TRUNCATE TABLE table_name;

DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
DELETE FROM table_name;

3. Data Control Language

DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:
Grant
Revoke

GRANT SELECT ON employees TO bob@localhost;

GRANT INSERT, UPDATE, DELETE ON employees TO bob@localhost;
GRANT DELETE ON classicmodels.employees TO bob@localhsot;
 GRANT INSERT ON classicmodels.* TO bob@localhost;

REVOKE INSERT, UPDATE ON classicmodels.* FROM rfc@localhost;

REVOKE INSERT ON *.* FROM 'jeffrey'@'localhost';
REVOKE 'role1', 'role2' FROM 'user1'@'localhost', 'user2'@'localhost';
REVOKE SELECT ON world.* FROM 'role3';
4. Transaction Control Language
TCL commands can only use with DML commands like INSERT, DELETE and UPDATE only.
These operations are automatically committed in the database that's why they cannot be used
while creating tables or dropping them.
Here are some commands that come under TCL:
COMMIT
ROLLBACK
SAVEPOINT
5. Data Query Language
DQL is used to fetch the data from the database.
It uses only one command:
SELECT

a. SELECT: This is the same as the projection operation of relational algebra. It is used to select
the attribute based on the condition described by WHERE clause

b. Views in SQL

c. Views in SQL are considered as a virtual table. A view also contains rows and columns.

d. To create the view, we can select the fields from one or more tables present in the database.

e. A view can either have specific rows based on certain condition or all the rows of a table.

f. CREATE VIEW DetailsView AS

g. SELECT NAME, ADDRESS

h. FROM Student_Details

i. WHERE STU_ID < 4;

j. SELECT * FROM DETAILSVIEW

k. CREATE OR REPLACE VIEW [Brazil Customers] AS
 SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';
l. ALTER salesOrders AS SELECT orderNumber, customerNumber, productCode,
quantityOrdered, priceEach, status FROM orders
m. DROP VIEW view_name;
LOGICAL DATABASE DESIGN
n. Logical database design is the process of deciding how to arrange the attributes of the entities
in a given business environment into database structures, such as the tables of a relational
database. The goal of logical database design is to create well structured tables that properly
reflect the company's business environment. The tables will be able to store data about the
company's entities in a non-redundant manner and foreign keys will be placed in the tables so
that all the relationships among the entities will be supported. Physical database design,
which will be treated in the next chapter, is the process of modifying the logical database
design to improve performance.
o. Relational Algebra
p. 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.

q. The fundamental operations of relational algebra are as follows −

r. Select
s. Project
t. Union
u. Set different
v. Cartesian product
w. Rename

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
Relational Calculus
In contrast to Relational Algebra, Relational Calculus is a non-procedural
query language, that is, it tells what to do but never explains how to do it.

Relational calculus exists in two forms −

Tuple Relational Calculus (TRC)
Filtering variable ranges over tuples

Notation − {T | Condition}

Returns all tuples T that satisfies a condition.

For example −

{ T.name | Author(T) AND T.article = 'database' }

Output − Returns tuples with 'name' from Author who has written article on
'database'.

TRC can be quantified. We can use Existential (∃) and Universal

Quantifiers (∀).

For example −

{ R| ∃T ∈ Authors(T.article='database' AND R.name=T.name)}

Output − The above query will yield the same result as the previous
one.
Domain Relational Calculus (DRC)
In DRC, the filtering variable uses the domain of attributes instead of
entire tuple values (as done in TRC, mentioned above).

Notation −

{ a1, a2, a3, ..., an | P (a1, a2, a3, ... ,an)}

Where a1, a2 are attributes and P stands for formulae built by inner
attributes.

For example −

{< article, page, subject > | ∈ TutorialsPoint 𝖠 subject = 'database'}

Output − Yields Article, Page, and Subject from the relation

TutorialsPoint, where subject is database.
Just like TRC, DRC can also be written using existential and universal
quantifiers. DRC also involves relational operators.

The expression power of Tuple Relation Calculus and Domain Relation

Calculus is equivalent to Relational Algebra.
Integrity Constraints

 Constraint describes conditions that every legal instance of a relation must satisfy.

 Inserts/deletes/updates that violate ICs are disallowed.

 Can be used to :

• ensure application

semantics e.g., sid is a key

• prevent

inconsistencies e.g., sname

has to be a string,

age must be < 200;

Types of Integrity Constraints

 Fundamental:

• Domain constraints,

• Primary key constraints,

• Foreign key constraints

• Plus NOT NULL, UNIQUE, etc.

 General:

• Check constraints (often limited to only simple ones)

• Table constraints or assertions (but not available in

most systems)

Check or Table Constraints

CREATE TABLE

Sailors ( sid

INTEGER,

sname CHAR(10),

rating INTEGER,

age REAL,

PRIMARY KEY

(sid),

CHECK ( rating >= 1

AND rating <= 10 ))

 Can use queries to express constraint.

 VERY LIMITED IN MOST DBMSs!!!!

Explicit Domain Constraints

CREATE DOMAIN values-of-ratings INTEGER

DEFAULT 1

CHECK ( VALUE >= 1 AND VALUE <= 10)

CREATE TABLE

Sailors ( sid

INTEGER,

sname CHAR(10),
 rating values-of-ratings,

age REAL,

PRIMARY KEY (sid))

More Powerful Table Constraints

 Constraint that Interlake boats cannot be reserved:

CREATE TABLE

Reserves ( sname

CHAR(10),

bid INTEGER,

day DATE,

PRIMARY KEY (bid,day),

CONSTRAINT noInterlakeRes

CHECK (`Interlake’ <>

( SELECT B.bname

FROM Boats B

WHERE B.bid=

bid)))

If condition evaluates to FALSE, update is rejected

Table Constraints

Associated with one table

 Only needs to hold TRUE when table is non-empty

More Table Constraint Examples

Number of boats plus number of sailors is < 100

CREATE TABLE

Sailors ( sid

INTEGER,

sname CHAR(10),

rating INTEGER,

age REAL,

PRIMARY KEY

(sid), CHECK

( (SELECT COUNT (S.sid) FROM Sailors S)

+ (SELECT COUNT (B.bid) FROM Boats B)

< 100 )

 Symmetric constraint, yet associated with Sailors.

 If Sailors is empty, the number of Boats tuples can be anything!

  Trigger: A procedure that starts automatically if specified
changes occur to the DBMS

Analog to a "daemon" that monitors a database for certain events to

occur

Triggers : Make Database Active

 Three parts:

 Event (activates the trigger)

 Condition (tests whether the triggers should run) [Optional]

 Action (what happens if the trigger runs)

 Semantics:

 When an event occurs,

 And this condition is satisfied,

 Then the associated action is performed.

Triggers – Event,Condition,Action

 Events could be :

BEFORE|AFTER INSERT|UPDATE|DELETE ON <tableName>

e.g.: BEFORE INSERT ON Professor

 Condition is an SQL expression (An SQL

query with a non-empty result means TRUE.)

 Action can be many different choices :

 SQL statements , body of PSM, and even DDL and transaction-
oriented statements like
“commit”.

Example Trigger

Assume our DB has a relation

schema : Professor (pNum,

pName, salary)

We want to write a trigger that :

Ensures that any new professor

inserted has salary >= 90,000;

Example Trigger

CREATE TRIGGER minSalary BEFORE INSERT ON Professor

for what context ?

BEGIN

check for violation here ?

END;
SET Operations in SQL
SQL supports few Set operations which can be performed on the table data.
These are used to get meaningful results from data stored in the table,
under different special conditions.
In this tutorial, we will cover 4 different types of SET operations,
along with example:

1. UNION

2. UNION ALL

3. INTERSECT

4. MINUS

UNION Operation
UNION is used to combine the results of two or more SELECT
statements. However it will eliminate duplicate rows from its resultset.
In case of union, number of columns and datatype must be same in
both the tables, on which UNION operation is being applied.

Example of UNION
The First table,
ID Name

1 abhi

2 adam

The Second table,

ID Name

2 adam

3 Chester

Union SQL query will be,

SELECT * FROM First
UNION
SELECT * FROM Second;

The resultset table will look like,

ID NAME

1 abhi

2 adam

3 Chester
UNION ALL
This operation is similar to Union. But it also shows the duplicate rows.

Example of Union All

The First table,

ID NAME

1 abhi

2 adam

The Second table,

ID NAME

2 adam

3 Chester

Union All query will be like,

SELECT * FROM First
UNION ALL
SELECT * FROM Second;
The resultset table will look like,

ID NAME

1 abhi

2 adam

2 adam

3 Chester

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.
NOTE: MySQL does not support INTERSECT operator.

Example of Intersect
The First table,
ID NAME

1 abhi

2 adam

The Second table,

ID NAME

2 adam

3 Chester

Intersect query will be,

SELECT * FROM First
INTERSECT
SELECT * FROM Second;

The resultset table will look like

ID NAME

2 adam

MINUS
The Minus operation combines results of two SELECT statements and return
only those in the final result, which belongs to the first set of the result.
Example of Minus
The First table,

ID NAME

1 abhi

2 adam

The Second table,

ID NAME

2 adam

3 Chester

Minus query will be,

SELECT * FROM First
MINUS
SELECT * FROM Second;

The resultset table will look like,

ID NAME

1 abhi
The SQL EXCEPT clause/operator is used to combine two SELECT
statements and returns rows from the first SELECT statement that are
not returned by the second SELECT statement. This means EXCEPT
returns only rows, which are not available in the second SELECT
statement.

Just as with the UNION operator, the same rules apply when using the
EXCEPT operator. MySQL does not support the EXCEPT operator.
Syntax
The basic syntax of EXCEPT is as follows.

SELECT column1 [, column2 ]

FROM table1 [, table2 ]
[WHERE condition]

EXCEPT

SELECT column1 [, column2 ]

FROM table1 [, table2 ]
[WHERE condition]

Here, the given condition could be any given expression based on your requirement.
Example
Consider the following two tables.

Table 1 − CUSTOMERS Table is as follows.

++++++

| ID | NAME | AGE | ADDRESS| SALARY|

++++++

| 1 | Ramesh | 32 | Ahmedab | 2000.0 |

ad 0
| 2 | Khilan | 25 | Delhi | 1500.0 |
0
| 3 | kaushik | 23 | Kota | 2000.0 |
0
| 4 | Chaitali | 25 | Mumbai | 6500.0 |
0
| 5 | Hardik | 27 | Bhopal | 8500.00 |

| 6 | Komal | 22 | MP | 4500.0 |
0
 | 7 | Muffy | 24 | Indore | 10000.00 |

++++++

Table 2 − ORDERS table is as follows.

+++++

|OID | DATE | CUSTOMER_ID | AMOUNT |

+++++

| 10 | 2009-10- 00:00:0 | 3 | 3000 |

2 08 0
| 10 | 2009-10- 00:00:0 | 3 | 1500 |
0 08 0
| 10 | 2009-11- 00:00:0 | 2 | 1560 |
1 20 0
+++++
| 10 | 2008-05- 00:00:0 | 4 | 2060 |
3 20 0

Now, let us join these two tables in our SELECT statement as shown
below.

SQL> SELECT ID, NAME, AMOUNT, DATE

FROM CUSTOMERS

LEFT JOIN

ORDERS

ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

EXCEPT

SELECT ID, NAME, AMOUNT,

DATE FROM CUSTOMERS

RIGHT JOIN ORDERS

This would produce the following result.

+++++
| ID | NAME| AMOUNT | DATE|
+++++
| 1 | Ramesh |NULL | NULL|
| 5 | Hardik |NULL | NULL|
| 6 | Komal|NULL | NULL |
| 7 | Muffy|NULL | NULL |
+++++