E-mail: ziyulin@xmu.edu.cn - PowerPoint PPT Presentation

Title: E-mail: ziyulin@xmu.edu.cn


1
??????????? ????
??????????????? ????????
??? ????
??? ?????????? E-mail ziyulin_at_xmu.edu.cn
??????????
2011?10?
2
??? ????
?3? ???? 3.1 ?????? 3.2 ??????????? 3.3
????????? 3.4 ??????????
3
3.1 ??????
3.1.1. ??????????? 3.1.2. ?????? 3.1.3
????????????????? 3.1.4. ???????????
4
3.1.1 ???????????
(1)????? ??????????????????,????????
??,???????????,???????????,???????????(?????????)?

5
3.1.1 ???????????

• (2)???????????
• ??????????????
• ????????????
• ????
• ???????
• ???????,????????????,??????????
• ????????????,???????????????,???????????????????
  ??????????????????????
• ???????????????????????????????????????

6
3.1.2 ??????
???????
??????????(???????),?????????(???)???????????????
????,?????????????????
7
3.1.3 ????????????????
???????????????,?????????? (1)??
??3.1??????????????????? ???
??3.2?????D1,D2,,Dn,????,?????????
D1,D2,,Dn?????? D1D2
Dn ( d1, d2 , , dn) di? Di,i1, 2,
, n ???????( d1, d2 , ,
dn)???? n ??(???),
????????di?????? ????????D1D2 Dn
??3.3 D1D2 Dn??????D1,D2,,Dn?????,???
R( D1,D2,,Dn) ??
,R?????,n?????(????)?
8
3.1.3 ????????????????
????????????????????????,??????????? ?1????? D1
????SUPERVISOR???,?? D2 ????SPECIALITY?????,
???? D3?????POSTGRADUATE??,??,?? ?D1
,D2,D3?????? D1D2D3(???,?????,??),(???,?????,
??), (???,?????,??),(???
,????,??),
(???,????,??),(???,????,??),
(??,?????,??),(??,?????,??),
(??,?????,??),(??,????,??),
(??,????,??),(??,????,??) ??(???,?
????,??)?(???,?????,??)???????????????????????????
9
3.1.3 ????????????????
?????????22312,????,D1D2D3???22312?????12?
??????????,??
D1,D2,D3?????
SUPERVISOR SPECIALITY POSTGRADUATE
??? ??? ??? ??? ??? ??? ?? ?? ?? ?? ?? ?? ????? ????? ????? ???? ???? ???? ????? ????? ????? ???? ???? ???? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ?? ??
10
3.1.3 ????????????????

• ??3.3??
• ??????????????????????,???????????
• ???????????,??????????(Primary Key)????????????(Pr
  ime attribute)?
• ???????????????????(Non-key attribute)?
• ????????,??????????????????,?????????????????????
  ??????(All-key)?
• ????????,??????????,???????????

11
3.1.3 ????????????????
(2)???? ??3.4??????????????????????
R(U,D,dom,F) ??R
????, U
????????????, D
????U????????,
dom ???????????,
F ?????????????? (3)?????
??3.5???????????,??????????????
?????????????
12
3.1.4 ???????????
3.1.4.1 ????????? 3.1.4.2
DDB?????? 3.1.4.3 DDB???????
3.1.4.4 ????(FS)?? 3.1.4.5 ????(AS)??
3.1.4.6 ??????? S 3.1.4.7 ????

13
3.1.4.1 ?????????

• ????????????,???R(U,D,dom,I,F,Q,S),??
• R???? U???R??????
• D?U?????? dom??????????????
• I ?????????? Q???????????(??)
• F??????????? S?????????
• ????r??????????R(U,D,dom,I,F,Q,S)?????S?????????U?
  ??D?????Q?????????,??????????????? ?
• ????????????
• ????????????????,????????,????????????Q??????(????
  )???????????????
• ????????????????
• ?????????,??????????,?????????????????????????????
  ?
• ????????????,????????????R(U,Q,S)

14
3.1.4.2 DDB??????

• ?????????R(U,Q,S)??????(GR),???????????????????,?
  ????????????????????????S??,??QTRUE,S?Ø???,?GR???
  ??
• ?????????R(U,Q,S)??????,???????DDB?????????,?????
  ?????,????????,????DDB?????????????????????????,??
  ????????GR?????,?????????????????,???????,????????
  ?? 1n ?
• ?????????R(U,Q,S)??????,??????????????????,??????
  ??????,????SØ??????????????????????????????,????
  ???????,????????,???? 11 ? 1n??(????????????????
  ??,??????????)?
• ??
• ???????????(??????)?????????????????????????(????
  ??)?????????????????????????????????S?

