Transitive dependency in DBMS

    บทความนี้จะเป็นบทความเพิ่มเติมจาก Example of 1NF, 2NF, 3NF นะครับเนื่องจากใน 3NF มีการพูดถึง Transitive Dependent โดยเราได้หาข้อมูลเพิ่มเติมจากเว็บเมื่อสักครู่ ในหัวข้อ Transitive dependency in DBMS =>  https://beginnersbook.com/2015/04/transitive-dependency-in-dbms/
   
    โดยเขาได้บอกว่าการมีลักษณะของ Transitive Dependency จะต้องเป็นดังนี้

A functional dependency is said to be transitive if it is indirectly formed by two functional dependencies. For e.g.

X -> Z is a transitive dependency if the following three functional dependencies hold true:

• X->Y
• Y does not ->X
• Y->Z

    จะต้องมีการเชื่อมหรือถ่ายทอดกันไปในทางเดียวและไม่สามารถย้อนกลับได้ เช่น

1. X สามารถเชื่อมไปหา Y ได้

2. Y จะต้องไม่สามารถเชื่อมกลับไปหา X ได้

3. และ Y จะต้องเชื่อมไปตัวต่อไปได้ (Z)

Example

    โดยจากตัวอย่างจะเห็นได้ว่า BOOK สามารถเชื่อมโยงไปหา AUTHOR ได้ และ ตัวของ AUTHOR นั้นไม่สามารถชี้ไปหา BOOK ได้ เพราะเราไม่รู้ว่า คนเขียนคนนี้เขียนหนังสืออะไร แต่เรารู้ได้ว่า หนังสือเล่มนี้ใครเขียน และสุดท้ายคือ AUTHOR สามารถชี้ไปหา AUTHOR_AGE ได้

Example of 1NF, 2NF, 3NF

    ได้ลอง search หาใน google และเจอกับ https://beginnersbook.com/2015/05/normalization-in-dbms/ ซึ่งได้อธิบายตัวอย่างของการทำ Normalization แบบ 1NF, 2NF, 3NF เอาไว้ผมเลยลองศึกษาแล้วนำมาอธิบายด้วยคำพูดเองได้ดังนี้

Normalization 

    คือ การทำให้ข้อมูลในฐานข้อมูลไม่มีความซับซ้อน ไม่มีความผิดปกติในการ insert, update, delete

First normal form (1NF)

    As per the rule of first normal form, an attribute (column) of a table cannot hold multiple values. It should hold only atomic values.

    หมายถึงว่าในส่วนของ Attribute ของตารางจะไม่เป็น Multiple Values หรือ มีหลายค่า มันควรจะเก็บแค่ค่า ค่าเดียว 

Example

    จากตารางด้านบนจะเห็นได้ว่ามันยังไม่เป็น 1NF เพราะว่าในคอลัมน์ emp_mobile นั้นของ emp_id 102 กับ 104 ยังเป็น Multiple Values มีค่าหลายค่า เพราะฉะนั้นวิธีแก้ก็คือ...

     จับมันแยกกัน โดย emp_id 102 กับ 104 ที่มีค่า emp_mobile 2 ค่า หรือ พนักงานที่มีเบอร์โทรศัพท์ 2 เบอร์ ก็จับแยกเป็น row ใหม่ ดังภาพ ก็จะมี row ของ emp_id 102 และ 104 เพิ่มมาอย่างละ 1 row ดังภาพ

Second normal form (2NF)

      A table is said to be in 2NF if both the following conditions hold:

• Table is in 1NF (First normal form)
• No non-prime attribute is dependent on the proper subset of any candidate key of table.

An attribute that is not part of any candidate key is known as non-prime attribute

    โดยการที่จะเป็น 2NF ได้นี้ จะต้องมีสองเงื่อนไข

1. ตารางนั้นจะต้องเป็น 1NF เสียก่อน

2. ตัว Attribute ที่เป็น non-prime ค่าของมันจะต้องไม่ depend หรือ ขึ้นอยู่กับ candidate key ในตาราง

โดยคำว่า non-prime attribute นั้นหมายถึงตัว Attribute ที่ไม่ใช่ Candidate key

