遇见时光

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

DatabaseNotes-Ch1,2

Ch1: Introduction

Ch2: The Rational Model

1.The CAP Database

2.Naming the Parts of the Database

3.Rational Rules:

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

4.Keys, Superkeys and Null values

5.Rational Algebra(关系代数)

NAMESYMBOLFORMEXAMPLE
UNION (并)UNIONR ∪ S
INTERSECTION (交)INTERSECTR ∩ S
DIFFERENCE (差)MINUSR — S
PRODUCT (乘积)×\timesTIMESR ×\times S
NAMESYMBOLFORMEXAMPLE
PROJECT (投影)R [ ]R [ ]R[Ai1,…,Aik]
$\Pi $Π\PiAi1,…,Aik ®
SELECT (选择)R where CR where CR where A1=5
σ\sigmaσ\sigmac®σ\sigmaA1=5®
JOIN (联接)\inftyJOINR \infty S
DIVISION (除法)÷\divDIVIDEBYR ÷\div S

Compatible Tables (相容表)

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

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

Assignment(赋值/取别名)

  1. 符号::=

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

  3. 举个栗子:

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


1) Set-Theoretic Operation

Union,Intersection,Difference,Product
- Example:

RS, RS, RS, R×SR\cup S,\ R\cap S,\ R-S,\ R\times S

zxkzxkzxk
zxkzxkzxk
zxkzxkzxk

Tips:

  • Product运算产生的新表列的个数为CS+CRC_S + C_R,新表行的个数为NR×NSN_R \times N_S

2) Native Theoretic Operation

Project,Select,Join,Division
- Example:

R[Ai1,...Aik], R where Ai=5, RS, R÷SR[A_{i1},...A_{ik}],\ R\ where\ A_i=5,\ R\infty S,\ R\div S

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

关系代数运算的优先级:

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

3) Complex Query

zxk

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

((C×\timesA) 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×\timesO) 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\inftyO)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]



zxk

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

  • 举个栗子:

zxk


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] ÷\divR1

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


zxk

大爷,赏点?