2110422 การออกแบบระบบการจัดการฐานขอมูล 6-1 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

บทที่ 6* พีชคณิตเชิงสัมพันธ

The Relational Algebra

วัตถุประสงค

1. เพื่อศึกษาตัวอยางฐานขอมูลทางธุรกิจ 2. เพื่อศึกษาโอเปอเรเตอรพีชคณิตเชิงเสน

3. เพื่อศึกษาภาษา QBE 6.1 ฐานขอมูลในธุรกิจ

ตัวอยางฐานขอมูลที่ใชในเชิงธุรกิจ ตัวอยางแสดงเคารางของรีเลชันที่มีความสัมพันธกัน โดยแตละรีเลชันจะมีคียหลักซ่ึง แอทตริบิวตจะขีดเสนใต

รูปที่ 6.1 ฐานขอมูล

EMPLOYEE

6.2 หลักการของพีชคณิตเชิงสัมพันธ

• การทําโอเปอเรชันสําหรับโมเดลความสัมพันธจะถูกเรียกวาเปนพีชคณิตเชิงเสน ซ่ึงจะทําการเรียกใชขอมูลจาก

รีเลชันตางๆไดตามความตองการ • ผลที่ไดจากการทําโอเปอเรชันพีชคณิต (algebra operations) น้ันจะทําใหเกิดรีเลชันใหม ซ่ึงอาจจะเกิดมา

จากรีเลชันเดียวหรือหลายรีเลชัน • การทําโอเปอเรชันพีชคณิตที่ซอนกัน ก็จะไดผลแสดงเปนคิวร่ีของฐานขอมูล (Database Query)

* อางอิงจากบทที่ 6 ของเอกสารอางอิง [1]

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-2 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

6.3 การจัดแบงประเภทโอเปอเรชันเชิงสัมพันธ (Relational

Operations) • โอเปอเรชันเชิงสัมพันธจากรีเลชันเดียว (Unary Relational operations)

- ตัวโอเปอเรเตอรไดแก SELECT , PROJECT and RENAME • โอเปอเรชันเชิงสัมพันธจาก 2 รีเลชัน (Binary Relational Operations)

- ตัวโอเปอเรเตอรไดแก JOIN and DIVISION • โอเปอเรชันเชิงสัมพันธจากหลายรีเลชัน (N-Nary Relation Operations)

- จะมีการนําเอาตัวโอเปอเรชั่นหลายตัวมาใชรวมกัน

6.3.1 โอเปอเรชนัเชิงสัมพันธจากรีเลชันเดยีว (Unary Relational operations)

SELECT • จะเปนการเลือกซับเซตของทูเพิลจากรีเลชันตามเงื่อนไขที่ตองการ • จะมีรูปแบบของการทําโอเปอเรชันคือ σ<selection condition>(R) • สัญญลักษณ σ เรียกวา ซิกมา (sigma) เปนสัญลักษณที่ใชในการทํา SELECT • selection condition จะเปนการใสเง่ือนไขที่ตองการ • R เปนชื่อของรีเลชันที่ตองการทําโอเปอเรต ตัวอยาง σSALARY>30000(EMPLOYEE) หมายถึง ตองการเลือกทูเพิลของรีเลชัน EMPLOYEE เฉพาะรายการที่มีเงินเดือนมากกวา 30000 เทาน้ัน

คุณสมบัติของ SELECT • โอเปอเรเตอร SELECT น้ันผลที่ไดจะมีเคาราง (Schema) เหมือนกับรีเลชันตั้งตน

σ<selection condition>(R) กับ R มีเคารางเดียวกัน • โอเปอเรเตอร SELECT มีคุณสมบัติการเรียงสับเปลี่ยน

σ<condition1>(σ<condition2>(R))=σ<condition2>(σ<condition1>(R)) • โอเปอเรเตอร SELECT อาจจะมีการเปลี่ยนลําดับการทําได

σ<condition1>(σ<condition2>(σ<condition3>(R))) =σ<condition2>(σ<condition3>(σ<condition1>(R)))

• โอเปอเรเตอร SELECT สามารถแทนไดโดยการใชตัวเชื่อม σ<condition1>(σ<condition2>(σ<condition3>(R))) =σ<condition1>AND <condition2> AND <condition3>(R)

