Session 3 Welcome To session 3 the 7

Session 3 Welcome: To session 3 - the 7 th. learning sequence “Relational algebra “ Recap : In the previous learning sequences, we discussed some operators of relational algebra. Present learning: We shall explore the following topic: - Example Queries 1

Relational Algebra 2

Relational Algebra u. A basic expression in the relational algebra consists of either one of the following: w A relation in the database w A constant relation

Relational Instances for the Purchasing System The Supplier relation: S-number S-name S-city S 100 Ahmed Amman S 200 Ali Jarash S 300 Kasim Irbid S 400 Jasim Aqaba S 500 Rana Amman

The Part relation: P-number P-name Color Price P-city P 1 TV Silver 300 Amman P 2 Camera Black 100 Jarash P 3 Video Black 200 Amman P 4 PC Silver 400 Irbid P 5 Printer Red 100 Irbid P 6 Scanner silver 150 Jarash 5

The shipment relation: S-number S 100 P-number P 1 P 2 P 3 Quantity 100 150 200 S 100 S 200 S 300 S 400 S 500 P 4 P 1 P 2 P 2 P 4 160 200 150 400 150 80 100 6

u Q 1 - Find The cities for all suppliers. S-city (Supplier)

u Q 1 - Find The cities for all suppliers. S-city (Supplier) The result relation S-city Amman Jarash Irbid Aqaba

n. Q 2 - Find city for Ahmed.

n. Q 2 - Find city for Ahmed. Temp 1 S-name = ‘Ahmed’ (Supplier)

n. Q 2 - Find city for Ahmed. Temp 1 S-name = ‘Ahmed’ (Supplier) S-number Temp 1 S 100 S-name Ahmed S-city Amman

n. Q 2 - Find city for Ahmed. Temp 1 S-name = ‘Ahmed’ (Supplier) S-number Temp 1 S 100 Result S-city (Temp 1) Result S-city Amman S-name Ahmed S-city Amman

u Q 3 - Find the number and name for suppliers in Amman.

u Q 3 - Find the number and name for suppliers in Amman. Temp 1 S-city= ‘Amman’ (Supplier)

u Q 3 - Find the number and name for suppliers in Amman. Temp 1 S-city= ‘Amman’ (Supplier) Temp 1 S-number S-name S-city S 100 Ahmed Amman S 500 Rana Amman

u Q 3 - Find the number and name for suppliers in Amman. Temp 1 S-city= ‘Amman’ (Supplier) Temp 1 S-number S-name S-city S 100 Ahmed Amman S 500 Rana Amman Result S-number, S-name (Temp 1)

u Q 3 - Find the number and name for suppliers in Amman. Temp 1 S-city= ‘Amman’ (Supplier) Temp 1 S-number S-name S-city S 100 Ahmed Amman S 500 Rana Amman Result S-number, S-name (Temp 1) Result S-number S-name S 100 Ahmed S 500 Rana

n Q 4 - For each part shipped, find the part names and the names of all cities storing these parts.

n Q 4 - For each part shipped, find the part names and the names of all cities storing these parts. Temp 1 P-number ( Shipment )

n Q 4 - For each part shipped, find the part names and the names of all cities storing these parts. Temp 1 P-number ( Shipment ) Temp 1 P-number P 1 P 2 P 3 P 4

n Q 4 - For each part shipped, find the part names and the names of all cities storing these parts. Temp 1 P-number ( Shipment ) Temp 2 Part Temp 1

n Q 4 - For each part shipped, find the part names and the names of all cities storing these parts. Temp 1 P-number ( Shipment ) Temp 2 Part Temp 1 Temp 2 P-number P-name Color P 1 TV Silver Price 300 P-city Amman P 2 Camera Black 100 Jarash P 3 Video Black 200 Amman P 4 PC Silver 400 Irbid

n Q 4 - For each part shipped, find the part names and the names of all cities storing these parts. Temp 1 P-number ( Shipment ) Temp 2 Part Temp 1 Result P-name, p-city(Temp 2) Result P-name P-city TV Amman Camera Jarash Video Amman PC Irbid

n Q 5 - For each part shipped, find the part numbers and names of all cities supplying the parts.

n Q 5 - For each part shipped, find the part numbers and names of all cities supplying the parts. Temp 1 Shipment Supplier

n Q 5 - For each part shipped, find the part numbers and names of all cities supplying the parts. Temp 1 Shipment Supplier Temp 1 S-number P-number Quantity S-name S-city S 100 P 1 100 Ahmed Amman S 100 P 2 150 Ahmed Amman S 100 P 3 200 Ahmed Amman S 100 P 4 160 Ahmed Amman S 200 P 1 200 Ali Jarash S 200 P 2 150 Ali Jarash S 300 P 2 400 Kasim Irbid S 400 P 2 150 Jasim Aqaba S 400 P 4 80 Jasim Aqaba S 500 P 4 100 Rana Amman

n Q 5 - For each part shipped, find the part numbers and names of all cities supplying the parts. Temp 1 Shipment Supplier Result P-number, S-city ( Temp 1 ) Result P-number S-city P 1 Amman P 2 Amman P 3 Amman P 4 Amman P 1 Jarash P 2 Irbid P 2 Aqaba P 4 Amman

n Q 6 - Get supplier numbers for suppliers who supply part p 2

n Q 6 - Get supplier numbers for suppliers who supply part p 2 Temp 1 p-number = ‘p 2’ (Shipment)

n Q 6 - Get supplier numbers for suppliers who supply part p 2 Temp 1 p-number = ‘p 2’ (Shipment) Temp 1 S-number P-number Quantity S 100 S 200 P 2 150 S 300 P 2 400 S 400 P 2 150

n Q 6 - Get supplier numbers for suppliers who supply part p 2 Temp 1 P-number = ‘p 2’ (Shipment) Result S-number ( Temp 1 ) Result S-number S 100 S 200 S 300 S 400

Relational Algebra Summary: In this learning sequence, we discussed some example queries about purchasing system. 32

END 33