Chapter 3

The relational data model and SQL

 Outline

Basic concepts:

SQL (Structured Query Language)

Domain Types in SQL

The Relational Algebra And Calculus

 Structured Query Language (SQL)
The ANSI (American National Standards Institute)
standard language for the definition and manipulation of
relational database.
Includes data definition language (DDL), statements that
specify and modify database schemas.

Includes a data manipulation language (DML), statements

that manipulate database content.
 Basics of SQL
SQL data is case-sensitive, SQL commands are not.

First Version was developed at IBM by Donald D.

Chamberlin and Raymond F. Boyce. [SQL]

Developed using Dr. E.F. Codd's paper, “A Relational Model

of Data for Large Shared Data Banks.”

SQL query includes references to tuples variables and the

attributes of those variables
SQL: DDL Commands

CREATE TABLE: used to create a table.

ALTER TABLE: modifies a table after it was created.

DROP TABLE: removes a table from a database.

SQL: CREATE TABLE Statement
Things to consider before you create your table are:
The type of data
the table name
what column(s) will make up the primary key
the names of the columns
CREATE TABLE statement syntax:
CREATE TABLE <table name>
( field1 datatype ( NOT NULL ),
field2 datatype ( NOT NULL )
);
SQL: Attributes Types
ALTER TABLE Statement
To add or drop columns on existing tables.
ALTER TABLE statement syntax:
ALTER TABLE <table name>
ADD attr datatype;
or
DROP COLUMN attr;
DROP TABLE Statement
Has two options:
CASCADE: Specifies that any foreign key constraint
violations that are caused by dropping the table will cause the
corresponding rows of the related table to be deleted.

RESTRICT: blocks the deletion of the table of any foreign key

constraint violations would be created.

DROP TABLE statement syntax:

DROP TABLE <table name> [ RESTRICT|CASCADE ];
Example:
CREATE TABLE FoodCart (
date varchar(10),
food varchar(20),
FoodCart
profit float
); date food profit

ALTER TABLE FoodCart (

FoodCart
ADD sold int
); date food profit sold

ALTER TABLE FoodCart( FoodCart

DROP COLUMN profit
); date food sold

DROP TABLE FoodCart;

DML Commands
INSERT: adds new rows to a table.

UPDATE: modifies one or more attributes.

DELETE: deletes one or more rows from a table.

 INSERT Statement
To insert a row into a table, it is necessary to have a value
for each attribute, and order matters.
INSERT statement syntax:
INSERT into <table name>
VALUES ('value1', 'value2', NULL);
Example: INSERT into FoodCart
VALUES (’02/26/08', ‘pizza', 70 );
FoodCart

date food sold

date food sold
02/25/08 pizza 350
02/25/08 pizza 350
02/26/08 hotdog 500
02/26/08 hotdog 500
02/26/08 pizza 70
 UPDATE Statement
To update the content of the table:
UPDATE statement syntax:
UPDATE <table name> SET <attr> = <value>
WHERE <selection condition>;
Example: UPDATE FoodCart SET sold = 349
WHERE date = ’02/25/08’ AND food = ‘pizza’;
FoodCart
date food sold date food sold
02/25/08 pizza 350 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 hotdog 500
02/26/08 pizza 70 02/26/08 pizza 70
 SQL: DELETE Statement
To delete rows from the table:
DELETE statement syntax:
DELETE FROM <table name>
WHERE <condition>;
Example: DELETE FROM FoodCart WHERE food = ‘Burger’;
FoodCart

date food sold date food sold

02/25/08 pizza 349 02/25/08 pizza 349
02/26/08 Burger 500 02/26/08 pizza 70
02/26/08 pizza 70

Note: If the WHERE clause is omitted all rows of data are deleted from the table.
DCL(data control language)

DCL mostly concerned with rights, permissions and other

controls of the database system includes commands such as
GRANT & REVOKE .
DCL statements control the level of access that users have
on database objects.
 GRANT – allows users to read/write on certain database objects
 REVOKE – keeps users from read/write permission on
database objects
SQL Statements, Operations, Clauses
SQL Statements:
Select
SQL Join
Inner join
Left Join
Right Join
Full join
SQL Clauses:
Order By
Group By
Having
 SQL: SELECT Statement
A basic SELECT statement includes 3 clauses

SELECT <attribute name> FROM <tables> WHERE <condition>

SELECT FROM WHERE

Specifies the attributes Specifies the tables Specifies the selection

