1. ER-to-relational Mapping Review
ER的entity對(duì)應(yīng)relational的table；ER entity的attribute對(duì)應(yīng)table的attribute；ER的relationship map到relational的時(shí)候盡量減少數(shù)據(jù)null的可能，同時(shí)少創(chuàng)建新的table。（n:m的relationship必須要?jiǎng)?chuàng)建新的table；1:n的relationship要把1的primary key和relationship的attribute都作為foreign key添加進(jìn)n的table；1:1的優(yōu)先側(cè)重total participation）
注意：ER map to relational的時(shí)候不能把所有關(guān)系map過(guò)去，比如一些total和partial participation是沒(méi)有辦法兼顧的。
2. from 2-way to n-way relationships
binary relationship根據(jù)1:1, 1:n, n:m很容易從ER map，之后是大于2個(gè)entity的relationship如何map。通常有兩種方式，一種是把relationship當(dāng)成一個(gè)entity將>2的relationship轉(zhuǎn)化成binary，另一種是不當(dāng)成entity。
（1）不將>2的relationship處理成entity
presribe連接了3個(gè)entity，doctor, patient, drug。仿寫(xiě)many to many的binary處理方法，新建一個(gè)table放置prescribe關(guān)系，將doctor, patient, drug的primary key放入新table作為foreign key，同時(shí)存儲(chǔ)quantity和date這樣的relationship attribute。剩下一個(gè)問(wèn)題是新table的primary key如何設(shè)置？一種方法是直接用相連的entity的primary key形成一個(gè)key組，但這樣的壞處是與relationship實(shí)際情況有差別，在這種情況下意味著database中一個(gè)病人從一個(gè)醫(yī)生開(kāi)一種藥一生只能有一次。所以推薦另一種方式，根據(jù)實(shí)際情況來(lái)設(shè)置。在當(dāng)前情境下，prescribe一定要有藥的dno，由病人取藥，事情一定發(fā)生在具體的一個(gè)date。這樣，primary key一定要有date, pid, dno，意味著不同date同一個(gè)病人找同一個(gè)醫(yī)生可以開(kāi)同一種藥，doctor沒(méi)有放入primary key是因?yàn)榭梢酝ㄟ^(guò)這組primary key檢索到prescribe的doctor。這里不能再省略drug，不然primary key只有date和pid，意味著一個(gè)病人在一天內(nèi)找一個(gè)醫(yī)生只能開(kāi)一種藥。
create domain NameValue as varchar(100) not null;
create table Doctor(
tfn integer,
name NameValue,
specialty text not null,
primary key (tfn)
);
create table Drug(
dno integer,
name NameValue unique,
formula text,
primary key (dno)
);
create table Patient(
pid integer,
name NameValue,
address text not null,
primary key (pid)
);
create table Prescribes(
doctor integer not null references Doctor(tfn),
drug integer references Drug(dno),
patient integer references Patient(pid),
quantity integer not null,
"date" date,
primary key ("date", drug, patient)
);
（2）把relationship當(dāng)成一個(gè)entity將>2的relationship轉(zhuǎn)化成binary
把prescribe的relationship變成了entity，出現(xiàn)3個(gè)binary relationship，其中2個(gè)是1 to many，不需要新table，另一個(gè)many to many，需要一個(gè)新的table。
create domain NameValue as varchar(100) not null;
create table Doctor(
tfn integer,
name NameValue,
specialty text not null,
primary key (tfn)
);
create table Drug(
dno integer,
name NameValue unique,
formula text,
primary key (dno)
);
create table Patient(
pid integer,
name NameValue,
address text not null,
primary key (pid)
);
create table Prescription(
prNum integer,
"date" date not null,
doctor integer not null references Doctor(tfn),
patient integer not null references Patient(pid),
primary key (prNum)
);
create table PrescriptionItem(
prescription integer references Prescription(prNum),
drug integer references Drug(dno),
quantity integer check (quantity > 0),
primary key (prescription, drug)
);
3. Mapping composite attributes
上例中，address和name都是composite attributes，如何map取決于實(shí)際使用需要。如果需要讀取address和name，則第一種寫(xiě)法比較合適，把所有的下層attribute打個(gè)包寫(xiě)在address和name中。但是如果需要搜索在某個(gè)postcode中的所有人，那么下層的attribute需要被使用，這個(gè)時(shí)候第二個(gè)寫(xiě)法比較合適，把所有下層的attribute分開(kāi)寫(xiě)入table。
4. Mapping multi-valued attributes
上例中，favourite colours是multi-valued，必須新建一個(gè)table，不然一個(gè)確定的人在這個(gè)數(shù)據(jù)庫(kù)中就只能有一個(gè)喜歡的顏色。新建的table中不僅要有colour，還要有SSN，而且二者都要是primary key。如果只有SSN是primary key，那么在FavColour table中會(huì)出現(xiàn)相同的SSN對(duì)應(yīng)不同的colour，而SSN在person中是primary key，必須是unique的，產(chǎn)生沖突，故SSN和colour都必須是primary key。
5. Derived attributes
上例中，age可以由birthDate計(jì)算得出，所以不要寫(xiě)入person的table中
create table Person(
ssn char(20),
familyName varchar(50),
givenName varChar(50) not null,
weight float,
birthDate date,
primary key (ssn)
);
create table PersonHobbies(
person char(20) references Person(ssn),
hobby varchar(50),
primary key (person, hobby)
);
6. Mapping subclasses
通常有三種方法，一種是用ER style，subclass的table中包含subclass的attribute和superclass的primary key；一種是object-oriented style，subclass中包括subclass的attribute和所有superclass的attribute；一種是single table with nulls，扁平化，只有一個(gè)table。
（1）ER style
Employee和Manager的table都包含Person的primary key，SSN在Employee和Manager中都作為primary key，但同時(shí)也是foreign key連接到Person。
（2）OO style
Employee和Manager的table都包含Person的SSN，但SSN不做搜索用，因?yàn)樗行畔⒍家呀?jīng)在Employee和Manager的table中。
（3）Single table with Nulls
一個(gè)table的壞處是會(huì)出現(xiàn)很多有null的instance，這里如果只有bonus是NULL，則這個(gè)人是employee；如果salary, position, bonus都是NULL，則這個(gè)人是person。
7. Mapping disjoint subclass
（1）ER style
這個(gè)例子中student的subclass是total participation，在ER style中無(wú)法體現(xiàn)。
create table Student (
sid integer primary key,
name text,
address text
);
create table Ugrad (
sid integer references Student(sid),
degree text,
primary key (sid)
);
create table Masters (
sid integer references Student(sid),
major text,
primary key (sid)
);
create table Research (
sid integer references Student(sid),
thesis text,
primary key (sid)
);
（2）OO style
這個(gè)例子中student的subclass是total participation，在OO style中無(wú)法體現(xiàn)。
create table Student (
sid integer primary key,
name text,
address text
);
create table Ugrad (
sid integer references Student(sid),
name text,
address text,
degree text,
primary key (sid)
);
create table Masters (
sid integer references Student(sid),
name text,
address text,
major text,
primary key (sid)
);
create table Research (
sid integer references Student(sid),
name text,
address text,
thesis text,
primary key (sid)
);
（3）Single table with Nulls
這個(gè)例子中student的subclass是total participation，在single table中可以表示出來(lái)。
create table Student (
sid integer primary key,
name text,
address text,
degree text,
major text,
thesis text,
constraint DisjointTotal check
((degree is not null and major is null and thesis is null)
or
(degree is null and major is not null and thesis is null)
or
(degree is null and major is null and thesis is not null))
);
如果是partial participation
create table Student (
sid integer primary key,
name text,
address text,
degree text,
major text,
thesis text,
constraint DisjointPartial check
((degree is not null and major is null and thesis is null)
or
(degree is null and major is not null and thesis is null)
or
(degree is null and major is null and thesis is not null)
or
(degree is null and major is null and thesis is null))
);
8. Mapping overlapping subclass
ER style和OO style沒(méi)有辦法直接表示出overlapping和disjoint的區(qū)別，需要加trigger，比如
stype text not null
check (stype in ('ugrad', 'masters', 'research')),
single table with Nulls, total participation
create table Student (
sid integer primary key,
name text,
address text,
degree text,
major text,
thesis text,
constraint OverlappingTotal check
(degree is not null or major is not null or thesis is not null)
);
single table with Nulls, partial participation
create table Student (
sid integer primary key,
name text,
address text,
degree text,
major text,
thesis text
-- no constraint needed
);
9. Relational DBMSs
Relational DBMSs近似于Relational model，幫助定義domains, attributes, tuples, tables, constraints(domain, key, referential)。區(qū)別是它不嚴(yán)格限定table必須要有keys，使用bag semantics而不是set semantics。
bag semantics可以理解為multiset，在集合中允許重復(fù)元素，比如{a, a, b}
RDBMS的operation要符合ACID：ACID (atomicity, consistency, isolation, durability) is a set of properties that guarantee database transactions are processed reliably.
10. Using PostgreSQL in CSE
注意使用后要停止server并且登出CSE的主機(jī)。
wagner$ ssh YOU@grieg
grieg$ priv srvr
grieg$ source /srvr/YOU/env
grieg$ pg start
grieg$ psql mydb
... do stuff with your database ...
grieg$ pg stop
grieg$ exit
11. Building/Maintaining Databases
Shell commands
createdb dbname
dropdb dbname
psql dynamo -f dumpfile
--restore/dump file
SQL statements
CREATE DATABASE dbname
DROP DATABASE dbname
CREATE TABLE table ( Attributes+Constraints )
ALTER TABLE table TableSchemaChanges
COPY table ( AttributeNames ) FROM STDIN
DROP TABLE table(s)
TRUNCATE TABLE table(s)
--Remove the content of the table, but the table stays
INSERT INTO table ( attrs ) VALUES tuple(s)
DELETE FROM table WHERE condition
UPDATE table SET AttrValueChanges WHERE condition
