Download - Database Languages
![Page 1: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/1.jpg)
Database Languages
Chapter 7
![Page 2: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/2.jpg)
![Page 3: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/3.jpg)
The Relational Algebra
![Page 4: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/4.jpg)
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 -join Division
![Page 5: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/5.jpg)
Sample database
R r1 r2 r3 S1 s1 s2
1 x 3 3 p4 x 3 4 q3 y 4 4 p2 z 7
S2 s1 s2
4 q2 m
![Page 6: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/6.jpg)
Cartesian product
R x S2 r1 r2 r3 s1 s2
1 x 3 4 q4 x 3 4 q3 y 4 4 q2 z 7 4 q1 x 3 2 m4 x 3 2 m3 y 4 2 m2 z 7 2 m
![Page 7: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/7.jpg)
Projection , -join
Projection R [ r2 , r3 ] r2 r3
x 3y 4z 7
-join R [ r3 > s1 ] S1
r1 r2 r3 s1 s2
3 y 4 3 p2 z 7 3 p2 z 7 4 q2 z 7 4 p
![Page 8: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/8.jpg)
Left Outer-join
r1 r2 r3 s1 s2
1 x 3 3 p
4 x 3 3 p
3 y 4 4 q
3 y 4 4 p
2 z 7
R[r3 =ls1]S1
![Page 9: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/9.jpg)
Union , Intersection , Difference
UNION S1 S2 s1 s23 p4 q4 p2 m
Intersection S1 S2 s1 s24 q
Difference S1 \ S2 s1 s23 p4 p
![Page 10: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/10.jpg)
Division
S# P#
s1 p1s1 p2s1 p3s1 p4s1 p5s1 p6s2 p1s2 p2s3 p2s4 p2s4 p4s4 p5
Divide by ÷ Result DEND/DORDENDDOR 1 P#
p1
DOR 2
DOR 3
P#p2p4
P#p1p2p3p4p5p6
S#s1s2
S#s1s4
S#s1
![Page 11: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/11.jpg)
Example
S S# Sname
1 Jones2 Duval3 Codd4 Carter
SP S# P#
1 111 152 123 15
1. Give all parts
2. Give names of suppliers supplying part 15
3. Give those suppliers that do not supply part 15
4. Give those suppliers that supply something else than part 15
5. Give those suppliers that supply something but not part 15
![Page 12: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/12.jpg)
Solutions
1. SP [ P# ]
2. ( ( S [ S# = S# ]SP )[ P# ÷ 15] [15 ]) [ Sname ]
3.
![Page 13: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/13.jpg)
Data Description Language
![Page 14: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/14.jpg)
Sample Database
S S# SNAME STATUS CITY
S1 Smith 20 London
S2 Jones 10 Paris
S3 Blake 30 Paris
S4 Clark 20 London
S5 Adams 30 Athens
P P# PNAME COLOR WEIGHT CITYP1Nut Red 12 LondonP2Bolt Green 17 ParisP3Screw Blue 17 RomeP4Srew Red 14 LondonP5Cam Blue 19 LondonP6CogRed 19 London
SP S# P# QTYS1 P1 300S1 P2 200S1 P3 400S1 P5 200S1 P6 100S2 P1 300S2 P2 400S3 P2 200S4 P2 200S4 P4 300S4 P5 400
![Page 15: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/15.jpg)
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, ....)
Create Table
![Page 16: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/16.jpg)
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 )
![Page 17: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/17.jpg)
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 CASCADEON UPDATE CASCADE,
FOREIGN KEY ( P# ) REFERENCE PON DELETE CASCADEON UPDATE CASCADE,
CHECK ( QTY > 0 AND QTY < 5001 ) ) ;
![Page 18: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/18.jpg)
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
![Page 19: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/19.jpg)
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 ;
![Page 20: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/20.jpg)
Data Manipulation Language
![Page 21: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/21.jpg)
DML - Data Manipulation Language
SELECT [ DISTINCT ] field(s)
FROM table(s)
[ WHERE predicate ]
[ GROUP BY field(s) [ HAVING predicate ] ]
[ ORDER BY field(s) ] ;
SQL
![Page 22: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/22.jpg)
Simple Retrieval - SQL
Get part numbers for all parts supplied
SELECT P#FROM SP ;
SELECT DISTINCT P#FROM SP ;
P#P1P2P3P4P5P6P1P2P2P2P4P5
P#P1P2P3P4P5P6
![Page 23: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/23.jpg)
Simple retrieval - QBE
Get part numbers for all parts supplied
SP S# P# QTY
P._PX
SP S# P# QTY
P.ALL._PX
![Page 24: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/24.jpg)
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*454FROM P ;
P P# Pname Color weight City
P._PX P.Weight *454
P# P1 5448P2 7718P3 7718P4 6356P5 5448P6 8626
OUTPUT
![Page 25: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/25.jpg)
Simple retrieval of table
Get full details of all suppliers
SELECT *FROM S ;
S S# Sname Status City
P._SX P._SN P._ST P._SC
S S# Sname Status City
P.
SQL
QBE
![Page 26: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/26.jpg)
Qualified retrieval 1
Get supplier numbers for suppliers located in Paris or with status greater than 20.
SELECT S#FROM SWHERE City = ‘PARIS’ OR Status > 20 ;
S S# Sname Status CityP._SX > 20P._SY Paris
![Page 27: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/27.jpg)
Qualified retrieval - 2
Get supplier numbers for suppliers located in Paris with status greater than 20.
SELECT S#FROM SWHERE City = ‘PARIS’ AND Status > 20 ;
S S# Sname Status CityP._SX > 20 Paris
![Page 28: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/28.jpg)
Qualified retrieval with ordering
Get supplier numbers and status for suppliers in Parisin descending order of status
SELECT S# , StatusFROM S
WHERE City = ‘Paris’ORDER BY Status DESC ;
S S# Sname Status City
P._SX P.DO._ST Paris
![Page 29: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/29.jpg)
Simple Equi-join
Get all combinations of supplier and part information such thatthe supplier and part in question are located in the same city
SELECT S.* , P.*FROM S , PWHERE S.City = P.City ;
S S# Sname Status City
P. _X
P.
P P# Pname Color weight City
_X
SQL
QBE
![Page 30: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/30.jpg)
Greater-than join
Get all combinations of supplier and part information such thatthe supplier city follows the part city in alphabetical order.
SELECT S.* , P.*FROM S , PWHERE S.City > P.City
S S# Sname Status City
P. > _X
P.
P P# Pname Color weight City
_X
SQL
QBE
![Page 31: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/31.jpg)
Join with additional condition
Get all combinations of supplier and part information such thatthe supplier and part are colocated , but omitting supplierswith status > 20 .
SELECT S.* , P.*FROM S , PWHERE S.City = P.City AND S.Status NOT > 20 ;
S S# Sname Status City
P. NOT > 20 _X
P.
P P# Pname Color weight City
_X
SQL
QBE
![Page 32: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/32.jpg)
Retrieving specified fields from a join
Get all part-number / supplier-number combinations such thatsupplier and part are colocated
SELECT S.S# , P.P#FROM S , PWHERE S.City = P.City
S S# Sname Status City
P._SX _X
P._PX
P P# Pname Color weight City
_X
SQL
QBE
![Page 33: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/33.jpg)
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
SELECT DISTINCT S.City , P.CityFROM S , P , SPWHERE S.S# = SP.S# AND SP.P# = P.P# ;
S S# Sname Status City
_X P._CS
_Y
P P# Pname Color weight City
P._PC
SQL
QBE
SP S# P# QTY
_X _Y
![Page 34: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/34.jpg)
Function in a select clause
Get the total number of suppliers
SQL
QBE
SELECT count ( * )FROM S ;
S S# Sname Status City
P.COUNT._SX
![Page 35: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/35.jpg)
Function in select clause with predicate
Get the total number of suppliers supplying part 2.
SQL
QBE
SELECT count ( * )FROM SPWHERE P# = ‘P2’ ;
SP S# P# QTY
P.CNT.ALL._SX P2
![Page 36: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/36.jpg)
Join of table with itself
Get all pairs of supplier numbers such that the two suppliersare colocated .
SELECT FIRST.S# , SECOND.S#FROM S FIRST , S SECOND
WHERE FIRST.City = SECOND.City AND FIRST.S# < SECOND.S# ;
S S# Sname Status City
_SX_SY
_CZ_CZ
Conditions
_SX < _SY
RESULT FIRST SECOND
P. _SX _SY
![Page 37: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/37.jpg)
Use of GROUP BY
For each part supplied , get the part number and the total shipment quantity for that part
SQL
QBE
SELECT P# , SUM(QTY) FROM SPGROUP BY P# ;
SP S# P# QTY
P.G._PX P.SUM.ALL._QX
![Page 38: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/38.jpg)
Use of HAVINGGet part numbers for all parts supplied by more than one supplier.
SQL
QBE
SELECT P# FROM SPGROUP BY P#HAVING COUNT(*) > 1 ;
SP S# P# QTY
_SX P._PXNOT._SX _PX
or
SP S# P# QTY
CNT.ALL._SX> 1 P.G._PX
![Page 39: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/39.jpg)
Retrieval involving a subquery
Get supplier names for suppliers who supply part P2 .
SELECT UNIQUE SNAME FROM S , SPWHERE S.S# = SP.S# AND SP.P# = ‘P2’ ;
SELECT UNIQUE SnameFROM SWHERE S# IN (SELECT S# FROM SP WHERE P# = ‘P2’) ;
SELECT SnameFROM SWHERE ‘P2’ IN (SELECT P# FROM SP WHERE S#= S.S#) ;
SP S# P# QTY
S S# Sname Status City
_X P._SN
_X P2
![Page 40: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/40.jpg)
Single-record Update
Change color of part P2 to yellow
SQL: Update distinct PSet color = “yellow”where P# = P2 ;
QBE: P p# pname color weight city
p2 U.yellow
![Page 41: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/41.jpg)
Multiple Update
Double the status of all suppliers in London
SQL Update SSet status = status * 2where City = “London” ;
QBES S# sname status city
_SX _ST LondonU. _SX 2 * _ST
![Page 42: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/42.jpg)
Update involving sub-query
Set quantity to zero for all suppliers in London
SQL Update SPSet qty = 0where “London” =
( select city from S where s# = SP.s# ) ;
QBESP s# p# qty
U. _SX 0
S s# sname status city
_SX London
![Page 43: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/43.jpg)
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
![Page 44: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/44.jpg)
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
![Page 45: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/45.jpg)
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 * SELECT s# , sname , statusFROM LONDON-SUPPLIERS FROM SWHERE status < 50 WHERE status < 50ORDER by s# ; AND city = ‘London’ ORDER BY s# ;
![Page 46: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/46.jpg)
SQL System Catalog
The system catalog is also a relational database
SYSTABLES ( name , creator , colcount , ... ) SYSCOLUMNS ( name , tbname , coltype , ... ) SYSINDEX ( name , tbname , creator , ... )
SELECT tbnameFROM SYSCOLUMNSWHERE name = ‘s#’ ;
SELECT name FROM SYSCOLUMNSWHERE tbname = ‘S’ ;
Examples:
Updating the catalog is not possible
![Page 47: Database Languages](https://reader036.vdocuments.us/reader036/viewer/2022062504/56813ffc550346895dab2a8c/html5/thumbnails/47.jpg)
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 Citytype char 5 char 20 fixed char 15key Y U.N U.N U.Ninvers Y U.N U.N U.N