Relational Algebra Relational Calculus 204204 Database Management Algebra

Relational Algebra & Relational Calculus 204204 Database Management

พชคณตเชงสมพนธ Algebra( n Selection (Restrict) n Project n Product (Relational

พชคณตเชงสมพนธ Algebra( n Union n Intersection n Difference (Relational

พชคณตเชงสมพนธ Algebra( n Join n Division (Relational

Selection (Restrict)

Selection (Restrict) รปแบบ ชอรเลชน WHERE <CONDITION< condition(Relation)

Project

พชคณตเชงสมพนธ Algebra( n Selection (Restrict) Project n Product n Union n Intersection n Difference n Join n Division n (Relational

Product R a b S 1 2 3 R S a 1 a 2 a 3 b 1 b 2 b 3 S R 1 a 2 b 3 c

Product R a b S 1 2 3 R S a 1 a 2 a 3

Product R a b S 1 2 3 R S a 1 a 2 a 3 b 1 b 2 b 3

Product R a b S 1 2 3 S R 1 a 1 b

Product R a b S 1 2 3 S R 1 a 1 b 2 a 2 b

Product R a b S 1 2 3 S R 1 a 1 b 2 a 2 b 3 a 3 b

พชคณตเชงสมพนธ Algebra( n Selection (Restrict) Project n Product n Union n Intersection n Difference n Join n Division n (Relational

Union

Intersection

Difference

พชคณตเชงสมพนธ Algebra( n Selection (Restrict) Project n Product n Union n Intersection n Difference n Join n Division n (Relational

Join T A B a 1 b 2 U B 1 1 3 C x y z T⊳⊲U A B C a 1 X a 1 y

Join T A B a 1 b 2 U B 1 1 3 C x y z T⊳⊲U A B C

Join T A B a 1 b 2 U B 1 1 3 C x y z T⊳⊲U A B C a 1 x

Join T A B a 1 b 2 U B 1 1 3 C x y z T⊳⊲U A B C a 1 x a 1 y

Join T A B a 1 b 2 U B 1 1 3 C x y z T ⋈U A B C a 1 x a 1 y

Join ตวอยาง X S# SName B 001 Jack B 002 Tom SCity Korat Bangkok

Join ตวอยาง Y TName Jim Noi Lin Subject Math English Physic PCity Korat Bangkok Korat

Join X S# SName SCity B 001 Jack Korat B 002 Tom Bangkok Y TName Jim Noi Lin Subject Math English Physic PCity Korat Bangkok Korat

Join X S# SName SCity B 001 Jack Korat B 002 Tom Bangkok S# Y SName SCity TName Jim Noi Lin Subject Math English Physic PCity Korat Bangkok Korat PCity

Join X S# SName SCity B 001 Jack Korat B 002 Tom Bangkok S# S# B 001 SName Jack SCity Korat Y TName Jim Lin TName Jim Noi Lin Subject Math Physic Subject Math English Physic PCity Korat Bangkok Korat PCity Korat

Join X S# SName SCity B 001 Jack Korat B 002 Tom Bangkok X⋈ Y S# B 001 B 002 SName Jack Tom SCity Korat Bangkok Y TName Jim Lin Noi TName Jim Noi Lin Subject Math Physic English Subject Math English Physic PCity Korat Bangkok Korat PCity Korat Bangkok

Join T A B a 1 b 2 U B 1 1 3 C x y z T⋈U A B C a 1 X a 1 y A a a b T⋊c. U B C 1 X 1 y 2 null

Join T A B a 1 b 2 U B 1 1 3 C x y z T⋊c. U A B C a 1 X a 1 y

Join T A B a 1 b 2 U B 1 1 3 C x y z T⋊c. U A B C

Join T A B a 1 b 2 U B 1 1 3 C x y z T⋊c. U A B C a 1 x

Join T A B a 1 b 2 U B 1 1 3 C x y z T⋊c. U A B C a 1 x a 1 y

Join T A B a 1 b 2 U B 1 1 3 C x y z A a a b T⋊c. U B C 1 x 1 y 2 null

Division R Remainder S R S V W A B B a a b b c 1 2 1 1 2 V W A a b

O SNO PNO ตวอยาง O P S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 6 S 2 P 3 S 2 P 4 S 3 P 3 S 4 P 2 S 4 P 4 S 4 P 5 P PNO P 2

Division ตวอยาง O P SNO S 1 S 4 O d SNO PNO S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 6 S 2 P 3 S 2 P 4 S 3 P 3 S 4 P 2 S 4 P 4 S 4 P 5 P PNO P 2

Division ตวอยาง O P O d SNO P PNO S 1 P 2 P 4 S 1 P 3 S 1 P 6 S 2 P 3 S 2 P 4 S 3 P 3 S 4 P 2 S 4 P 4 S 4 P 5

Division ตวอยาง O P SNO S 4 O d SNO P PNO S 1 P 2 P 4 S 1 P 3 S 1 P 6 S 2 P 3 S 2 P 4 S 3 P 3 S 4 P 2 S 4 P 4 S 4 P 5

Division ตวอยาง O P O d SNO P PNO S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 6 S 2 P 3 S 2 P 4 S 3 P 3 S 4 P 2 S 4 P 4 S 4 P 5

Division ตวอยาง O P SNO S 1 O d SNO P PNO S 1 P 1 S 1 P 2 S 1 P 3 S 1 P 6 S 2 P 3 S 2 P 4 S 3 P 3 S 4 P 2 S 4 P 4 S 4 P 5

แคลคลสเชงสมพนธ Algebra( n Target Item n For All n Exists n Function (Calculus

แคลคลสเชงสมพนธ SX Algebra( S# P 1 P 2 SNAME SP# Part 1 S 1 Part 2 S 2 (Calculus SPX SP# S 1 S 2 PNAME Spplier 1 Supplier 2 CITY Bangkok Korat PX PX# Color PX 1 Red PX 2 Blue QTY 200 10

Target Item รปแบบ Relation. Attribute As Variable ตวอยาง SX. S# AS SNO

Target Item SX S# SNAME SP# P 1 P 2 Part 1 Part 2 ตวอยาง SX. S# AS SNO S 1 S 2

Target Item SX S# P 1 P 2 SNAME SP# Part 1 S 1 Part 2 S 2 ตวอยาง SX AS SUPPLIER

Exists รปแบบ EXISTS variable(condition) ตวอยาง EXISTS spx(spx. sp# = sx. sp# AND sx. s# = “P 2”)

Exists SPX SP# S 1 S 2 PNAME Spplier 1 Supplier 2 SX CITY Bangkok Korat S# SNAME SP# P 1 P 2 Part 1 Part 2 S 1 S 2 ตวอยาง EXISTS spx(spx. sp# = sx. sp# AND sx. s# = “P 2”)

Exists SPX SX S# SNAME SP# P 1 P 2 Part 1 Part 2 S 1 S 2 SP# S 1 S 2 PNAME Spplier 1 Supplier 2 CITY Bangkok Korat ตวอยาง EXISTS spx(spx. sp# = sx. sp# AND sx. s# = “P 2”)

For All รปแบบ FORALL relation(condition) ตวอยาง FORALL px(px. color = “Red”)

For All PX PX# Color PX 1 Red PX 2 Blue QTY 200 10 ตวอยาง FORALL px(px. color = “Red”)

Function รปแบบ FUNCTION(expression) ตวอยาง SUM(spx. qty)

Function PX PX# Color PX 1 Red PX 2 Blue ตวอยาง SUM(px. qty) QTY 200 10

Function PX PX# Color PX 1 Red PX 2 Blue ตวอยาง AVG(px. qty) QTY 200 10

Function PX PX# Color PX 1 Red PX 2 Blue ตวอยาง MAX(px. qty) QTY 200 10

Function PX PX# Color PX 1 Red PX 2 Blue ตวอยาง MIN(px. qty) QTY 200 10

พชคณตเชงสมพนธ Algebra( (Relational Remainder T U T⊳⊲U A B B C A B C a 1 1 x a 1 X b 2 1 y a 1 y 3 z R S a 1 b 2 a 2 3 a 3 b 1 b 2 b 3

แคลคลสเชงสมพนธ (Calculus Algebra( relation. attribute AS variable n Target Item n For All FORALL relation(condition) n Exists EXISTS variable(condition) n Function FUNCTION(expression)