PROJECT • จะเปนการเลือกเฉพาะคอลัมนที่ตองการจากตารางหรือรีเลชันเทาน้ัน • จะมีรูปแบบของการทําโอเปอเรชันคือ π<attribute list>(R) • โดย π เรียกวา พาย (pi) เปนสัญลักษณที่ใชในการทํา PROJECT • attribute list คือแอทตริบิวตตามท่ีตองการ • R เปนชื่อของรีเลชันที่ตองการทําการโอเปอเรต • โอเปอเรเตอร PROJECT น้ันถามีทูเพิลซํ้ากันก็จะใหลบทูเพิลที่ซํ้าออก ตัวอยาง πLNMAE,FNAME,SALARY(EMPLOYEE) หมายถึง ตองการเลือกเฉพาะแอทตริบิวต LNAME,FNAME และSALARY จากรีเลชัน EMPLOYEE เทาน้ัน

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-3 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

คุณสมบัติของ PROJECT • การ PROJECT น้ันผลที่ไดจะมีจํานวนทูเพิลนอยกวาหรือเทากับรีเลชันตั้งตน • ถาแอทตริบิวตที่เปนคียถูกเลือกใน การโอเปอเรชัน PROJECT จะทําให π<attribute list>(R) กับ R มีจํานวนทูเพิลเทากัน

รูปที่ 6.2 การ SELECT

และ PROJECT

รูปที่ 6.2 แสดงการใชคําส่ัง SELECT และ PROJECT (a) σ (DNO=4 AND SALARY>25000) OR (DNO=5 AND SLARY>30000)(EMPLOYEE) (b) πLNMAE,FNAME,SALARY(EMPLOYEE) (c) πSEX,SALARY(EMPLOYEE)

RENAME ถาในบางครั้งเราจําเปนที่จะตองนําผลลัพธ จากการทําโอเปอเรชันพีชคณิตเชิงสัมพันธ ไปหาผลลัพธอยางตอเนื่อง

ดังน้ันเราจึงจําเปนที่จะตองสรางรีเลชันที่เปนส่ือกลาง (intermediate result relation) ที่จะนํารีเลชันส่ือกลางที่ไดน้ีไปหาผลลัพธตอไป ซ่ึงมีสัญลักษณที่ใชแทนคือ ρ เรียกวา โรห (rho) ตัวอยาง ถาตองการขอมูลชื่อ นามสกุล และเงินเดือน ที่อยูในแผนกหมายเลข 5 เราจะสามารถเขียนไดเปน

πFNAME, LNAME, SALARY(σ DNO=5(EMPLOYEE)) หรือถาตองการใหแสดงอยูในรูปของรีเลชันส่ือกลางจะไดเปน DEP5_EMPS ← σ DNO=5(EMPLOYEE) RESULT ← π FNAME, LNAME, SALARY (DEP5_EMPS)

จากคําส่ังขางบน จะมีการสรางรีเลชันชื่อ DEP5_EMPS เพื่อดึงขอมูลของแผนก 5 ออกมากอนแลวจึงสรางรเีลชนั

ชื่อ RESULT เพื่อดึงขอมูลเฉพาะชื่อ นามสกุลและเงินเดือน ออกมาจากรีเลชันชื่อDEP5_EMPS อีกครั้งหน่ึง ดังแสดงไวในรูปที่ 6.2

รูปที่ 6.3 การใชรีเลชัน

ส่ือกลางและ

RENAME

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-4 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

รูปที่ 6.3 แสดงการสรางรีเลชันส่ือกลางและคําส่ัง RENAME (a) πFNAME, LNAME, SALARY(σ DNO=5(EMPLOYEE)) (b) TEMP ← σ DNO=5(EMPLOYEE)

R ← π FNAME, LNAME, SALARY (TEMP)

โอเปอเรชันพีชคณิตเชิงสัมพันธโดยใชทฤษฏีของเซต UNION การ UNION แทนดวยสัญลักษณ R ∪ S หมายถึง จะมีการสรางรีเลชันใหม โดยจะนําทูเพิลของ R และทูเพิลของ S มารวมกัน โดยถามีทูเพิลที่ซํ้ากัน ก็จะ

นําทูเพิลน้ันมาลงในรีเลชันใหมเพียงทูเพิลเดียว ดังตัวอยางรูปที่ 6.4

