Summary
在lab1基礎(chǔ)上把一個(gè)ER design應(yīng)用postgreSQL實(shí)現(xiàn)狸剃。
1.Download files
（1）應(yīng)用lab1方法進(jìn)入實(shí)驗(yàn)路徑
(1) ssh z1234567@login.cse.unsw.edu.au
(2)輸入密碼
(3)ssh grieg
(4)source /srvr/z1234567/env
(5)pgs start
(6)cd /srvr/z1234567
（2）拷貝schema, data, bad
cp /home/cs9311/web/17s2/labs/02/schema.sql /home/cs9311/web/17s2/labs/02/data.sql /home/cs9311/web/17s2/labs/02/bad.sql /srvr/z1234567
2.Create and load database
（1）創(chuàng)建database company
createdb company
注：create database的語(yǔ)法是createdb [connection-option...] [option...] [dbname [description]]裳擎，例如create the database demo using the server on host eden, port 5000, using the LATIN1 encoding scheme:
createdb -p 5000 -h eden -E LATIN1 -e demo
（2）load schema.sql到database company
psql company -f schema.sql
（3）check
進(jìn)入company database
psql company
重要：database中和terminal中都可以操作數(shù)據(jù)庫(kù)丧鸯，但是命令截然不同。后續(xù)lab中募壕，所有psql開(kāi)頭的命令都是在terminal中運(yùn)行的，database的prompt是：
“database名稱”=#，
例如company=#
退出database的方法是輸入\q后回車
database中的load方法是輸入\i "需要load的文件名"坏怪，
例如\i schema.sql
顯示所有table
\d
顯示table的所有attributes
\d "table名稱"
例如\d employees
顯示table中所有instances
select * from "table名稱";
注意不要忘記語(yǔ)句末尾的分號(hào)，例如
select * from employees;
（4）load valid data到database company
和上文方法一致
psql company -f data.sql
或者
\i data.sql
根據(jù)上文重要部分的提示根據(jù)情況選擇命令
（5）load invalid data到database company
和上文方法一致
psql company -f bad.sql
或者
\i bad.sql
根據(jù)上文重要部分的提示根據(jù)情況選擇命令
3.drop previous database and modify documents
由于schema的數(shù)據(jù)庫(kù)搭建沒(méi)有限定绊茧，產(chǎn)生了很多錯(cuò)誤數(shù)據(jù)铝宵，比如有人一周工作時(shí)間是小于0的，所以刪除之前的company database再修改schema
（1）刪除company database
dropdb company
注意一定要在terminal中運(yùn)行該命令华畏。
（2）修改schema
先進(jìn)入修改VIM編輯器
vim schema.sql
鍵盤(pán)i進(jìn)入編輯狀態(tài)鹏秋，編輯好之后esc退出編輯狀態(tài)，:wq保存退出亡笑。
create table Employees (
tfn char(11) check (tfn ~ '^(\d{3}-){2}\d{3}$'),
--^是開(kāi)始侣夷，\d是數(shù)字，{3}是三次仑乌，(\d{3}-)重復(fù)2次百拓，$是結(jié)束
givenName varchar(30) not null,
--not null不能是空的琴锭，完全關(guān)系total participation
familyName varchar(30),
hoursPweek float check (hoursPweek <= 168 AND hoursPweek >= 0),
primary key (tfn)
);
create table Departments (
id char(3) check (id ~ '^\d{3}$'),
name varchar(100) unique,
manager char(11) unique not null,
primary key (id),
foreign key (manager) references Employees(tfn)
);
create table DeptMissions (
department char(3) references Departments(id),
keyword varchar(20),
primary key (department, keyword)
);
create table WorksFor (
employee char(11) not null references Employees(tfn),
department char(3) references Departments(id),
percentage float check (percentage <= 100 AND percentage >= 0),
primary key (employee, department)
);
create function check_worksfor_insert()
returns trigger as $$
declare
percentage1 float;
percentage2 float;
begin
select into percentage1 sum(percentage)
from WorksFor
where employee = New.employee;
percentage2 = percentage1 + New.percentage;
if percentage2 > 100 then
raise exception 'work percentage cannot exceed 100 percent';
end if;
return New;
end;
$$ language plpgsql;
create trigger sum_of_percentage before insert or update on WorksFor for each row execute procedure check_worksfor_insert();
（3）修改data
同時(shí)注意還要修改data的輸入順序：
vim data.sql
insert into Employees values ('777-654-321','Yusif','Budianto',40.0);
insert into Employees values ('123-987-654','Maria','Orlowska',40.0);
insert into Employees values ('323-626-929','Tom','Robbins',35.0);
insert into Employees values ('993-893-864','Susan','Ryan',60.0);
insert into Employees values ('419-813-573','Max','Schmidt',40.0);
insert into Employees values ('222-333-444','Pradeep','Sharma',30.0);
insert into Employees values ('123-234-456','John','Smith',40.0);
insert into Employees values ('632-647-973','Steven','Smooth',45.0);
insert into Employees values ('747-400-123','Adam','Spencer',50.0);
insert into Employees values ('326-888-711','Walter','Wong',50.0);
insert into Departments values ('001','Administration','123-234-456');
insert into Departments values ('002','Sales','222-333-444');
insert into Departments values ('003','Research','326-888-711');
insert into DeptMissions values ('001','innovation');
insert into DeptMissions values ('001','reliability');
insert into DeptMissions values ('001','profit');
insert into DeptMissions values ('002','customer-focus');
insert into DeptMissions values ('002','growth');
insert into DeptMissions values ('003','innovation');
insert into DeptMissions values ('003','technology');
insert into WorksFor values ('777-654-321','003',100);
insert into WorksFor values ('123-987-654','003',100);
insert into WorksFor values ('323-626-929','001',50);
insert into WorksFor values ('323-626-929','002',50);
insert into WorksFor values ('993-893-864','001',100);
insert into WorksFor values ('419-813-573','003',100);
insert into WorksFor values ('222-333-444','002',100);
insert into WorksFor values ('123-234-456','001',100);
insert into WorksFor values ('632-647-973','002',100);
insert into WorksFor values ('747-400-123','001',10);
insert into WorksFor values ('747-400-123','002',90);
insert into WorksFor values ('326-888-711','003',100);
VIM中cut, copy和paste的操作：
1.光標(biāo)放置于要cut/copy的開(kāi)始位置；
2.鍵盤(pán)v復(fù)制character耐版；鍵盤(pán)V復(fù)制whole line祠够；鍵盤(pán)Ctrl-v選擇rectangular blocks；
3.光標(biāo)放置于要cut/copy的結(jié)束位置粪牲；
4.鍵盤(pán)d是cut（d是delete）古瓤；鍵盤(pán)y是copy（y是yank）；
5.光標(biāo)放置于要paste的位置腺阳；
6.鍵盤(pán)p在光標(biāo)后paste落君；鍵盤(pán)P在光標(biāo)前paste
（4）編輯好后重新運(yùn)行：
createdb company
psql company
\i schema.sql
\i data.sql
\i bad.sql
