# 遇见时光

Keep Looking , Don't Settle
Stay Hungry , Stay Foolish

## Ch2: The Rational Model

### 3.Rational Rules:

1. First Normal Form Rules
2. Access Rows By Content Only Rule
3. The Unique Row Rule
4. Entity Integrity rule

### 5.Rational Algebra(关系代数)

NAMESYMBOLFORMEXAMPLE
UNION (并)UNIONR ∪ S
INTERSECTION (交)INTERSECTR ∩ S
DIFFERENCE (差)MINUSR — S
PRODUCT (乘积)$\times$TIMESR $\times$ S
NAMESYMBOLFORMEXAMPLE
PROJECT (投影)R [ ]R [ ]R[Ai1,…,Aik]
$\Pi$$\Pi$Ai1,…,Aik ®
SELECT (选择)R where CR where CR where A1=5
$\sigma$$\sigma$c®$\sigma$A1=5®
JOIN (联接)$\infty$JOINR $\infty$ S
DIVISION (除法)$\div$DIVIDEBYR $\div$ S

Compatible Tables (相容表)

Assignment(赋值/取别名)

1. 符号：:=

2. 将表格R的值赋给(取别名)S: S := R

3. 举个栗子：

$T1 := (R \cap S)\\ T2 := (R \cup S)\\ T := T1 – T2$

#### 1) Set-Theoretic Operation

Union,Intersection,Difference,Product
- Example:

$R\cup S,\ R\cap S,\ R-S,\ R\times S$

Tips:

• Product运算产生的新表列的个数为$C_S + C_R$，新表行的个数为$N_R \times N_S$

#### 2) Native Theoretic Operation

Project,Select,Join,Division
- Example:

$R[A_{i1},...A_{ik}],\ R\ where\ A_i=5,\ R\infty S,\ R\div S$

(Projection)CN:= C[cname]
(Selection)T :=CUSTOMERS where city = ‘Kyoto’
(Selection)Pairs:=(L$\times$ M) where L.city=M.city
Join($R\infty S$)
Division($R\div S$)

Precedence（优先级）Operators(运算符)
HighestPROJECT
SELECT
PRODUCT
JOIN, DIVIDEBY
INTERSECTION
LowestUNION, DIFFERENCE

#### 3) Complex Query

1. Find cname and city of customers and aname of agents that the customer lives in the same city with agents.

((C$\times$A) where C.city=A.city)[C.cname,C.city,A.aname]

(C$\infty$ A)[C.cname,C.city,A.aname]

2. Find pid, month and qty of order whose customer’s name is ‘Allied’.

((C$\times$O) where C.cid=O.cid and C.cname=‘Allied’)[O.pid,O.month,O.qty]

or

( ( ( (C where cname = ‘Allied’) [cid] ) x O ) where C.cid = O.cid ) [ O.pid, O.month, O.qty ]

((C$\infty$O)where C.cname=‘Allied’)[O.pid,O.month,O.qty]

3. Find ordno of orders for customer, agent and product combinations that are all in the same city.

( (C [ cid, city ] x A [ aid, city ] x P [ pid, city ] x O)

where C.city = A.city and P.city = A.city and

C.cid = O.cid and A.aid = O.aid and P.pid = O.pid )

[ O.ordno ]

(C$\infty$ O$\infty$ P$\infty$ A)[O.ordno]

4. Find cid of customers who have a highest discount in all customers.

Step 1: Find all cids of customers

R1 := C[pid]

Step 2: Find cid of customers whose discount less than another customer. Let S := C, then

R2: = ((CxS) where C.discnt<S.discnt)[C.cid]

Step 3: calculate the result of this query by DIFFERENCE operation.

T := R1 - R2

if R = T $\times$ S, then

T = R $\div$ S

S = R $\div$ T

if T = R $\div$ S，then

T $\times$ S $\subseteq$ R

• 举个栗子：

#### 4) Native Relational Operation

Questions:

1. Get cids of customers who order  products  p01 ?

(O where pid=‘p01’) [ cid ]

2. Get cids of customers who order  products  p01 and p02 ?

(O where pid=‘p01’)[cid] $\cap$ (O where pid=‘p02’)[cid]

//出现求ALL(所有一类属性)的记录时，需要使用除法运算

T := ORDERS[cid , pid] $\div$ PRODUCTS[pid]

1.Why must project PRODUCTS on pid ?

• columns of divisor must be subset of columns of dividend.

Why must project ORDERS on cid,pid?

