ASSIGNMENT 2 (Draft) : Security of Inventory Transaction Process

GROUP # 6

    1) นายภุมมิฑล ไชยเชิดเกียรติ      56-010116-2131-8
    2) นายนิติธร บุญภักดี                  56-010126-2003-0
    3) นางสาวอรพรรณ เทียมสระคู      56-010126-2006-4
    4) นาวสาวปนัดดา อ่อนพลับ          56-010126-3018-3  (G#5)

สไลด์เนื้อหาการพรีเซนอยู่ที่ http://db5630183.blogspot.com/2016/05/assignment-2-security-of-inventory.html

PostgreSQL : Present 20 April 2016

Present 20 April 2016 

about PostgeSQL

สรุปจากการทดลอง

ใส่ข้อมูลตารางละ 20 ล้านrecord

Size (เรียงลำดับจากขนาดไฟล์ใหญ่สุดไปเล็กสุด)

MySQL > PostgeSQL > Sqlite

_________________________________________________________________

Time (เรียงลำดับจากใช้เวลามากสุดไปน้อยสุด)

Sqlite > MySQL > PostgeSQL 

_________________________________________________________________

https://drive.google.com/file/d/0B37DLu0T26KXLVF2dTZwRFZlTGc/view

NORMAL FORM

Find example 1NF, 2NF, 3NF

 


https://drive.google.com/file/d/0BznlD1uGWW-VSTRla3VmLUhEWW8/view

PostgreSQL : Finish inserting !!! (compare with Sqlite and MySQL)

PostgreSQL compare with Sqlite and MySQL

          เป็นการใส่ข้อมูลเกี่ยวกับคลังเก็บสินค้าที่จะส่งให้ลูกค้า โดยใส่ข้อมูลตารางละ 20 ล้านrecord เท่ากันทั้ง 3 โปรแกรม 
          Sqlite และ MySQL ทำงานบน Windows 7 และ PostgreSQL ทำงานบน Ubuntu

Size

          Sqlite (9.34 GB)

          MySQL (20.9 GB)

          PostgreSQL (17GB)

ภาพแสดงขนาดของ database ในโปรแกรม PostgreSQL

          การเช็คขนาดของ database ทำได้โดยการใช้คำสั่ง \l+ ชื่อdb  ในที่นี้คือ \l+ invtrans 

 _________________________________________________________________

select data from a database

Select ครั้งที่ 1
          เชื่อมสองตารางเข้าด้วยกัน โดยใช้ord_idเป็นเงื่อนไขให้สองตารางเชื่อมกัน คือ ตาราง inv_trans และ order_item ที่มีเลข order เท่ากันจะนำมาเชื่อมกัน เพื่อให้ทราบว่า สินค้าที่จะต้องมีการขนส่งเป็นสินค้าชนิดใด และเลือกมาแต่ข้อมูลที่มีเลข order เท่ากับ 12300000

select i.*,o.ty_group from inv_trans i inner join order_item o
on i.ord_id=o.ord_id and o.ord_id='12300000';


          Sqlite 
                      ใช้เวลา 7 min 52.2 sec
          
          MySQL 
                      ครั้งแรกใช้เวลา 3 min 33.58sec
                      ครั้งที่ 2 ใช้เวลา 39.76 sec
                      ครั้งที่ 3 และครั้งถัดๆไปจะใกล้เคียงกับครั้งที่ 2 ไม่มาก
                      
          PostgreSQL
                      ครั้งแรกใช้เวลา 2 min 9.74sec
                      ครั้งที่ 2 ใช้เวลา 12.2 sec
                      ครั้งที่ 3 ใช้เวลา 5.97 sec
                      ครั้งที่ 4 และครั้งถัดๆไปจะใกล้เคียงกับครั้งที่ 3 ไม่มาก  

ภาพแสดงการ select ครั้งที่ 1 ใช้เวลา 2 min  9.74 sec

ภาพแสดงการ select ครั้งที่ 2 ใช้เวลา 12.2 sec

ภาพแสดงการ select ครั้งที่ 3 ใช้เวลา 5.97 sec

          สรุป  ถ้าเทียบในครั้งแรก PostgreSQL ใช้เวลาในการ select data น้อยที่สุด รองลงมาคือ MySQL และ Sqlite ตามลำดับ

************************************************************************

Select ครั้งที่ 2
          เชื่อมสองตารางเข้าด้วยกัน โดยใช้ trans_id เป็นเงื่อนไขให้สองตารางเชื่อมกัน คือ ตาราง inv_trans และ tr_item ที่มีเลข trans_id เท่ากันจะนำมาเชื่อมกัน เพื่อให้ทราบว่า ตำแหน่งที่ตั้งของสินค้าและ item_id ของสินค้าที่จำต้องส่งไปยังลูกค้า โดยกำหนดให้แสดงแต่ข้อมูลที่มีเลข order เป็น12300000

select i.*,t.loc_id,t.item_id from inv_trans i inner join tr_item t
on i.trans_id=t.trans_id and i.ord_id='12300000';


          Sqlite 
                      ใช้เวลา 5 min 52.79 sec
          
          MySQL 
                      ครั้งแรกใช้เวลา 6 min 55.73 sec
                      ครั้งที่ 2 ใช้เวลา 2 min 30.46 sec
                      ครั้งที่ 3 และครั้งถัดๆไปจะใกล้เคียงกับครั้งที่ 2 ไม่มาก
                      
          PostgreSQL
                      ครั้งแรกใช้เวลา 26.997 sec
                      ครั้งที่ 2 ใช้เวลา 25.31 sec
                      ครั้งที่ 3 ใช้เวลา 6.484 sec
                      ครั้งที่ 4 และครั้งถัดๆไปจะใกล้เคียงกับครั้งที่ 3 ไม่มาก 

ภาพแสดงการ select ครั้งที่ 1 ใช้เวลา 26.997 sec

ภาพแสดงการ select ครั้งที่ 1 ใช้เวลา 25.31 sec

ภาพแสดงการ select ครั้งที่ 1 ใช้เวลา 6.484 sec

          สรุป  ถ้าเทียบในครั้งแรก PostgreSQL ใช้เวลาในการ select data น้อยที่สุด รองลงมาคือ Sqlite และ MySQL ตามลำดับ

PostgreSQL : Inserting data

Inserting data

          การใส่ข้อมูลในDatabaseที่ได้สร้างไว้ โดยการใส่ข้อมูลทีละตาราง เลือกใส่ข้อมูลที่ไม่มี FK ก่อน เพื่อให้ไม่เกิดการ error เมื่อมีการอ้างอิงไปยังตาราง

โดยลำดับการใส่ข้อมูลในตารางทั้ง 10 ตารางจะทำดังนี้

1. inv_type
2. orders
3. location
4. users
5. lab_assistant
6. item
7. storage
8. order_item
9. inv_trans
10. tr_item

ปัญหาที่พบ

          ในการใส่ข้อมูลคิดว่าสามารถใช้code ข้อความ เดียวกับ การใส่ข้อมูลใน MySQL หรือใน Sqlite ได้
นั่นคือ
    exec_str = 'insert into inv_type values ("'   +str(i) + ' ");' 
    c.execute(exec_str)


          แต่เมื่อทำการใช้ code ดังตัวอย่างข้างบน จะขึ้น error ว่า "psycopg2.ProgrammingError: column "1" does not exist"

psycopg2.ProgrammingError: column "1" does not exist

จากนั้นก็ได้แก้ไขเป็น

     c.execute("insert into inv_type values (%s);",(str(i)))

อ้างอิง : http://stackoverflow.com/questions/4113910/python-psycogp2-inserting-into-postgresql-help

          ตามที่ได้มีการแนะนำไว้ในเว็บด้านบน errorเก่าไม่ได้แจ้งเตือนแล้ว แต่ได้มีการเกิด error ใหม่ขึ้น นั่นคือ

"TypeError: not all arguments converted during string formatting python "

TypeError: not all arguments converted during string formatting python

 จึงได้ทำการศึกษาเพิ่มเติมแล้วได้ทำการเปลี่ยน code ใหม่เป็น

    c.execute("insert into inv_type values ('%s');"%(str(i)))

อ้างอิง : http://stackoverflow.com/questions/18053500/typeerror-not-all-arguments-converted-during-string-formatting-python

การเปลี่ยน password ของ user  postgres

ภาพแสดงการเปลี่ยน password

          ซึ่งได้ทำการเปลี่ยน password เป็น sam55166 เนื่องจากตอนเชื่อมต่อ db ใน user postgresจะต้องใส่ password จึงต้องทำการเปลี่ยนก่อน

อ้างอิง : http://serverfault.com/questions/110154/whats-the-default-superuser-username-password-for-postgres-after-a-new-install

Code ที่ใช้ในการใส่ข้อมูล

          ใน MySQL และ Sqlite ได้ทำการใส่ข้อมูลตารางละ 20 ล้านrecord ใน PostgreSQL จึงได้ทำการใส่ตารางละ 20 ล้านrecordเท่ากัน เพื่อใช้ในการเปรียบเทียบ

http://pastie.org/10794705

อ้างอิงการใส่ข้อมูลจาก
1) http://www.tutorialspoint.com/postgresql/postgresql_python.htm
2) http://zetcode.com/db/postgresqlpythontutorial/

