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

数据库应用基础 PowerPoint PPT Presentation


  • 88 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


6048501

NDAT

(NDAT)


6048501

4


6048501


6048501

4.1

4.1.1

  • E.F.Codd


6048501


6048501

    • Normal Form

  • 4.1.2

  • :


6048501

SCD

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

  • SNOSNAGEDEPTMNCNOSCORE

    • 1.

    • 2.

    • 3.

    • 4.

  • SCD4.1


  • 6048501

    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


    6048501

    • (SNO,CNO)(SNO,CNO)

      • 1.

      • 2.

        • (SNO,CNO)SNOCNO

        • CNO


    6048501

    • 3.

    • C1C1C1

    • 4.

    • SN

    • MN


    6048501

    • SCD

    • SCD4.2

      • S(SNO,SN,AGE,DEPT)

      • SC(SNO,CNO,SCORE)

      • D(DEPT,MN)


    6048501

    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


    6048501

      • S

      • D

      • SC

    • SCD

      • D

      • S

      • SD


    6048501

      • 1.

      • 2.

      • 3.

      • 4.


    6048501


    6048501

    4.2

    4.2.1

    • ,

    • Functional Dependency

      • SCDSNOSNAGEDEPT

      • SNOSNOSNAGEDEPT

      • Y=F(X)XY

      • SNOSNAGEDEPTSNAGEDEPTSNO


    6048501

    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


    6048501

    1

    • YXXY,

    • YXXY

    • 2

    • S

      • SNAGE

      • SNDEPT


    6048501

    3

    • 1XY1:1XYYXX Y

      • SNO SN

  • 2XY1:mXY

  • SNOAGEDEPT1:mSNOAGESNODEPT

  • 3XYm: nXY

  • SNOCNO


  • 6048501

    4

    • SSNOSNSNSNOSNSNO


    6048501

    5

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

    • R[XY]RXYRXR

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


    6048501

    4.2.1.2

    1

    • SCDSNOCNOSNOSNOCNOCNO

      2

    • XYWZXWYZ

    • SNOSNAGEDEPTMNSNODEPTSNAGEMN

      3

    • XYXZXYZ

    • SCDSNOSN,AGESNODEPT,MNSNOSN,AGEDEPTMN

      4

    • XYZ,XYXZ

    • XA1A2,AnXAii=1,2,,n


  • 6048501

    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


  • 6048501

    4.2.3

    4.3RUUXYZU

    • XYY XYZY XZ YZXTransitive Functional DependencyXZ

    • YXX YZX

    • SCDSNODEPTNDEPTN SNODEPTNMNSNO MNSNOSNSNSNOSNO SNSNDEPTNDEPTNSNO


    6048501

    4.3

    • Normal Form


    6048501

    • E.F.Codd

    • 1971Codd1NF2NF3NF

    • 1974CoddBoyceBoyce-CoddBC

    • 1976Fagin

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

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

      • 4.3


    6048501

    1NF

    2NF

    3NF

    BCNF

    4NF

    5NF

    4.3


    6048501

    4.3.1

    • First Normal Form

      4.4RR1NFR1NF

    • 2

    • 1NF


    6048501

    • 4.1SCD

    • SCDSNOCNO

      • SNOCNO SCORE

      • SNOSNSNOCNO SN

      • SNOAGESNOCNO AGE

      • SNODEPTSNOCNO DEPT

      • SNO MNSNOCNO MN


    6048501

    P

    f

    P

    SDN

    SNO

    SNO

    CNO

    MN

    4.4 SCD

    4.4

    • SCD


    6048501

    4.3.2

    4.3.2.1

    4.5R1NFRRSecond Normal Form2NFR2NF

    • SCDSNOCNOAGEDEPTMNMNSCORESCD2NF

    • 4.2SCDSDSCSSNODDEPT

    • SCSNOCNO SCORESCDSDSC2NF


    6048501

    • 2.4.2TCSTCS

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

      • 11NF2NF

      • 2RRR2NF


    6048501

    4.3.2.2 2NF

    • 2NF1NF2NF

    • SCD2NF

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

      • SNOSNSNOAGESNODEPTSNOCNO SCORESCD

      • SNOSNAGEDEPTMN

      • SNOCNOSCORE

      • SCD4.5


    6048501

    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


    6048501

    SN

    SNO

    SCORE

    AGE

    SNO

    CNO

    DEPT

    MN

    • SDSCSNOSNOCNOSD2NFSC2NFSCD

    • SDSC4.64.7

    4.6 SD 4.7 SC


    6048501

    • 1NF2NF

    • 4.5SDSCSCD

    • C1SCC1SD

    • SDSCSCD


    6048501

    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


    6048501

    4.3.2.3 2NF

    • 2NF1NF2NF1NF2NF

      • 1

      • 2

      • 3

      • 4

    • SCD

    • SCDSNOSNSNOAGESNODEPTDEPTMNSNO MNMNSNO

    • SCD3NF


    6048501

    4.3.3

    4.3.3.1

    4.6R2NFRRThird Normal Form3NFR3NF

    • 1R3NFR2NF

      • 3NFRXYY XYZXYZZ YY XYXXYYZXX Z3NFRXR2NF


    6048501

    2R2NFR3NF

    • SCDSDSC2NFSC3NFSDMNSNOSD3NFSD3NF

      4.3.3.2 3NF

  • 3NF2NF3NF

  • 2NF

  • 2NFSD3NF


  • 6048501

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

    • SDSD

    • SNOSNAGEDEPT

    • DEPTMN

    • SD4.8

    • S(SNO,SN,AGE,DEPT)

    • D(DEPTMN)


    6048501

    SNO

    SN

    AGE

    DEPT

    DEPT

    MN

    S1

    17

    S2

    18

    S3

    20

    S4

    21

    S D

    4.8 SD

    • SDSNODEPTS3NFD3NF


    6048501

    SN

    DEPT

    MN

    SNO

    AGE

    DEPT

    4.9 S

    4.10 D

    • SD4.94.10

    SD2NF3NF2NF


    6048501

    1D

    2DS

    3SD

    4DMN

    • SCD3NF

    • 3NF

    • 3NFBoyceCoddBoyce-CoddBCNFBC3NF


    6048501

    4.3.4 BC

    4.3.4.1 BC

    4.7R1NFXYY X,XRRBCBoyce-Codd Normal FormRBCNF

    BCNF

    • 1BCNFRBCNFR3NF

    • R3NFXYY XYZXYZZ YYZYBCNFRBCNFR3NF


    6048501

    2R3NFRBCNF

    • SNCSNOSNCN0SCORESNOSNCNOSCORESNCSNOCNOSNCNO

      • SNO SN

      • SNOCNOSCORE

      • SNCNOSCORE

    • SCORESNC3NF

    • SNO SNSNOSN SNOCNO SNSNCNO SNOSNCBCNF

    • SNC

    • SNCSNCBCNF


    6048501

    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


    6048501

    SNO

    SCORE

    SN

    SNO

    CNO

    S1S24.114.12

    4.11 S1 4.12 S2

    • SNCBCNF

    • S1SN


    6048501

    S

    S

    T

    C

    C

    T

    4.4TCSTCSTCS

    • TCS

      • SCTSTCTC

    • 4.13

    4.13 TCS


    6048501

    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


    6048501

    1

    2,

    3,

    4


    6048501

    T

    C

    S

    T

    • STCBCNF

    • TCSSTSTTCTCSTCSTSTCTSTBCNFTCBCNF4.154.16

    4.15 ST 4.16 TC


    6048501

    • TCSBCNF

      • 1TC

      • 2TCST

      • 3STTC

      • 4TC

    • 3NF

    • BCNF


    6048501

    4.4

    • 4NF5NF

    • Normalization

      4.4.1


    6048501

    • 4.4.2

      • 11NF1NF2NF

      • 22NF2NF3NF

      • 33NFBCNF


    6048501

    1NF

    2NF

    3NF

    BCNF

    4.17

    4.17

    • 3NFBCNF3NF


    6048501

    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


    6048501

      • 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


    6048501

    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


    6048501

    • SNO3NF

    • SD=S1*D1SDS1D1SNO

      • 1

      • 2

      • 3

      • 4


    6048501

    • SDDEPTMNS1D1SNO MN


    6048501

    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


    6048501

    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


    6048501

    • S2*D2SDS117S421SD

    • SDDEPTMNSNODEPTSNO MN


    6048501

    • 3NFBCNF

    • 3NF


    6048501

    • 1NF

    • 1NF2NF2NF3NF3NFBCNF

    • 3NF


    6048501

    NDAT

    the End.

    DB


  • Login