6 84729625EmailCZY_SOPHY@163.COM

1Armstrong234

5.1 5.2 Normalization5.3

5.1 5.1.1

RUDdomF UFR(UF)

, Functional DependencyFDMultivalued DependencyMVDNormal Form

5.1.2 (P171)(Student)

SNoSDeptMNCNameGU={ SNoSNameSDeptMNCNameG }

UF={ SNOSdept ,SDept MN,(SNO,CName) G } 5.1 Student

S

SNoSDeptMNCNameG950019095001869500167950027895003749500387

123

,3S (SNoSDeptSNoSDept)SG (SNoCNameG (SNoCName) G)D (SDeptMNSDeptMN)

5.2 Normalization 1971E.F.Codd

5.2.1 5.2.2 5.2.3 Normal FormNF5.2.4 5.2.5 4NF

5.2.1 1R(U)UXYUR(U)rr t,sXt[X]=s[X]Yt[Y]s[Y]XYYXXY

XY XY xf(x)=y

R Rr Rr R

R12={(S)(CB)(CN) (T)(CG)(G)} CBT; (S, CB)G; CBCN; CBCG; (S, CB, CN)G X YX Y X Y X YXDeterminant X YY XXY

2 R12={S, CB, CN, T, CG, G} CBTTCB (S, CB)G (S, CB, CN)G GS, CB, CN XY YX X YX

1 l SNo SName lSNo SDept l SNo SAge

2

3PJTP(** )

3 R={A, B, C, D} F={AB, BC, AC, ABD} AC

5.2.2 4KR(UF)Prime attributeNon Prime attributeNon-Key-attributeall key5 R XR XXRForeign Key Primary Key

5.2.3 Normal FormNF 19711972E.F.Codd1NF,2NF,3NF1974CoddBoyceBCNF1976Fagin4NF5NFRR xNF

5NF 4NF BCNF 3NF 2NF 1NF

1NF 1NF

------ 1A 2B 1NF

2NF 2NF6 R 1NFR 2NF P175 2NF 2NF(Second Normal Form)(2NF)12NF 22NF

Sno SnameSex Sdept SL Spec (SC) Cno Cname T CreditG RUF U={Sno, Sname, Sex, ID, Sdept, SL, Spec, SC, Cno, Cname, T, Credit, G} F={SnoSname, SnoSex, SnoSdept, SnoSL, SnoSpec, SnoSC, CnoCname , CnoT, CnoCredit, (Sno, Cno)G} Sno,Cno--F R1NF--- R2NF---

R (1) (2) Cno=1011 Cname= =3 R (3) C5 C5 C5 C5

R2NF---RR1U1F1 ----Sno 2NFU1={Sno, Sname, Sex, ID, Sdept, SL, Spec, SC}F1={SnoSname, SnoSex, SnoSdept, SnoSL, SnoSpec, SnoSC}R2U2F2 ----Cno 2NFU2={Cno, Cname, T, Credit}F2={CnoCname , CnoT, CnoCredit}R3U3F3 ----(Sno,Cno) 2NFU3={Sno CnoG}F3={(Sno, Cno)G}

-- 1NF + + + + 2NF* *

()

1NF2NF--2NF

3NF 3NF(Third Normal Form)3NF)

1 3NF

2 3NF *

QQ

R3NF,R2NF,R2NF ()R 3NF R3NFR2NF AX A, X R RA R3NF

BCNF( Boyce Codd Normal Form)Boyce Codd 19743NF R BCNFBCNF

BCNFSTJ STJ S T J SJ T ST J T J5.6TJ5.6 STJ

SJ ST STJ3NFJ (S, T) TTSTJBCNFBCNFSTSTTJTJ

BCNF3NFBCNFBCNF3NF

5.2.4 (P178-P179) T,CB

(C,T,B),A1l_KeyTEACHINGBCNF()(), (,,),(,,),(,,), Teaching

CTB

R(U)UXYZUZ=U-X-YR(U)rXxYYZ YXXY Y1 XYXY

CTB