that are part of the that serve as the input condition, including
resulting relation to the statement the join condition.

Note: that you don't need to use WHERE

 SQL: SELECT Statement (cont.)
Using a “*” in a select statement indicates that every
attribute of the input table is to be selected.
Example: SELECT * FROM … WHERE …;

To get unique rows, type the keyword DISTINCT after

SELECT.
Example: SELECT DISTINCT * FROM …
WHERE …;
 Example: 1) SELECT *
Person FROM person
Name Age Weight WHERE age > 30;
Harry 34 80 Name Age Weight
Sally 28 64 Harry 34 80
George 29 70 Helena 54 54
Helena 54 54 Peter 34 80
Peter 34 80

2) SELECT weight 3) SELECT distinct weight

FROM person FROM person
WHERE age > 30; WHERE age > 30;
Weight Weight
80 80
54 54
80
 SQL: Join operation
A join can be specified in the FROM clause which list the two
input relations and the WHERE clause which lists the join
condition.
A JOIN clause is used to combine rows from two or more tables, based on
a related column between them.
Example:
Emp Dept
ID State ID Division
1000 CA 1001 IT
1001 MA 1002 Sales
1002 TN 1003 Biotech
 SQL: Join operation (cont.)
inner join = join
SELECT *
FROM emp join dept (or FROM emp, dept)
on emp.id = dept.id;

Emp.ID Emp.State Dept.ID Dept.Division

1001 MA 1001 IT
1002 TN 1002 Sales
 SQL: Join operation (cont.)
left outer join = left join
SELECT *
FROM emp left join dept
on emp.id = dept.id;

Emp.ID Emp.State Dept.ID Dept.Division

1000 CA null null
1001 MA 1001 IT
1002 TN 1002 Sales
 SQL: Join operation (cont.)
right outer join = right join
SELECT *
FROM emp right join dept
on emp.id = dept.id;

Emp.ID Emp.State Dept.ID Dept.Division

1001 MA 1001 IT
1002 TN 1002 Sales
null null 1003 Biotech
 SQL: Like operation
Pattern matching selection
% (arbitrary string)
SELECT *
FROM emp
WHERE ID like ‘%01’;
 finds ID that ends with 01, e.g. 1001, 2001, etc
_ (a single character)
SELECT *
FROM emp
WHERE ID like ‘_01_’;
 finds ID that has the second and third character as 01, e.g.
1010, 1011, 1012, 1013, etc
SQL: The ORDER BY Clause
Ordered result selection
desc (descending order)
SELECT *
FROM emp
order by state desc
 puts state in descending order, e.g. TN, MA, CA
asc (ascending order)
SELECT *
FROM emp
order by id asc
 puts ID in ascending order, e.g. 1001, 1002, 1003
SQL: The GROUP BY Clause
The function to divide the tuples into groups and returns an
aggregate for each group.
SELECT food, sum(sold) as totalSold
FROM FoodCart
group by food;
FoodCart

date food sold food totalSold

02/25/08 pizza 349 Burger 500
02/26/08 Burger 500 pizza 419
02/26/08 pizza 70
SQL: The HAVING Clause
The substitute of WHERE for aggregate functions
SELECT food, sum(sold) as totalSold
FROM FoodCart
group by food
having sum(sold) > 450;
FoodCart

date food sold food totalSold

02/25/08 pizza 349 Burger 500
02/26/08 Burger 500
02/26/08 pizza 70
 SQL: Aggregate Functions
Are used to provide summarization information for SQL
statements, which return a single value.

COUNT(attr)
SUM(attr)
MAX(attr)
MIN(attr)
AVG(attr)

Note: when using aggregate functions, NULL values are not

considered, except in COUNT(*) .
SQL: Aggregate Functions (cont.)
FoodCart
date food sold
02/25/08 pizza 349
02/26/08 burger 500
02/26/08 pizza 70

COUNT(attr) -> return # of rows that are not null

Ex: COUNT(distinct food) from FoodCart; -> 2

SUM(attr) -> return the sum of values in the attr

Ex: SUM(sold) from FoodCart; -> 919

MAX(attr) -> return the highest value from the attr

Ex: MAX(sold) from FoodCart; -> 500
 SQL: Aggregate Functions (cont.)
FoodCart
date food sold
02/25/08 pizza 349
02/26/08 burger 500
02/26/08 pizza 70

MIN(attr) -> return the lowest value from the attr