Example

    จากภาพตารางด้านบนนั้นจะกำหนดให้คอลัมน์ teacher_id กับ subject นั้นเป็น Candidate Key และ teacher_age เป็น non-prime attribute และจะเห็นได้ว่าค่าของ teacher_age นั้นจะขึ้นอยู่กับ teacher_id เพราะคุณครูแต่ละคนจะมีอายุของตัวเอง ซึ่ง teacher_id เป็น candidate key ทำให้ผิดเงื่อนไขข้อ 2) ส่วนเงื่อนไขข้อแรก 1) นั้นผ่านเพราะไม่มี multiple values วิธีแก้ไขก็คือ...

    จับทำตารางแยกไปเป็น 2 ตารางไปเลย โดยจะได้เป็น teacher_details กับ teacher_subject โดยเมื่อแยกตารางแล้วจะทำให้ ค่าของ teacher_age จะไม่ขึ้นอยู่กับ candidate key อีกต่อไป

Third Normal form (3NF)

A table design is said to be in 3NF if both the following conditions hold:

• Table must be in 2NF
• Transitive functional dependency of non-prime attribute on any super key should be removed.

   โดยจะเป็น 3NF ได้จะมี 2 เงื่อนไขเช่นเดียวกันกับ 2NF ได้แก่

1. เป็น 2NF เสียก่อน

2. ไม่มี Transitive functional dependency ที่เกิดจาก non-prime attribute โดยอิงไปหา super key 

Example

    โดยจากตารางจะมี emp_id เป็น candidate keys และที่เหลือจะเป็น non-prime attribute และเราจะเห็นได้ว่า emp_state, emp_city และ emp_district นั้นจะ depend on หรือขึ้นอยู่กับ emp_zip และ emp_zip จะขึ้นอยู่กับ emp_id ซึ่งนั้นจะทำให้เกิด transitively dependent กับ super key หรือก็คือ emp_id นั้นเอง โดยวิธีแก้ก็คือ...

ทำการแยกตารางออกเพื่อไม่ให้เกิด Transitive Dependent ดังเช่นตารางด้านบน ที่มีการอิงข้อมูลกันเป็นทอดๆแล้วไปอิงกับ ตัว emp_id ที่เป็น super_key

Guideline 4

    ได้นำชื่อบทที่ 15 ไป Search หาเพื่อที่จะได้ไฟล์บท 15 แบบตัวเต็ม ผลที่ได้ก็คือ

    ซึ่งได้เป็น File PDF มาและได้ทำการหาหัวข้อเรื่อง Guideline 4 โดยมีใจความดังนี้

    โดยเมื่ออ่านทำความเข้าใจแล้วก็จะสามารถสรุปได้ดังนี้
Guideline 4
    ควรออกแบบ Relation schemas ที่สามารถ join ด้วยเงื่อนที่มีความเท่าเทียมกัน โดย attributes นั้นจะมีความเกี่ยวข้องกันเป็นคู่ๆ เช่น primary key กับ foreign key โดยจะเป็นการการันตี หรือ แน่ใจว่าจะไม่มี tuples ที่ผิดๆถูกสร้างขึ้น
    หลีกเลี่ยงความสัมพันธ์ที่มี attributes ที่ไม่ได้เกิดจากการผสมกัน เพราะว่าการ join กันจะทำให้เกิด attribute ที่ผิดได้

ทดลองใส่ข้อมูลจำนวนมากลง PGSQL

    ในสัปดาห์ที่แล้วอ. SPN ให้ลองไปใส่ข้อมูลของเกรดของนศ.ในจำนวนที่มากเป็น 2 เท่าของแรมที่มี ซึ่งตัวผมใช้คอมพิวเตอร์ที่มีแรมทั้งหมด 8 GB ทำให้ต้องใส่ข้อมูลเรื่อยๆจนมีขนาดไฟล์ของ db ที่มากกว่า 16 GB

    โดยวิธีที่ใช้ในการใส่ข้อมูลลงไปในตาราง (Insert) ตัวผมเองนั้นได้ลองใช้วิธีการใช้ Loop While ใน SQL โดย

DO $$
DECLARE
    counter integer := 0;
BEGIN
  WHILE counter <= 160000000 LOOP
      counter := counter + 1 ;
      INSERT INTO public.transcript(
          stu_id, sub_id, year, semester, gpa, comment)
  VALUES ('5801012610067', '010123101',counter, 1, 'A', '');
  END LOOP ;