PostgreSQL : create table

Create Database

         ก่อนอื่นต้องทำการ สร้าง database เพื่อจัดเก็บตารางที่มีความสัมพันธ์กันก่อน โดยใช้คำสั่ง createdb ชื่อdb ในที่นี้ตารางเราใช้เก็บข้อมูลเกี่ยวกับคลังเก็บสินค้าที่จะส่งให้ลูกค้า จึงใช้ชื่อdatabase ว่า "inv_trans"

ภาพแสดงคำสั่งที่ใช้สร้าง db

         วิธีเช็ค ว่ามี Database ใดบ้าง จะใช้คำสั่ง  
postgres=# \l         <-------เริ่มPostgres prompt โดยการพิมพ์ psql ก่อน
          จากรูปด้านล่างจะพบว่ามี database ชื่อ "inv_trans" ที่ได้ทำการสร้างไว้แล้ว หากต้องการจะออกจาก window ดังกล่าวแล้ว จะใช้คำสั่ง \q

ภาพแสดงlist ของ db

         ก่อนจะทำการสร้างตาราง จะต้องเลือก database ที่ต้องการจะสร้างตารางด้านในก่อน โดยใช้คำสั่ง \c ชื่อdb

ภาพแสดง การเลือก db ที่ชื่อว่า inv_trans

