Transcript Database Languages - Vrije Universiteit Brussel

Chapter 7
Database Languages
The Relational Algebra
The relational Algebra

The relational algebra is a complete set of
operations on relations which allows to select
data from a relational database.

Cartesian product
Union , Intersection , Difference
Projection
q-join
Division




Sample database
R
r1
r2
r3
1
4
3
2
x
x
y
z
3
3
4
7
S1
S2
s1
s2
3
4
4
p
q
p
s1
s2
4
2
q
m
Cartesian product
R x S2
r1
r2
r3
s1
s2
1
4
3
2
1
4
3
2
x
x
y
z
x
x
y
z
3
3
4
7
3
3
4
7
4
4
4
4
2
2
2
2
q
q
q
q
m
m
m
m
Projection , q -join
Projection
q-join
R [ r2 , r3 ]
r2
r3
x
y
z
3
4
7
R [ r3 > s1 ] S1
r1
r2
r3
s1
s2
3
2
2
2
y
z
z
z
4
7
7
7
3
3
4
4
p
p
q
p
Left Outer-join
R[r3 =ls1]S1
r1
r2
r3
s1
s2
1
4
3
3
2
x
x
y
y
z
3
3
4
4
7
3
3
4
4
p
p
q
p
Union , Intersection , Difference
S1  S2
s1
3
4
4
2
s2
p
q
p
m
Intersection S1  S2
s1
4
s2
q
Difference
s1
3
4
s2
p
p
UNION
S1 \ S2
Division
Divide by
DEND
S#
P#
s1
s1
s1
s1
s1
s1
s2
s2
s3
s4
s4
s4
p1
p2
p3
p4
p5
p6
p1
p2
p2
p2
p4
p5
÷
Result
DEND/DOR
DOR 1
P#
p1
S#
s1
s2
DOR 2
P#
p2
p4
S#
s1
s4
DOR 3
P#
p1
p2
p3
p4
p5
p6
S#
s1
Example
S
S#
1
2
3
4
Sname
Jones
Duval
Codd
Carter
1. Give all parts
2. Give names of suppliers
supplying part 15
3. Give those suppliers that do
not supply part 15
SP
S#
1
1
2
3
P#
11
15
12
15
4. Give those suppliers that
supply something else than
part 15
5. Give those suppliers that
supply something but not part
15
Solutions
1.
SP [ P# ]
2.
( ( S [ S# = S# ]SP )[ P# ÷ 15] [15 ]) [ Sname ]
3.
Data Description Language
Sample Database
S
S#
S1
S2
S3
S4
S5
SNAME
Smith
Jones
Blake
Clark
Adams
STATUS
20
10
30
20
30
CITY
London
Paris
Paris
London
Athens
SP S#
S1
S1
S1
S1
S1
S2
S2
P P# PNAME COLOR WEIGHT CITY
S3
P1 Nut
Red
12
London
S4
P2 Bolt
Green
17
Paris
S4
P3 Screw
Blue
17
Rome
S4
P4 Srew
Red
14
London
P5 Cam
Blue
19
London
P6 Cog
Red
19
London
P#
P1
P2
P3
P5
P6
P1
P2
P2
P2
P4
P5
QTY
300
200
400
200
100
300
400
200
200
300
400
Create Table
CREATE
CREATE TABLE base-table-name ( base-table-element - commmalist )
where base-table-element is a column-definition
or a base-table-constraint-definition
column-definition:
column representation [ default definition ]
default definition:
NOT NULL, NULL, current-date, ....)
Base-table-constraint


candidate key:
UNIQUE ( column-commalist )
primary key:
PRIMARY KEY ( column-commalist )

foreign key:
FOREIGN KEY ( column-commalist )
REFERENCE base-table [ ( column-commalist ) ]
[ ON DELETE option ]
[ ON UPDATE option ]
option: NO ACTION , CASCADE, SET DEFAULT, SET NULL

check constraint:
CHECK ( conditional-expression )
CREATE table example
CREATE TABLE SP
(S# S# NOT NULL, P# P# NOT NULL, QTY QTY NOT
NULL,
PRIMARY KEY ( S# , P# )
FOREIGN KEY ( S# ) REFERENCE S
ON DELETE CASCADE
ON UPDATE CASCADE,
FOREIGN KEY ( P# ) REFERENCE P
ON DELETE CASCADE
ON UPDATE CASCADE,
CHECK ( QTY > 0 AND QTY < 5001 ) ) ;
DDL - table modification
ALTER table
ALTER TABLE base-name-table
ADD column-name data-type ;
( “not null” is not permitted )
DROP table
DROP TABLE base-table-name
DDL - Indexes
CREATE index
CREATE [ UNIQUE ] INDEX index-name
ON base-table-name ( column-name [ ORDER ]
[ , column-name [ ORDER ] ... )
DROP index
DROP INDEX index-name ;
Data Manipulation Language
DML - Data Manipulation Language
SQL
SELECT [ DISTINCT ] field(s)
FROM
table(s)
[ WHERE predicate ]
[ GROUP BY field(s) [ HAVING predicate ] ]
[ ORDER BY field(s) ] ;
Simple Retrieval - SQL
Get part numbers for all parts supplied
SELECT P#
SELECT DISTINCT P#
FROM SP ;
FROM SP ;
P#
P1
P2
P3
P4
P5
P6
P1
P2
P2
P2
P4
P5
P#
P1
P2
P3
P4
P5
P6
Simple retrieval - QBE
Get part numbers for all parts supplied
SP
S#
P#
QTY
P._PX
SP
S#
P#
P.ALL._PX
QTY
Retrieval of expressions
For all parts get the part number and the weight in grams
( in the table weights are in pounds ).
SELECT P.p# , P.weight*454
FROM P ;
P
P#
P._PX
Pname
Color weight
P.Weight
*454
OUTPUT
City
P#
P1
P2
P3
P4
P5
P6
5448
7718
7718
6356
5448
8626
Simple retrieval of table
Get full details of all suppliers
SELECT *
FROM S ;
SQL
QBE
S
S# Sname
P._SX P._SN
S
P.
S# Sname
Status
P._ST
Status
City
P._SC
City
Qualified retrieval 1
Get supplier numbers for suppliers located in Paris or with
status greater than 20.
SELECT S#
FROM S
WHERE City = ‘PARIS’
OR Status > 20 ;
S
S# Sname Status
P._SX
> 20
P._SY
City
Paris
Qualified retrieval - 2
Get supplier numbers for suppliers located in Paris with
status greater than 20.
SELECT S#
FROM S
WHERE City = ‘PARIS’
AND Status > 20 ;
S
S# Sname Status
P._SX
> 20
City
Paris
Qualified retrieval with ordering
Get supplier numbers and status for suppliers in Paris
in descending order of status
SELECT S# , Status
FROM S
WHERE City = ‘Paris’
ORDER BY Status DESC ;
S
S# Sname Status
City
P._SX
P.DO._ST Paris
Simple Equi-join
Get all combinations of supplier and part information such that
the supplier and part in question are located in the same city
SELECT S.* , P.*
FROM S , P
WHERE S.City = P.City ;
SQL
QBE
S
S#
Sname Status
P.
P
P.
City
_X
P#
Pname
Color weight
City
_X
Greater-than join
Get all combinations of supplier and part information such that
the supplier city follows the part city in alphabetical order.
SQL
SELECT S.* , P.*
FROM S , P
WHERE S.City > P.City
QBE
S
S#
Sname Status
P.
P
P.
City
> _X
P#
Pname
Color weight
City
_X
Join with additional condition
Get all combinations of supplier and part information such that
the supplier and part are colocated , but omitting suppliers
with status > 20 .
SQL
SELECT S.* , P.*
FROM S , P
WHERE S.City = P.City
AND S.Status NOT > 20 ;
QBE
S
S#
Sname Status
P.
P
P.
NOT > 20
P#
Pname
City
_X
Color weight
City
_X
Retrieving specified fields from a join
Get all part-number / supplier-number combinations such that
supplier and part are colocated
SQL
SELECT S.S# , P.P#
FROM S , P
WHERE S.City = P.City
QBE
S
S#
Sname Status
P._SX
P
P#
P._PX
City
_X
Pname
Color weight
City
_X
Join of three tables
Get all pairs of city names such that a supplier located in the first
city supplies a part stored in the second city
SQL
SELECT DISTINCT S.City , P.City
FROM S , P , SP
WHERE S.S# = SP.S#
AND
SP.P# = P.P# ;
QBE
S
S#
Sname Status
_X
P
P#
_Y
Pname
City
SP S#
P#
P._CS
_X
_Y
Color weight
City
P._PC
QTY
Function in a select clause
Get the total number of suppliers
SQL
SELECT count ( * )
FROM S ;
QBE
S
S#
Sname
P.COUNT._SX
Status
City
Function in select clause with predicate
Get the total number of suppliers supplying part 2.
SQL
SELECT count ( * )
FROM SP
WHERE P# = ‘P2’ ;
QBE
SP
S#
P.CNT.ALL._SX
P#
P2
QTY
Join of table with itself
Get all pairs of supplier numbers such that the two suppliers
are colocated .
SELECT FIRST.S# , SECOND.S#
FROM S FIRST , S SECOND
WHERE FIRST.City = SECOND.City
AND FIRST.S# < SECOND.S# ;
S
S#
_SX
_SY
Conditions
_SX < _SY
Sname
Status
RESULT
P.
City
_CZ
_CZ
FIRST SECOND
_SX
_SY
Use of GROUP BY
For each part supplied , get the part number and the total
shipment quantity for that part
SQL
SELECT P# , SUM(QTY)
FROM SP
GROUP BY P# ;
QBE
SP
S#
P#
P.G._PX
QTY
P.SUM.ALL._QX
Use of HAVING
Get part numbers for all parts supplied by more than one supplier.
SQL
SELECT P#
FROM SP
GROUP BY P#
HAVING COUNT(*) > 1 ;
QBE
SP
S#
P#
_SX
NOT._SX
P._PX
_PX
QTY
or
SP
S#
CNT.ALL._SX> 1
P#
P.G._PX
QTY
Retrieval involving a subquery
Get supplier names for suppliers who supply part P2 .
SELECT UNIQUE SNAME
FROM S , SP
WHERE S.S# = SP.S#
AND SP.P# = ‘P2’ ;
S
S#
Sname
_X
SP
SELECT UNIQUE Sname
FROM S
WHERE S# IN
(SELECT S#
FROM SP
WHERE P# = ‘P2’) ;
S#
_X
Status
P._SN
P#
P2
QTY
City
SELECT Sname
FROM S
WHERE ‘P2’ IN
(SELECT P#
FROM SP
WHERE S#= S.S#) ;
Single-record Update
Change color of part P2 to yellow
SQL: Update distinct P
Set color = “yellow”
where P# = P2 ;
QBE:
P
p#
p2
pname
color
U.yellow
weight
city
Multiple Update
Double the status of all suppliers in London
SQL
QBE
Update S
Set status = status * 2
where City = “London” ;
S
S#
U.
_SX
_SX
sname
status
_ST
2 * _ST
city
London
Update involving sub-query
Set quantity to zero for all suppliers in London
SQL
QBE
Update SP
Set qty = 0
where “London” =
( select city
from S
where s# = SP.s# ) ;
SP
s#
U.
_SX
S
s#
_SX
sname
p#
qty
0
status
city
London
Views
CREATE VIEW name
AS SELECT statement ;
Example:
CREATE VIEW good-suppliers
AS SELECT s# , status , city
FROM S
WHERE status = 15 ;
The VIEW-definition is stored in the directory
but the select is not performed
VIEWS - 2
Example:
CREATE VIEW PQ ( P# , sumqty )
AS SELECT p# , SUM(qty)
FROM SP
GROUP BY p# ;



Views can be defined in terms of other views
Some views are updateble
Views can be dropped
VIEWS - usage
VIEWS can be used just like base tables
CREATE VIEW
LONDON-SUPPLIERS
AS SELECT s, sname , status
FROM S
WHERE city = ‘London’ ;
Two formulations with the same result
SELECT *
FROM LONDON-SUPPLIERS
WHERE status < 50
ORDER by s# ;
SELECT s# , sname , status
FROM S
WHERE status < 50
AND city = ‘London’
ORDER BY s# ;
SQL System Catalog
The system catalog is also a relational database
SYSTABLES ( name , creator , colcount , ... )
SYSCOLUMNS ( name , tbname , coltype , ... )
SYSINDEX
( name , tbname , creator , ... )
Examples:
SELECT tbname
FROM SYSCOLUMNS
WHERE name = ‘s#’ ;
SELECT name
FROM SYSCOLUMNS
WHERE tbname = ‘S’ ;
Updating the catalog is not possible
QBE dictionary
Retrieval of table names : Get all tables known to the system
P.
Creation of new table
I. S
S#
Sname Status City
I. S
S#
Sname Status City
domain S#
Sname Status City
type char 5 char 20 fixed char 15
key
Y
U.N
U.N
U.N
invers Y
U.N
U.N
U.N