END $$;

ต้องขอบคุณเว็บ http://www.postgresqltutorial.com สำหรับข้อมูลการใช้งานคำสั่ง

    โดยในตอนแรกได้ทดลองวนลูปใส่ข้อมูล 10 ล้านตัว ซึ่งจะทำให้ขนาดของ Database อยู่ที่ราวๆ 1.7 GB เลยได้ทดลองใส่ข้อมูลจำนวน 160 ล้านตัว ผลที่ได้เป็นดังนี้

ใช้เวลาไปทั้งหมด 2 ชั่วโมง 49 นาที 26 วินาที

ขนาดไฟล์ Database อยู่ที่ราวๆ 27 GB

    

    แต่ทว่าในการลอง Select ข้อมูลออกมาเพื่อชมผลลัพธ์นั้น กับเจอกับสิ่งนี้เแทน

    ซึ่งหลังจากพยายามอยู่ทั้ง 3 รอบ โดยหลังจากพบปัญหารอบแรกก็ได้เข้าโปรแกรม pgAdmin แล้วก็รันสคริปต์ แล้วปล่อยไว้ไม่ได้ทำอะไรกับมันอีกเลย แต่ผลที่ได้มาก็เป็นเช่นเดิม โดยในตอนนี้ก็ได้ปล่อยให้มันรันต่อไป ถ้ามีผลยังไงจะมาชี้แจงในบทความหน้า ขอบคุณครับ

PostgreSQL pt.4

    ในบทความนี้จะขอกล่าวถึงเรื่องของการ สร้าง table และการตั้งค่าต่างๆ (PK,FK) และ การ import file csv ลงใน database โดยใช้ pgAdmin 4 โดยจะเป็นส่วนหนึ่งของการทำงานที่ได้รับมอบหมายในสัปดาห์ที่ 4 ที่อาจารย์ SPN ให้ไปลองใช้ DBMS ตัวอื่น ซึ่งตัวผมได้รับมอบหมายให้ศึกษา PostgreSQL

    การออกแบบ Table ของ Group 5

     จากการหารือและคุยกันเลยได้ข้อสรุปดังนี้

1. จะมีการทำ tables 3 ตาราง

2. tables ทั้ง 3 มีดังนี้

     2.1 student มี attribute ดังนี้
        1) stu_id (Primary Key)  เป็น PK และเป็นรหัสประจำตัวนักศึกษา
        2) f_name  เป็นชื่อจริง
        3) l_name  เป็นชื่อนามสกุล
        4) email   เป็นอีเมล์
   
    2.2 subject มี attribute ดังนี้
        1) sub_id (Primary Key) เป็น PK และเป็นรหัสวิชา
        2) sub_name  เป็นชื่อวิชา
        3) credits  เป็นหน่วย

    2.3 transcript มี attribute ดังนี้
        1) stu_id (Primary Key)  เป็น PK และเป็นรหัสนศ. โดยจะมี Foreign key เป็น stu_id ของ table student
        2) sub_id (Primary Key)  เป็น PK และเป็นรหัสวิชา โดยจะมี Foreign key เป็น sub_id ของ table subject
        3) year (Primary Key) เป็น PK และเป็นปีที่ลง
        4) semester (Primary Key)  เป็น PK และเป็นเทอมที่ลง
        5) gpa  เป็นเกรดเฉลี่ยนเป็นตัวอักษร
        6) comment  เป็นข้อความระบุรายละเอียด(ไม่จำเป็น)

    โปรแกรม pgAdmin 4

    โปรแกรม pgAdmin 4 เป็นโปรแกรมที่ติดมาจากการติดตั้ง PostgreSQL โดยจะเป็นตัวจัดการ Database แบบ GUI ทำให้สามารถทำงานได้อย่างสะดวกสบายมากขึ้น

 การสร้าง table ใน pgAdmin 4

    เนื่องจากเป็นตัวจัดการ Database แบบ GUI ทำให้มีความสะดวกสบายในการทำงานที่ค่อนข้างสูง โดยเราสามารถที่จะสร้าง table ได้โดยการคลิกขวาที่ หมวด table แล้วเลือก Create ได้ทันที

    โดยเมื่อกด Create -> Table แล้วก็จะสามารถใส่รายละเอียดต่างๆดังนี้

    ในแถบ Columns จะเป็นการกำหนดค่า attributes ต่างๆ ว่าชื่ออะไร เป็นข้อมูลประเภทไหน สามารถกำหนดความยาวของข้อมูลได้ ตั้งค่า Not NULL? ได้ และตั้งค่าให้เป็น Primary key ของ table นี้ด้วยหรือเปล่า

   โดยจะทำเหมือนกันทั้ง 3 tables แต่จะมีความพิเศษตรง table สุดท้ายคือ จะมีการกำหนด Foreign key ที่ชี้ไปยัง table student และ subject ที่เป็น table อ้างอิง โดยวิธีการตั้งค่าส่วนนี้มีดังนี้

    ในแถบ Constraints จะมีแถบย่อยๆอีกมากมาย โดยในแถบแรกจะเป็นแถบของ Primary key ซึ่งจะบอกรายละเอียดของ PK ที่มีอยู่ใน table นี้และในแถบ Foreign Key จะเป็นส่วนที่เราต้องการให้คลิกที่แถบนั้น

  

     ในแถบนี้เมื่อกดเครื่องหมายบวกทางขวาจะเป็นการเพิ่ม Foreign key โดยเมื่อกดไปแล้วจะสามารถตั้งค่าได้ดังนี้ 

    โดยในแถบ Columns จะมีช่องว่างให้เติม 3 ช่องก็คือ