Create Tables

        ในการสร้างตาราง ในขั้นตอนแรกจะต้องสร้างตารางที่ไม่มี FK เพื่อให้ไม่เกิดปัญหาการอ้างอิง
        คำสั่งที่ใช้ในการสร้าง เช่นเดียว คำสั่ง ในMySQL หรือ Sqlite

        ในขั้นตอนต่อมา ต้องเลือกลำดับการสร้างตาราง เนื่องจากมี FK ที่จะต้องอ้างอิงไปยังตารางอื่น หากตารางที่ถูกอ้างอิงยังไม่ได้ถูกสร้าง จะเกิดการ error ได้

 

        เมื่อทำการสร้างตารางทั้ง 10 ตารางเสร็จหมดแล้ว หากต้องการตรวจสอบว่ามีตารางใดบ้างใน db นี้ จะใช้คำสั่ง \d

ภาพแสดงตารางต่างๆใน database

   
        แต่หากต้องการทราบรายละเอียดในแต่ละตาราง จะใช้คำสั่ง \d+ ชื่อตาราง

DESCRIBE TABLE ใช้คำสั่ง \d+ inv_type

        ซึ่งจะเห็นได้ว่าในตาราง inv_type มีattribute เดียวคือ ty_group แล้วก็ยังมีการแสดงข้อมูลอื่นๆเกี่ยวกับ attributeในตารางนี้


อ้างอิงข้อมูลจาก
1) http://stackoverflow.com/questions/109325/postgresql-describe-table
2) https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04

PostgreSQL

PostgreSQL 

          เป็นระบบการจัดการฐานข้อมูลเชิงวัตถุ-สัมพันธ์ (object-relational) แบบ ORDBMS โดยสามารถใช้รูปแบบคำสั่งของภาษา SQL ได้เกือบทั้งหมด ซึ่งเราได้ทำการศึกษาภาษา SQL มาแล้วในตอนต้น ทั้งในสัปดาห์ที่ 2 และ สัปดาห์ที่ 3
