UNIT-III

Syllabus
SQL: Queries, Constraints, Triggers: Form of
Basic SQL Query, UNION, INTERSECT, and
EXCEPT, Nested Queries, Aggregate
Operators, NULL values, Natural JOINS,
Complex Integrity Constraints in SQL,
Triggers and Active Data bases..
Schema Refinement and Normal Forms:
Introduction to Schema Refinement,
Functional Dependencies - Reasoning about
FDs, Normal Forms, Properties of
Decompositions, Normalization, Schema
Refinement in Database Design, Other Kinds
SQL
SQL: The Query Language

Life is just a bowl of queries.

What is a Query?
When we need to extract information from the Database, we ask a
question, or Query to the DBMS.

A database query is a request for data from a database. Usually the

request is to retrieve data; however, data can also be manipulated
using queries.
For e.g. to fetch the employee name from the database table
EMPLOYEE, we write the SQL Query like this:

SELECT employee_name from EMPLOYEE;

Structured Query Language
Structured Query Language (SQL) is the
standard language for accessing
information in a database.
SQL is case-insensitive and free format.
Enter commands interactively or in a script
file.
SQL statements can use multiple lines
sql>
endUSE world;
each statement with a semi-colon
SQL statements ;
end with semi-
database changed. colon.
sql> SHOW tables;
sql> SHOW columns FROM city;
sql> DESCRIBE country;
Data Types in SQL
 Characters:
 CHAR(20) -- fixed length
 VARCHAR(40) -- variable length
 Numbers:
 NUMERIC,BIGINT, INT, SMALLINT,
TINYINT
 REAL, FLOAT -- differ in precision
 MONEY
 Times and dates:
 DATE
 DATETIME -- SQL Server
 Others... All are simple
Exercise: Is SQL Case Sensitivity?

mysql> DESCRIBE city;

mysql> describe city;

mysql> use world;

mysql> use WORLD;
mysql> describe city;
mysql> describe City;
mysql>select*from student;
mysql>SELECT*FROM STUDENT;
What’s contained in an SQL Query?
Basic SQL Query
SELECT target-list
FROM relation-list
WHERE qualification

Every SQL Query must have:

SELECT clause: specifies columns to be
retained in result
FROM clause: specifies selection
conditions on the tables mentioned in the
FROM clause
 WHERE CLAUSE
The WHERE clause is used to filter records.
The WHERE clause is used to extract only
those records that fulfill a specified condition.
WHERE Syntax

SELECT column1, column2, ...

FROM table_name
WHERE condition;
Example
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 AHMEDABA 2000.00
D
2 Khilan 25 DELHI 1500.00
3 Kaushik 23 KOTA 2000.00
4 Chaitali 25 MUMBAI 6500.00
5 Hardik 27 BHOPAL 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 INDORE 10000.00
SQL> SELECT ID, NAME, SALARY FROM
CUSTOMERS WHERE SALARY > 2000;

ID | NAME | SALARY

4 | Chaitali | 6500.00 |
5 | Hardik | 8500.00 |
6 | Komal | 4500.00 |
7 | Muffy | 10000.00 |
 Table name Attribute names

Tables in SQL
Product

PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks

Powergizmo $29.99 Gadgets GizmoWorks

SingleTouch $149.99 Photography Canon

MultiTouch $203.99 Household Hitachi

Tuples or rows
The schema of a table is the table name and
its attributes:
Product(PName, Price, Category,
Manfacturer)

A key is an attribute whose values are

unique;
we underline a key

Product(PName, Price, Category,

Manfacturer)
 SQL Query

SELECT
SELECT attributes
attributes
FROM
FROM relations
relations(possibly
(possibly
multiple)
multiple)
WHERE
WHERE conditions
conditions(selections)
(selections)
 Simple SQL Query
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT
SELECT **
FROM
FROM Product
Product
WHERE
WHERE category=‘Gadgets’
category=‘Gadgets’

PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
“selection”
 Simple SQL Query
Product PName Price Category Manufacturer
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

SELECT
SELECT PName,
PName,Price,
Price,Manufacturer
Manufacturer
FROM
FROM Product
Product
WHERE
WHERE Price
Price>
>100
100

PName Price Manufacturer

“selection” and SingleTouch $149.99 Canon
“projection” MultiTouch $203.99 Hitachi
 A Notation for SQL Queries
Input Schema