R(U)r t,st[X]s[X]w,vrw,vs,tw[X]=v[X]=t [X]w[Y]=t[Y]w[Z]=s[Z]v[Y]=s[Y]v[Z]=t[Z] s,tYr XYUZ=UXY Y XXYXY Z=UXY= XY

1. XY XZ Z=UXY2.XYYZ XZ-Y 3. XY XY 4.XY XZ XYZ 5. XY XZXYZ 6. XYXZXY ZX ZY

XYUW(XYWU) XYW(WU)UXYUZXYX,YXYR(U)Y Y XY XYR(U)Y YXY

5.2.5 4NF R1NFRXY Y !XXR 4NF4NF XYXXY 4NFBCNF4NF

4NFWSC(WSC)WS WC UWS=CUWC = SW,S,CW WSR 4NFWS(WS) WC(WC)

1

2

3NFBCNF3NF

R

FD

5.3 5.3.1 Armstrong----Armstrong5-11R(UF) X Y U FR XYR Rr F rXY FXY XYF XYFFRrXY(rt,st[X]=s[X]t[Y]= s [Y] )FXY (XYF )

Armstrong1974ArmstrongUFURRu A1ReflexivityYX U XYFuA2XYFZ U XZYZFu A3XYYZFXZF: R, U={A,B,C}, F={AB,B C} :A ABC?:A BA AB BCB BCAB ABCA ABC.

: R(CITY, ST, ZIP) CITY ST ZIP F={(CITY, ST)ZIP, ZIPCITY} {ST, ZIP}{CITY, ST}

ZIPCITY (ST, ZIP)(CITY, ST) (CITY, ST)ZIP (CITY, ST)(CITY, ST, ZIP) (ST, ZIP)(CITY, ST, ZIP) ST(CITY, ST, ZIP) ZIP(CITY, ST, ZIP) (ST, ZIP) (CITY, ST)

5.1 Armstrong:P184YXURrt,s:t[X]=s[X],YX,t[Y]=s[Y],XY ().XYFZURrt,s:t[XZ]=s[XZ],t[X]=s[X]t[Z]=s[Z], XY,t[Y]=s[Y], t[YZ]=s[YZ], XZYZF()

A1A2A3u XY XZXYZ u XY WYZ WXZ u XYZ YXZ XYYZFRrt,s:t[X]=s[X],XY,t[Y]=s[Y],YZ,t[Z]=s[Z],XZF()

5.1XA1,A2,,Ak XAii=1,2, , k5.12 RFFF +R(U) U={A, B, C}, F={ABC, CB}R(U) F+={AA, ABA, ACA, ABCA, BB, ABB, BCB, ABCB, CC, ACC, BCC, ABCC, ABAB, ABCAB, ACAC, ABCAC, BCBC, ABCBC, ABCABC, ABC, ABAC, ABBC, ABABC, CB, CBC, ACB}

5.13 FUX U XF+ ={A|X AArmstrong} XF+XF5.2 FUXYUXYFArmstrongY XF+XYFArmstrongXF+YXF+