Local col. =>  ที่อยู่ใน table ปัจจุบัน References => table ที่อ้างอิง Referencing => column อ้างอิงของ table ที่เลือก

 การ import csv ใน pgAdmin 4

ในการ import นั้นเราจะมีไฟล์ csv ของทั้ง 3 ตารางเรียบร้อยแล้ว

หลังจากนั้นในหมวดหมู่ table ให้เราคลิกขวาแล้วเลือก import/export 

    หลังจากนั้นจะมีช่องให้ browser file csv แล้วเลือก Encoding ซึ่งที่นี้ใช้ตัว UTF8 และเลือกมี Header

    โดยเมื่อกด apply เสร็จแล้วก็สามารถดูข้อมูลได้โดยการใช้ SQL Script 

- table => student

               

- table => subject

- table => transcript

PostgreSQL pt.3

การสร้าง table และ insert ข้อมูล

    ทดลองการสร้าง table โดยการใส่คำสั่งใน shell จะได้ผลดังนี้

    และเมื่อเราใช้โปรแกรม pgAdmin 4 เพื่อดูข้อมูลของ database จะได้ผลดังนี้

    ส่วนการ insert ข้อมูลนั้นสามารถทำได้โดยใช้ sql script ใน pgAdmin ได้

    โดยเมื่อ insert เข้าไปแล้วสามารถลองดูข้อมูลได้ด้วยคำสั่ง select 

PostgreSQL pt.2

การ create และ drop database

    ในการสร้างหรือลบ database สามารถทำได้ทั้งผ่าน gui และผ่าน SQL shell

ผ่าน shell

ผ่าน gui

    หากต้องการที่จะ drop จะสามารถทำได้ผ่านทาง shell และ gui เช่นกัน

PostgreSQL pt.1

    ในสัปดาห์ที่ 4 อ.SPN ได้บอกให้ไปทดลองใช้ตัว DBMS ตัวอื่นๆ โดยในกลุ่มของผมก็ได้แบ่งงานกันทำ และผมได้รับมอบหมายให้ทำในส่วนของ PostgreSQL

    เบื้องต้นนั้นผมได้ลองโหลดตัว PostgreSQL มาและติดตั้ง

    จากนั้นก็ลองไปหา Tutorial บน Youtube ซึ่งก็เจอ Channel หนึ่งที่ชื่อว่า Programming Guru มีคลิปสอนการใช้งานอยู่ ก็ได้ลองทำตามและศึกษา

https://www.youtube.com/watch?v=jNq5EAb2biY&list=PLk1kxccoEnNEtwGZW-3KAcAlhI_Guwh8x