สัปดาห์ที่ 2: http://db5620064.blogspot.com/search/label/W2
สัปดาห์ที่ 3: http://db5620064.blogspot.com/search/label/W3

          นอกจากนี้ PostgreSQL ยังเป็น OpenSource ที่สามารถทำงานได้บนระบบปฏิบัติการหลายตัว ทั้ง  Linux, UNIX และ Windows ซึ่งทางเราได้ทำการศึกษาบนระบบ Ubuntu

อ้างอิงข้อมูลจาก : http://www.mdsoft.co.th/%E0%B8%84%E0%B8%A7%E0%B8%B2%E0%B8%A1%E0%B8%A3%E0%B8%B9%E0%B9%89/92-what-is-postgresql.html

 

HOW TO INSTALL

ใช้ทั้งสองคำสั่งนี้ใน terminal บน ubuntu 

~$ sudo apt-get update
~$ sudo apt-get install postgresql postgresql-contrib
 จากที่ได้ทำการทดลองลงโปรแกรม ไม่พบข้อผิดพลาดใดๆ

***_______________________________________________________________***

Using PostgreSQL Roles and Databases

ในขั้นตอนการติดตั้งจะสร้าง user account ที่ชื่อว่า postgres
หากจะใช้ postgres เราจะต้อง log in เข้า account ก่อน นั่นคือ

คำสั่ง
~$ sudo -i -u postgres


เมื่อต้องการเริ่มใช้ PostgreSQL prompt จะใช้คำสั่ง
~$ psql 
.
.
.
.
.
เมื่อต้องการออกจาก PostgreSQL prompt จะใช้คำสั่ง 
postgres=#      \q


แต่หากต้องการlog out ออกจาก account postgres จะใช้คำสั่ง
~$ exit


 ศึกษาเพิ่มเติมจาก : https://www.digitalocean.com/community/tutorials/how-to-install-and-use-postgresql-on-ubuntu-14-04


 

ASSIGNMENT I : The inventory transaction process

GROUP # 6

    1) นายนิติธร บุญภักดี                  56-010126-2003-0
    2) นางสาวอรพรรณ เทียมสระคู      56-010126-2006-4
    3) นายภุมมิฑล ไชยเชิดเกียรติ      56-010116-2131-8

เนื้อหาภายในสไลด์

           https://drive.google.com/file/d/0B37DLu0T26KXRTRSVjFJUmxQMW8/view

- การแปลง ER-Diagram ไปเป็น Relation database (Tables)
- Requirement ของแต่ละตาราง
- การแสดงตารางแต่ละตาราง (คำสั่งและการกำหนด types ให้ attribute)
- ทำการเปรียบเทียบระหว่าง 2 โปรแกรมในเครื่องเดียวกัน ทำจำนวน 3 เครื่อง (มาจากสมาชิกในกลุ่ม)
- เวลาแต่ละตัวแตกต่างกันอย่างไร (Run time : Real , User , System)
- ปัญหาที่พบและวิธีการแก้ไขที่ได้ลองทดลองแล้วจริง
- Code python ที่ใช้ในการ insert ข้อมูลให้กับทั้งสองโปรแกรม คือ
       1) sqlite
       2) MySQL





นี่คือ ER-Diagram ที่ทางกลุ่มสนใจศึกษา

ซึ่ง ER-Diagram ดังกล่าวมาจากหนังสือ

_____________________________________________________________________

Database Systems - Design, Implementation, and Management (9th Edition)

Carlos Coronel, Steven A. Morris, Peter Rob

ISBN: 0538469684

_____________________________________________________________________

Relational Algebra

More examples of queries in Relational Algebra

     โดยการเขียน Relational Algebra นั่นเกิดขึ้นมาก่อนภาษา MySQL ที่ได้ทำการศึกษาไปก่อนหน้าเสียอีก ในบทเรียนวิชา DATABASE SYSTEMS จึงได้มีการสอนในเรื่องrelational algebra เพื่อให้มองถึงความสัมพันธ์ที่เกิดขึ้นของข้อมูล มากกว่าการเรียกใช้เพียงคำสั่ง ในภาษาระดับที่ 4 

     Relational Algebra มี operations พื้นฐานได้แก่

