Database Systems

วันจันทร์ที่ 9 เมษายน พ.ศ. 2561

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

วันอาทิตย์ที่ 8 เมษายน พ.ศ. 2561

Guideline 4

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

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

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

วันอังคารที่ 20 กุมภาพันธ์ พ.ศ. 2561

ทดลองใส่ข้อมูลจำนวนมากลง 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 แล้วก็รันสคริปต์ แล้วปล่อยไว้ไม่ได้ทำอะไรกับมันอีกเลย แต่ผลที่ได้มาก็เป็นเช่นเดิม โดยในตอนนี้ก็ได้ปล่อยให้มันรันต่อไป ถ้ามีผลยังไงจะมาชี้แจงในบทความหน้า ขอบคุณครับ

วันอังคารที่ 6 กุมภาพันธ์ พ.ศ. 2561

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 จะเป็นส่วนที่เราต้องการให้คลิกที่แถบนั้น