Product(PName, Price, Category, Manfactu

SELECT
SELECT PName,
PName,Price,
Price,Manufacturer
Manufacturer
FROM
FROM Product
Product
WHERE
WHERE Price
Price>
>100
100

Answer(PName, Price, Manfacturer)

Output Schema
Eliminating Duplicates
Category
SELECT
SELECT DISTINCT
DISTINCTcategory
category Gadgets
FROM
FROM Product
Product Photography
Household

Compare to:

Category
Gadgets
SELECT
SELECT category
category Gadgets
FROM
FROM Product
Product Photography
Household
 Ordering the Results
SELECT
SELECT pname,
pname,price,
price,manufacturer
manufacturer
FROM
FROM Product
Product
WHERE
WHERE price
price>
>5050
ORDER
ORDERBY
BY pname;
pname;

Ordering is ascending, unless you specify the DESC keyword.

SELECT pname, price, manufacturer

FROM Product
WHERE price > 50
ORDER BY pname DESC;
Ordering the Results
SELECT
SELECT category
category
FROM
FROM Product
Product
ORDER
ORDERBY
BY pname
pname

Pname Price Category Manufacturer

?
Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi
Ordering the Results
Category
SELECT
SELECT DISTINCT
DISTINCTcategory
category Gadgets
FROM
FROM Product
Product Household
ORDER
ORDERBY
BYcategory
category
Photography

Compare to:

?
SELECT
SELECT category
category
FROM
FROM Product
Product
ORDER
ORDERBY
BYpname
pname
Joins in SQL
Connect two or more tables:

Product PName Price Category Manufacturer

Gizmo $19.99 Gadgets GizmoWorks
Powergizmo $29.99 Gadgets GizmoWorks
SingleTouch $149.99 Photography Canon
MultiTouch $203.99 Household Hitachi

Company Cname StockPrice Country

GizmoWorks 25 USA
What is
the connection Canon 65 Japan
between
them ? Hitachi 15 Japan
Joins
Product (pname, price, category, manufacturer)
Company (cname, stockPrice, country)

Find all products under $200 manufactured in Japan;

return their names and prices.
Join
between Product
and Company
SELECT
SELECT pname,
pname,price
price
FROM
FROM Product,
Product,Company
Company
WHERE
WHERE manufacturer=cname
manufacturer=cnameAND
ANDcountry=‘Japan’
country=‘Japan’
AND
ANDprice
price<=<=200
200
 Joins in SQL
Product
Company
PName Price Category Manufacturer
Cname StockPrice Country
Gizmo $19.99 Gadgets GizmoWorks
GizmoWorks 25 USA
Powergizmo $29.99 Gadgets GizmoWorks
Canon 65 Japan
SingleTouch $149.99 Photography Canon
Hitachi 15 Japan
MultiTouch $203.99 Household Hitachi

SELECT
SELECT pname,
pname,price
price
FROM
FROM Product,
Product,Company
Company
WHERE
WHERE manufacturer=cname
manufacturer=cnameAND
AND
country=‘Japan’
country=‘Japan’ PName Price
AND
ANDprice
price<=
<=200
200 SingleTouch $149.99
Joins
Product (pname, price, category, manufacturer)
Company (cname, stockPrice, country)

Find all countries that manufacture some product in the

‘Gadgets’ category.

SELECT
SELECT country
country
FROM
FROM Product,
Product,Company
Company
WHERE
WHERE manufacturer=cname
manufacturer=cnameAND
ANDcategory=‘Gadgets’
category=‘Gadgets’
 Joins in SQL
Product
Company
Name Price Category Manufacturer
Cname StockPrice Country
Gizmo $19.99 Gadgets GizmoWorks
GizmoWorks 25 USA
Powergizmo $29.99 Gadgets GizmoWorks
Canon 65 Japan
SingleTouch $149.99 Photography Canon
Hitachi 15 Japan
MultiTouch $203.99 Household Hitachi

SELECT
SELECT country
country
FROM
FROM Product,
Product,Company
Company
WHERE
WHERE manufacturer=cname
manufacturer=cnameAND
ANDcategory=‘Gadgets’
category=‘Gadgets’

Country
??
??
Disambiguating Attributes
Sometimes two relations have the same
attr:
Person(pname, address, worksfor)
Company(cname, address)
Which
SELECT DISTINCT address ?
SELECT DISTINCTpname,
pname,address
address
FROM
FROM Person,
Person,Company
Company
WHERE
WHERE worksfor
worksfor= =cname
cname

SELECT
SELECT DISTINCT
[Link],
[Link],[Link]
[Link]
FROM
FROM Person,
Person,Company
Company
WHERE
WHERE [Link]
[Link]=
=[Link]
[Link]
 Example Database
Sailors Boats

sid sname ratin age bid bname color

g 101 Nina red
1 Fred 7 22 102 Pinta blue
2 Jim 2 39 103 Santa red
3 Nancy 8 27 Maria

Reserves

sid bid day

1 102 9/12
2 102 9/13
 The SQL DDL
CREATE TABLE Sailors (sid INTEGER,
sname CHAR(20), rating INTEGER, age REAL,
PRIMARY KEY sid)

CREATE TABLE Boats (bid INTEGER,

bname CHAR (20), color CHAR(10)
PRIMARY KEY bid)

CREATE TABLE Reserves (sid INTEGER,

bid INTEGER, day DATE,
PRIMARY KEY (sid, bid, date),
FOREIGN KEY sid REFERENCES Sailors,
FOREIGN KEY bid REFERENCES Boats)
 The SQL DML Sailors
sid sname ratin age
g
1 Fred 7 22
Find all 18-year-old sailors:
2 Jim 2 39
3 Nancy 8 27
SELECT *
FROM Sailors S
WHERE [Link]=18

• To find just names and ratings, replace the first lin

SELECT [Link], [Link]

 Querying Multiple Relations
SELECT [Link]
FROM Sailors S, Reserves R
WHERE [Link]=[Link] AND [Link]=102

Sailors Reserves

sid bid day

sid sname ratin age
g 1 102 9/12
1 Fred 7 22 2 102 9/13
2 Jim 2 39
3 Nancy 8 27
Table Definitions
We will be using the following relations in our
examples:

Sailors(sid:integer, sname:string,
rating:integer, age:real)
Boats(bid:integer, bname:string,
color:string)
Reserves(sid:integer, bid:integer, day:date)
Relation Instances…1
An Instance of Sailors

sid sname rating age

22 Dustin 7 45.0

29 Brutus 1 33.0

31 Lubber 8 55.5

32 Andy 8 25.5

58 Rusty 10 35.0

64 Horatio 7 35.0

71 Zorba 10 16.0

74 Horatio 9 35.0

85 Art 3 25.5

95 Bob 3 63.5
Relation Instances…2
An Instance of Reserves

sid bid day

22 101 10/10/9
8
22 102 10/10/9
8
22 103 10/08/9
8
22 104 10/07/9
8
31 102 11/10/9
8
31 103 11/06/9
8
31 104 11/12/9
8
64 101 09/05/9
8
64 102 09/08/9
8
74 103 09/08/9
8
Relation Instances…3
An Instance of Boats

bid bname color

101 Interlake blue

102 Interlake red

103 Clipper gree

n
104 Marine red
Simple SQL Query

Find the names and ages of all

sailors

SELECT [Link], [Link]

FROM Sailors S
 Result of Previous Query
SELECT [Link], [Link]
FROM Sailors S
sname age

Dustin 45.0

Brutus 33.0

Lubber 55.5

Andy 25.5 Duplicate Results

Rusty 35.0

Horatio 35.0

Zorba 16.0

Horatio 35.0

Art 25.5

Bob 63.5
Preventing Duplicate Tuples in Result
Use the DISTINCT keyword in the SELECT
clause:

SELECT DISTINCT [Link], [Link]

FROM Sailors S
Results of Original Query without
Duplicates

sname age
Dustin 45.0
Brutus 33.0
Lubber 55.5
Andy 25.5
Appears only once
Rusty 35.0
Horatio 35.0
Zorba 16.0
Art 25.5
Bob 63.5
Union, Intersect, and Except
 SQL provides three set-manipulation
constructs that extend the basic query
form presented earlier.
 Union ()
 Intersection ()
 Except ()
(many systems recognize the keyword MINUS for
EXCEPT)
 UNION Operator

The SQL UNION operator is used to combine

the result sets of 2 or more SELECT
statements. It removes duplicate rows
between the various SELECT statements.
Each SELECT statement within the UNION
must have the same number of fields in the
result sets with similar data type
The syntax for the UNION operator in SQL is:
SELECT expression1, expression2, ...
expression_n FROM tables [WHERE
conditions]
UNION
SELECT expression1, expression2, ...
expression_n FROM tables [WHERE
conditions];
 INTERSECT Operator

The SQL INTERSECT operator is used to

return the results of 2 or more SELECT
statements. However, it only returns the rows
selected by all queries or data sets. If a
record exists in one query and not in the
other, it will be omitted from the INTERSECT
results.
Syntax
The syntax for the INTERSECT operator in
SQL is:
SELECT expression1, expression2, ...
expression_n FROM tables [WHERE
conditions]
 INTERSECT
 SELECT expression1, expression2, ...
expression_n FROM tables [WHERE
conditions];
 EXCEPT Operator

The SQL Server (Transact-SQL) EXCEPT

operator is used to return all rows in the first
SELECT statement that are not returned by
the second SELECT statement.
Each SELECT statement will define a dataset.
The
EXCEPT operator will retrieve all records
from the first dataset and then remove from
the results all records from the second
dataset.
Syntax
The syntax for the EXCEPT operator in SQL
Server (Transact-SQL) is:
SELECT expression1, expression2, ...
expression_n FROM tables [WHERE
conditions]
 EXCEPT
 SELECT expression1, expression2, ...
expression_n FROM tables [WHERE
conditions];
 sailors sid sname rating age
Boats bid bname color
Reserves sid bid day

Example: Find sid’s of sailors who’ve reserved a red or a green boat

SELECT [Link]
FROM Sailors S, Boats B, Reserves R
WHERE [Link]=[Link] AND [Link]=[Link]
AND ([Link]=‘red’ OR [Link]=‘green’)

SELECT [Link]
FROM Sailors S, Boats B, Reserves R
WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=‘red’
UNION
SELECT [Link]
FROM Sailors S, Boats B, Reserves R
WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=‘green’
 sailors sid sname rating age
Boats bid bname color
Reserves sid bid day

Example: Find sid’s of sailors who’ve reserved a red and a green boat

SELECT [Link]
FROM Sailors S, Boats B, Reserves R,
WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=[Link] AND [Link]=[Link]
AND ([Link]=‘red’ AND [Link]=‘green’)

SELECT [Link]
FROM Sailors S, Boats B, Reserves R
WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=‘red’
INTERSECT
SELECT [Link]
FROM Sailors S, Boats B, Reserves R
WHERE [Link]=[Link] AND [Link]=[Link]
AND [Link]=‘green’
 sailors sid sname rating age
Boats bid bname color
Reserves sid bid day

Example: Find sid’s of all sailors who’ve reserved red boat but not green
boat.
SELECT [Link]
FROM Sailors S, Boats B, Reserves
Indeed, since the
R
Reserves relation contains WHERE [Link]=[Link] AND
sid information, there is [Link]=[Link]
no need AND [Link]=‘red’
to look at the Sailors EXCEPT
relation. SELECT [Link]
FROM Sailors S, Boats B, Reserves
R
SELECT [Link]
WHERE [Link]=[Link] AND
FROM Boats B, Reserves R
[Link]=[Link]
WHERE [Link]=[Link] AND
AND [Link]=‘green’
[Link]=‘red’
EXCEPT
SELECT [Link]
FROM Boats B, Reserves R
WHERE [Link]=[Link] AND
[Link]=‘green’
Nested Queries
A Subquery or Inner query is a query within another SQL
query and embedded within the WHERE clause.
Subqueries generally occur within the WHERE clause (but
can also appear within the FROM and HAVING clauses)

Nested queries are a very powerful feature of SQL. They

help us to write short and efficient queries.
Syntax:

SELECT column-names
FROM table-name1
WHERE value IN (SELECT column-name
FROM table-name2
WHERE condition)
mple: Find names of sailors who’ve reserved boat #1
SELECT [Link]
FROM Sailors S, Reserves R
WHERE [Link]=[Link] AND [Link]=103
alternative:
SELECT [Link]
FROM Sailors S
WHERE [Link] IN (SELECT [Link]
FROM Reserves R
WHERE
[Link]=103)
Consider: Find names of sailors who’ve not reserved boat #103:

SELECT [Link] correct answer:

FROM Sailors S, Reserves R SELECT [Link]
WHERE [Link]=[Link] AND [Link]103 FROM Sailors S
WHERE [Link] NOT IN (SELECT [Link]
FROM Reserves R
WHERE
[Link]=103)
Correlated Nested Queries
 In the previous example, the inner
subquery has been completely
independent of the outer query.
 In general, the inner subquery could
depend on the row currently being
examined in the outer query.

 We can make the inner subquery depend

on the outer query. This is called
correlation.
 Example: Find names of sailors who’ve reserved boat #103:

SELECT [Link] EXISTS is another

FROM Sailors S set
WHERE EXISTS (SELECT * comparison
FROM Reserves R operator,
WHERE [Link]=103 AND [Link]=[Link])
which allows us to
Sailors test
Reserves
sid
whether
bid
a setday
is
sid sname rating age
S R 22
nonempty.
103 10/10/9
22 dustin 7 45.0
6
S 31 lubber 8 55.5 R 31 101 11/12/9
103 6
12/12/0
S 58 rusty 10 35.0 R 22
3
58 105 8/21/05
 Example: Find names of sailors with at most one
reservation for boat #103
SELECT [Link]
FROM Sailors S
WHERE EXISTS UNIQUE (SELECT [Link]
FROM Reserves R
WHERE [Link]=103 AND
[Link]=[Link])

EXIST UNIQUE evaluates

to true if the subquery
returns
a relation that contains no
duplicated tuples (empty is
a special case)
UNIQUE operator
When we apply UNIQUE to a subquery, it
returns true if no row is duplicated in the
answer to the subquery.
What would the following SQL query return?

SELECT [Link]
FROM Sailors S
WHERE UNIQUE (SELECT [Link]
FROM Reserves R
WHERE [Link]=103
AND [Link]=[Link])

(All sailors with at most one reservation for boat

Set-comparison Operators
 We’ve already seen IN, EXISTS and UNIQUE.
We can also use NOT IN, NOT EXISTS and NOT
UNIQUE. ,,,,,
 Also available: op ANY, op ALL
 Where op is one of the arithmetic comparison
operator
 SOME is also available, but it is just a synonym for
ANY.
 Example:
SELECT * Find sailors whose rating is
FROM Sailors S
greater
WHERE than
[Link] that
> ANY of some
(SELECT sailor called
[Link]
Horatio: FROM Sailors S2
WHERE [Link]=‘Horatio’)
 sailors sid sname rating age
Boats bid bname color
Reserves sid bid day

Example: Find sailors whose rating is better than every sailor called
Horatio.

SELECT *
FROM Sailors S
WHERE [Link] > ALL (SELECT [Link]
FROM Sailors S2
WHERE [Link]=‘Horatio’)

Example: Find the sailors with the highest rating.

SELECT *
FROM Sailors S
WHERE [Link] >= ALL (SELECT [Link]
FROM Sailors
S2)
Aggregate Operators
 SQL allows the use of arithmetic expressions.
 SQL supports five aggregate operations, which can be applied on any

column of a relation .
 What is aggregation?
 Computing arithmetic expressions, such as Minimum or
Maximum
COUNT([DISTINCT] A) The number of (unique) value in the A
column.
SUM ( [DISTINCT] A) The sum of all (unique) values in the
A column.
AVG ([DISTINCT A) The average of all (unique) values in
the A column.
MAX (A) The maximum value in the A column.

MIN (A) The minimum value in the A column.

Using the COUNT operator
Count the number of sailors

SELECT COUNT (*)

FROM Sailors S
 sailors sid sname rating age
Boats bid bname color
Reserves sid bid day

Example: Count the number of Sailor

SELECT COUNT (*)

FROM Sailors S

Example: Count the number of different sailor names

SELECT COUNT (DISTINCT [Link])

FROM Sailors S
Example of SUM operator
Find the sum of ages of all sailors with a
rating of 10

SELECT SUM ([Link])

FROM Sailors S
WHERE [Link]=10
Example of AVG operator
Find the average age of all sailors with
rating 10

SELECT AVG ([Link])

FROM Sailors S
WHERE [Link]=10
 sailors sid sname rating age
Boats bid bname color
Reserves sid bid day

Example: Find the average age of all sailors

SELECT AVG ([Link])

FROM Sailors S

Example: Find the average age of sailors with rating of 10

SELECT AVG ([Link])

FROM Sailors S
WHERE [Link] = 10
Example of MAX operator
Find the name and age of the oldest sailor

SELECT [Link], MAX([Link])

FROM Sailors S
Correct SQL Query for MAX

SELECT [Link], [Link]

FROM Sailors S
WHERE [Link] = ( SELECT MAX([Link])
FROM Sailors S2 )
 sailors sid sname rating age
Boats bid bname color
Reserves sid bid day

Example: Find the name and age of the oldest sailor

SELECT [Link], MAX ([Link])

FROM Sailors S

SELECT [Link], [Link]

FROM Sailors S
WHERE [Link] =
(SELECT MAX ([Link])
FROM Sailors S)

SELECT [Link], [Link]

Equivalent to the second
FROM Sailors S
query, and is allowed in
WHERE (SELECT MAX ([Link])
the SQL/92 standard, but
FROM Sailors S)
is not supported in some
= [Link]
systems.
 sailors sid sname rating age
Boats bid bname color
Reserves sid bid day

Aggregate operations offer an alternative to the ANY and

ALL constructs.

Example: Find the names of sailors who are older than

the oldest sailor with a rating of 10.

SELECT [Link]
FROM Sailors S
WHERE [Link] > ALL (SELECT [Link]
FROM Sailors S
WHERE [Link] = 10)

Alternative
SELECT [Link]
FROM Sailors S
WHERE [Link] > (SELECT MAX ([Link])
FROM Sailors S
WHERE [Link] = 10)
Example of MIN operators
Select min([Link])from sailors s;
BETWEEN and AND operators

The BETWEEN ... AND operator selects a

range of data between two values.

 These values can be numbers, text, or

dates.
BETWEEN and AND Example
Find the names of sailors whose age is
between 25 and 35

SELECT sname
FROM Sailors
WHERE age BETWEEN 25 AND 35;
 GROUP BY CLAUSE
The SQL GROUP BY clause is used in
collaboration with the SELECT statement to
arrange identical data into groups.
Important Points:
GROUP BY clause is used with the SELECT
statement.
In the query, GROUP BY clause is placed
after the WHERE clause.
In the query, GROUP BY clause is placed
before ORDER BY clause if used any.
Syntax:
SELECT column1, function_name(column2)
FROM table_name WHERE condition GROUP
BY column1, column2;
 function_name: Name of the function used
for example, SUM() , AVG().
table_name: Name of the table.
condition: Condition used.
Employee
Student
Group By single column: Group By single
column means, to place all the rows with
same value of only that particular column in
one group.
Consider the query as shown below:

SELECT NAME, SUM(SALARY) FROM

Employee GROUP BY NAME;
output
Group By multiple columns: Group by
multiple column is say for example, GROUP
BY column1, column2. This means to place
all the rows with same values of both the
columns column1 and column2 in one
group.
 Consider the below query:
SELECT SUBJECT, YEAR, Count(*) FROM
Student GROUP BY SUBJECT, YEAR;
output
 HAVING

HAVING clause is used to specify a search

condition for a group or an aggregate.
Having is used in a GROUP BY clause. If you
are not using GROUP BY clause then you can
use HAVING function like a WHERE clause.
Syntax:
SELECT column1, function_name(column2)
FROM table_name WHERE condition GROUP
BY column1, column2 HAVING condition;
 function_name: Name of the function used
for example, SUM() , AVG().
table_name: Name of the table.
condition: Condition used.
 Example
SELECT NAME, SUM(SALARY) FROM
Employee GROUP BY NAME HAVING
SUM(SALARY)>3000;
 ORDER BY

The ORDER BY clause sorts the result-set in

ascending or descending order.
It sorts the records in ascending order by
default. DESC keyword is used to sort the
records in descending order.
Syntax:
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1, column2... ASC|DESC;
Where
ASC: It is used to sort the result set in
ascending order by expression.
DESC: It sorts the result set in descending
order by expression.
 Example
 +----+----------+-----+-----------+----------+
 | ID | NAME | AGE | ADDRESS | SALARY |
 +----+----------+-----+-----------+----------+
 | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
 | 2 | Khilan | 25 | Delhi | 1500.00 |
 | 3 | kaushik | 23 | Kota | 2000.00 |
 | 4 | Chaitali | 25 | Mumbai | 6500.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00 |
 | 6 | Komal | 22 | MP | 4500.00 |
 | 7 | Muffy | 24 | Indore | 10000.00 |
 +----+----------+-----+-----------+----------+

 SQL> SELECT * FROM CUSTOMERS ORDER BY NAME;

 Output
+----+----------+-----+-----------+----------+
| ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
+----+----------+-----+-----------+----------+
SQL> SELECT * FROM CUSTOMERS ORDER BY
NAME DESC;
+----+----------+-----+-----------+----------+ |
 ID | NAME | AGE | ADDRESS | SALARY |
 +----+----------+-----+-----------+----------+ |
 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
| 7 | Muffy | 24 | Indore | 10000.00 |
| 6 | Komal | 22 | MP | 4500.00 |
| 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00 |
 | 4 | Chaitali | 25 | Mumbai | 6500.00
| +----+----------+-----+-----------+----------+
 Null Values
The SQL NULL is the term used to represent
a missing value. A NULL value in a table is a
value in a field that appears to be blank.
A field with a NULL value is a field with no
value. It is very important to understand that
a NULL value is different than a zero value or
a field that contains spaces.
Comparison Using Null Values
Three Valued Logic:
TRUE
FALSE
UNKNOWN
Logical Connectives:(AND,OR NOT)
Operation Result Reason
X AND Y TRUE If both X and Y are true
FALSE If either X or Y is false
UNKNOWN If one is unknown and other is true
X OR Y TRUE If either of them is true
FALSE If both of them is false
UNKNOWN If one of them is false and other is
unknown
NOT X TRUE If X is false
FALSE If X is true
UNKNOWN If X is unknown
Disallowing Null Values
The insertion of Null values for a field can be
restricted by specifying the Not Null
constraint. This implies that the field cannot
take null values.
Syntax
The basic syntax of NOT NULL while
creating a table.
SQL> CREATE TABLE CUSTOMERS( ID INT
NOT NULL, NAME VARCHAR (20) NOT
NULL, AGE INT NOT NULL, ADDRESS CHAR
(25) , SALARY DECIMAL (18, 2), PRIMARY
KEY (ID) );
+----+----------+-----+-----------+----------+ |
 ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
 | 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
 | 2 | Khilan | 25 | Delhi | 1500.00 |
 | 3 | kaushik | 23 | Kota | 2000.00 |
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | |
| 7 | Muffy | 24 | Indore |
| +----+----------+-----+-----------+----------+
SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY
FROM CUSTOMERS WHERE SALARY IS NOT NULL;
+----+----------+-----+-----------+----------+ |
 ID | NAME | AGE | ADDRESS | SALARY |
 +----+----------+-----+-----------+----------+
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
 | 2 | Khilan | 25 | Delhi | 1500.00 |
| 3 | kaushik | 23 | Kota | 2000.00 |
 | 4 | Chaitali | 25 | Mumbai | 6500.00 |
 | 5 | Hardik | 27 | Bhopal | 8500.00
| +----+----------+-----+-----------+----------+
SQL> SELECT ID, NAME, AGE, ADDRESS,
SALARY FROM CUSTOMERS WHERE
SALARY IS NULL;
+----+----------+-----+-----------+----------+ |
 ID | NAME | AGE | ADDRESS | SALARY |
+----+----------+-----+-----------+----------+
| 6 | Komal | 22 | MP | |
| 7 | Muffy | 24 | Indore |
| +----+----------+-----+-----------+----------+
COMPLEX INTEGRITY CONSTRAINTS IN
SQL
1. Constraints applied on a single table with
Check option.
2. Defining domain constraints.
3. Defining constraints over several table:
Assertions
[Link] applied on a single table with
check option
Constraints that are applied on a single table
are called ‘table constraints’ and can be
applied on the table with the help of check
option.
For example if we want to make sure that all
the employees salary must be greater than
15000 but less than 40,000 in an employee
relation, we can insert this condition at the
time of creation of employee table.
Contd…
Create table employee(eid int not null,ename
char(20) not null,Dno int not null,esal
float,eage int not null,phone char(20),primary
key(eid),foreign key(Dno) references
Department(Dno),check(esal>=15000 and
esal<=40000));

When new tuples, are inserted into the table,

the check condition is evaluated. If it returns
true then the row is inserted, else it is
rejected.
[Link] Domain Constraint
New domains can be defined in SQL, by using
the statement CREATE DOMAIN.
We can even restrict the values of new
domains by using check option.
Syntax
Syntax for creating a new domain
Create domain domain_name source
domain(default value) check (value)
Example
Create domain salary decimal default 15000.00
Check(value>=15000.00 and
value<=40000.00)
[Link] Constraints over several
tables:assertions
Asserions are group of tables on
which a constraint is
applied,Unlike table constraints
which are applied on single
table,assertions are applied on
multiple tables.
Example
consider that total no. of employees
working as department manager and
project manager must be exactly 20,then
query can be written as
Contd…
Create table dept_manager(eid int,ename
char(20),Dno int,esal float,eage int,phone
char(20),primary
key(eid),check(esal>=15000.00 and
esal<=40000.00)check(select
count([Link])from employeeE)+(select
count(D.dept_managerid)from Department
D)+(select
count(p.project_managerid)from
projectP)<20));
Contd..
The above query involves only employer
table whereas both department and
project table must also be involved
equally. So, we can modify the above
query by considering three tables as
follows:
Contd…
Create Assertion total
check((select count([Link])from employeeE)+
(select count(D.dept_managerid)from
DepartmentD)+
(select count(p.project_managerid)from
projectP)<20));
Triggers and Active databases
 Triggers are the SQL statements
that are automatically executed when there
is any change in the database. The triggers
are executed in response to certain
events(INSERT, UPDATE or DELETE) in a
particular table. These triggers help in
maintaining the integrity of the data by
changing the data of the database in a
systematic fashion.
Contd…
The general format of the trigger includes:
Event
Condition
Action
 Syntax
create trigger Trigger_name
(before | after) [insert | update | delete]
on [table_name]
[for each row]
[trigger_body]
Contd…
CREATE TRIGGER: These two keywords
specify that a triggered block is going to be
declared.
TRIGGER_NAME: It creates or replaces an
existing trigger with the Trigger_name. The
trigger name should be unique.
BEFORE | AFTER: It specifies when the
trigger will be initiated i.e. before the
ongoing event or after the ongoing event.
Contd…
INSERT | UPDATE | DELETE: These are
the DML operations and we can use either of
them in a given trigger.
ON[TABLE_NAME]: It specifies the name of
the table on which the trigger is going to be
applied.
FOR EACH ROW: Row-level trigger gets
executed when any row value of any column
changes.
TRIGGER BODY: It consists of queries that
need to be executed when the trigger is
Example
Contd…
Now, we want to create a trigger that will
add 100 marks to each new row of
the Marks column whenever a new student is
inserted to the table.
The SQL Trigger will be:
CREATE TRIGGER Add_marks BEFORE
INSERT ON Student FOR EACH ROW SET
[Link] = [Link] + 100;
Contd…
After creating the trigger, we will write
the query for inserting a new student in
the database.
INSERT INTO Student(Name, Address,
Marks) VALUES('Alizeh', 'Maldives', 110);

To see the final output the query would be:

SELECT * FROM Student;
Contd…
3.2 SCHEMA REFINEMENT
INTRODUCTION TO SCHEMA REFINEMENT

It is a technique of organizing the data in the

database.
It is a systematic approach of decomposing
tables to eliminate data redundancy and
undesirable characteristics like Insertion,
Updation and deletion anomalies.
This division into smaller schemas is based
on functional and other dependencies which
are specified by database designer.
 Problems Caused by Redundancy
Storing the same information redundantly, that is, in more
than one place within a database, can lead to several
problems:

Redundant storage: Some information is stored

repeatedly.
Update anomalies: If one copy of such repeated data is
updated, an inconsistency is created unless all copies
are similarly updated.
Insertion anomalies: It may not be possible to store some
information unless some other information is stored as
well.
Deletion anomalies: It may not be possible to delete some
information without losing some other information as well.
2
 Decomposition
• It is the solution to the problem
caused by data redundancy.
• It means breaking up the large
schema into smaller multiple
schemas.
• It removes all the anomalies and also
helps to maintain data integrity.

8
 Contd…
• When a relation in the relational
model is not in appropriate normal
form then the decomposition of a
relation is required.
• If the relation has no proper
decomposition, then it may lead to
problems like loss of information.
• Decomposition is used to eliminate
some of the problems of bad design
like anomalies, inconsistencies, and
redundancy. 8
Types of decomposition
Lossless Decomposition
• If the information is not lost from the relation
that is decomposed, then the decomposition
will be lossless.
• The lossless decomposition guarantees that
the join of relations will result in the same
relation as it was decomposed.
Employee table
Department table
Dependency preserving
• It is an important constraint of the database.
• In the dependency preservation, at least one decomposed
table must satisfy every dependency.
• If a relation R is decomposed into relation R1 and R2, then the
dependencies of R either must be a part of R1 or R2 or must
be derivable from the combination of functional dependencies
of R1 and R2.
• For example, suppose there is a relation R (A, B, C, D) with
functional dependency set (A->BC). The relational R is
decomposed into R1(ABC) and R2(AD) which is dependency
preserving because FD A->BC is a part of relation R1(ABC).
Problems Related to decomposition
• Unless we are careful, decomposing a relation schema can
create more problems than it solves.
• Two important questions must be asked repeatedly:
1. What problems (if any) does a given decomposition
cause?
[Link] we need to decompose a relation?
 Functional dependency

The attributes of a table is said to be dependent on

each other when an attribute of a table uniquely
identifies another attribute of the same table.
For example:
Stu_Id, Stu_Name, Stu_Age.
Stu_Id->Stu_Name
we can say Stu_Name is functionally dependent on
Stu_Id.
Formally:
If column A of a table uniquely identifies the column B
of same table then it can represented as A->B
(Attribute B is functionally dependent on attribute A)
Types of Functional Dependencies

Trivial functional dependency

Non trivial functional dependency
Multivalued dependency
Transitive dependency
Trivial functional dependency
The dependency of an attribute on a set of
attributes is known as trivial functional
dependency if the set of attributes includes
that attribute.
Symbolically: A ->B is trivial functional
dependency if B is a subset of A
The following dependencies are also trivial:
A->A & B->B
For example: Consider a table with two
columns Student_id and Student_Name.
{Student_Id, Student_Name} -> Student_Id
is a trivial functional dependency as
Student_Id is a subset of {Student_Id,
Student_Name}.
Also, Student_Id -> Student_Id &
Student_Name -> Student_Name are trivial
dependencies too.
Non trivial functional dependency

If a functional dependency X->Y holds true where

Y is not a subset of X then this dependency is
called non trivial Functional dependency.
An employee table with three attributes: emp_id,
emp_name, emp_address.
The following functional dependencies are non-
trivial:
emp_id -> emp_name (emp_name is not a subset
of emp_id)
emp_id -> emp_address (emp_address is not a
subset of emp_id)
On the other hand, the following
dependencies are trivial:
{emp_id, emp_name} -> emp_name
[emp_name is a subset of {emp_id,
emp_name}]
 Multivalued dependency

Multivalued dependency occurs when there

are more than one independent multivalued
attributes in a table.
bike_model manuf_year color

M1001 2007 Black

M1001 2007 Red

M2012 2008 Black

M2012 2008 Red

M2222 2009 Black

M2222 2009 Red

 Transitive dependency in DBMS

A functional dependency is said to be

transitive if it is indirectly formed by two
functional dependencies.
For Ex:
X -> Z is a transitive dependency if the
following three functional dependencies hold
true:
X->Y
Y does not ->X
Y->Z
 Example
Book Author Author_age

George R. R.
Game of Thrones 66
Martin

Harry Potter J. K. Rowling 49

George R. R.
Dying of the Light 66
Martin
 REASONING ABOUT FD’S
We say that an FD F is implied by a given set F of FD’s
if F holds on every relation instance that satisfies all
dependencies in F.i.e f holds whenever all FD’s hold.
Closure of set of FD’s:
The set of all FD’s implied by a given set F of FD’s is
called closure of F denoted as F+.
How can we infer or compute the closure of given set
F of FD’s.
Sol: Armstrong axioms can be applied repeatedly to
infer all FD’s implied by set of F of FD’s

13
 We use A,B,C to denote sets of attributes over
a relation schema R

Reflexivity: If B is a subset of A then A->B

Augmentation: If A->B then AC->BC for any
C
Transitivity: If A->B and B->C then A->C

Union: If A->B ,A->C then A->BC

Decomposition: If A->BC then A->B and A->C
Pseudotransitivity: If A->B and CB->D
Then AC->D 14
Example:
Let R=(ABCGHI) and F={A->B,A->C,CG-
>H,CG->I B->H} then find closure of F.
F+={A->B,A->C,CG->H,CG->I,B->H,A-
>H,CG->HI,AG->H,AG->I}
A->H is obtained by TRANSITIVITY of A->B
&B->H
CG->HI is obtained by UNION i.e CG->H &
CG->I.
AG->H is obtained by PSEUDOTRANSITIVITY
i.e A->C & CG->H
AG->I is obtained by PSEUDOTRANSITIVITY
i.e A->C & CG->I
Example:
• Sale(product, date, customer, vendor, street)
CONTD..
CONTD..
Closure of an attribute/attribute set
The set of all those attributes which
can be functionally determined from
an attribute set is called as a closure
of that attribute set.

Closure of attribute set {X} is

denoted as {X}+.
Contd…
Steps to Find Closure of an Attribute
Set-
Step-01:
Add the attributes contained in the
attribute set for which closure is being
calculated to the result set.

Step-02:
Recursively add the attributes to the
result set which can be functionally
determined from the attributes already
Example:
[Link] a relation R(A,B,C,D,E,F,G) with the
functional dependencies
A->BC
BC->DE
D->F
CF->G
Find the closure of an attributes and
attribute sets.
Solution:
Closure of attribute A-

A+ = { A }
= { A , B , C } ( Using A → BC )
= { A , B , C , D , E } ( Using BC → DE )
= { A , B , C , D , E , F } ( Using D → F )
= { A , B , C , D , E , F , G } ( Using CF → G )
Thus,
A+ = { A , B , C , D , E , F , G }

Contd…
Closure of attribute D-

D+ = { D }
= { D , F } ( Using D → F )
We can not determine any other attribute using
attributes D and F contained in the result set.
Thus,
D+ = { D , F }
Contd…
Closure of attribute set {B, C}-

{ B , C }+= { B , C }
= { B , C , D , E } ( Using BC → DE )
= { B , C , D , E , F } ( Using D → F )
= { B , C , D , E , F , G } ( Using CF → G )
Thus,
{ B , C }+ = { B , C , D , E , F , G }
Finding the Keys Using Closure-
Super Key-
If the closure result of an attribute set
contains all the attributes of the relation, then
that attribute set is called as a super key of that
relation.
Thus, we can say-
“The closure of a super key is the entire
relation schema.”
Example-
In the above example,
The closure of attribute A is the entire relation
schema.
Thus, attribute A is a super key for that
Contd…
Candidate Key-
If there exists no subset of an attribute set
whose closure contains all the attributes of
the relation, then that attribute set is called
as a candidate key of that relation.
Example-
In the above example,
No subset of attribute A contains all the
attributes of the relation.
Thus, attribute A is also a candidate key for
that relation.
 2. Consider a relation R(A,B,C,D,E,F) F: E->A,
E->D, A->C, A->D, AE->F, AG->K. Find the
closure of E or E+
The closure of E or E+ is as follows −
E+ = E
=EA {for E->A add A}
=EAD {for E->D add D}
=EADC {for A->C add C}
=EADC {for A->D D already added}
=EADCF {for AE->F add F}
=EADCF
[Link] the relation R(A,B,C,D,E,F)
F: B->C, BC->AD, D->E, CF->B. Find the
closure of B.
Solution
The closure for B is as follows −
B+ = {B,C,A,D,E}
4. Consider the given functional dependencies-
AB → CD
AF → D
DE → F
C → G
F → E
G → A
Which of the following options is false?
(A) { CF }+ = { A , C , D , E , F , G }
(B) { BG }+ = { A , B , C , D , G }
(C) { AF }+ = { A , C , D , E , F , G }
(D) { AB }+ = { A , C , D , F ,G }

Contd…
Option-(A):

{ CF }+ = { C , F }
= { C , F , G } ( Using C → G )
= { C , E , F , G } ( Using F → E )
= { A , C , E , E , F } ( Using G → A )
= { A , C , D , E , F , G } ( Using AF → D )

Since, our obtained result set is same as the

given result set, so, it means it is correctly
given.
Contd…
Option-(B):

{ BG }+ = { B , G }
= { A , B , G } ( Using G → A )
= { A , B , C , D , G } ( Using AB → CD )

Since, our obtained result set is same as the

given result set, so, it means it is correctly
given.
Contd…
Option-(C):

{ AF }+ = { A , F }
= { A , D , F } ( Using AF → D )
= { A , D , E , F } ( Using F → E )

Since, our obtained result set is different

from the given result set, so,it means it is not
correctly given.
Contd…
Option-(D):

{ AB }+ = { A , B }
= { A , B , C , D } ( Using AB → CD )
= { A , B , C , D , G } ( Using C → G )
Since, our obtained result set is different from
the given result set, so,it means it is not
correctly given.
Thus,
Option (C) and Option (D) are correct.
Finding the no of candidate keys using
closure
Let us take one simple example..
Consider R(A,B,C,D,E) & FD={A->B,D->E}
Sol: consider all attributes and find closure
which is super key.
(ABCDE)+={A,B,C,D,E}
As we know that from the given FD A->B,that
means A derives B,so we can discard B,because
B is derived by A.
Now we get(ACDE)+={ACDEB}
Similarly we can also discard E in above
closure from the FD D->E.
CONTD…
Now we get
(ACD)+={A,C,D,B,E}
We cant discard further the above closure, as
we don’t have any dependencies given.
Now we have to find out the proper subset of
(ACD)+.
(AC)+={A,C,B}
(AD)+={A,D,B,E}
(CD)+={C,D,E}
(A)+={A,B}
(C)+={C}
(D)+={D,E}
Contd..
Since the proper subsets of (ACD)+ are not
having super key ,therefore it is a candidate key.
But we cannot say that only (ACD) is a candidate
key, there can be any number of candidate keys.
Here comes the concept of prime attributes.
Prime attributes are those attributes which are
part of candidate key.
In this example, A,C,D are prime attributes.
Now we need to check whether these prime
attributes (A,C,D) are available in right hand side
of given dependencies.
Since we cannot find A,C,D in right hand side of
dependencies, then only ACD is candidate key for
the given relation.
Example 2:
R(A,B,C,D),FD={A->B,B->C,C->A}
SOL: (A,B,C,D)+={A,B,C,D}
(A,C,D)+={A,C,D,B} (discard B since A->B)
If A->B,B->C THEN A->C
(A,D)+={A,D,B,C} (discard C since A->C)
(A)+={A,B,C}
(D)+={D}
PRIME ATTRIBUTES=(A,D)
Since A is available in right hand side of given
FD’S we can replace A with C from C->A
Now (A,D) becomes (C,D)
Contd..
(C)+={C,A,B}
(D)+={D}
Now prime attributes= C,D
Since C is available in right hand side of given
FD’S we can replace C with B from B->C
Now (C,D) becomes (B,D)
(B)+={B,C,A}
(D)+={D}
Since B is available in right hand side of given
FD’S we can replace B with A from A->B,But as
AD is already a candidate key so we can stop
here.
Total possible Candidate keys are:AD,CD,BD.
 Normalization
Normalization is the process of organizing the
data in the database.
Normalization is used to minimize the
redundancy from a relation or set of relations. It
is also used to eliminate the undesirable
characteristics like Insertion, Update and
Deletion Anomalies.
Normalization divides the larger table into the
smaller table and links them using relationship.
The normal form is used to reduce redundancy
from the database table.
 Normalization
Here are the most commonly used
normal forms:

First normal form(1NF)

Second normal form(2NF)
Third normal form(3NF)
Boyce Codd normal form (BCNF)
Types of Normal Forms
 First normal form (1NF)

As per the rule of first normal form,

an attribute (column) of a table cannot
hold multiple values. It should hold
only atomic values.
 EXAMPLE
emp_id emp_name emp_address emp_mobile

101 Herschel New Delhi 8912312390

102 Jon Kanpur 8812121212

9900012222

103 Ron Chennai 7778881212

104 Lester Bangalore 9990000123

8123450987
 Contd…..
emp_addres
emp_id emp_name emp_mobile
s

101 Herschel New Delhi 8912312390

102 Jon Kanpur 8812121212

102 Jon Kanpur 9900012222

103 Ron Chennai 7778881212

104 Lester Bangalore 9990000123

104 Lester Bangalore 8123450987

 Second normal form (2NF)

A table is said to be in 2NF if both the

following conditions hold:
Table is in 1NF (First normal form)
No non-prime attribute is dependent on the
proper subset of any candidate key of table.
An attribute that is not part of any candidate
key is known as non-prime attribute.
CONTD…
All non prime attributes should be fully
functional dependent on candidate key.
Ex:
Cust-id Store id location
1 1 Delhi
1 3 Mumbai
2 1 Delhi
3 2 Bangalore
4 3 Mumbai
Candidate key: (cust-id, store id)
Partial dependency: proper subset of ck->non
prime attribute
Contd…
Here Location is determined by store id
which is a part of candidate key(partial
dependency),so it is not in 2NF.
Therefore, we need to decompose the table
as
Cust-id Store id Store id Location
1 1
1 Delhi
1 3
2 1 2 Bangalore
3 2
3 Mumbai
4 3

Ck(cust-id,store id) ck(store id)

 Third Normal form (3NF)
A table design is said to be in 3NF if both the
following conditions hold:
Table must be in 2NF
Transitive functional dependency of non-
prime attribute should be removed.

Shortly, table must be in 2NF.

There should be no transitive dependency in
table.
 Example: (3NF)
Roll no state city

1001 Telangana Hyderabad

1002 Ap Guntur

1003 Telangana Hyderabad

1004 Ap Guntur

1005 Karnataka Bangalore

Candidate Keys: {Roll no}
FD: roll no->state, state->city
Prime attributes: Roll no
Non prime attributes: state,city
Here one non prime attribute is determining
another non prime attribute(state->city)
which is represented as transitive
dependency. So, we need to decompose the
above table into two tables.
Contd…

Roll no state Roll no city

1001 Telangana 1001 Hyderabad
1002 Ap 1002 Guntur
1003 Telangana 1003 Hyderabad
1004 Ap 1004 Guntur
1005 Karnataka 1005 Bangalore
Contd…
A table is in 3NF iff
1. L.H.S of all FD’S are super key or candidate
key or
2. R.H.S of all FD’S are prime attributes.
Ex: Given R(A,B,C,D) and FD’s AB->CD,D->A.
Sol: (ABCD)+=(ABCD)
(AB)+=(ABCD)
A+=(A)
B+=(B)
So AB is candidate key.
Contd…
(DB)+=(DBAC)
D+=(DA)
B+=(B)
So, DB is also a candidate key.
Ck(AB,DB)
Prime attributes are A,B,D
Non prime attributes are C
By the above mentioned conditions for all FD’s
i.e;
In AB->CD, L.H.S is CK.
In D->A, R.H.S is prime attribute. So, it is in
Example
 Check whether the following relation is in 3NF.
R(ABCD),FD’s are A->B,B->C,C->D
Sol: (ABCD)+=(ABCD)
We can decompose ABCD as A from the above FD’s.
(A)+=(ABCD)
SO A is CK as well. And it is one and only one ck as there are
no A’s in right side of given FD’s.
A: Prime attribute
B,C,D: Non prime attributes
Now check, for A->B : L.H.S is ck
B->C: L.H.S is not ck, or R.H.S C is not prime attribute
C->D: L.H.S is not ck, or R.H.S C is not prime attribute
So, the given relation is not in 3NF.
 BOYCE CODD NORMAL FORM
It is an advanced version of 3NF that’s why it
is also referred as 3.5NF.
BCNF is stricter than 3NF.
A table complies with BCNF if it is in 3NF
and for every functional dependency X->Y,
X should be the super key of the table.
 Example
emp_nati dept_no_o
emp_id emp_dept dept_type
onality f_emp
Production
1001 Austrian and D001 200
planning

1001 Austrian stores D001 250

design and
1002 American technical D134 100
support
Purchasing
1002 American departmen D134 600
t
Functional dependencies in the table
above:
emp_id -> emp_nationality
emp_dept -> {dept_type,
dept_no_of_emp}
Candidate key: {emp_id, emp_dept}
The table is not in BCNF as neither
emp_id nor emp_dept alone are keys.
emp_nationality
emp_id table:
emp_nationality

1001 Austrian

1002 American
 emp_dept table:
emp_dept dept_type dept_no_of_emp

Production and
D001 200
planning

stores D001 250

design and
D134 100
technical support

Purchasing
D134 600
department
emp_dept_mapping table:

emp_id emp_dept

1001 Production and planning

1001 stores

1002 design and technical support

1002 Purchasing department

Functional dependencies:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}

