DatabaseNotes-Ch1,2

Ch1: Introduction

Ch2: The Rational Model

1.The CAP Database

2.Naming the Parts of the Database

3.Rational Rules:

First Normal Form Rules
Access Rows By Content Only Rule
The Unique Row Rule
Entity Integrity rule

4.Keys, Superkeys and Null values

5.Rational Algebra(关系代数)

NAME	SYMBOL	FORM	EXAMPLE
UNION (并)	∪	UNION	R ∪ S
INTERSECTION (交)	∩	INTERSECT	R ∩ S
DIFFERENCE (差)	—	MINUS	R — S
PRODUCT (乘积)	$\times$	TIMES	R $\times$ S

NAME	SYMBOL	FORM	EXAMPLE
PROJECT (投影)	R [ ]	R [ ]	R[A_i1,…,A_ik]
	$\Pi $		$\Pi$ _Ai1,…,Aik ®
SELECT (选择)	R where C	R where C	R where A₁=5
	$\sigma$	$\sigma$ _c®	$\sigma$ _A1=5®
JOIN (联接)	$\infty$	JOIN	R $\infty$ S
DIVISION (除法)	$\div$	DIVIDEBY	R $\div$ S

Compatible Tables (相容表)

表R和表S是相容表，当且仅当，

表R和表S的表头相同，并且对应的列中的数据是相同的类型和相同的语义。

Assignment(赋值/取别名)

符号：:=
将表格R的值赋给(取别名)S: S := R
举个栗子：
$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(运算符)
Highest	PROJECT
…	SELECT
…	PRODUCT
…	JOIN, DIVIDEBY
…	INTERSECTION
Lowest	UNION, 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]

3. Get cnames of customers who order all products ?

//出现求ALL(所有一类属性)的记录时，需要使用除法运算
First: get cids of customers who orders all products
T := ORDERS[cid , pid] $\div$ PRODUCTS[pid]

注意：//使用除法运算时，Dividend(被除数)中的属性经过除法运算后剩余属性也需要是候选键性质
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’ ?

Step 1: find 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]
而不是O[cid] – (O where aid = ‘a03’) [cid]

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

O[cid] – (O where aid $\neq$ ‘a03’) [cid]
而不是C[cid] – (O where aid $\neq$ ‘a03’) [cid]

4.More Complex One (Question And Steps):