DBMS pt.2

    ในตอนนี้ได้ลองเปลี่ยนไปใช้ MySQLWorkbench ในการทดลองเล่นกับ Database โดย download และติดตั้งเรียบร้อย

    โดยได้ทดลองต่างๆเช่น

1. ทดลองสร้าง schema

    ซึ่งจริงแล้วเราสามารถเขียนเป็นโค้ดก็ได้ เช่นการสร้างดังกล่าวจะเขียนแทนด้วยโค้ดดังนี้

CREATE SCHEMA `db_week_3` DEFAULT CHARACTER SET utf8 ;

2. การสร้าง table เก็บข้อมูล

    เมื่อสร้าง schema แล้วเราสามารถที่จะสร้าง table และกำหนดค่าต่างๆ เช่นสร้างคอลัมน์เก็บ idstudent โดยสามารถจะเลือกชนิดของข้อมูลได้ และเลือกว่าจะเป็น Primary Key หรือเปล่า

    โดยโค้ดที่สามารถเขียนแทนได้ก็มีดังนี้

CREATE TABLE `db_week_3`.`student` (

  `idstudent` VARCHAR(20) NOT NULL,

  PRIMARY KEY (`idstudent`));

    ซึ่งการจะลบหรือจะแก้ไขอะไรจำเป็นจะต้องเปิดตัว server เอาไว้ด้วย

DBMS pt.1

    นอกจากงานที่ได้รับมอบหมายยังมีการทดลองใช้ DBMS หรือตัวจัดการระบบฐานข้อมูล ก็ได้มีคำแนะนำจากเพื่อนว่าลองใช้ xampp เพราะว่าตอบโจทย์และก็เป็น opensource และมี DBMS มี Apache กับ MariaDB

    แต่ตอนนี้ติดปัญหาเรื่องตัวโปรแกรม เพราะฉะนั้นขอเกริ่นไว้ก่อน ไว้จะมาต่อบทความหน้าขอบคุณครับ

 

My bitbucket profile

https://bitbucket.org/thanut_suwannawong/

Score and Grade (Python)

    จากสัปดาห์แรกของการเรียนการสอน อ.SPN ได้ขอให้ไปทำการทดลองมาเพิ่มเติมโดยให้ลองเอา spreadsheet ที่ได้ทำนั้นมาลอง export เป็นไฟล์ csv และนำมาคำนวณ

    ปัญหาที่ได้พบทั้งหมด

1. ในการเปิดไฟล์ csv มีปัญหาเรื่อง UnicodeError โดยวิธีการแก้ก็คือการใช้ตัว encoding เข้าไประบุว่าจะใช้ตัว decode ตัวไหน ในขั้นตอนการ open file csv

2. ปัญหานี้เป็นปัญหาใหญ่ซึ่งเกิดจากการออกแบบ spreadsheet ไว้ไม่ดีทำให้เมื่อเวลาดึงข้อมูล หรือ อ่านใน python แล้วไม่สามารถนำมาใช้ในการคำนวณได้ เลยจะต้องตัดคอลัมน์ออกไปหลายๆส่วนด้วยกัน และเหลือเอาไว้ที่จำเป็น ซึ่งมีแค่ ปีการศึกษา เกรด หน่วยกิต เพียงเท่านั้นจากตอนแรกที่ใส่คอลัมน์ที่ไม่จำเป็นไว้เยอะ โดยตัวผมเองนั้นใช้วิธีการตัดส่วนของ string เลยทำให้ไม่สามารถทำงานได้สะดวกหากยังเก็บคอลัมน์ที่ไม่จำเป็นไว้

3. เนื่องจากช่วงปิดเทอมนั้นไม่ได้ฝึกการทำงานเลย ทำให้มีผลกระทบอย่างมาก ทำให้งานที่ออกมางานแรกนั้นไม่ค่อยมีประสิทธิภาพ ต้องขออภัยไว้ ณ ที่นี้ด้วย และจะขอแก้ไขในงานครั้งต่อไปครับผม

4. ไม่ได้เขียนคอมเมนท์สักตัว ไม่ควรเอาเยี่ยงอย่าง

ผลการทำงาน

เทียบกับเว็บของมหาลัย

    จะเห็นได้ว่ามีความถูกต้องตามที่ได้ทดลองทำ

