To do week 8

    

• To do
• Continue..
• Find example ER diagram from other textbooks
• Draw them using Google Slides
• Explain their relationships: degree, cardinality, ratio, participation
• Use seven-step algorithm to convert the ER model constructs into relations
• Explain each step clearly
• Find an exercise from selected textbook, design and draw ER diagram
• Use seven-step algorithm.. (another practice)
• Explain each step clearly
• Insert (random?) data into your designed database using selected DBMS
• Write 5 meaningful/practical SQL queries
• Select another DBMS
• Export data to it
• Query it and compare the time/space

Continue WEEK 7

• Select another DBMS
• postgresql 

• mysql

• Export data to it
• ในการ Export data ที่จะทำการทดลองในครั้งนี้จะ Export data ออกมาจาก postgresql น่ะครับโดยนำออกมาที่ล่ะ table ในรูปเเบบของไฟล์ csv เพื่อนำเอาข้อมูลไปใส่ใน DBMS ตัวอื่นเเละทำการเปรียบเทียบต่อไป :)
• ในการนำข้อมูลออกจาก postgresql นั้นให้เราเข้าไปที่ CMD คับ

• คำสั่งที่ใช้ในการ Export ข้อมูลออกจาก postgresql 

> psql -U postgres -d database_name -c "COPY table_name TO stdout DELIMITER ',' CSV HEADER;" > file_output_name.csv

• ตาราง player

 > psql -U postgres -d db_w_8 -c "COPY player TO stdout DELIMITER ',' CSV HEADER;" > player.csv

• ตาราง player_nation

 > psql -U postgres -d db_w_8 -c "COPY player_nation TO stdout DELIMITER ',' CSV HEADER;" > player_nation.csv

• ตาราง team

 > psql -U postgres -d db_w_8 -c "COPY team TO stdout DELIMITER ',' CSV HEADER;" > team.csv

• ตาราง stadium

 > psql -U postgres -d db_w_8 -c "COPY stadium TO stdout DELIMITER ',' CSV HEADER;" > stadium.csv

• จะได้ไฟล์ออกมา 4 ไฟล์ตามจำนวนของ table ของเรา

• Query it and compare the time/space
• ก่อนอื่นเราต้องนำข้อมูลที่เรา Export ข้อมูลออกจาก postgresql นำเข้าไปยัง DBMS ที่ต้องการเปรียบเทียบก่อน

• การนำข้อมูลชนิด csv เข้า mysql

• สร้าง tables ให้เหมือนกันกับที่เราทดลองใน postgresql ก่อน
• ทำการสร้าง database คับโดยการใช้คำสั่ง

 create database db_w_8;

• เข้าใช้งาน database ที่เราสร้างคับโดยการใช้คำสั่ง

 use db_w_8;

• สร้าง table Stadium
• ในการสร้าง table Stadium ใช้คำสั่งดังนี้

 CREATE TABLE Stadium(
  Stadium_name varchar(50) NOT NULL,
  Stadium_capacity int NOT NULL, 
  PRIMARY KEY (Stadium_name)
 );

• สร้าง table Team
• ในการสร้าง table Team ใช้คำสั่งดังนี้

CREATE TABLE Team(
 Team_ID int NOT NULL,
 Team_Name varchar(50) NOT NULL,
 Team_manager varchar(20) NOT NULL,
 Stadium_name varchar(50) NOT NULL,
 PRIMARY KEY (Team_ID),
 FOREIGN KEY (Stadium_name) REFERENCES Stadium(Stadium_name)
);

• สร้าง table Player
• ในการสร้าง table Playerใช้คำสั่งดังนี้

CREATE TABLE Player(
 Player_id int NOT NULL,
 Player_name varchar(20) NOT NULL,
 Player_last varchar(20) NOT NULL,
 Player_skill varchar(5) NOT NULL,
 Team_ID int,
 PRIMARY KEY (Player_id),
 FOREIGN KEY (Team_ID) REFERENCES Team(Team_ID)
);

• สร้าง table Player_nation
• ในการสร้าง table Player_nation ใช้คำสั่งดังนี้