รูปที่ 6.4 การสรางรีเลชัน

RESULT

รูปที่ 6.4 แสดงการสรางรีเลชัน RESULT ที่เกิดจาก RESULT1 ∪ RESULT2 จะเห็นไดวา คา 333445555 มีอยูทั้งใน RESULT1 และ RESULT2 แตเม่ือมีทําการ UNION จะปรากฏอยู

ใน RESULT เพียงคาเดียว Type ที่สามารถใชได (Type Compatibility) • แอทตริบิวตที่จะนํามา UNION กัน จําเปนตองมีจํานวนแอทตริบิวตและโดเมนที่เขากันได • รีเลชันที่เปนผลลัพทธของ R1 ∪ R2 , R1 ∩ R2 , R1 – R2 น้ันจะมีชื่อแอทตริบิวตตามรีเลชันตัวแรก

(R1)

INTERSECTION สัญลักษณการ INTERSECTION แทนดวย R ∩ S หมายถึง จะมีการสรางรีเลชันใหม โดยจะนําทูเพิลของ R และทูเพิลของ S มาเลือกเฉพาะทูเพิลที่ซํ้ากัน ก็จะนําทู

เพิลน้ันมาลงในรีเลชันใหมซ่ึงถาทูเพิลไมซํ้ากันก็ใหตัดทิ้ง

MINUS สัญลักษณการ MINUS แทนดวย R - S หมายถึง จะมีการสรางรีเลชันใหม โดยจะนําทูเพิลเฉพาะที่อยูใน R แตไมอยูใน S มาลงในรีเลชันใหมเทาน้ัน ซ่ึงถา

ทูเพิลน้ันอยูเฉพาะใน S อยางเดียวหรือทูเพิลน้ันอยูทั้งใน R และ S ใหตดทิ้ง

รูปที่ 6.5 UNION

INTERSECTION และ MINUS

รูปที่ 6.5 แสดงการ UNION INTERSECTION และ MINUS

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-5 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

(a) แสดงรีเลชัน STUDENT กับ รีเลชัน INSTRUCTOR (b) แสดงผลลัพธของ STUDENT INSTRUCTOR (c) แสดงผลลัพธของ STUDENT ∩ INSTRUCTOR (d) แสดงผลลัพธของ STUDENT - INSTRUCTOR (e) แสดงผลลัพธของ INSTRUCTOR - STUDENT

จากรูป 6.5 จะเห็นวาชื่อแอทตริบิวตของผลลัพธของ (d) กับ (e) จะขึ้นอยูกับรีเลชันตัวแรก

• UNION กับ INTERSECTION มีคุณสมบัติการเรียงสับเปลี่ยน

R ∪ S = S ∪ R และ R ∩ S = S ∩ R • UNION กับ INTERSECTION มีคุณสมบัติการจัดกลุม R ∪ (S ∪ T) = (R ∪ S) ∪ T และ R ∩ (S ∩ T) = (R ∩ S) ∩ T • MINUS ไมมีคุณสมบัติการสลับที่ R - S ≠ S - R

CARTESIAN สัญลักษณการ CARTESIAN แทนดวย R x S หมายถึง จะมีการสรางรีเลชันใหม โดยจะเกิดจากการนําแอทตริบิวตของ R มารวมกับแอทตริบิวตของ S โดยนําทูเพิลทุกตัวของ R มาตอกันกับทูเพิลทุกตัวของ S ดังน้ันจํานวนแอทตริบิวตของ R x S จะเทากับ จํานวนแอทตริบิวต R + จํานวนแอทตริบิวต S และ จํานวนทูเพิลของ R x S ซ่ึงเขียนแทนไดเปน ⎢R x S ⎢ = จํานวนทูเพิล R * จํานวนทูเพิล S

รูปที่ 6.6a UNION

INTERSECTION และ MINUS

รูปที่ 6.6a FEMALE_EMPS ← σ SEX=’F’(EMPLOYEE) EMPNAMES ← π FNAME, LNAME, SSN (FEMALE_EMPS)

รูปที่ 6.6b ผลการ

CARTESIAN

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-6 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

รูปที่ 6.6b แสดงผลการ UNION INTERSECTION และ MINUS EMP_DEPENDENTS ← EMPNAMES x DEPENDENT