Ex: MIN(sold) from FoodCart; -> 70

AVG(attr) -> return the average value from the attr

Ex: AVG(sold) from FoodCart; -> 306.33
Note: value is rounded to the precision of the datatype
Relational Algebra & calculus
Relational Algebra
Relational algebra presents the basic set of operations for
relational model.

It is a procedural language, which describes the procedure to

obtain the result.

 Relational algebra is prescriptive because it describes

the order of operations in the query that specifies how to
retrieve the result of a query.
 Cont’d

The sequence of operations in relation algebra is called relational

algebra expression.

The Relational Algebra Expression either takes one relation or

two relations as an input to the expression or produces a new
relation as a result.

The resultant relation obtained from the relational algebra

expressions can be further composed to the other relational algebra
expression whose result will again be a new relation.
Relational Query Languages
Query languages: Allow manipulation and retrieval of data
from a database.
Relational model supports simple, powerful QLs:
 Strongformal foundation based on logic.
 Allows for much optimization.

Query Languages != programming languages!

 QLs not expected to be “Turing complete”.
 QLs not intended to be used for complex calculations.
 QLs support easy, efficient access to large data sets.
Preliminaries
A query is applied to relation instances, and the result of a query
is also a relation instance.
◦ Schemas of input relations for a query are fixed.
◦ The schema for the result of a given query is also fixed!
Determined by definition of query language constructs.
Relational Algebra
Basic operations:
◦ Selection ( ) Selects a subset of rows from relation.
◦ 
Projection ( ) Deletes unwanted columns from relation.
◦ 
Cross-product ( ) Allows us to combine two relations.
◦

Set-difference ( ) Tuples in reln. 1, but not in reln. 2.
◦ Union ( ) Tuples in reln. 1 and in reln. 2.

Additional operations:
◦ Intersection, join, division, renaming

Since each operation returns a relation, operations can be composed! (Algebra is

“closed”.)
Selection and Projection are called unary relational
operations.

Set-difference , Union and Intersection are called

Relational Algebra Operations From Set Theory.

Join and division are called binary relational operations.

 R1 S1
ExampleInstances: “Sailors”and“Reserves”relationsforourexamples.

sid bid day sid sname rating age

22 101 10/10/96 22 dustin 7 45.0
58 103 11/12/96 31 lubber 8 55.5
58 rusty 10 35.0
B1 S2
bid bname color sid sname rating age
101 Interlake blue
28 yuppy 9 35.0
102 Interlake red
103 Clipper green
31 lubber 8 55.5
104 Marine red 44 guppy 5 35.0
58 rusty 10 35.0
 sname rating
Projection yuppy
lubber
9
8
Deletes attributes that are not in projection list. guppy 5
Schema of result contains exactly the fields in rusty 10
the projection list, with the same names that
they had in the (only) input relation.  sname,rating(S2)
Projection operator has to eliminate duplicates!

age
35.0
55.5
 age(S2)
 sid sname rating age
Selection 28 yuppy 9 35.0
58 rusty 10 35.0
Selects rows that satisfy
selection condition.  rating 8(S2)
No duplicates in result!
Schema of result identical to
schema of (only) input
relation.
sname rating
Result relation can be the
input for another relational
yuppy 9
algebra operation! (Operator rusty 10
composition.)
 sname,rating( rating 8(S2))
UNION INTERSECTION
 Union, Intersection, Set-Difference
sid sname rating age
All of these operations take two 22 dustin 7 45.0
input relations, which must be 31 lubber 8 55.5
union-compatible: 58 rusty 10 35.0
◦ Same number of fields.
◦
44 guppy 5 35.0
`Corresponding’ fields have the same type.
28 yuppy 9 35.0
S1 S2

sid sname rating age

sid sname rating age 31 lubber 8 55.5
22 dustin 7 45.0 58 rusty 10 35.0
S1 S2 S1 S2
 Cross-Product
Each row of S1 is paired with each row of R1.
Result schema has one field per field of S1 and R1, with field
names `inherited’ if possible.
 Conflict: Both S1 and R1 have a field called sid.
(sid) sname rating age (sid) bid day
22 dustin 7 45.0 22 101 10/10/96
22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 22 101 10/10/96
31 lubber 8 55.5 58 103 11/12/96
58 rusty 10 35.0 22 101 10/10/96
58 rusty 10 35.0 58 103 11/12/96

 Renaming operator:  (C(1 sid1, 5  sid 2), S1 R1)