1) Select*

2) Project*

3) Union**

4) Rename*

5) Cartesian-product**

6) Difference**

* คือ เป็น Unary หรือ การมี Operand เพียงตัวเดียว
** คือ เป็น Binary operations หรือการมี Operand 2 ตัว

• Select Operation

         แทนด้วยสัญลักษณ์ :   (อ่านว่า sigma) 

         ลักษณะการใช้ : ทำหน้าที่เป็นตัวเลือกแถว ข้อมูลจากเงื่อนไขที่ได้กำหนดไว้ ซึ่งคล้ายกับคำสั่ง where ในภาษา MySQL ที่เคยได้ศึกษาก่อนหน้านี้ 

         Syntax : 

         * condition สามารถเป็นได้ทั้งเครื่องหมาย มากกว่า น้อยกว่า เท่ากับ ไม่เท่ากับ และยังสามารถใช้ตรรกะ Boolean เข้ามาร่วมได้ในการสร้างเงื่อนไข

         ตัวอย่างการใช้ : 

         เช่น ในตารางความสัมพันธ์ของนักศึกษาคณะวิศวกรรมศาสตร์มีข้อมูลนักศึกษาอยู่ 

ภาพแสดงตารางข้อมูลทั้งหมด ของ ความสัมพันธ์ S_engineer

         เราจะเลือกเอาแต่ข้อมูลของนักศึกษาที่สังกัดภาควิชา  ECE หรือวิศวกรรมไฟฟ้าและคอมพิวเตอร์ นั่นเอง ซึ่งสามารถแปลงเป็น Relational algebra ได้ 

ภาพแสดงตัวอย่างการใช้Relational algebra

ภาพแสดงตารางใหม่ ตามที่ต้องการ

         โดย relational algebra ดังกล่าวมีความหมายเดียวกับ คำสั่ง MySQL ดังต่อไปนี้

                  select * from S_engineer

                  where department="ECE" ;

-----------------------------------------------------------------------------

• Project Operation

         แทนด้วยสัญลักษณ์ :  (อ่านว่า pi)

         ลักษณะการใช้ : ทำหน้าที่เป็นตัว เลือกคอลลัมน์แบบ distinct  เพื่อใช้ในการแสดงตารางข้อมูล ซึ่งคล้ายกับคำสั่ง Select ในภาษา MySQL ที่เคยได้ศึกษาก่อนหน้านี้ 

         Syntax : 

         ตัวอย่างการใช้ : 

         เช่น ต้องการให้แสดงข้อมูลชื่อและนามสกุลของนักศึกษา โดยไม่สนใจข้อมูลอื่นๆ

ภาพแสดงตารางข้อมูลทั้งหมด ของ ความสัมพันธ์ S_engineer

         ซึ่งการที่เราจะเลือก attribute ที่จะนำมาแสดง สามารถแปลงเป็น Relational algebra ได้ ดังนี้

ภาพแสดงตัวอย่างการใช้Relational algebra

ภาพแสดงตารางใหม่ ตามที่ต้องการ

         โดย relational algebra ดังกล่าวมีความหมายเดียวกับ คำสั่ง MySQL ดังต่อไปนี้

                  select name,surname from S_engineer;

-----------------------------------------------------------------------------

• Union

         แทนด้วยสัญลักษณ์ :  U (อ่านว่า Union)

         ลักษณะการใช้ : หลักการทำงานของมันเหมือนในเรื่อง set เลยคือ เอาข้อมูลทุกตัว โดยจะต้องไม่ซ้ำกัน ใช้เมื่อต้องการเชื่อมข้อมูลกับตารางมากกว่า 1 ตาราง

         Syntax : 

R U S 

โดย R และ S เป็นความสัมพันธ์

ภาพแสดง รูปแบบกรทำงานของ union

         ตัวอย่างการใช้ : 

         เช่น ต้องการให้หา id ของลูกค้าทั้งหมดที่ทำการกู้ยืมเงินหรือฝากเงิน หรือเป็นทั้งสองกรณีในคนเดียว

