okuyama@alice.asahi-u.ac.jp

3

417424501508515SQL522529605612616626

3

3

- A relation is a set of ordered pairs. If A and B are sets and is a relation, then we call a relation from A to B if AB.- C, P* CP, where (c, p) * if city c is in prefecture p, is a relation from C to P.

3

3

1969Ted Codd(Edger F. Codd)Relation(relation between USA and China)

3

(Domain)(Domain, ): : D1D2Dn D1D2Dn = {(d1, d2, ,dn) | d1D1, d2D2,,dnDn(tuple): (n-)t =(d1, d2, , dn)diti

3

(Relation)D1, D2, , Dn R D1D2DnDi (1in)RinR(degree)RR(cardinality)nn122D0R(R=) R(=)

3

3 D1={1, 2}, D2={a, b, c}, D3=D1D1D2D3={(1,a,1), (1,a,2), (1,b,1), (2,b,1), (1,c,1), (2,c,1), (2,a,1), (2,a,2), (2,b,1), (2,b,2), (2,c,1), (2,c,2)}R={(1,a,1), (1,b,2), (2,b,1), (2,c,2)}

3

ordered pairs*=CP C P R=CP R={(, ), (, ), (, }()

3

3

(1) (2)

3

RD1, D2, , DnRiAiAiDidomdom(Ai) = Di (1in)RA1, A2, , AnRRA1, A2, , AnR (A1, A2, ,An)dom(A1)dom(A2)dom(An)t=(d1, d2, , dn)Rdi(1in)tAit[Ai]

3

(projection)Ai1, Ai2,,Aik(1i1

RD1, D2, , DnR(1st normal form, 1NF)(1)D(2)D

3

{(x,y)| xy}(1)(2)ABC(XA,YA)(XB,YB)(XC,YC)PAPBPC001002003ABC{H,I,J}{K}

3

(normalization)ABCXAXBXCPAPBPC001002003ABCHkYAYBYC001AI001AJ

3

ANSI/X3/SPARC

3

R(relation schema)

3

K55K55K55K81K81K550010020030040050065065406045-35

3

R(A1, A2, , An){A1, A2, , An}R(instance, )

3

(key, )(retrieval)(candidate key)(1)RR(t, tR)(t[K]=t[K]t=t)(2)K(1)K={Ak1, Ak2, , Akp}R

3

(primary key)(1)(2)1(semantic)

3

(1)(2)(3)

3

3

(Integrity)(Integrity Constraint)(Semantic Constraint)DBMS

3

(Domain Constraint)7070RDBMS

3

(Key Constraint)(Null Value)(Unique)

3

(Null)(Null Value)Null Nonexistent, Insubstantial, Meaningless

3

RH(HR)S(Foreign Key)RSRSR (1)t[H](2)t[H]=s[K]SsKSRHS(1)(2)(Referential Constraints)

3

99010199010299010299510172568362BCAC995101OS30D44499

3

f1{,}f2{,}f3f4f5

3

R(A1,A2,,An)X,YRXY(Function Dependency, FD)R(t,tR)(t[X]=t[X]t[Y]=t[Y])RtXtXttY()XYXY

3

(trivial)YX(YX)XYX, XX

3

3

(Multivalued Dependency, MVD)XYRXYYXXY(t,tR)(t[X]=t[X]((t[XY],t[Z])R (t[XY],t[Z])R)Z=R - (XY)

3

ttt[X]=t[X]Rtt2u = (t[XY], t[Z])v = (t[XY], t[Z])R

3

X.a1 a2 ap.a1 a2 ap.a1 a2 ap.a1 a2 ap.RY

b1 b2 bq

b1 b2 bq

b1 b2 bq

b1 b2 bq

Zc1 c2 cr

c1 c2 cr

c1 c2 cr

c1 c2 cr

t

t

u

v

t[X]=t[X]ttuvR

3

RR[X]R[Y]R[X]R[Z](Orthogonal)XYZXYRZ=R-(XY)XZXYZ

3

XY=RXYYXXY

2-1XYXYXY-(XY)

3

(

3

3

3

(Data Manipulation)(retrieval)(Update)(insertion)(deletion)(out-of-date)(modification)

3

3

(Algebra)(Calculus)

(relational complete)

3

3

uniondifferenceintersectiondirect product

projectionselectionjoindivision

3

3

(union)(difference)(direct product)(projection)(selection)

3

(union compatible)R(A1, A2, ---,An)S(B1, B2, ---,Bm)

RSn=mi(1 i n)Ai Bi dom(Ai)=dom(Bi)

3

3

3

(Union)R(A1, A2,,Am)S(B1, B2, , Bn)RS={t|tRtS}

3

3

(Difference)R(A1, A2,,Am)S(B1, B2, , Bn)RS={t|tRtS}

3

3

(Intersection)R(A1, A2,,Am)S(B1, B2, , Bn)RS={t|tRtS}

3

3

3

(Expanded Direct Product)R(A1, A2,,Am)S(B1, B2, , Bn)RS={(r, s)|rRsS}RSRSR.A1, R.A2, , R.Am, S.B1, S.B2, , S.Bn

3

3

(Projection)R(A1, A2,,An)X={Ai1, Ai2, , Aip} (1i1

(selection, restriction)

-(-comparable) -R(A1, A2, ---,An)RAiAj-R[Ai Aj]

3

(comparable)R(A1, A2,,An)AiA(dom(Ai)=dom(Aj))RAiAjt[Ai]t[Aj]

3

(selection)(join)(selection)R(A1, A2,,An)AiA-R[AiAj]={t|tRt[Ai]t[Aj]}(join)R(A1, A2,,Am)S(B1, B2, , Bn)AiBR[AiBj]S={v|vRSv[R.Ai]v[S.Bj]}AiB(join attribute)

3

3

3

3

3

3

(natural join)R(A1, A2,,Am)S(B1, B2, , Bn){C1, C2, , Cl}RS=(R[R.C1=S.C1]SR[R.C2=S.C2]SR[R.Cl=S.Cl]S)[R.A1, R.A2, ,R.Am, S.D1, S.D2, , S.Dn-l]S.D1, S.D2, , S.Dn-lSC1, , Cl

3

3

F1F1S1S2F2S1F1F1S1S2F2S1F2S2F1F2[]S1S2[, ]

3

(division)R(A1, A2,,An-m, B1, B2, , Bm)S(B1, B2, , Bm)BiBiR[A1, A2,,An-m, B1, B2, , BmB1, B2, , Bm)]S={v|v R[A1, A2,,An-m ] (uS)((v, u)R)} []C2C2C3C4C1C4C2C4

3

3

3

(query expression)DBR1, R2, , Rn

3

(1)R(2)C(3)RSRSRSR-S(4)RSRS(5)R{Ai1, Ai2, , Ail}RR[Ai1, Ai2, , Ail](6)RAiAjRR[AiAj](7)(1)(6)

3

3

3

(unknown, (F)=T()(T)FFFFFFTFTFTFFFTTTTTTT

3

RAiAjR[AiAj]={t|tRt[Ai]t[Aj]T}R[AiAj]={t|tRt[Ai]t[Aj]}

3

RSAiBjR[AiBj]S={(t, u)|tRuS t[Ai]u[Bj] T} R[AiBj]S={(t, u)|tRuS t[Ai]u[Bj] }

3

(outer union):R(A1, A2, , Ap, B1, B2, , Bq)S(B1, B2, , Bq, C1, C2, , Cr)R= RC, S=CSCCC1, C2, , CrA1, A2, , ApR S=RS(outer -join)R(A1, A2, , Ap, B1)S(B2, C1, C2, , Cq)B1B2

3

R[B1 B2]SR[B1 B2]S=T(RD)(DS)T=R[B1B2]SR=R-T[A1, A2, , Ap, B1]S=S-T[B2, C1, C2, , Cq]DDSR

3

3

--515okuyama@dsl.gr..jp

3