遇见时光

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):