รูปที่ 6.6c ผลลัพธของ

UNION INTERSECTION

และ MINUS

รูปที่ 6.6c แสดงผลลัพธ ACTUAL_DEPENDENTS ← σ SSN=ESSN(EMP_DEPENDENTS ) RESULT ← π FNAME, LNAME,DEPENDENT_NAME (ACTUAL_DEPENDENTS)

6.3.2 โอเปอเรชนัเชิงสัมพันธจาก 2 รีเลชัน (Binary Relational Operations) JOIN • ถาตองการดึงขอมูลจาก 2 รีเลชันที่มีทูเพิลอางอิงถึงกันเราเรียกวา JOIN • โอเปอเรเตอร JOIN น้ีมีความสําคัญอยางมากในการสรางความสัมพันธของฐานขอมูลที่มีมากกวา 1 รีเลชัน

เพราะถือวาโอเปอเรเตอร JOIN น้ีทําใหรีเลชันมีการเชื่อมโยงกันภายในฐานขอมูลเดียวกัน • สัญลักษณการ JOIN จะถูกแทนดวย R <join condition> S

รูปที่ 6.7 ผลลัพธของ JOIN

รูปที่ 6.7 DEPT_MGR ← DEPARTMENT MGRSSN=SSN EMPLOYEE

จากรูปที่ 6.7 น้ี เปนการดึงขอมูลของผูจัดการประจําแผนกโดยการโอเปอเรชัน JOIN ของรีเลชัน

DEPARTMENT มา JOIN กับ รีเลชัน EMPLOYEE โดยเชื่อมโยงจากคาของ MGRSSN ตองเทากับคาของ SSN EQUI JOIN • เปนการเชื่อมโยงขอมูลที่สนใจ “คาของขอมูล” ที่อยูในแอทตริบิวตที่เชื่อมโยงกันใหมีคาเทากัน โดยใช

เครื่องหมาย = เชื่อมกันระหวางชื่อแอทตริบิวตของแตละรีเลชัน ที่มีความเชื่อมโยงกัน

NATURAL JOIN • เปนการเชื่อมโยงขอมูลที่สนใจ “ชื่อแอทตริบิวต” โดยรีเลชันที่จะนํามาเชื่อมโยงกัน จะตองมีชื่อแอทตริบิวตที่

เหมือนกัน โดยแทนดวยเครื่องหมาย *

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-7 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

รูปที่ 6.7 ผลลัพธของ

NATURAL JOIN

รูปที่ 6.7 แสดงถึงผลลัพธของการทํา NATURAL JOIN (a) PROJ_DEPT ← PROJECT * DEPT. (b) DEPT_LOCS ← DEPARTMENT * DEPT_LOCATIONS

เซตสมบูณของโอเปอเรชันเชิงสัมพันธ ประกอบดวย SELECT σ , PROJECT π ,UNION ∪ INTERSECTION ∩ , MINUS – และ CARTESIAN X ซ่ึงถือวาเปนเซตสมบูรณ เพราะวา ในความสัมพันทางพีชคณิตน้ันสามารถนําทั้ง 5

โอเปอเรเตอรมาจัดการหาคาของขอมูลได

ตัวอยาง R ∩ S = (R ∪ S) – ((R – S) ∪ (S - R)) R <join condition>S = σ<join condition>( R x S )

DIVISION สัญลักษณการ DIVISION แทนดวย R(Z) ÷ S(X) โดย X เปนซับเซตของ Z หมายถึง จะมีการสรางรีเลชันใหม โดยมีแอทตริบิวตที่ไดจะเปนแอทตริบิวตที่อยูใน R แตไมอยูใน S สวนทูเพิลที่ไดน้ัน จะตองเปรียบเทียบกับทูเพิลของ R กับ S ใหมีคาในแอทตริบิวตที่เหมือนกัน แลวคาในแอทตริบิวตอื่น (ที่อยู

ใน R แตไมอยูใน S)ของ R จะตองเหมือนกัน จึงนําคาที่ไดน้ันไปใสในรีเลชันใหม ดังตัวอยางรูปที่ 6.8

รูปที่ 6.8 ผลลัพธของ

DIVISION

รูปที่ 6.8 แสดงคาผลลัพธ (a) Dividing SSN_PNOS by SMITH_PNOS (b) T ← R ÷ S

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-8 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