5.1 XX UUFXF+XF XF+[1.] X0=Xi=0[2.] Xi+1 = Xi B.B={A|(V)(W)(VWFV X(i)AW}[3.] Xi+1 = XiX(i+1)=U Xi+1 XF+ i=i+1

1 R(U)F, U={A, B, C, D, E, I}; F={AD, ABE, BIE, CDI, EC} (AE)+ X(0)=AE i=0; FAE ADX(1)=AED; EC X(2)=AEDC CDI, X(3)=AEDCI; VWF X(3+1)X(3) X+=X(3), (AE)+=ACDEI

2 RU={ABCDE}F={ABCBDCEECBACB} ABF+X0=ABX1 =AB CD=ABCDX2 =ABCD EB=ABCDE ABF+=ABCDERU={ABCDEG}F={ABCDEGCABECCGBDACD BCEG} CDF+

5.2 ArmstrongFArmstrong F+ F+FArmstrong ArmstrongFF F+FArmstrong

5.3.2F

1. FG5.14 FG: RFG F+=G+ FG FG FG FG GF5.3: G+=F+F G+G F+ 1. F G+, XF+ XG+ +2. XYF + , Y XF+ XG+ + XY(G +)+=G+ F+ G+ 3. G+ F+ , F + = G+

2. F5.15 P186 FF1F2FX A FF{X A }

F3FX A XZ F{X A }{ZA}FFF

SU={ SNoSDeptMNCNameG }F={ SNoSDeptSDept MN(SNo,CName) G }F F = { SNoSDept, SNoMN, SDept MN, (SNoCName) G , (SNoSDept) SDept } 5.15F 1 2F {SNoMN}F 3F {(SNo,SDept) SDept }F2

5.3 FFmFmF1. FFDiXYY=A1A2Ak, k2{XAj|j=1,2,k}2. FXAG=F-{X A}, A XG+, F ( FGAXG+) 3. FXAX= B1B2Bm,Bi(i=1,2,m), A(X-Bi)F+, X-BiX(FF-{X A} {Z A} AZF+, Z=X-Bi)

FFmFDiXA R(U)F, U={A, B, C, D, E, G, }; F={ABC, BCD, ACDB, DEG, CA, BEC, CGBD, CEAG} F 1 F F1={ABC, BCD, ACDB, DE, DG, CA, BEC, CGB, CGD, CEA, CEG}

2 CEA, CA, E ACDB,(CD)+F1=ABCDEGCD B, A F2={ABC, BCD, CDB, DE, DG, CA, BEC, CGB, CGD, CEG} (3) F2 CGB,G= F2- CG B = {ABC, BCD, CDB, DE, DG, CA, BEC, CGD, CEG} (CG)G+=ABCDEG, CGB F F={ABC, BCD, CDB, DE, DG, CA, BEC, CGD, CEG}

5.3.3 1NF 3NF

1. R(U) U={A1, A2, , An}, FR(U) ={R1, R2 , ,Rn }R R1R2Rn=R, U=U1U2Un Ui Uj,1i

2. 3. (1) (2) (3)

1. R(U) FR ={R1, R2, , Rn}R RFr

2. R(A1, A2, , An) R={R1, R2, ,Rn} RF

(1) kn i Ri jAj AjRi ijai bij

(2) F FXY X Y aj, bij aj; bij(bij ) (3) a1, a2, , an a F

R(U) U={A, B, C, D, E} RF={AC, BC, CD, DEC, CEA} ={R1({A, D}), R2({A, B}), R3({B, E}), R4({C, D, E}), R5({A, E})}, (1) 5-1

ABCDER1a1b12b13a4b15R2a1a2b23b24b25R3b31a2b33b34a5R4b41b42a3a4a5R5a1b52b53b54a5

(2) AC R1A=R2A=R5A, b13, b23, b53 b13 5-2

5-2

ABCDER1a1b12b13a4b15R2a1a2b13b24b25R3b31a2b33b34a5R4b41b42a3a4a5R5a1b52b13b54a5

(3) BC R2B=R3B, b13, b33 b13 5-35-3

ABCDER1a1b12b13a4b15R2a1a2b13b24b25R3b31a2b13b34a5R4b41b42a3a4a5R5a1b52b13b54a5

(4) CD R1C=R2C=R3C=R5C, b24, b34, b54 a4 5-4 5-4

ABCDER1a1b12b13a4b15R2a1a2b13a4b25R3b31a2b13a4a5R4b41b42a3a4a5R5a1b52b13a4a5

(5) DEC R3DE=R4DE=R5DE, R3C, R5C, a3 5-5 5-5

ABCDER1a1b12b13a4b15R2a1a2b13a4b25R3b31a2a3a4a5R4b41b42a3a4a5R5a1b52a3a4a5

(6) CEA R3CE=R4CE=R5CE, R3A, R4A, a1 5-6

5-6

ABCDER1a1b12b13a4b15R2a1a2b13a4b25R3a1a2a3a4a5R4a1b42a3a4a5R5a1b52a3a4a5

1. R(U) ={R1, R2, , Rn}R UiRi FiFUi FF1F2Fn,

F+=(Fi)+R ={R1,R2,,Rk}

5.3.4 3NF3NF 3NF BCNF 5.5 R3NF R RFF; R={R1, R2, , Rn} Ri3NF(i=1, 2, , n)

(1) FXA XA=R ={R} (4) (2) RF Rj R (3)FkFiUi k UiUj(ij)Ui2U=Ui i=1={R1,,Rk}R,Ri3NF (4)

R(U) U={C, T, H, R, S, G} RF={CSG, CT, THR, HRC, HSR} 3NF F F={CSG, CT, THR, HRC, HSR}(HS)+={C, T, H, R, S, G} HSR Armstrong HSC CT HST T 3NF 5.5 R1=CSG R2=CT R3=THR R4=HRC R5=HSR {R1, R2, R3, R4, R5}3NF

5.6 3NF RRF R={R1, R2, , Rn} Ri3NFi=1, 2, , n (1)XR R5.5={R1,R2,,Rk} ={R*}. (2)Ui, X Ui, R*. (3)

5.7 RBCNF RF R={R1, R2, , Rn} (1) ={R} (2) BCNF (4) (3) RiBCNF RiXA AXXRi RiRi1=XA Ri2=RiA Ri1Ri2Ri (2); (4)

R(U) U={C, T, H, R, S, G} RF={CSG, CT, THR, HRC, HSR} BCNF R HS RBCNF CSG CS 5.7RR1(U1) (U1={C, S, G}, F1={CSG})R2(U2) (U2={C, T, H, R, S} F2={CT, THR, HRC, HSR}) ={R1, R2}

R1BCNF R2BCNF CT R2HS CR2 5.7 R2R21(U21) (U21={C, T}, F21={CT})R22(U22) (U22={C, H, R, S}, F22={ HRC, HSR, HCR}) , ={R1, R21, R22}

R21BCNF R22BCNF HRC R22 HS HRR22 5.7R22R221(U3) (U3={H, R, C}, F3={ HRC, HCR})R222(U4) (U4={H, R, S} F4={HSR}) ={R1, R21, R221, R222} BCNF

5.4 R(A1,A2,An)FLFRFNFLRF

1RFXX R LXR1RABCDF={DBBDADBACD}RFACL1ACR ACF+={ABCD}ACR1RFXXRLX F+RXR

2RFXXRRX3RFXXRNXR

2RABCDEPF={ADEDDBBCD DCA}RFECL1ECRPNPRCEPF+={ABCDEP}CEPR2RFXXRLNX F+RXR

51 (1) R{A, B, C} F={ABC} (2) R{A, B, C, D} F={BD, ABC} (3) R{A, B, C, D, E, G} F={CG, EA, CED, AB} (4) R{A, B, C} F={BA, CB, AB} (5) R{A, B, C} F={ACB, BC} (6) R{A, B, C, D} F={ABC, CDA, BCD, ADB} (7) R{A, B, C, D, E} F={ACB, BD, DC, DE}

2R{A, B, C}, F={AB, AC, BA, BC, CA, CB, ABC, ACB, BCA} F 3R{A, B, C, D, E} F={ABC, CDE, BD, EA} (1) B+, E+ (2) F (3) R 4S(S, SN, SD, MN, C, GR) RF={SSD SSN, SDMN (S, C)GR } S1(S, SN, SD, MN) SC(S, C, GR) S1S11(S, SN, MN) S12(SD, MN), S S1SC S11 S12

5 R{A, B, C, D, E} F={ AD, ED, BCD, DCA, DB} ={R1(A, B), R2(A, E), R3(C, E), R4(B, C, D), R5(A, C)}6R{C, D, M, N, } F={MC, DCM, NCM, CM} (1) R (2) F (3) R (4) R 3NF7 BCNF3NF

5 1 (1) RABRBCNF (2) RABR1NF DAB (3) RCER1NF G,ACE (4) RCR2NF AC (5) RABACR3NF B (6) RABBCCDADRBCNF (7) RACR2NF DAC

5 CONT. 0121) 2) ABC, AC ACB, AB BCA, BAF1={ AB, AC, BA, BC, CA, CB } 3) AB, BC, AC, ACF2={ AB, BA, BC, CA, CB } BC, CA, BA, BAF3={ AB, BC, CA, CB } CA, AB, CB, CB; Fm={ AB, BC, CA} AC, CB, AB, AB BC, CA, BA, BA Fm={ AC, BC, CA, CB}