15
3.1.4.2 DDB??????
? ? ? ? ? ? ? ? ? ?
? ? ? ? ? ? ? ? ? ?
??DDB????????????
16
3.1.4.3 DDB???????
??(??)????????????????????(???)???GDB? ?????????
???(????)?????????????LgDB? ?????????????????????
????PDB? ??????????????? ????
???? GDB LgDB
PDB ?????????????????,???????????
??,???????,??????????(????????????????????),????
??????????????????? ???????????????????????????
17
3.1.4.4 ????(FS)??
????FS???????,??GDB???GR???LgDB????,?
FS(GDB) LgDB ???????????,??????????????? FS-1
(LgDB)GDB
18
3.1.4.5 ????(AS)??
???????????,??LgDB??????????PDB?????????,?
AS(LgDB) PDB ???????????,?PDB?????????????????,?
???LgDB,?? AS-1(PDB)LgDB?
19
3.1.4.6 ???????S

• ??R(U,Q,S)?????S???R????????????
• ??R?????,??????????,??SØ
• ??R?????,?????????,??S??R?????
• ??R?????,??S???R??????????????

20
3.1.4.7 ????
????????????,??????????,??????????????????????????
? ????R(U,Q,S)??????????????????S??????????????,
????????????????S?Ø?
21
3.2 ???????????
3.2.1 ?????? 3.2.2 ???? 3.2.3 ????????
22
3.2.1 ??????
(1)????????????DDB????????,????DDB?????,?????????
????????????? ??????????DDB???????,??????
??????? ?1????????????Dept,??????????????
???????Dept???????(????DNO,???????AREA???????)????
?????????? ?2????????????Emp,???????????E
mp?????????(????????TAX???SAL???????,?????Emp?????
?????????)??????????? ??????DDB?????? (2)
??????DDB???,????DDB?????,????????????????????
?????????????????
23
3.2.2 ????
3.2.2.1 ???? 3.2.2.2 ???? 3.2.2.3
???? 3.2.2.4 ???? 3.2.2.5 ???????????
24
3.2.2.1 ????
?????????????????????????,(???????????)??????????
???,??????
??1????R(U,Q,S)??????(H)????,??R?????????
P1,P2,,Pn,????????? R1(U1,Q1,S1),,Rn(Un,Qn,Sn)
??1)U1U2UnU 2)K1K2KnK
3)QiQ Pi 4)Si ?Ø ??i,j?l,,n,Pi
PjØ, PiTrue,?? R(H)ltPgt R1,,Rn
??PP1,,Pn?
???????????????????????????????????Ri,??????sq
(R)???
25
3.2.2.1 ????
?1???????Supplier(SNO,SNAME,SCITY)???????SCITY
??????????????London?Paris,??????,??????
Supplier1sscityLondon(Supplier)
Supplier2sscityParis(Supplier)
26
3.2.2.2 ????
??????????????????????????????,??????????,??????
??????(???????)?
??2????R(U,Q,S)?????(V)????,??R?????????A1,,An??
??????? R1(U1,Q1,S1),,Rn(Un,Qn,Sn) ??1)K1K2
KnK 2)UiAi 3)Q1Q2QnQ 4)pk1
(R1)pk2(R2)pkn(Rn) pk(R) 5)Si?Ø
??i,j? 1,,n , Ai U,
AinAj K,?Ai U
??R(V)ltAgt R1,,Rn ??A A1,,An
??????,??????????????????????????,R??????????R????
??????Ri,??pAi(R)??,??K Ai?
27
3.2.2.2 ????
?2????Emp(ENO,ENAME,SAL,TAX,MGRSSN,DNO)?????????
???????,?SAL,TAX?????????????,?ENO,ENAME,MGRSSN,D
NO???????????????,??ENO???????????????????Emp???
Emp1pENO,SAL,TAX(Emp) Emp2pENO,ENAME,MGRSSN,
DNO(Emp)
28
3.2.2.3 ????
????????????????????
??3????R(U,Q,S)??????(M)????,??????????A1 ,,
An?????P1 ,,Pn??? R1(U1,Q1,S1),,Rn(Un,Qn,Sn,) ?
?1)Ui Ai 2)K1K2KnK 3)QiQ?Pi 4)Si?Ø?
??i,j?l,,n,Ai
PjØ,
U,AinAj K,?Ai U,Pi
PjTrue,?? R (M)ltAPgt R1, ,Rn ??AP
ltAi,Pigt i1,,n ?
?????????????????????????,???????????????????????,
?????????(UNION)?????(JOIN)???
29
3.2.2.3 ????
?3??2?Emp???Emp1,Emp2?,?Emp2?????DNO1?DNO3?????
?,?DNO2?????,?Emp????
Emp1pENO,SAL,TAX(Emp)
Emp2sDNOD1or DNOD3 (pENO,ENAME,MGRSSN,DNO
Emp) Emp3sDNOD2(pENO,ENAME,MGRS
SN,DNO Emp)
30
3.2.2.4 ????
??????????????????,???????????????????????????????
????????
??4????R(U,Q,S),????????T (T????????T1(U1,Q1,S1),
,Tn(Un,Qn, Sn) )?????A??????(DH)????,??R???
R1( ) ,, Rn( )
?? 1)
2)
3)
4)
5)Si?Ø ??R(DH)ltTgt R1,,Rn ??T T1,,Tn