รูปที่ 6.9 ตารางแสดงโอเปอ

เรชันของพีชคณิต

เชิงสัมพันธ

6.3.3 โอเปอเรชนัเชิงสัมพันธอืน่ๆ (Additional Relational Operations

การจัดกลุมและหาผลรวม การใชงานของฐานขอมูลน้ัน ในสวนที่เปนตัวเลขสามารถที่จะนํามาใชได โดยมีการจัดกลุมตามคาโดเมนของแตละ

แอทตริบิวต แลวสามารถนํามาโอเปอเรชันทางคณิตศาสตรได ซ่ึงในแตละกลุมจะมีการคํานวณไดคือ ผลรวม

(sum) , คาเฉลี่ย (average) , คาสูงสุด (maximum) , คาต่ําสุด (minimum) และการนับ (count)

รูปที่ 6.10 การนับจํานวน

พนักงานและการหา

คาเฉลี่ยของ

เงินเดือน

รูปที่ 6.10 แสดงการหาการนับของจํานวนพนักงานและการหาคาเฉลี่ยของเงินเดือน DNO ℱCOUNT SSN, AVERAGE Salary (Employee)

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-9 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

ฟงกชันนัลโอเปอเรชัน • การหาคาสูงสุดในแอทตริบิวต สามารถเขียนไดเปน ℱ MAX <Attribute> (R)

• การหาคาต่ําสุดในแอทตริบิวต สามารถเขียนไดเปน ℱ MIN <Attribute> (R)

• การหาคาผลรวมในแอทตริบิวต สามารถเขียนไดเปน ℱ SUM <Attribute> (R)

Outer JOIN • ใน Natural JOIN น้ันถาขอมูลในการเชื่อมโยงกันน้ันไมตรงกันก็จะถูกตัดทิ้ง ซ่ึงจะทําใหขอมูลเกิดการสูญ

หาย ดังน้ันจึงเกิดเปน Outer JOIN ที่จะเก็บคาทั้งหมดที่เกิดจากโอเปอเรชั่น JOIN โดยถาคาในขอมูลใดไมมีก็จะใหเปนคาวาง (null)

• สัญลักษณ R S หมายถึงเก็บขอมูลทุกคาใน R แลว ขอมูลใน S บางคาที่ไมมีจะใสคาวาง • สัญลักษณ R S หมายถึงเก็บขอมูลทุกคาใน S แลว ขอมูลใน R บางคาที่ไมมีจะใสคาวาง

รูปที่ 6.11 ผลการ JOINที่มี

คา NULL

รูปที่ 6.11 แสดงถึงรีเลชันที่ JOIN แลวทําใหเกิดคาวาง (null)

Outer UNION โอเปอเรเตอร Outer UNION ถูกพัฒนาขึ้นมาจากการที่ไมสามารถนํารีเลชัน 2 รีเลชันมาทําการ UNION ไดเน่ืองจากแอทตริบิวต ของทั้ง 2 รีเลชันน้ัน ไมเหมือนกันทั้งหมด ดังน้ันถามีรีเลชัน R(X,Y) กับ S(X,Z) ซ่ึงจะไดรีเลชันใหมเปน T(X,Y,Z) ซ่ึงจะทําใหในแตละทูเพิลของรีเลชัน T มีคาวาง (null) ในแอทตริบิวต Y หรือ แอทตริบิวต Z ในคาใดคาหนึ่ง

2110422 การออกแบบระบบการจัดการฐานขอมูล 6-10 ภาควิชาวิศวกรรมคอมพิวเตอร คณะวิศวกรรมศาสตร จุฬาลงกรณมหาวิทยาลัย

แบบฝกหัด 1. จงอธิบายความสําคัญของการทําโอเปอรเรชัน rename 2. จงอธิบายความแตกตางระหวาง JOIN กับ OUTER JOIN 3. จงพิจารณาฐานขอมูล Companyในรูป เพื่อตอบคําถามตอไปนี้ • แอทตริบิวตใดในรีเลชั่น WORKS_ON ใน ไมสามารถเปนคา NULL ได เพราะเหตุใด • แอทตริบิวตใดในรีเลชั่น PROJECT เปนคียนอก และคียดังกลาวเปนคียหลักของรีเลชั่นใด