ภาพแสดงตารางข้อมูลทั้งหมด ของ ความสัมพันธ์ทั้งสองตาราง

         ซึ่งเราจะเลือกนำแต่ id ของลูกค้ามาแสดงเท่านั้นจึงเลือกใช้ project operation เข้ามาร่วมในการใช้ union ด้วย และ สามารถแปลงเป็น Relational algebra ได้ ดังนี้

ภาพแสดงตัวอย่างการใช้Relational algebra

         โดยมีลำดับขั้นตอนการทำงานดังนี้ คือทำ Project ทั้งสองตารางก่อนจึงค่อยทำในส่วนของ Union

ภาพแสดงลำดับการทำงานเมื่อใช้ union กับproject

-----------------------------------------------------------------------------

• Rename

         แทนด้วยสัญลักษณ์ :       (อ่านว่า rho)

         ลักษณะการใช้ : ใช้ในการเปลี่ยนชื่อตารางหรือ attribute ในตารางใหม่ ซึ่งคล้ายกับคำสั่ง Alter ในภาษา MySQL ที่เคยได้ศึกษาก่อนหน้านี้ 

         Syntax : 

ภาพแสดง syntax การ rename ทั้ง 3 แบบ

         ตัวอย่างการใช้ : 

         เช่น ต้องการเปลี่ยนชื่อ ตารางจาก S_engineer เป็น Student_Eng และชื่อ attribute ภายในตาราง โดยก่อนเปลี่ยนเป็น id , name , surname และ department ซึ่งต้องการเปลี่ยนเป็น S_ID , Fname , Lname และ department ตามลำดับ

ภาพแสดงตารางก่อนทำการ rename

         จากความต้องการด้านบน สามารถแปลงเป็น relational algebra ได้ดังนี้ โดยจะทำการเปลี่ยนชื่อ attribute ตามลำดับ

ภาพแสดง relational algebra โดยใช้ rename

ภาพแสดงตารางใหม่ที่ได้หลังการ rename

-----------------------------------------------------------------------------

• Cartesian-product

         แทนด้วยสัญลักษณ์ :  X (อ่านว่า cross) 

         ลักษณะการใช้ : ใช้ในการรวมข้อมูลของ 2 relations ซึ่งหลักการเหมือนทางคณิตศาสตร์เลย คือการนำข้อมูลแต่ละแถว มาcross กัน ซึ่งดูเหมือนจะไม่มีประโยชน์เท่าไหร่ หากดำเนินการเพียงเท่านั้น ดังนั้นจึงควรมี select เข้ามาเกี่ยวข้องด้วยเพื่อกำหนดเงื่อนไข และบอกความสัมพันธ์ของทั้งสองตารางที่มีต่อกัน เมื่อมี select เข้ามาเกี่ยวข้องด้วยแล้ว เราสามารถใช้เป็น join ได้เลย ซึ่งมีความหมายเหมือนกัน 

         Syntax : 

R x S 

โดย R และ S เป็นความสัมพันธ์

ภาพแสดง syntax เมื่อใช้ select เข้ามาเกี่ยวข้องด้วย

ภาพแสดง syntax เมื่อใช้ join แทนการใช้ cross กับ select 

         * ซึ่งสัญลักษณ์  หมายถึง join จึงขอรวบการนำเสนอ มาอยู่ในเรื่องนี้เลย

         ตัวอย่างการใช้ : 

         1) ในตัวอย่างการใช้แรกจะนำเสนอ ในกรณีที่ cross กันธรรมดา ไม่ได้ใช้ select เข้ามาเกี่ยวข้อง เพื่อแสดงให้เห็นหลักการทำงานของ Cartesian-product ได้อย่างชัดเจน

Borrower x Depositor

ภาพแสดงขั้นตอนการทำงานของ Cartesian-product

         2) ในตัวอย่างการที่สองจะนำเสนอ ในกรณีที่ cross และใช้ select เข้ามาเกี่ยวข้อง เพื่อเลือกบอกเงื่อนไขความต้องการข้อมูล เช่น 
            มีความสัมพันธ์อยู่ 2 ตาราง คือตารางผู้กู้เงินจากธนาคาร กับ ตารางผู้ฝากเงินกับธนาคาร ซึ่งผลจากการ cross กัน จะเห็นได้จากตัวอย่างเมื่อซักครู่ โดยตารางที่จะแสดงต่อไปนี้ เพียงแค่ทำการเปลี่ยนชื่อเพื่อสามารถเทียบค่าตัวแปรให้ได้เห็นได้ชัดว่ามาจากตารางใด และขอตั้งชื่อตารางที่เกิดการ cross กันดังกว่าว่า CustomerBD