CREATE TABLE Player_nation(
 Player_id int NOT NULL,
 Nationality varchar(10) NOT NULL,
 FOREIGN KEY (Player_id) REFERENCES Player(Player_id)
);

• การนำข้อมูลชนิด CSV เข้า Mysql :)

 load data local infile 'file_name.csv' into table table_name
 fields terminated by ','
 enclosed by '"'
 lines terminated by '\n'
(column1, column1, ...)

• นำข้อมูลจากไฟล์ stadium.csv เข้า Mysql
• ใช้คำสั่งดังนี้

 load data local infile 'stadium.csv' into table stadium
 fields terminated by ','
 enclosed by '"'
 lines terminated by '\n'
(Stadium_name, Stadium_capacity)

• นำข้อมูลจากไฟล์ team.csv เข้า Mysql
• ใช้คำสั่งดังนี้

 load data local infile 'team.csv' into table team
 fields terminated by ','
 enclosed by '"'
 lines terminated by '\n'
(Team_ID, Team_Name, Team_manager, Stadium_name)

• นำข้อมูลจากไฟล์ player.csv เข้า Mysql
• ใช้คำสั่งดังนี้ 

 load data local infile 'player.csv' into table player
 fields terminated by ','
 enclosed by '"'
 lines terminated by '\n'
(Player_id, Player_name, Player_last, Player_skill, Team_ID)

• นำข้อมูลจากไฟล์ player_nation.csv เข้า Mysql
• ใช้คำสั่งดังนี้ 

 load data local infile 'player_nation.csv' into table player_nation
 fields terminated by ','
 enclosed by '"'
 lines terminated by '\n'
(Player_id, Nationality)

• หลังจากที่เราได้ทำการเพิ่มข้อมูลเข้าไปใน DBMS ที่เราต้องการจะเปรียบเทียบ ทั้ง 2 ตัวเเล้วเราก็จะทำการเปรียบเทียบระหว่าง เวลาในการ Query เเละ ขนาดของข้อมูลดั้งนี้
• เปรียบเทียบเวลาในการ Query ของ Postgresql กับ Mysql
• เเสดงชื่อทีมที่มีความจุของสนามอยู่ในช่วง 700000 - 800000 คน

 SELECT Stadium_name,Stadium_capacity 
 FROM Stadium 
 WHERE Stadium_capacity > 700000 and Stadium_capacity < 800000;

• Postgresql 

• Mysql

• เปรียบเทียบเวลาในการ Query ของ Postgresql กับ Mysql
• เเสดงจำนวนนักเเตะที่ถนัดเท้า Left จากทีมทั้งหมด

 SELECT COUNT(Player_id) AS playerSkill_left
 FROM Player
 WHERE Player_skill = 'Left';

• Postgresql  

• Mysql

• เปรียบเทียบเวลาในการ Query ของ Postgresql กับ Mysql
• เเสดงชื่อเเละนามสกุล ของนักเเตะที่ถนัดทั้ง 2 เท้า

 SELECT Player.Player_name,Player.Player_last,Player.Player_skill  
 FROM Player 
 WHERE Player.Player_skill = 'All';

• Postgresql 

• Mysql

• เปรียบเทียบเวลาในการ Query ของ Postgresql กับ Mysql
• เเสดงชื่อ นามสกุล เเละเท้าที่ถนัดของนักเเตะที่อยู่ในทีมเดียวกัน Ex.'TEAM@10'

 SELECT Team.Team_Name, Player.Player_name, Player.Player_last, Player.Player_skill
 FROM Player INNER JOIN Team ON Player.Team_ID=Team.Team_ID
 WHERE Team.Team_Name = 'TEAM@10';

• Postgresql

• Mysql

• เปรียบเทียบเวลาในการ Query ของ Postgresql กับ Mysql
• เเสดงจำนวนนักเเตะในเเต่ล่ะทีมว่ามีกี่คน

 SELECT team.team_name,count(player_id) 
 FROM Player join team on player.team_id = team.team_id group by team.team_id;

• Postgresql 

• Mysql

