Summary
在lab1基础上把一个ER design应用postgreSQL实现。
1.Download files
（1）应用lab1方法进入实验路径
(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）创建database company
createdb company
注：create database的语法是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
进入company database
psql company
重要：database中和terminal中都可以操作数据库，但是命令截然不同。后续lab中，所有psql开头的命令都是在terminal中运行的，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名称";
注意不要忘记语句末尾的分号，例如
select * from employees;
（4）load valid data到database company
和上文方法一致
psql company -f data.sql
或者
\i data.sql
根据上文重要部分的提示根据情况选择命令
（5）load invalid data到database company
和上文方法一致
psql company -f bad.sql
或者
\i bad.sql
根据上文重要部分的提示根据情况选择命令
3.drop previous database and modify documents
由于schema的数据库搭建没有限定，产生了很多错误数据，比如有人一周工作时间是小于0的，所以删除之前的company database再修改schema
（1）删除company database
dropdb company
注意一定要在terminal中运行该命令。
（2）修改schema
先进入修改VIM编辑器
vim schema.sql
键盘i进入编辑状态，编辑好之后esc退出编辑状态，:wq保存退出。
create table Employees (
tfn char(11) check (tfn ~ '^(\d{3}-){2}\d{3}$'),
--^是开始，\d是数字，{3}是三次，(\d{3}-)重复2次，$是结束
givenName varchar(30) not null,
--not null不能是空的，完全关系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
同时注意还要修改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.光标放置于要cut/copy的开始位置；
2.键盘v复制character；键盘V复制whole line；键盘Ctrl-v选择rectangular blocks；
3.光标放置于要cut/copy的结束位置；
4.键盘d是cut（d是delete）；键盘y是copy（y是yank）；
5.光标放置于要paste的位置；
6.键盘p在光标后paste；键盘P在光标前paste
（4）编辑好后重新运行：
createdb company
psql company
\i schema.sql
\i data.sql
\i bad.sql