5 CONT. 022. 3) CA, AB, CB, CB BA, AC, BC, BC Fm={ AB, AC, BA, CA} BA, AC, BC, BC CB, BA, CA, CA AC, CB, AB, AB Fm={AC, BA , CB}Fm={AB, BA, BC, CB}

5 CONT. 033. 1) BF+ =BD EF+=ABCDE 2) i) F1= {AB, A C, CDE, BD, EA} ii) CDE, E CF+=C E DF+=D iii) ABG={A C, CDE, BD, EA} B AG+=AC ACG={A B, CDE, BD, EA} C AG+=ABD CDEG={A B, AC, BD, EA} E CDG+=CD BDG={A B, CDE, AC, EA} D BG+=B EAG={A B, CDE, BD, AC} A EG+=E Fm= {AB, A C, CDE, BD, EA}

5 CONT. 043. 3) EF+=ABCDE=U CDF+=ABCDE=U AF+=ABCDE=URE, CDA BR3NF4. SS#C#L S#C# F+={S, SN, SD, MN, C, GR}=U S#F+={S, SN, SD, MN}U C#F+={C}U S#C# SSD S#C# S 1NF S1{SSD SSN, SDMN } S#L S#F+={S, SN, SD, MN}=U S#S1SN, SD, MNS#MNS#,S12NF SC{(S#,C#)GR } S#,C#SCSCBCNF S11{SMN SSN} S#S11S11BCNF S12{SDMN} SDS12S12BCNF