• การใช้พื้นที่ของ Postgresql สามารถตรวจสอบได้จากคำสั่ง

  \d+

•  การใช้พื้นที่ของ Mysql สามารถตรวจสอบได้จากคำสั่ง

 SELECT table_name "Table Name", table_rows "Rows Count", round(((data_length + index_length)/1024/1024),2)
 "Table Size (MB)" FROM information_schema.TABLES WHERE table_schema = "db_w_8";

"หมายเหตุ" ชื่อ database 

• ขอขอบคุณข้อมูลจาก 
• https://tecadmin.net
• https://stackoverflow.com

To do week 7

    

• To do
• Continue..
• Find example ER diagram from other textbooks
• Draw them using Google Slides
• Explain their relationships: degree, cardinality, ratio, participation
• Use seven-step algorithm to convert the ER model constructs into relations
• Explain each step clearly
• Find an exercise from selected textbook, design and draw ER diagram
• Use seven-step algorithm.. (another practice)
• Explain each step clearly
• Insert (random?) data into your designed database using selected DBMS
• Write 5 meaningful/practical SQL queries

Continue week 6

• DBMS ที่เลือกใช้คือ PostgreSQL 

• การทดลองใช้งาน DBMS ในครั้งนี้ผมจะเลือกใช้ตัว PostgreSQL น่ะครับ PostgreSQL คือ โปรแกรม OpenSource ที่สามารถนำไปใช้งานได้โดยไม่มีค่าใช้จ่ายใด  เป็นจัดการฐานข้อมูลแบบ object-relational database management system  หรือ (ORDBMS) น่ะครับสามารถที่จะ Download ได้จาก www.postgresql.org ได้เลยครับ
• เข้า CMD (Command Prompt)

• ทดลองเชื่อมต่อกับ PostgreSQL ครับ :) โดยการใช้คำสั่ง

 > psql -U postgres -h localhost

• ขั้นตอนนี้จะไม่สามารถที่จะใช้เจ้าตัว psql ได้เพราะเรายังไม่ได้ตั้ง PATH ครับ (เเต่ถ้าสามารถใช้ > psql -U postgres -h localhost ได้ก็สามารถข้ามขั้นตอนนี้ไปได้เลยครับ)
• ขั้นตอนในการเเก้ปัญหาคือ
• เข้าไปที่ my computer หรือ this pc จากนั้นคลิก ขวา เลือก properties
• เข้า Advanced system settings เลือก Environment Variables
• ที่ System variables เลือกที่ PATH กด Edit
• เลือกที่ NEW ใส่ PATH ที่เราติดตั้งตัว Postgres ลงไปครับ ของผมจะอยู่ที่ (C:\Program Files\PostgreSQL\9.2\bin)
• กด OK เสร็จครับ

• ทดลองเชื่อมต่อกับ PostgreSQL ใหม่ครับ :) โดยการใช้คำสั่ง

 > psql -U postgres -h localhost

• Password for user postgre : (ให้ใส่ Password ที่เราใส่ตอนติดตั้งในขั้นตอนการติดตั้งคับ)

• จะพบว่าสามารถที่จะเข้าใช้งาน PostgreSQL ได้เเล้วในตอนนี้ :)
• ทำการสร้าง database คับโดยการใช้คำสั่ง

 create database mydb;

• mydb ( ชื่อ Database )
• ทำการเชื่อมต่อกับ database mydb ที่เราสร้างขึ้นมาจากขั้นตอนก่อนหน้าเพื่อเข้าใช้งาน โดยใช้คำสั่ง

 \c mydb;

• ทำการสร้าง table ตามที่ออกเเบบเอาไว้ครับ

• สร้าง table Stadium

• ในการสร้าง table Stadium ใช้คำสั่งดังนี้

CREATE TABLE Stadium(
 Stadium_name varchar(50) NOT NULL,
 Stadium_capacity int NOT NULL, 
 PRIMARY KEY (Stadium_name)
);

• สร้าง table Team

• ในการสร้าง table Team ใช้คำสั่งดังนี้

