數(shù)據(jù)庫模式設(shè)計及其規(guī)范化

1、n矩形表示矩形表示強(qiáng)強(qiáng)實體集實體集n菱形表示聯(lián)系集菱形表示聯(lián)系集n直線將屬性和實體集直線將屬性和實體集/聯(lián)系集、實體集和聯(lián)系集聯(lián)系在一起聯(lián)系集、實體集和聯(lián)系集聯(lián)系在一起n橢圓表示屬性，三種特殊屬性：橢圓表示屬性，三種特殊屬性：l雙橢圓表示多值屬性雙橢圓表示多值屬性l虛線橢圓表示派生屬性虛線橢圓表示派生屬性l復(fù)合屬性：屬性上的屬性：姓名復(fù)合屬性：屬性上的屬性：姓名=姓姓+名；地址名；地址=區(qū)域區(qū)域+街道街道+號碼號碼n下劃線表示主碼屬性下劃線表示主碼屬性n在實體集和聯(lián)系集之間，用箭頭或線段表示基數(shù)約束：n有向直線()指向“一”方，n無向直線()表示“多”。n完全參與完全參與 (用雙線表達(dá)用雙線表

2、達(dá))：實體集中的每個實體參與聯(lián)系集中的至少一：實體集中的每個實體參與聯(lián)系集中的至少一個聯(lián)系個聯(lián)系l如，如，borrower中的中的 loan 是完全參與是完全參與4每項每項 loan 通過通過borrower必須有一個客戶與之相關(guān)聯(lián)必須有一個客戶與之相關(guān)聯(lián)n1:n,1:1聯(lián)系集對應(yīng)的關(guān)系模式可與實體集對應(yīng)的關(guān)系模式合并。n多對一：多對一：如果如果“多多”端是部分參與，會引起空值端是部分參與，會引起空值nIn order to comply with the relational model it is necessary to n1) remove repeating groups n2) a

3、void redundancy and data anomalies by removing： npartial functional dependencies.ntransitive functional dependencies.n多值依賴，投影依賴等nRelational Database Design: All attributes in a table must be atomic, and solely dependant upon the fully primary key of that table.nTHE KEY, THE WHOLE KEY, AND NOTHING BU

4、T THE KEY!（碼，完整的碼）這是規(guī)范化的標(biāo)準(zhǔn)碼，完整的碼）這是規(guī)范化的標(biāo)準(zhǔn)O R D E RC u sto m er N o :0 0 1 9 6 4O rd er N u m b er:0 0 0 1 2 3 4 5N am e:M ark C am p b ellO rd er D ate:1 4 -F eb -2 0 0 2A d d ress:1 T h e H o u seL ey to n sto n eE 1 1 9 Z ZP ro d u ctP ro d u ctU n it O rd erL in eN u m b erD escrip tio nP riceQ u

5、 an tityT o talT 5 0 6 0H o o k5 .0 052 5 .0 0P T 4 2B o lt2 .5 01 02 0 .5 0Q Z E 4 8S p an n er2 0 .0 012 0 .0 0O rd er T o tal:6 5 .5 0nRemove repeating groups into a new relationnA repeating group is shown by a pair of brackets within the relational schema.nORDER (order-no, order-date, cust-no, c

6、ust-name, cust-add,(prod-no, prod-desc, unit-price, ord-qty, line-total)*, order-total)Example: We have the following relation that contains staff and department details and a list of telephone contact numbers for each member of staff.staffNo job dept dname city contact numberSL10 Salesman 10 Sales

7、Stratford 018111777, 018111888, 079311122 SA51 Manager 20 Accounts Barking 017111777DS40 Clerk 20 Accounts BarkingOS45 Clerk 30 Operations Barking 079311555Repeating Groups are not allowed in a relational design, since all attributes have to be atomic - i.e., there can only be one value per cell in

8、a table!staffNo job dept dname SL10 Salesman 10 SalesSA51 Manager 20 AccountsDS40 Clerk 20 AccountsOS45 Clerk 30 OperationsExample:staffNo jobstaffNo deptstaffNo dnamedept dname Functional Dependenciesorder# line# qty price A001 001 10 200A002 001 20 400A002 002 20 800A004 001 15 300Example:(Order#,

9、 line#) qty(Order#, line#) price Full Functional Dependenciesstudent# unit# room grade9900100 A01 TH224 29900010 A01 TH224 149901011 A02 JS075 39900001 A01 TH224 16Example:(student#, unit#) grade Full Functional Dependenciesunit# room Partial Functional DependenciesRepetition of data!staffNo job dept dname SL10 Salesman 10 SalesSA51 Manager 20 AccountsDS40 Clerk 20 AccountsOS45 Clerk 30 Operation