ภาพแสดงตารางหลังจาก cross กัน

         ต้องการทราบข้อมูลที่ลูกค้าเป็นทั้งผู้ฝากเงินและ ผู้กู้เงิน โดยกำหนดให้แสดง รหัสลูกค้า 

จำนวนเงินที่กู้ยืม และ จำนวนเงินที่ฝาก โดยสามารถแปลงเป็น relational algebra ได้ดังนี้

ภาพแสดง relational algebra 

         ขั้นตอนการทำงานของ relational algebra ที่ได้ออกแบบไว้นี้ จะทำในวงเล็บก่อน นั่นคือการทำ select ก่อนค่อยทำ project

ภาพแสดงข้อตอนการดำเนินการของ relational algebra ที่ได้ออกแบบไว้

-----------------------------------------------------------------------------

• Difference

         แทนด้วยสัญลักษณ์ :  - (เครื่องหมาย -) หรือ เรียกอีกอย่างว่า minus

         ลักษณะการใช้ : หลักการทำงานของมันเหมือนในเรื่อง set เลยคือ พบค่าในความสัมพันธ์หนึ่งแต่ต้องไม่พบในอีกความสัมพันธ์หนึ่ง หรือก็คือการเอาส่วนที่อยุ่ในทั้งสองความสัมพันธ์ออก เอาเพียงที่อยุ่ในความสัมพันธ์ที่ 1 เพียงอย่างเดียว

         Syntax : 

R - S 

โดย R และ S เป็นความสัมพันธ์

ภาพแสดง รูปแบบกรทำงานของdifference

         ตัวอย่างการใช้ : 

         เช่น ต้องการให้หา id ของลูกค้าทั้งหมดที่ทำการฝากเงิน โดยไม่มีการกู้เงินเลย

ภาพแสดงตารางข้อมูลทั้งหมด ของ ความสัมพันธ์ทั้งสองตาราง

         ซึ่งเราจะเลือกนำแต่ id ของลูกค้ามาแสดงเท่านั้น จึงเลือกใช้ project operation เข้ามาร่วมในการใช้ difference ด้วย และ สามารถแปลงเป็น Relational algebra ได้ ดังนี้

ภาพแสดงตัวอย่างการใช้ Relational algebra

        

         โดยมีลำดับขั้นตอนการทำงานดังนี้ คือทำ Project ทั้งสองตารางก่อนจึงค่อยทำในส่วนของ difference

ภาพแสดงลำดับการทำงานเมื่อใช้ difference กับ project

 ข้อมูลอ้างอิงจาก :

1) http://slideplayer.in.th/slide/2117930/

2) http://www3.cs.stonybrook.edu/~kifer/Courses/cse532/slides/ch5.pdf

3) http://www.cs.cornell.edu/projects/btr/bioinformaticsschool/slides/gehrke.pdf

4) http://www.srisangworn.go.th/home/databaselearnx/ms2t2-2.htm

5) http://www.go4expert.com/articles/relational-algebra-operations-sql-t21166/

6) http://www.tutorialspoint.com/dbms/relational_algebra.htm

7) https://www.cs.sfu.ca/CourseCentral/354/jpei/slides/relational-algebra.pdf

-----------------------------------------------------------------------------