Code

    ในส่วนของโค้ดผมจะขออธิบายในส่วนของภาพรวมเพียงเท่านั้น เพราะว่าวิธีที่ใช้ในการทดลองนั้นไม่ได้ใช้วิธีที่ซับซ้อนมาก ใช้วิธีแบบเรียบง่าย มีการ import csv เพื่อใช้ในการเปิดไฟล์ csv โดยในฟังก์ชั่น main จะมีเพียงส่วนของ print และ เรียกใช้ฟังก์ชั่น 2 ตัวด้วยกันได้แก่

1. calculate_each(ปีการศึกษา)

    เป็นฟังก์ชั่นในการคำนวณเกรด โดยจะเรียกใช้ฟังก์ชั่น get_grade() กับ get_credit() เพื่อนำค่าของเกรดและหน่วยกิตทุกตัวเข้ามาเก็บไว้ในตัวแปร และหลังจากนั้นจะวนลูป เพื่อทำการคูณค่าของหน่วยกิต และ เกรดของวิชานั้นๆ เมื่อเสร็จแล้วนำมาหารกับ หน่วยกิตทั้งหมด แล้วปัดเอาทศนิยม 2 ตำแหน่ง return กับไป  ซึ่งค่าที่รับมาจากการเรียกนั้นคือ term เพื่อที่จะสื่อว่า ต้องการของเทอม เทอมไหน

2. calulate_all(จำนวนปีการศึกษาทั้งหมด)

    เป็นฟังก์ชั่นการคำนวณเกรดเฉลี่ยสะสมโดยวิธีการคำนวณก็จะคล้ายๆกับตัว calculate_each() โดยจะใส่จำนวนปีการศึกษาว่าต้องการทราบถึงเท่าไร  โดนสิ่งที่แตกต่างกับโค้ดตัวด้านบนคือจะมีการวนลูปนอกสุดเพิ่มขึ้นมา หรือก็คือการวนลูปคิดในแต่ละปีนั้นเอง โดยจะให้วนเท่ากับจำนวนของปีที่ต้องการ 

ฟังก์ชั่น get_grade() และ get_credit()

    ฟังก์ชั่นทั้งสองตัวนั้นมีการทำงานที่คล้ายกันมากๆ ที่ต่างกันคือตำแหน่งของ string ที่เลือกจะเก็บข้อมูลอย่างเช่น 

    ถ้ามองจากรูปประกอบจะเห็นได้ว่า ตัวหลัง 3 ตัว(รวมจุดทศนิยม) นั้นคือเกรดที่เรา เพราะฉะนั้นถ้าหากเราต้องการเอาเฉพาะตัวเกรด เราก็อาจจะใช้คำสั่งด้านล่าง

   

 term_1_grade.append(row[-3:])     กับ    term_1_grade.append(row[-4:-1])

    เป็นคำสั่งใช้ในการ append เอาค่าเข้าไปใน list เปล่าๆโดยที่ต้องมีสองค่าก็เพราะว่า บรรทัดแรกที่จะมีปีการศึกษาด้วยนั้นจะทำให้ string มีตำแหน่งที่ไม่ตรงกัน

    แต่ถ้าเป็นการหาหน่วยกิตก็จะใช้โค้ดดังนี้

term_1_credit.append(row[10])  กับ  term_1_credit.append(row[1])

    ซึ่งเป็นตำแหน่งของหน่วยกิตโดยที่มีสองตัวก็เพราะเหตุผลเดียวกัน

    โดยในแต่ละฟังก์ชันจะมีการเก็บค่าของเทอมแต่ละเทอมเป็น 1 list จะทำให้มีทั้งหมด 4 lists และ เราเก็บทั้งหน่วยกิตกับเกรด จะทำให้เป็นทั้งหมด 8 lists แต่ว่าตอน return ค่านั้นจะนำค่าไปใส่กับ list เปล่าๆอีกทีนึงดังนี้

all_grade = [term_1_grade,term_2_grade,term_3_grade,term_4_grade]

        return all_grade

all_credit = [term_1_credit,term_2_credit,term_3_credit,term_4_credit]

        return all_credit

ส่วนตัวโค้ดเต็มๆนั้นผมจะอัพลง bitbucket แล้วแปะลิ้งค์ไว้ให้ครับผม