• We only try to find the value of the cid in ORDERS of the same for all cid.

Second: get names of customers

T1 := ( ( T ) $\infty$ C ) [cname]

4. Get names of customers who order all products ordered by customer ‘c006’ ?

R1 := (ORDERS where cid = ‘c006’)[pid]

Step 2: find cid of customers who order all products ordered by customer ‘c006’

R2 := ORDERS[cid,pid] $\div$R1

Step 3: find cnames of customers

R3 := ( R2 $\infty$ C ) [ cname ]

5. Get pid of products ordered through all agents ?

ORDERS [ aid, pid ] $\div$ AGENTS [ aid ]

6. Get name of products ordered by all customers who live in Dallas ?

R1 := (CUSTOMERS where city=‘Dallas’)[cid]

R2 := ORDERS [ cid, pid ] $\div$ R1

R3 := ( PRODUCTS $\infty$ R2 )[ pname ]

7. Get cids of customers who order all products priced at $0.50. R1 := (PRODUCTS where price = 0.50) [pid] R2 := ORDERS [ cid, pid ] $\div$ R1 8. Get cids of customers who order all products that anybody orders. ORDERS [ cid, pid ] $\div$ ORDERS [ pid ] 条件： Customers: C(cid, cname, city, discnt) Agents: A(aid, aname, city, percent) Products: P(pid, pname, city, quantity, price) Orders: O(ordno, month, cid, aid, pid, qty, dollars) 9. Get aids of agents who take orders on at least that set of products(相当于all the products) ordered by c004. O[aid, pid] ÷ (O where cid = ‘c004’)[pid] 10. Get cids of customers who order p01 and p07. (O where pid = ‘p01’)[cid] $\cap$ (O where pid = ‘p07’)[cid](正确) (O where pid = ‘p01’ and pid = ‘p07’)[cid](错误) 11. Get cids of customers who order p01 or p07. (O where pid=‘p01’)[cid] $\cup$ (O where pid=‘p07’)[cid] 或者 (O where pid = ‘p01’ or pid = ‘p07’)[cid] 12. List all cities inhabited by customers who order product p02 or agents who place an order for p02. T1 := ((O where pid = ‘p02’) $\infty$ C)[city] T2 := ((O where pid = ‘p02’) $\infty$ A)[city] T := T1 $\cup$ T2 13. Get aids of agents who place an order for at least one customer that uses product p01. ((O where pid = ‘p01’)[cid] $\infty$ O) [aid] 14. Get aids of agents who place orders for all customers that uses product p01. O[cid, aid] ÷ (O where pid = ‘p01’)[cid] 15. Retrieve product ids for all products that are not ordered by any customers living in a city beginning with the letter “D”. T1 := C where C.city >= ‘D’ and C.city < ‘E’ T2 := P [ pid ] - ( O $\infty$ T1 ) [ pid ] 条件： Customers: C(cid, cname, city, discnt) 16. Retrieve cids of customers with the largest discounts. Answer 1: 1)CY := C 2)T1:=((CY $\times$ C) where CY.discnt > C.discnt)[C.cid] 3)T2 := C[cid] - T1 Answer 2: 1)CY := C 2)T1(cyid, cid) := ((CY $\times$ C) where CY.discnt >= C.discnt) [CY.cid, C.cid] 3)T2 := T1 ÷ C[cid] 条件： Customers: C(cid, cname, city, discnt) Agents: A(aid, aname, city, percent) Products: P(pid, pname, city, quantity, price) Orders: O(ordno, month, cid, aid, pid, qty, dollars) 17. Get aids of agents who do not supply product p02. A[aid] — (O where pid = ‘p02’)[aid] 18. Get aids of agents who supply only product p02. O[aid] — (O where pid <> ‘p02’)[aid] (正确) A[aid] — (O where pid <> ‘p02’)[aid] (错误) #### 5) Illustrative Examples(示例) 条件： Customers: C(cid, cname, city, discnt) Agents: A(aid, aname, city, percent) Products: P(pid, pname, city, quantity, price) Orders: O(ordno, month, cid, aid, pid, qty, dollars) 1.Get the names of customers who order at least one product priced at$0.50.

(((P where price=0.50)[pid] $\infty$ O) $\infty$ C) [cname]

2.Find cids of all customers who don’t place any order through agent a03.

C[cid] – (O where aid = ‘a03’) [cid]

3.Retrieve customers who place orders only through agent a03.

O[cid] – (O where aid $\neq$ ‘a03’) [cid]

4.More Complex One (Question And Steps):