Candidate keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}
EXAMPLE:
Check whether R(A,B,C) is in BCNF or not
using FD’s : A->B,B->C,C->A.
Sol:(ABC)+=(ABC)
A+=(ABC),so A is CK.
C+=(CAB),so C is CK.
B+=(BCA),so B is CK.
Now as per BCNF rule,left hand side of all FD’s
must be a super key.
So,the given relation is in BCNF.
Other kinds of dependencies
Multivalued dependencies
Join dependencies
Inclusion dependencies
Fourth Normal form
For a table to satisfy the fourth normal form,it
should satisfy the following conditions:
[Link] should be in BCNF
[Link] table should not have any multivalued
dependency.
Multivalued dependency
A table is said to have multivalued
dependency,if the following conditions are
true:
[Link] a dependency A->B,if for a single value
of A multiple value of B exists,then the table
may have multivalued dependency.
[Link],a table should have atleast 3 columns
for it to have a multivalued dependency.
3. And for a Relation(A,B,C)If there is a
multivalued dependency between A &B,then
B&C should be independent of each other.
Example
STU_ID COURSE HOBBY
21 COMPUTER DANCING
21 MATH SINGING

34 CHEMISTRY DANCING

74 BIOLOGY CRICKET

59 PHYSICS HOCKEY