CREATE TABLE Team(
 Team_ID int NOT NULL,
 Team_Name varchar(50) NOT NULL,
 Team_manager varchar(20) NOT NULL,
 Stadium_name varchar(50) NOT NULL,
 PRIMARY KEY (Team_ID),
 FOREIGN KEY (Stadium_name) REFERENCES Stadium(Stadium_name)
);

• สร้าง table Player

• ในการสร้าง table Playerใช้คำสั่งดังนี้

CREATE TABLE Player(
 Player_id int NOT NULL,
 Player_name varchar(20) NOT NULL,
 Player_last varchar(20) NOT NULL,
 Player_skill varchar(5) NOT NULL,
 Team_ID int,
 PRIMARY KEY (Player_id),
 FOREIGN KEY (Team_ID) REFERENCES Team(Team_ID)
);

• สร้าง table Player_nation

• ในการสร้าง table Player_nation ใช้คำสั่งดังนี้

CREATE TABLE Player_nation(
 Player_id int NOT NULL,
 Nationality varchar(10) NOT NULL,
 FOREIGN KEY (Player_id) REFERENCES Player(Player_id)
);

• สรุปคำสั่งที่ใช้ในการสร้างตาราง

CREATE TABLE Stadium(
 Stadium_name varchar(50) NOT NULL,
 Stadium_capacity int NOT NULL, 
 PRIMARY KEY (Stadium_name)
);
CREATE TABLE Team(
 Team_ID int NOT NULL,
 Team_Name varchar(50) NOT NULL,
 Team_manager varchar(20) NOT NULL,
 Stadium_name varchar(50) NOT NULL,
 PRIMARY KEY (Team_ID),
 FOREIGN KEY (Stadium_name) REFERENCES Stadium(Stadium_name)
);
CREATE TABLE Player(
 Player_id int NOT NULL,
 Player_name varchar(20) NOT NULL,
 Player_last varchar(20) NOT NULL,
 Player_skill varchar(5) NOT NULL,
 Team_ID int,
 PRIMARY KEY (Player_id),
 FOREIGN KEY (Team_ID) REFERENCES Team(Team_ID)
);
CREATE TABLE Player_nation(
 Player_id int NOT NULL,
 Nationality varchar(10) NOT NULL,
 FOREIGN KEY (Player_id) REFERENCES Player(Player_id)
);

• Insert (random?) data into your designed database using selected DBMS
• ในการ Insert ข้อมูลเข้าใน database นั้นผมเลือกใช้ python เป็นตัวช่วยในการ Random เเละ Insert เข้าไปครับ

• โปรเเกรม Python ที่ใช้ในการทดลอง

import psycopg2
import random

def check_connect_to_database(db_name, user, password, host, port):
    try:
        connect_to_database = psycopg2.connect(database=db_name, user=user, password=password, host=host, port=port)
        print ("Opened database successfully"+"\nStart generator data ")
        return True
    except:
        return False    