• ?????????????,?????????
• ??????

31
3.2.2.4 ????

• ???????
• ??????????(JOIN)?????-???????
• ???????????????(JOIN)?????????R?S??????R?S,??????
  R?S????(Natural JOIN)??R???????,?????????
• R?SpR(R8S)(??)
• ??R?S???????????S??R??????????????,???R??????
• ?????????????,?R?S?S?R
• ?????????????,??????????????????(??)???(??)

32
3.2.2.4 ????
????????? R ? S ?????????????????????????????????
???????????
? ??????
33
3.2.2.4 ????
?-??(?????????) ????????????????,?????????????????
?,?????????????????,??? ?-??(? theta-??)?? ????
lt, , , gt, ????????????? R ? S ??????
????????????? S ? R ????????,????????????,?-??????
?????? ?????????????????????????????????????
???,????????????????????-?? CarPrice BoatPrice
?????????????
? ?-????
34
3.2.2.4 ????
????????? S ?????????????????? R
????? ???????????????? R?SpR(R8S)
R
S
Manager
Harriet
Charies
?????
? ?????
??????
35
3.2.2.4 ????
?4?????Supply(SNO,PNO,QTY),?????????????SCITY????
?? ????SCITY??Supply?????,?Supply?Supplier??????
??Part???(?????????????????)?Supply?Supplier?Part?
???SNO?PNO????,?Supply?Supplier??,SNO?????????,??S
upply???(foreign key)???,Supply???????????SNO??,?S
CITY???????????????,SCITY????Supply??????
36
3.2.2.4 ????
????Supplier1sSCITYLondonSupplier
Supplier2sSCITYParisSupplier
??????????Supply???,? Supply1Supply ?
Supplier1
Supply2Supply ? Supplier2
????????,?????? Supply1pSNO,PNO,QTY(Supply8
(sSCITYLondonSupplier)) Supply2pSNO,PNO,QTY(S
upply8(sSCITYParisSupplier))
??????Supply?????????????,????????????????????
??????????????????? Q1Supply.SNOSupplie
r. SNO and SCITYLondon
Q2Supply.SNOSupplier.SNO and SCITYParis
?R?SpR(R8S)
37
3.2.2.4 ????
38
3.2.2.5 ???????????
?????????????????? R(Oj),ltCgtR1,,Rn ??,Oj?
H,V,M,DH , C C1,,Cn ? ?OjDH?,Ci???? ?Oj?DH
?,CiltAi,Pigt??OjH,?AiU??OjV,?PiTrue? ?????
??????R(U,Q,S)???Oj????????????(???)R1(U1,Q1,S1),
,Rn(Un,Qn,Sn),??, ?Oj ? DH?, UiAi,KiK, QiQ?Pi
,Si ? Ø,i1,,n ?OjDH?,UiU, KiK, QiCi, Si ?
Ø,i1,,n?
39
3.2.3 ????????

• ????????????????????????????????????
  ?,???????????????
• ??????????????????,???????????,?????
  ??????????????????????
• ??????????????????????,????????(?AiU),??????????
  ?
• ????????????????????
• ???????????????????????
• ????????????,???????????????????????
  ??????,???????????,?????????????????????(?????????
  ????)?

40
3.2.3 ????????

• ?1??Supplier??????Supplier1?Supplier2???????
  ?????
• ?????????????? SCITY?,??????????
• ?????????????????????Suppler??
• ????????????????
• Q1SCITYLondon,
• Q2SCITYParis,
• ????????,??????????????

41
3.3 ?????????
3.3.1 ?????????
3.3.2 ??????
3.3.3 ??????
42
3.3.1 ?????????
?? 4 ????????????????????? 4 ??????????????????
???
????
?????
?????
??????
?????
?????????,????/???
- ????? ??? - ?????????? - ???? -
?????? - ??????????

• ????????
• ????????
• ????????
• ?????????

• ????
• ??????
• - ??????????????(????????????????????????????)

• ????????
• ?????
• ??????????
• ??????????