Joins
R  c S  c(RS )
Condition Join:
(sid) sname rating age (sid) bid day
22 dustin 7 45.0 58 103 11/12/96
31 lubber 8 55.5 58 103 11/12/96
S1  R1
S1. sid  R1. sid

Result schema same as that of cross-product.

Fewer tuples than cross-product, might be able to compute more
efficiently Sometimes called a theta-join.
Joins
Equi-Join: A special case of condition join where the condition c
contains only equalities.

sid sname rating age bid day

22 dustin 7 45.0 101 10/10/96
58 rusty 10 35.0 103 11/12/96
S1  R1
sid

Result schema similar to cross-product, but only one copy of fields

for which equality is specified.
Natural Join: Equijoin on all common fields.
 Division
Not supported as a primitive operator, but useful for expressing
queries like:
Find sailors who have reserved all boats.
Let A have 2 fields, x and y; B have only field y:
 A/B =
x |  x , y  A  y  B
 i.e.,
A/B contains all x tuples (sailors) such that for every y tuple (boat) in B, there is
an x y tuple in A.
 Or: If the set of y values (boats) associated with an x value (sailor) in A contains
all y values in B, the x value is in A/B.
Examples of Division A/B
sno pno pno pno pno
s1 p1 p2 p2 p1
s1 p2 B1 p4 p2
s1 p3 p4
s1 p4
B2
B3
s2 p1 sno
s2 p2 s1
s3 p2 s2 sno
s4 p2 s3 s1 sno
s4 p4 s4 s4 s1
A/B1 A/B2 A/B3
A
Relational Calculus
Comes in two flavors: Tuple relational calculus (TRC) and Domain relational
calculus (DRC).
Calculus has variables, constants, comparison ops, logical connectives and
quantifiers.
TRC: Variables range over (i.e., get bound to) tuples.
DRC: Variables range over domain elements (= field values).
we may use
Logical operators:- AND (∧) , OR (∨) NOT(¬)
Quantifiers:- ∀, for all (every )
∃, which means there exist(at least one)
 Tuple relational calculus
In tuple relational calculus, we work on filtering tuples based on the given
condition.
Syntax: { T | Condition }
For example if our table is Student, we would put it as Student(T)
Then comes the condition part, to specify a condition applicable for a particular
attribute(column), we can use the . dot variable with the tuple variable to specify
it
Eg. if we want to get data for students with age greater than 17, then, we can write
it as, T.age > 17, where T is our tuple variable.
T.name | Student(T) AND T.age > 17
Tuple Relational Calculus (TRC)

First_Name Last_Name Age

Rachel Eric 30
Samantha Eric 31
Roy Johnson 27
Carl Pratap 28
A. Query to display the last name of those students where age is greater than 30 will be
{ t.Last_Name | Student(t) AND t.age > 30 } : Eric
B. Query to display all the details of students where Last name is ‘Eric’
{ t | Student(t) AND t.Last_Name = ‘Eric' } :
Domain Relational Calculus (DRC)

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

First_Name Last_Name Age

Rachel Eric 30
Samantha Eric 31
Roy Johnson 27
Carl Pratap 28
Eg. Query to find the first name and age of students where student age is greater than 27
{< First_Name, Age > | ∈ Student ∧ Age > 27}
Example: TRC query
Consider two relations
◦ EMP(Name, MGR, DEPT, SAL)
◦ CHILDREN(Ename, Cname, Age)
Q1: Retrieve Salary and Children’s name of Employees whose manager is ‘white’.
{r|(e)(c)(EMP(e) AND CHILDREN(c) AND
/* initiate tuple variables */
e[Name] = c[Ename] AND /* join condition */
e[MGR] = ‘white’ AND /* selection cond. */
r[1st attr] = e[SAL] AND
r[2nd attr] = c[Cname] } /* projection */
DRC Examples
Consider two relations
◦ EMP(Name, MGR, DEPT, SAL)
◦ CHILDREN(Ename, Cname, Age)
Q1: Retrieve Salary and Children’s name of Employees whose manager is
‘white’
{q,s|(u)(v)(w)(x)(y)(EMP(u,v,w,q) AND CHILDREN(x,s,y) AND
/* initiate domain variables */
u = x AND /* join condition */
v = ‘white’ } /* selection condition */
/* projection is implied (q, s) */
Reading Assignment

Read on how to Translate SQL into the

Relational Algebra