NDAT
This presentation is the property of its rightful owner.
Sponsored Links
1 / 67

数据库应用基础 PowerPoint PPT Presentation


  • 92 Views
  • Uploaded on
  • Presentation posted in: General

NDAT. “ 数据库应用技术 (NDAT) ” 资格认证培训项目. 数据库应用基础. 信息产业部电子行业职业技能鉴定指导中心 网络与信息培训办公室. 第 4 讲 关系数据库理论. 本讲概要. 前面已经讲述了 关系数据库 、 关系模型 的基本概念以及关系数据库的 标准语言 。 如何使用关系模型设计关系数据库,也就是面对一个现实问题,如何选择一个比较好的关系模式的集合,每个关系又应该由哪些属性组成。这属于数据库设计的问题,确切地讲是数据库 逻辑设计 的问题,有关数据库设计的全过程可以参考有关章节及文献。 本讲讲述 关系数据库规范化理论 ,这是数据库逻辑设计的理论依据。

Download Presentation

数据库应用基础

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


NDAT

(NDAT)


4



4.1

4.1.1

  • E.F.Codd



    • Normal Form

  • 4.1.2

  • :


SCD

  • SCD(SNO,SN,AGE,DEPT,MN,CNO,SCORE)

  • SNOSNAGEDEPTMNCNOSCORE

    • 1.

    • 2.

    • 3.

    • 4.

  • SCD4.1


  • SNO

    SN

    AGE

    DEPT

    MN

    CNO

    SCORE

    S1

    17

    C1

    90

    S1

    17

    C2

    85

    S2

    18

    C5

    57

    S2

    18

    C6

    80

    S2

    18

    C7

    70

    S2

    18

    C5

    70

    S3

    20

    C1

    0

    S3

    20

    C2

    70

    S3

    20

    C4

    85

    S4

    C1

    93

    4.1 SCD


    • (SNO,CNO)(SNO,CNO)

      • 1.

      • 2.

        • (SNO,CNO)SNOCNO

        • CNO


    • 3.

    • C1C1C1

    • 4.

    • SN

    • MN


    • SCD

    • SCD4.2

      • S(SNO,SN,AGE,DEPT)

      • SC(SNO,CNO,SCORE)

      • D(DEPT,MN)


    SNO

    SN

    AGE

    DEPT

    SNO

    CNO

    SCORE

    S1

    17

    S1

    C1

    90

    S2

    18

    S1

    C2

    85

    S3

    20

    S2

    C5

    57

    S4

    21

    S2

    C6

    80

    S2

    C7

    D

    S2

    C5

    70

    DEPT

    MN

    S3

    C1

    0

    S3

    C2

    70

    S3

    C4

    85

    S4

    C1

    93

    S SC

    4.2


      • S

      • D

      • SC

    • SCD

      • D

      • S

      • SD


      • 1.

      • 2.

      • 3.

      • 4.



    4.2

    4.2.1

    • ,

    • Functional Dependency

      • SCDSNOSNAGEDEPT

      • SNOSNOSNAGEDEPT

      • Y=F(X)XY

      • SNOSNAGEDEPTSNAGEDEPTSNO


    4.2.1.1

    4.1R(UF)UFUXYUR(U)rXYXYYXXYXYYXX YXYYX X Y

    • SCD

      • U={SNO,SN,AGE,DEPT,MN,CNO,SCORE}

      • F={SNOSNSNOAGESNODEPT}

    • SNOSCORESCORESCORESNO SNO SCORE

    • SCORESNOCNOSNOCNOSCORE


    1

    • YXXY,

    • YXXY

    • 2

    • S

      • SNAGE

      • SNDEPT


    3

    • 1XY1:1XYYXX Y

      • SNO SN

  • 2XY1:mXY

  • SNOAGEDEPT1:mSNOAGESNODEPT

  • 3XYm: nXY

  • SNOCNO


  • 4

    • SSNOSNSNSNOSNSNO


    5

    • RXYZXYZXYXZ,R(XYZ)=R[XY]*R[XZ]

    • R[XY]RXYRXR

    • SCDSNOSNAGEDEPTMNSCDSNOSNAGEDEPTMNCNOSCORE=SCD[SNOSNAGEDEPTMN]*SCD[SNOCNOSCORE]SNOSCD


    4.2.1.2

    1

    • SCDSNOCNOSNOSNOCNOCNO

      2

    • XYWZXWYZ

    • SNOSNAGEDEPTMNSNODEPTSNAGEMN

      3

    • XYXZXYZ

    • SCDSNOSN,AGESNODEPT,MNSNOSN,AGEDEPTMN

      4

    • XYZ,XYXZ

    • XA1A2,AnXAii=1,2,,n


  • 4.2.2

    4.2R(U)UXYU

    • XYXX,X YYXFull Functional Dependency X Y

    • XXXYYPartial Functional DependencyX Y

    • SCDSNO SCORECNO SCORESNOCNO SCORE

    • SNOAGESNOCNO AGE

  • 4.2

    • SSNOSNAGEDEPTSNOSNOSNAGEDEPT


  • 4.2.3

    4.3RUUXYZU

    • XYY XYZY XZ YZXTransitive Functional DependencyXZ

    • YXX YZX

    • SCDSNODEPTNDEPTN SNODEPTNMNSNO MNSNOSNSNSNOSNO SNSNDEPTNDEPTNSNO


    4.3

    • Normal Form


    • E.F.Codd

    • 1971Codd1NF2NF3NF

    • 1974CoddBoyceBoyce-CoddBC

    • 1976Fagin

    • 1NF,2NF,3NF,BCNF,4NF,5NF,

      • 5NF 4NF BCNF 3NF 2NF 1NF

      • 4.3


    1NF

    2NF

    3NF

    BCNF

    4NF

    5NF

    4.3


    4.3.1

    • First Normal Form

      4.4RR1NFR1NF

    • 2

    • 1NF


    • 4.1SCD

    • SCDSNOCNO

      • SNOCNO SCORE

      • SNOSNSNOCNO SN

      • SNOAGESNOCNO AGE

      • SNODEPTSNOCNO DEPT

      • SNO MNSNOCNO MN


    P

    f

    P

    SDN

    SNO

    SNO

    CNO

    MN

    4.4 SCD

    4.4

    • SCD


    4.3.2

    4.3.2.1

    4.5R1NFRRSecond Normal Form2NFR2NF

    • SCDSNOCNOAGEDEPTMNMNSCORESCD2NF

    • 4.2SCDSDSCSSNODDEPT

    • SCSNOCNO SCORESCDSDSC2NF


    • 2.4.2TCSTCS

      • (T,C,S)T,C,STCS2NF

      • 11NF2NF

      • 2RRR2NF


    4.3.2.2 2NF

    • 2NF1NF2NF

    • SCD2NF

      4.1 SCD(SNO,SN,AGE,DEPT,MN,CNO,SCORE)2NF

      • SNOSNSNOAGESNODEPTSNOCNO SCORESCD

      • SNOSNAGEDEPTMN

      • SNOCNOSCORE

      • SCD4.5


    SNO

    SNO

    CNO

    SN

    SCORE

    AGE

    DEPT

    MN

    S1

    S1

    C1

    17

    90

    S1

    S2

    C2

    85

    18

    S3

    S2

    C5

    20

    57

    S2

    S4

    C6

    80

    21

    S2

    C7

    S2

    C5

    70

    S3

    C1

    0

    S3

    C2

    70

    S3

    C4

    85

    S4

    C1

    93

    • SD(SNO,SN,AGE,DEPTMN)

    • SC(SNO,CNO,SCORE)

      SD

    SC

    4.5 SDSC


    SN

    SNO

    SCORE

    AGE

    SNO

    CNO

    DEPT

    MN

    • SDSCSNOSNOCNOSD2NFSC2NFSCD

    • SDSC4.64.7

    4.6 SD 4.7 SC


    • 1NF2NF

    • 4.5SDSCSCD

    • C1SCC1SD

    • SDSCSCD


    2NF

    • RXYZR1NFR2NFXYZXYXX1X2X1 YRXYZR[X1Y]R[XZ]

    • X1YR(XYZ)=R[X1Y]*R[X1X2Z]=R[X1Y]*R[XZ]RR[X1Y][XZ]X1R

    • X1 YR[X1Y]2NFR[XZ]2NFR[XZ]2NF


    4.3.2.3 2NF

    • 2NF1NF2NF1NF2NF

      • 1

      • 2

      • 3

      • 4

    • SCD

    • SCDSNOSNSNOAGESNODEPTDEPTMNSNO MNMNSNO

    • SCD3NF


    4.3.3

    4.3.3.1

    4.6R2NFRRThird Normal Form3NFR3NF

    • 1R3NFR2NF

      • 3NFRXYY XYZXYZZ YY XYXXYYZXX Z3NFRXR2NF


    2R2NFR3NF

    • SCDSDSC2NFSC3NFSDMNSNOSD3NFSD3NF

      4.3.3.2 3NF

  • 3NF2NF3NF

  • 2NF

  • 2NFSD3NF


  • 4.2SD(SNO,SN,AGE,DEPT,MN)3NF

    • SDSD

    • SNOSNAGEDEPT

    • DEPTMN

    • SD4.8

    • S(SNO,SN,AGE,DEPT)

    • D(DEPTMN)


    SNO

    SN

    AGE

    DEPT

    DEPT

    MN

    S1

    17

    S2

    18

    S3

    20

    S4

    21

    S D

    4.8 SD

    • SDSNODEPTS3NFD3NF


    SN

    DEPT

    MN

    SNO

    AGE

    DEPT

    4.9 S

    4.10 D

    • SD4.94.10

    SD2NF3NF2NF


    1D

    2DS

    3SD

    4DMN

    • SCD3NF

    • 3NF

    • 3NFBoyceCoddBoyce-CoddBCNFBC3NF


    4.3.4 BC

    4.3.4.1 BC

    4.7R1NFXYY X,XRRBCBoyce-Codd Normal FormRBCNF

    BCNF

    • 1BCNFRBCNFR3NF

    • R3NFXYY XYZXYZZ YYZYBCNFRBCNFR3NF


    2R3NFRBCNF

    • SNCSNOSNCN0SCORESNOSNCNOSCORESNCSNOCNOSNCNO

      • SNO SN

      • SNOCNOSCORE

      • SNCNOSCORE

    • SCORESNC3NF

    • SNO SNSNOSN SNOCNO SNSNCNO SNOSNCBCNF

    • SNC

    • SNCSNCBCNF


    4.3.4.2 BCNF

    • BCNF3NFBCNF

      3NFSNCBCNF

      4.3SNC(SNO,SN,CNOSCORE)BCNF

    • SNCSNOSNSNOCNOSCORE

    • SNC

      • S1(SNO,SN)

      • S2(SNO,CNO,SCORE)

    • S1SNOSN

    • S2SNOCNO

    • S1BCNFS2BCNF


    SNO

    SCORE

    SN

    SNO

    CNO

    S1S24.114.12

    4.11 S1 4.12 S2

    • SNCBCNF

    • S1SN


    S

    S

    T

    C

    C

    T

    4.4TCSTCSTCS

    • TCS

      • SCTSTCTC

    • 4.13

    4.13 TCS


    T

    C

    S

    T1

    C1

    S1

    T1

    C1

    S2

    T2

    C1

    S3

    T2

    C1

    S4

    T3

    C2

    S2

    T4

    C2

    S2

    T4

    C3

    S2

    • TCSSCSTSTCSTCS3NF

    • TCS4.14

    4.14 TCS


    1

    2,

    3,

    4


    T

    C

    S

    T

    • STCBCNF

    • TCSSTSTTCTCSTCSTSTCTSTBCNFTCBCNF4.154.16

    4.15 ST 4.16 TC


    • TCSBCNF

      • 1TC

      • 2TCST

      • 3STTC

      • 4TC

    • 3NF

    • BCNF


    4.4

    • 4NF5NF

    • Normalization

      4.4.1


    • 4.4.2

      • 11NF1NF2NF

      • 22NF2NF3NF

      • 33NFBCNF


    1NF

    2NF

    3NF

    BCNF

    4.17

    4.17

    • 3NFBCNF3NF


    4.4.2

    • Lossless JoinR(UF)R1(U1F1)R2(U2F2), Rn(UnFn)U=U1U2UNUNUjFiFUjRR1R2RnR

    • Preserve DependencyR(UF)R1(U1F1)R2(U2F2), Rn(UnFn)U=U1U2UNUNUjFiFUjFFiR


      • 1

      • 2

      • 3

        4.3.2.24.2SD(SNO,SN,AGE,DEPT,MN)3NF

      • S(SNO,SN,AGE,DEPT)

      • D(DEPTMN)

      • SDSNOSNAGEDEPTMN=S[SNOSNAGEDEPT]*D[DEPTMN]

      • DEPTSD

    • SSNODEPTDDEPTMNSDSNODEPTDEPTMN


    SNO

    SN

    AGE

    DEPT

    SNO

    MN

    S1

    17

    S1

    S2

    18

    S2

    S3

    20

    S3

    S4

    21

    S4

      • S1(SNO,SN,AGE,DEPT)

      • D1(SNOMN)

    • 4.18

      S1 D1

    4.18 S1D1


    • SNO3NF

    • SD=S1*D1SDS1D1SNO

      • 1

      • 2

      • 3

      • 4


    • SDDEPTMNS1D1SNO MN


    SNO

    SN

    AGE

    MN

    DEPT

    MN

    S1

    17

    S2

    18

    S3

    20

    S4

    21

      • S2(SNO,SN,AGE,MN)

      • D2(DEPTMN)

    • 4.19

      S2 D2

    4.19 S2D2


    SNO

    SN

    AGE

    DEPT

    MN

    S1

    17

    S1

    17

    S2

    18

    S3

    20

    S4

    21

    S4

    21

    • 3NFMNMN SNOMN DEPTS2*D2SD

    • S2D2MN4.20

    4.20 S2D2


    • S2*D2SDS117S421SD

    • SDDEPTMNSNODEPTSNO MN


    • 3NFBCNF

    • 3NF


    • 1NF

    • 1NF2NF2NF3NF3NFBCNF

    • 3NF


    NDAT

    the End.

    DB


  • Login