What is an inner join? outer join?

     ในการ join นั้น มีหลายรูปแบบมาก หนึ่งในนั้นคือ inner join และ outer join ซึ่ง inner join จะให้ความหมายเหมือนการ join ซึ่งได้ทำการศึกษาเพิ่มเติมไปใน week ที่ 3 แล้ว จึงขอสรุปสั้นๆ ดังนี้
     inner join คือ การนำข้อมูลที่มีการเก็บค่าเหมือนกันจากสองความสัมพันธ์ มาซ้อนทับกัน คล้ายหลักการ intersection ของเรื่อง set เช่น การแสดงข้อมูล Customers และตาราง Orders ที่มีความสัมพันธ์กัน ดังนั้น แสดงว่า ข้อมูล Customers ที่ออกมา จะแสดงออกมาแค่ที่มีอยู่ตารางOrders เท่านั้น นั่นคือ เป็นข้อมูลลูกค้าที่มีการสั่งสิ้นค้าเท่านั้น 

     ส่วน outer join จะคล้ายกับ Union(ในกรณีเป็น full outer join) ซึ่ง outer join จะถูกนำมาใช้แก้ปัญหาด้านบนในกรณีที่ต้องการทราบลูกค้าทั้งหมด 

     ยกตัวอย่างเช่น การจะแสดงข้อมูลจากตาราง Customers และตาราง Orders ที่มีความสัมพันธ์กัน หากเราต้องการจะแสดงข้อมูลในตาราง Customers ทั้งหมดถึงแม้จะไม่มีข้อมูลในตาราง Order ก็ตามหากใช้วิธีเหมือนใน inner join จะไม่สามารถแสดงข้อมูลชื่อ Customers ออกมาได้ทั้งหมด ต้องใช้วิธี outer join เท่านั้น

     โดยสามารถอธิบายได้ดังรูปนี้

ภาพแสดงรูปแบบความสัมพันธ์ของ join ชนิดต่างๆ

Outer join สามารถแบ่งย่อยได้อีก 3 กรณีคือ
1) Left Outer join
2) Right Outer join
3) Full Outer join

     นอกจากจะได้รูปด้านบนแล้วยังมีกรณีของ where null อีกด้วย ซึ่งสามารถแสดงได้ดังรูปต่อไปนี้

ภาพแสดงรูปแบบการใช้join แบบต่างๆ พร้อมคำสั่ง

     จะเห็นได้จากรูปว่า รูปแบบของouter join ที่ไม่เอาส่วนที่ซ้อนทับกัน หรือ ไม่เอาส่วนที่ intersection กัน จะมีคำสั่ง where null ต่อท้ายคำส่งเสมอ 

อ้างอิงข้อมูลจาก :

1) https://madhukiranb.wordpress.com/2012/11/22/different-types-of-sql-joins-and-differences-between-inner-join-left-outer-join-right-outer-join-full-outer-join-self-join/

2) http://www.siamcoding.com/ForumId-109-View.aspx
3) http://blog.sqlauthority.com/2009/04/13/sql-server-introduction-to-joins-basic-of-joins/
4) http://i.stack.imgur.com/qje6o.png

-----------------------------------------------------------------------------

What is a division operation?

     การทำ Division ไม่ค่อยถูกใช้ใน Relation Algebra แต่มีประโยชน์ในการค้นหาบางประเภท รูปแบบของ Division คือ r/s

ภาพแสดงตัวอย่างการใช้ division

     จากรูปจะเห็นได้ว่าตารางแรกเป็นตาราง r เก็บ attribute A กับ B  และตารางที่สองเป็นตาราง s เก็บ attribute B และ ตารางที่สามเก็บค่าความสัมพันธ์ ของ r/s ซึ่งผลออกมาคือ การนำข้อมูลของตาราง s ไปเทียบกับ attribute ในตาราง r ซึ่งได้ข้อมูลออกมาเป็น set  โดยข้อมูลที่ได้ คือการที่ attribute A ตัวเดียวกันมีค่าใน attribute B ทั้งสองค่า ไม่ใช่แค่มีค่าใดค่า  1 (ต้องมีการจับคู่อยู่กับค่าทุกค่าของ tuple ในตารางที่สอง) ซึ่งในตารางที่หนึ่ง ได้เน้นเป็นตัวอักษรสีแดงให้ดูชัดเจน ว่ามีข้อมูลตัวใดบ้างที่มีความสัมพันธ์แบบ r/s

 ข้อมูลอ้างอิงจาก :

1) http://www.srisangworn.go.th/home/databaselearnx/ms2t2-2.htm

2) https://www.cs.sfu.ca/CourseCentral/354/jpei/slides/relational-algebra.pdf

3) http://www.cs.cornell.edu/projects/btr/bioinformaticsschool/slides/gehrke.pdf