To do week 7
- To do
- Continue..
Find example ER diagram from other textbooksDraw them using Google SlidesExplain their relationships: degree, cardinality, ratio, participationUse seven-step algorithm to convert the ER model constructs into relationsExplain each step clearlyFind an exercise from selected textbook, design and draw ER diagramUse 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';
- 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';
- ขอขอบคุณข้อมูลจาก
- ขอขอบคุณข้อมูลจาก
ไม่มีความคิดเห็น:
แสดงความคิดเห็น