Introduction to Computer Systems Department of Computer Science

Introduction to Computer Systems Department of Computer Science and Information Systems Lecturer: Steve Maybank sjmaybank@dcs. bbk. ac. uk Spring 2020 Week 11 a: Relational Operations 24 th March 2020 Birkbeck College, U. London 1

Database Queries n n n 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 a systematic way? Brookshear, Section 9. 2 2

Relational Operations n n n The answers to database queries are obtained using the relational operations SELECT PROJECT JOIN These operations produce new relations from old ones Brookshear, Section 9. 2 3

Relational Database O Num C Price Due Product date 37 102 £ 1000 1. 7. 19 Plate 43 103 £ 2000 5. 5. 19 Case 20 54 £ 3400 2. 4. 19 Panel Num Name Address 102 Sperry 1 The Lane 103 Univac 15 Retail Road 54 Honeywell 205 North Street O: relation containing orders C: relation containing details of customers Birkbeck College 4

Table of Orders for Plate O Num 37 CNum 102 SELECT: take all tuples for which the product is Plate, to give … Price Due Product date £ 1000 1. 7. 19 ANS 1 Plate Num 43 103 £ 2000 5. 5. 19 Case 20 54 £ 3400 2. 4. 19 Panel 37 CNum 102 Price Due Product date £ 1000 1. 7. 19 Plate ANS 1<- SELECT from O where Product=Plate Brookshear, Section 9. 2 5

Table of Order Numbers and Products O Num CNum Price Due Product date PROJECT: remove attributes CNum, Price and Due date to leave… ANS 2 37 102 £ 1000 1. 7. 19 Plate Num Product 43 103 £ 2000 5. 5. 19 Case 37 Plate 20 54 £ 3400 2. 4. 19 Panel 43 Case 20 Panel ANS 2 <- PROJECT Num, Product from O Brookshear, Section 9. 2 6

Example of a JOIN A. V A. W Relation A B. X B. Y a 1 b 2 Relation B m 5 n 2 A. V A. W B. X B. Y C <- JOIN A and B a a b b 1 1 2 2 Brookshear, Section 9. 2 m n 5 2 7

Definition of JOIN n n n 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. Brookshear, Section 9. 2 8

Relation Containing O. Num and C. Name O Num 37 43 20 CNum 102 103 54 C Price Due Product date £ 1000 £ 2000 £ 3400 1. 7. 19 5. 5. 19 2. 4. 19 Num Name 102 Sperry 1 The Lane 103 Univac 15 Retail Road 54 Honeywell 205 North Street Plate Case Panel Address JOIN: combine tuples in O and C. Then use PROJECT to remove all attributes except O. Num and C. Name. Brookshear, Section 9. 2 9

JOIN and then PROJECT TEMP <- JOIN O and C where O. CNum=C. Num O. CNum O. Prc O. D O. Prd C. Num C. Name 37 102 £ 1000 1. 7. 19 Plate 102 Sperry 1 The Lane 43 103 £ 2000 5. 5. 19 Case 103 Univac 15 Retail Road 20 54 £ 3400 2. 4. 19 Panel 54 Honey well ANS 3 C. A 205 North Street O. Num C. Name 37 Sperry 43 Univac 20 Honeywell ANS 3<-PROJECT O. Num, C. Name from TEMP Brookshear, Section 9. 2 10

SELECT § The original relation is R 1, the new relation created by SELECT is R 2 and the conditions on the attribute values in R 2 are c 1, …cn § R 2 <- SELECT from R 1 where c 1, …, cn § Example: ANS 1 <- SELECT from O where O. Product=‘Plate’ Brookshear, Section 9. 2 11

PROJECT § The original relation is R 1, the new relation created by PROJECT is R 2 and the attributes projected to R 2 are att 1, …, attn. § R 2 <- PROJECT att 1, …attn from R 1 § Example: ANS 2 <- PROJECT O. Num, O. Product from O Brookshear, Section 9. 2 12

JOIN § The original relations are R 1, R 2, the new relation created by JOIN is R 3 and the conditions on the attribute values in R 3 are c 1, …cn § R 3 <- JOIN R 1 and R 2 where c 1, …, cn § Example: Temp <- JOIN O, C where O. CNum=C. Num Brookshear, Section 9. 2 13

Second Example of JOIN n R 3 <- JOIN O and C where O. Price>£ 1500 and O. CNum=C. Num R 3 O. Num O. CNum O. Prc O. D O. Prd C. Num C. Name C. A 43 103 £ 2000 5. 5. 19 Case 103 Univac 15 Retail Road 20 54 £ 3400 2. 4. 19 Panel 54 Honey 205 North well Street Brookshear, Section 9. 2 14

Join and Keys A key B foreign key data 34 16 D 1 28 22 D 2 key data 16 DB 1 22 DB 2 C <- JOIN A and B where A. foreign key=B. key C A. key A. Foreign key A. data B. key B. data 34 16 D 1 16 DB 1 28 22 D 2 22 DB 2 15

Symmetries n A relation does not specify the order of the attributes or the order of the tuples C C 102 Sperry 1 The Lane 103 Univac 15 Retail Road 54 Honeywell 205 North Street is the same as 102 1 The Lane Sperry 54 Honeywell 205 North Street 103 15 Retail Road cf. Brookshear, Section 9. 2 Univac 16

Repeated Tuples n A relation cannot have repeated tuples 102 Sperry 1 The Lane 103 Univac 15 Retail Road 54 Honeywell 205 North Street Not a relation in a relational database. cf. Brookshear, Section 9. 2 17

Attributes in Different Relations n n An attribute of a given relation is not an attribute of any other relation E. g. O. CNum is a different attribute from C. Num Birkbeck College 18

Problem 1 In terms of the relations shown below, what is the appearance of the relation RESULT after executing each of these instructions? X relation U V W Y relation R S A Z 5 3 J B D 3 4 K C O 5 a. b. c. d. RESULT <<<<- PROJECT W from X SELECT from X where W=5 PROJECT S from Y JOIN X and Y where X. W>= Y. R. Brookshear Ch. 9, problem 12 19

Problem 2 n D. Name D. ID D. Credits C. Name C. ID Networks IT 655 Computing CS 543 4 Database CS 543 Computing EC 653 5 VLSI EC 653 Electronics IT 655 4 Electronics EC 653 5 IT CS 543 4 IT IT 655 4 Use select, project and join to answer a) which depts (D) offer IT 665? b) list all depts. ; c) which depts offer 4 credit courses (C)? Brookshear, Ch 9 rev. problem 13 20

Problem 3 n To what extent is the order in which SELECT and PROJECT operations are applied to a relation significant? That is, under what conditions will Selecting and then Projecting produce the same results as first Projecting and then Selecting? BB Ch 9 Review Problem 10 21

Structured Query Language n n 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. Brookshear, Section 9. 2 22

SQL Statement 1 SELECT O. Num, C. Name FROM O, C WHERE O. CNum=C. Num Interpretation from O, C: Join O and C where O. Cnum=C. Num: retain tuples satisfying this condition select O. Num, C. Name: project, retaining these attributes 23

General Form SELECT <attributes> FROM <relations> WHERE <conditions> Interpretation Join relations Impose conditions Project attributes 24

SQL Statement 2 SELECT O. Num, O. Price FROM O Interpretation Project O. Num and O. Price from O 25