def add_data_to_database(db_name, user, password, host, port, team_id, _id):
    try:
        connect_to_database = psycopg2.connect(database=db_name, user=user, password=password, host=host, port=port)
        console = connect_to_database.cursor()
        # Stadium_name | Stadium_capacity
        values_stadium = ['STADIUM@TEAM@'+str(team_id), random.randint(500000, 1000000)]
        console.execute("INSERT INTO Stadium (Stadium_name, Stadium_capacity) VALUES (%s,%s)",tuple(values_stadium))
        connect_to_database.commit()
        #print ('Table Team :', values_stadium)
        # Team_ID | Team_Name | Team_manager | Stadium_name
        values_team = [team_id, 'TEAM@'+str(team_id), 'MANAGER@TEAM@'+str(team_id), 'STADIUM@TEAM@'+str(team_id)]
        console.execute("INSERT INTO Team (Team_ID, Team_Name, Team_manager, Stadium_name) VALUES (%s,%s,%s,%s)",tuple(values_team))
        connect_to_database.commit()
        #print ('Table Team :', values_team)
        count_player = 0
        random_count_player = random.randint(20, 70)
        id_player = _id
        while (count_player < random_count_player):
            # Player_id | Player_name | Player_last | Player_skill | Team_ID
            values_player = [id_player, 'PLAYERNAME@'+str(count_player), 'PLAYERLAST@'+str(count_player), random.choice(['Left', 'Right','All']), team_id]
            console.execute("INSERT INTO Player (Player_id, Player_name, Player_last, Player_skill, Team_ID) VALUES (%s,%s,%s,%s,%s)",tuple(values_player))
            connect_to_database.commit()
            #print(values_player) 
            if (random.randint(1, 15)%2) == 0:
                # Player_id | Nationality
                values_player_nation = [id_player, random.choice(['En', 'Th'])]
                console.execute("INSERT INTO Player_nation (Player_id, Nationality) VALUES (%s,%s)",tuple(values_player_nation))
                connect_to_database.commit()
                #print(values_player_nation)
            else:
                # Player_id | Nationality
                values_player_nation = [id_player, random.choice(['AF', 'AS', 'KE'])]
                console.execute("INSERT INTO Player_nation (Player_id, Nationality) VALUES (%s,%s)",tuple(values_player_nation))
                connect_to_database.commit() 
                #print(values_player_nation)
                values_player_nation = [id_player, random.choice(['UAC','AS','DE','AY'])]
                console.execute("INSERT INTO Player_nation (Player_id, Nationality) VALUES (%s,%s)",tuple(values_player_nation))
                connect_to_database.commit()
                #print(values_player_nation)
            count_player = count_player + 1
            id_player = id_player + 1
        return(id_player)
    except:
        print ("I am unable to connect to the database")

if __name__ == "__main__":
    # ข้อมูลสำหรับติดต่อกับ Database
    db_name = 'mydb'
    user = 'postgres'
    password = 'root'
    host = '127.0.0.1'
    port = '5432'
    # ตรวจสอบการเชื่อมต่อกับ Database
    if check_connect_to_database(db_name, user, password, host, port):
        count = 0
        player_id = 0
        while(count < 1000):
            player_id = add_data_to_database(db_name, user, password, host, port, count, player_id)
            print('Add Data To Database At : ', count, 'Ok..')
            count = count + 1
    else:
        print ("I am unable to connect to the database") 

• ในการทดลองนี้ได้ทำการ INSERT ข้อมูลเข้าไปจำนวน 1000 ชุด 

• Write 5 meaningful/practical SQL queries
• เเสดงชื่อทีมที่มีความจุของสนามอยู่ในช่วง 700000 - 800000 คน

 SELECT Stadium_name,Stadium_capacity 
 FROM Stadium 
 WHERE Stadium_capacity > 700000 and Stadium_capacity < 800000;

• เเสดงจำนวนนักเเตะที่ถนัดเท้า Left จากทีมทั้งหมด

 SELECT COUNT(Player_id) AS playerSkill_left
 FROM Player
 WHERE Player_skill = 'Left';

• เเสดงชื่อเเละนามสกุล ของนักเเตะที่ถนัดทั้ง 2 เท้า

 SELECT Player.Player_name,Player.Player_last,Player.Player_skill  
 FROM Player 
 WHERE Player.Player_skill = 'All';

• เเสดงชื่อ นามสกุล เเละเท้าที่ถนัดของนักเเตะที่อยู่ในทีมเดียวกัน Ex.'TEAM@10'

 SELECT Team.Team_Name, Player.Player_name, Player.Player_last, Player.Player_skill
 FROM Player INNER JOIN Team ON Player.Team_ID=Team.Team_ID
 WHERE Team.Team_Name = 'TEAM@10';

• เเสดงรายชื่อ เท้าที่ถนัด เเละสัญชาติ ของนักเเตะจากทุกทีมที่มีสัญชาติไทย Ex.'Th'

 SELECT Player.Player_name,Player.Player_last,Player.Player_skill, Player_nation.Nationality
 FROM Player INNER JOIN Player_nation ON Player.Player_id=Player_nation.Player_id
 WHERE Player_nation.Nationality = 'Th';

• ขอขอบคุณข้อมูลจาก 
• https://www.w3schools.com