8th december 2015birkbeck college, u. london1 introduction to computer systems lecturer: steve...
TRANSCRIPT
8th December 2015 Birkbeck College, U. London 1
Introduction to Computer Systems
Lecturer: Steve Maybank
Department of Computer Science and Information [email protected]
Autumn 2015
Week 11a: Relational Operations
8th December 2015 Brookshear, Section 9.2 2
Database Queries
Show all orders for plate. Show all order numbers and the associated
products. Show all order numbers and the associated
company names.In the relational model the answers are always relations, ie. tables.
Can the answers to database queries be found in asystematic way?
8th December 2015 Brookshear, Section 9.2 3
Relational Operations The answers to database queries
are obtained using the relational operations
SELECT PROJECT JOIN These operations produce new
relations from old ones.
8th December 2015 Birkbeck College 4
Relational Database
37 102 £1000 1.7.06 Plate
43 103 £2000 5.5.06 Case
20 54 £3400 2.4.06 Panel
102 Sperry 1 The Lane
103 Univac 15 Retail Road
54 Honeywell
205 North Street
Num CNum
Price
Due date
Product
O C Num Name Address
O: relation containing ordersC: relation containing details of customers
8th December 2015 Brookshear, Section 9.2 5
Table of Orders for Plate
37 102 £1000
1.7.06
Plate
43 103 £2000
5.5.06
Case
20 54 £3400
2.4.06
Panel
Num CNum
Price
Due date
Product
37 102 £1000
1.7.06
Plate
O SELECT: take all tuplesfor which the productis Plate, to give …
Num CNum
Price
Due date
Product
ANS1
ANS1<- SELECT from O where Product=Plate
8th December 2015 Brookshear, Section 9.2 6
Table of Order Numbers and Products
37 102 £1000
1.7.06
Plate
43 103 £2000
5.5.06
Case
20 54 £3400
2.4.06
Panel
Num
CNum
Price
Due date
Product
37 Plate
43 Case
20 Panel
O PROJECT: remove attributes CNum, Price and Due date to leave…
Num Product
ANS2
ANS2 <- PROJECT Num, Product from O
8th December 2015 Brookshear, Section 9.2 7
Example of a JOIN
a 1
b 2m 5
n 2
A.V A.W
Relation A Relation B
B.X B.Y
a 1 m 5
a 1 n 2
b 2 m 5
b 2 n 2
A.V A.W B.X B.Y
C <- JOIN A and B
8th December 2015 Brookshear, Section 9.2 8
Definition of JOIN
Let A, B, C be relations such that C=JOIN(A,B)
The tuples in C are obtained by merging tuples a from A and b from B
All possible pairs a, b are used to construct the tuples in C.
8th December 2015 Brookshear, Section 9.2 9
Relation Containing O.Num and C.Name
37 102 £1000 1.7.06 Plate
43 103 £2000 5.5.06 Case
20 54 £3400 2.4.06 Panel
102 Sperry 1 The Lane
103 Univac 15 Retail Road
54 Honeywell
205 North Street
Num CNum
Price
Due date
Product
O CNum Name Address
JOIN: combine tuples in O and C. Then use PROJECT toremove all attributes except O.Num and C.Name.
8th December 2015 Brookshear, Section 9.2 10
JOIN and then PROJECT
37 102 £1000 1.7.06
Plate 102 Sperry 1 The Lane
43 103 £2000 5.5.06
Case 103 Univac
15 Retail Road
20 54 £3400 2.4.06
Panel
54 Honeywell
205 North Street
O.Num
O.CNum
O.Prc O.D O.Prd C.Num
C.Name C.A
37 Sperry
43 Univac
20 Honeywell
TEMP <- JOIN O and C where O.CNum=C.Num
ANS3O.Num C.Nam
e
ANS3<-PROJECT O.Num, C.Name from TEMP
8th December 2015 Brookshear, Section 9.2 11
SELECT The original relation is R1, the new
relation created by SELECT is R2 and the conditions on the attribute values in R2 are c1,…cn.
R2 <- SELECT from R1 where c1,…, cn.
Example:ANS1 <- SELECT from O where O.Product=‘Plate’
8th December 2015 Brookshear, Section 9.2 12
PROJECT
The original relation is R1, the new relation created by PROJECT is R2 and the attributes projected to R2 are att1,…, attn.
R2 <- PROJECT att1,…attn from R1 Example:
ANS2 <- PROJECT O.Num, O.Product from O
8th December 2015 Brookshear, Section 9.2 13
JOIN
The original relations are R1, R2, the new relation created by JOIN is R3 and the conditions on the attribute values in R3 are c1,…cn.
R3 <- JOIN R1 and R2 where c1,…, cn Example:
Temp <- JOIN O, C where O.CNum=C.Num
8th December 2015 Brookshear, Section 9.2 14
Second Example of JOIN
R3 <- JOIN O and C where O.Price>£1500 and O.CNum=C.Num
43 103 £2000 5.5.06
Case 103 Univac
15 Retail Road
20 54 £3400 2.4.06
Panel 54 Honeywell
205 North Street
O.Num
O.CNum
O.Prc
O.D O.Prd
C.Num
C.Name
C.A
R3
8th December 2015 15
Join and Keys
34 16 D1
28 22 D2
key foreign key
data key data
16 DB1
22 DB2
BA
C <- JOIN A and B where A.foreign key=B.key
34 16 D1 16 DB1
28 22 D2 22 DB2
A.key
A.Foreign key
A.data B.key B.data
C
8th December 2015 cf. Brookshear, Section 9.2 16
Symmetries A relation does not specify the order of
the attributes or the order of the tuples.
102
Sperry 1 The Lane
103
Univac 15 Retail Road
54 Honeywell
205 North Street
is thesame as
102
1 The Lane
Sperry
54 205 North Street
Honeywell
103
15 Retail Road
Univac
C C
8th December 2015 cf. Brookshear, Section 9.2 17
Repeated Tuples
A relation cannot have repeated tuples
102 Sperry 1 The Lane
103 Univac 15 Retail Road
54 Honeywell 205 North Street
54 Honeywell 205 North Street
Not a relation in arelational database.
8th December 2015 Birkbeck College 18
Attributes in Different Relations
An attribute of a given relation is not an attribute of any other relation.
E.g. O.CNum is a different attribute fromC.Num.
Structured Query Language
Standardised language for database queries originally developed and marketed by IBM.
An SQL command describes the required information. It does not specify how to obtain that information.
8th December 2015 19Brookshear, Section 9.2
8th December 2015 20
SQL Statement
select O.Num, C.Namefrom O, Cwhere O.CNum=C.Num
Interpretationfrom O, C: Join O and Cwhere O.Cnum=C.Num: retain tuples satisfying this conditionselect O.Num, C.Name: project, retaining these attributes
8th December 2015 Brookshear Ch. 9, problem 12 21
Problem
3 J
4 K
In terms of the relations shown below, what is the appearance of therelation RESULT after executing each of these instructions?
X relationU V W
A Z 5
B D 3
C O 5
Y relation R S
a. RESULT <- PROJECT W from Xb. RESULT <- SELECT from X where W=5c. RESULT <- PROJECT S from Yd. RESULT <- JOIN X and Y where X.W>= Y.R.