The given STUDENT table is in 3NF, but the COURSE and

HOBBY are two independent entity. Hence, there is no
relationship between COURSE and HOBBY.
Contd…
In the STUDENT relation, a student with
STU_ID, 21 contains two
courses, Computer and Math and two
hobbies, Dancing and Singing. So there is a
Multi-valued dependency on STU_ID, which
leads to unnecessary repetition of data.
So to make the above table into 4NF, we can
decompose it into two tables:
STUDENT-COURSE
STU_ID COURSE
21 COMPUTER
21 MATH
34 CHEMISTRY
74 BIOLOGY
59 PHYSICS
STUDENT-HOBBY
STU_ID HOBBY
21 DANCING
21 SINGING
34 DANCING
74 CRICKET
59 HOCKEY
 Fifth normal form (5NF)
A relation is in 5NF if it is in 4NF and not
contains any join dependency and joining
should be lossless.
5NF is satisfied when all the tables are
broken into as many tables as possible in
order to avoid redundancy.
5NF is also known as Project-join normal
form (PJ/NF).
Join dependency
If the join of R1 and R2 is equal to relation R,
then we can say that a join dependency
exists. where R1 and R2 are the
decomposition R1 (P, Q) and R2 (Q, S) of a
given relation R (P, Q, S). R1 and R2 are a
lossless decomposition of R.
EXAMPLE:
Department table:
Dept Subject Student
Cse C Nav
It C++ Siri
Cse Java Balu
Cse Dbms Nag
Mech Se Sam
Ece Oops Aish
Contd…
The above table, contains multivalued
dependency between dept->>subject and
dept->>student.
So we need to decompose into two tables to
beDept
in 4NF. Subject Dept Student
Cse C Cse Nav
Dsub Dstu
It C++ It Siri
Cse Java Cse Balu
Cse Dbms Cse Nag
Mech Se Mech Sam
Ece Oops Ece Aish
Contd..
According to lossless property of join, if we
are having parent table i.e department, we
decompose the department table into Dsub
and Dstu which are child tables, now this will
be a good database design if we tried to join
these child tables then we should get same
parent table without any loss of information
and without any redundant tuple or extra
tuple.
So, again if we apply natural join on child
tables we should get parent table without loss
of information or any extra tuple.
Contd…
Select * from Dsub d1,Dstu d2 where
[Link]=[Link].
On the basis of department these tables should
be joined.
Note: we are doing natural join ,so common
tuple will be appeared once and based on
common attribute join operation will be
performed.
Contd..
Dsub⋈ Dstu
dept Subject Student
Cse C Nav
Cse C Balu
Cse C Nag
It C++ siri
Cse Java Nav
Cse Java Balu
Cse Java Nag
Cse Dbms Nav
Cse Dbms Balu
Cse Dbms Nag
Mech Se Sam
Ece Oops Aish
Contd..
We got 6 extra tuples when we joined
decomposed tables.
These extra tuples are called as spurious
tuples which are not in original table.
This is the problem of join dependency, when
we try to join decomposed tables we are
getting redundant or spurious tuples through
natural join operation.
After so much of decomposition also we are
getting redundancy.
Therefore we need to further decompose into
three tables instead of two.
Contd…
Dept Subject Dept Student
Cse C Cse Nav
It C++ It Siri
Cse Java Cse Balu
Cse Dbms Cse Nag
Mech Se Mech Sam
Ece Oops Ece Aish

Subject Student
C Nav
C++ Siri
Java Balu
Dbms Nag
Se Sam
Oops Aish
Contd…
Then perform natural join operation on these
three tables
Join substu table with Dsub ⋈ Dstu

Select [Link],[Link],[Link] from Dsub

d1,Dstu d2,substu d3 where [Link]=[Link]
and [Link]=[Link] and
[Link]=[Link];
Now, we can get original table without any
spurious or redundant tuples.
Inclusion dependencies
The inclusion dependency is a statement in
which some columns of a relation are
contained in other columns.
Example of inclusion dependency is a foreign
key.