43
3.3.2 ??????
??1 ??????R(U,Q,S)?????(AO),??R??
?????r1,,rn?,????ri???????????
Rri(Uri,Qri,Sri),?? UriU
QriQ SriØ? ?? R(AO)ltrgt Rr1,
Rr2Rrn, rr1, , rn
44
3.3.3 ??????
????????? ???????????????????
???,?????????????,???????
??1???????? ??2?????????????
??3?????????
45
3.4 ??????????
3.4.1 ??
3.4.2 ???????????????
3.4.3 ????????
46
3.4 ???????????????
3.4.1 ??

• ?????? R(U,D,dom,I,F,Q,S)
• ???? R(U,Q,S),
• ??,S???????? ,???R?????????????
• ??R?????,??S???R??????????????
• ??R?????,?????????,??S??R?????
• ??R?????,??????????,??SØ
• ????????
• ??????????????????S?????
• ????????????????,?????????????????

47
3.4 ??????????
3.4.2 ???????????????
3.4.2.1 ???????? 3.4.2.2 ???????????? 3.4.2.3
???????????? 3.4.2.4 ????????????
48
3.4.2.1 ????????
??1????????????G(V,E,L),?? 1)
V??????????????????????????,?Ri(Ui,Qi,Si)(Oj)??
2) E????????????R(Oj)ltCgtR1,,Rn,?????R(U,Q,S)(
Oj)?R1(U1,Q1,S1)(O1),,Rn(Un,Qn,Sn)(On)?n????R(R,
Q,S)(Oj),Ri(Ui,Qi,Si)(Oi),???????Ci,i1,2,,n? 3)
L???????????Ci???? ????????????,????????
49
3.4.2.1 ????????
? ?????
50
3.4.2.2 ????????????
??1???????????CiltAi,Pigt???? R(U,Q,S)(O),R(U,Q
,S)(O) ,??QQ?Pi,UAi,KK ?
??2?????,??????R0(U0,Q0,S0)(O0)?Rf(Uf,Qf,Sf)(Of)?
?????????
,?
1)
2)
3)
???????.
4)
51
3.4.2.2 ????????????
??2 ???????T(V,E),?? 1)V???????????Ri
(Ui,Qi,Si)(Oj),Qi????,Oj?????H,V,M,?????,?QiTru
e,SiT 2)E?????????????,R(Oj)ltCgtR1,,Rn,??,C
C1,,Cn,CiltAi,Pigt,(i1,,n)???????,???R(U,Q,S)(
Oj)?Ri(Ui,Qi,Si)( )??,??,i1,,n,?UiAi,KiK,
QiQ?Pi ?
?????????????????????
52
3.4.2.3 ????????????
??3 ???????
,??
1)V???????????
?????????,
?????
???
2)E???????????
,?????????
??,
?Ri(Ui,Qi,Si)(Oj )?,
?UiAi,KiK,QiQ?Pi
??
,??
??????,??
,????T?????
???
????,
????
,?
53
3.4.2.4 ????????????
??4 ???????
,??
1)V???????????
??,
?????????????,
?????
?????,?
2)E???????????
??Oj?H,V,M,??
,?????????
?Ri(Ui,Qi,Si)(Oj )??,
?UiAi,KiK,QiQ?Pi
Si?Ri(Ui,Qi,Si)(Oj )?????,
??Oj?DH,?,??
????T?????
???
??,
????
,?
(???, Qi ?????)
54
3.4 ????????
3.4.3 ????????
DDB????????????????????,?????????????,????
??????????????????????????????????????????????,???
?????????????????????? ???????,???????????
?????????????,????????????????????,???????????????
????????????????????WDDBS?L-SQL???
55
3.4 ????????
?6 ??2?????Emp,????3????????Emp?,?L-SQL?????? C
reate Table Emp (ENO n 4 ENAME c 12 SAL n 5
TAX n 4 MGRSSN n 8 DNO n 2) Key ENO Fragment
aaEmp (ENO,ENAME,MGRSSN,DNO)
Emp1Emp (ENO, SAL, TAX)
Emp2aa (DNO1 or DNO3)
Emp3aa (DNO2) Site Empl(1,2) Emp2(1)
Emp3(2)
56
3.4 ????????
?7 ???1??4???????????? Create talbe Supplier
(SNO n 4 SNAME c 12 SCITY c 10) Key SNO
Fragment Supplier1Supplier(SCITYLondon)
Supplier2Supplier(SCITYParis) Si
te Supplier1 (1) Supplier2(2,3) Create Table
Supply (SNO n 4 PNO n 4 QTY n 5) Key SNO
PNO Fragment Supply1Supply SJSNOSNOSupplier.S
upplier1 Supply2Supply
SJSNOSNOSupplier.Supplier2 Site Supply1(1,3)
Supply2(2,3)
57
Department of Computer Science, Xiamen
University, Oct, 2011