5 CONT. 055. 1) 2) A DR1[A]=R2[A]b14, b24 b14

ABCDER1a1a2b13b14b15R2a1b22b23b24a5R3b31b32a3b34a5R4b41a2a3a4b45R5a1b52a3b54b55

ABCDER1a1a2b13b14b15R2a1b22b23b14a5R3b31b32a3b34a5R4b41a2a3a4b45R5a1b52a3b14b55

5 CONT. 063) E DR3[E]=R2[E]b14, b34 b144) BC D DC ABC

ABCDER1a1a2b13b14b15R2a1a2b23b14a5R3a1a2a3b14a5R4b41a2a3a4b45R5a1a2a3b14b55

5 CONT. 075) D BR1[D]=R2[D] =R3[D] a2 ,b22, b32 a2 a

ABCDER1a1a2b13b14b15R2a1a2b23b14a5R3b31a2a3b14a5R4b41a2a3a4b45R5a1b52a3b54b55

5 CONT. 08 6. 1) DNLDNF+={D,C,N,M}=U DF+=DCMUNF+=NCMUDNR 2i) F1= {MC, D C, D M, NC, NM, CM} ii) MCG={D C, DM, NC, NM, C M} C MG+=M DMG={M C ,D C, NC, NM, CM} M DG+=DCMF2= {MC, D C, NC, NM, CM}; DCG={M C, NC, NM, CM} C DG+=D NCG={MC, D C, NM, CM} C NG+=NMC F2= {MC, D C, NM, CM} NM, CM Fm= {MC, D C, NM, CM}

5 CONT. 09 6. 3) DNRD MR 1NF 4i) Fm R1 R2 R3 R4 R4R1R4R* ={R1,R2,R3,R*}

5 CONT. 10 7. () R BCNF,R3NFRXYZZYXYYX YZ R BCNFYZZYYYXR3NF

Thank You

F={ABBABCACCA}Fm1={ABBCCA}Fm2= {ABBAACCA}FFm1 Fm2