资料库系统理论与实务.ppt

《资料库系统理论与实务.ppt》由会员分享，可在线阅读，更多相关《资料库系统理论与实务.ppt（136页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章第三章 關聯式模型關聯式模型資料庫系統理論與實務邏輯思維系列邏輯思維系列資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型本章在架構中的位置2資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型本章內容 3-13-1關聯式資料模型的發展關聯式資料模型的發展關聯式資料模型的發展關聯式資料模型的發展3-2關聯式資料庫簡介關聯式資料庫簡介3-3關聯式模型的基本介紹關聯式模型的基本介紹3-4關聯式模型的完整性限制關聯式模型的完整性限制3-5關聯式模型的轉換與設計關聯式模型的轉換與設計3-6不當設計所引發的

2、異動操作異常問題不當設計所引發的異動操作異常問題3-7鍵值屬性的介紹鍵值屬性的介紹3-8功能相依性功能相依性3-9正規化正規化3-10實體關聯圖的實例探討與設計實體關聯圖的實例探討與設計3-11案例資料之參考案例資料之參考3資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型3-1關聯式資料模型的發展1970年代由IBM公司的研究人員Codd博士首先發展關聯式資料模型，並首度發表“ARelationalModelofDataforLargeSharedDataBanks”之論文Codd博士並於1985年對外發佈，關聯式資料庫管理系統的十二項規則建議，說明如下：4資料庫系統理論

3、與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則1：資訊規則TheInformationRule在資料庫內的所有資訊都必須以資料表(Table)的形式存在，並透過資料表的常式(Routines)或應用軟體來存取資訊5資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型(a)訂單基本資料(b)訂單明細資料圖3-1錯誤操作所造成的資料不一致性對應不到，而產生錯誤6資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型常式(Routines)或應用軟體(Application)資料表(Tables)一致性的控制一致性的控制資料(c)透過常式或應用軟體的存

4、取圖3-1錯誤操作所造成的資料不一致性7資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則2：必要的存取規則GuaranteedAccessRule關聯式資料庫内的任何資料項目，都要透過資料表名稱(TableName)、主要鍵值(PrimaryKeyValue)與行名稱(ColumnName)來進行存取的動作，不可直接將資料表的橫向記錄(由數個欄位所組成)當成一長字串，來取得其中的某些字串，例如從第20個字取至第40個字元。透過系統目錄(SystemCatalog)的描述和對應，讓應用程式的開發人員能針對資料表名稱、主要鍵值與行名稱，不致於在資料結構改變之後，造成所有相

5、關程式都必須要異動的麻煩8資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則3：具有空值的處理能力SystematicTreatmentofNullValues空值(NullValue)通常發生於某屬性的屬性值不適用(NotApplicable)或未知(Unknown)情況下所使用空值(NullValue)不同於一般的空字串(EmptyCharacterString)、空白字元、零或任何數值9資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則4：關聯性模型的動態線上目錄DynamicOn-LineCatalogBasedonTheRelation

6、alModel線上目錄(On-LineCatalog)亦就是資料庫描述(DatabaseDescription)或稱資料字典(DataDictionary)，所儲存的是所有資料庫中的物件定義或描述資料，用以每一位資料庫系統使用者登入此系統之後皆能以相同的資料定義或描述來進行對資料的不同操作10資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則5：不同的通用性資料語言規則ComprehensiveDataSub-LanguageRule在關聯性模型系統中，應該提供不同的語言，包括不同的語言資料定義(DataDefinition)視界定義(ViewDefinition)資料

7、操作(DataManipulation)完整性的限制(IntegrityConstraints)授權(Authorization)11資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則6：視界的更新規則ViewUpdatingRule由於視界(View)的資料是來自於資料表(Table)，視界本身並不儲存資料，且有可能一個視界是合併了數個資料表的資料。所以，若是要透過視界來異動底層的資料，必須要針對所有的視界(View)訂定更改的規範，以保護資料異動前、後的一致性12資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則7：高階的新增、更新及刪除Hi

8、gh-LevelInsert,UpdateandDelete除了對資料查詢可以一次查詢多筆資料(set-at-a-time)之外，對於新增、更新及刪除的操作功能皆有此特性，而非一次一筆資料的處理(record-at-a-time)13資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則8：實體資料的獨立性PhysicalDataIndependence針對使用者所使用的應用程式而言，如果資料所儲存的實體結構改變，不應該影響到使用者的應用程式14資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則9：邏輯資料的獨立性LogicalDataIndepen

9、dence針對使用者所使用的應用程式而言，如果資料的邏輯層改變，不應該影響到使用者的應用程式例如對關聯式資料庫內的資料表(Table)新增一行(Column)，並不會直接影響到與此新增的行(Column)無關的應用程式之執行15資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則10：完整性的獨立IntegrityIndependence在關聯式資料庫管理系統中，必須能定訂完整性的限制，並且儲存於線上目錄(On-LineCatalog)內，與應用程式完全獨立，並且在不同的應用程式使用此關聯式管理系統時，能同時從此目錄(Catalog)中取得此完整性限制16資料庫系統理論與

10、實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則11：分散式的獨立性DistributionIndependence關聯式資料庫管理系統可以是集中於同一部伺服器，或是分散於不同的伺服器，不論資料實體位於何處，都不會影響使用者的高階程式語言的邏輯17資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型規則12：沒有破壞性的規則Non-SubversionRule縱使關聯式資料庫管理系統允許低階程式語言的存取，但也不能因此而破壞資料完整性(Integrity)的限制18資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型本章內容3-1關聯式資料模型的發

11、展 3-23-2關聯式資料庫簡介關聯式資料庫簡介關聯式資料庫簡介關聯式資料庫簡介3-3關聯式模型的基本介紹關聯式模型的基本介紹3-4關聯式模型的完整性限制關聯式模型的完整性限制3-5關聯式模型的轉換與設計關聯式模型的轉換與設計3-6不當設計所引發的異動操作異常問題不當設計所引發的異動操作異常問題3-7鍵值屬性的介紹鍵值屬性的介紹3-8功能相依性功能相依性3-9正規化正規化3-10實體關聯圖的實例探討與設計實體關聯圖的實例探討與設計3-11案例資料之參考案例資料之參考19資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型3-2關聯式資料庫簡介使用關聯式模型為基礎觀念所建立的資

12、料庫管理系統(DatabaseManagementSystem，簡稱為DBMS)，稱之為關聯式資料庫管理系統(RelationalDatabaseManagementSystem，簡稱為RDBMS)現在廣為市場使用的資料庫管理系統，大部份皆為關聯式資料庫管理系統；可以區分為商業軟體開放原始碼(OpenSource)20資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型商業軟體(1/6)-OracleOracle是由甲骨文(Oracle)公司所開發的一套大型資料庫管理系統使用的結構化查詢語言是相容於ANSI-SQL99所發展出來的PL/SQL，此套軟體在市場佔有率相當之高，並

13、開放其軟體於網站供個人免費下載試用目前最新版本為Oracle10g，並使用分散式計算之技術GridComputing，提升其運算的能力詳細資料可參考網站http:/21資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型商業軟體(2/6)-SybaseASESybaseASE是由賽貝斯(Sybase)公司所發展的一套大型的資料庫管理系統，其原名為SybaseSQLServer，後改名為SybaseAdaptiveServerEnterprise(簡稱為ASE)目前最新版本為為AdaptiveServerEnterprisev15.0詳細資料可參考網站http:/22資料庫系統

14、理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型商業軟體(3/6)-DB2UDBDB2UniversalDatabase(簡稱DB2UDB)是由IBM公司所開發的一套大型資料庫管理系統，也是IBM在資料庫市場上的一套主要產品目前最新版本為DB2UniversalDatabasev8.2詳細資料可參考網站23資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型商業軟體(4/6)-InformixInformix原由Informix公司所開發，後被IBM公司所併購，成為IBM的第二套主要資料庫管理系統詳細資料可參考網站http:/24資料庫系統理論與實務陳祥輝著200

15、7版權所有，翻印必究第三章關聯式模型商業軟體(5/6)MSSQLServer此套資料庫管理系統是微軟公司(Microsoft)所發展的商用軟體，其軟體的前身是源自於Sybase的資料庫管理系統使用的結構化查詢語言也是相容於ANSI-SQL99的標準發展出的Transact-SQL(或簡稱T-SQL)目前最新的版本為MSSQLServer2005，主要標榜的特色在於商業智慧(BusinessIntelligence，簡稱BI)詳細資料可參考網站25資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型商業軟體(6/6)-MSAccessMSAccessMSAccess資料庫是微軟

16、公司最早開發出來的小型資料庫管理系統此套軟體主要是適合於個人使用，目的在於推動辦公室自動化的過程爭，讓一般使用者能擁有自己的一套小型資料庫系統使用，所以微軟公司將此產品合併在MicrosoftOffice系列中，所以此套資料庫軟體較適合於辦公室個人化的資料管理，而較不適合於多人多工處理的環境詳細資料可參考網站26資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型開放原始碼(1/4)-MySQLMySQLMySQL資料庫是MySQLAB公司所開發，主要可分為兩種版本一種為商業用版本，必須付費購買的MySQLEnterpriseEdition一種為開放原始碼的MySQLComm

17、unityEdition。MySQL是一種服務(service)型的資料庫管理系統啟動服務後，預設值會於TCP/IPPort3306傾聽參考網址：提供完整的客戶端連線和管理工具軟體，表列如表3-127資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型表3-1MySQL的相關軟體軟體類型名稱軟體說明後端資料庫伺服器MySQLServer此為MySQL伺服端的主要元件前端工具軟體MySQLMigrationToolkit此軟體提供將傳統的資料庫系統內的資料做轉移的功能MySQLWorkbench此軟體為一套前端資料庫設計的工具軟體MySQLAdministrator此軟體主要安

18、裝於前端電腦中，提供遠端連線管理資料庫伺服器的工具MySQLQueryBrowser此軟體提供圖形化界面，供使用者下查詢(Query)命令使用驅動程式(Drivers)與連接器(Connectors)MySQLConnector/J提供Java連線MySQLMySQLConnector/Net提供.NET連線MySQLConnector/ODBC提供MySQL的ODBC驅動程式MySQLConnector/MXJ提供Java在嵌入式MySQL伺服器連線使用MySQLConnector/PHP提供Windows平台（不同Unix平台下皆不需要）下之PHP程式連線MySQL28資料庫系統理論與實務

19、陳祥輝著2007版權所有，翻印必究第三章關聯式模型表3-1軟體類型名稱軟體說明APIsDBI-forconnectingtoMySQLfromPerlPerl的APIRuby-forconnectingtoMySQLfromRubyRuby的APIPython-forconnectingtoMySQLfromPythonPython的API.NET-forconnectingtoMySQLfrom.NET.NET的APIMySQL+-forconnectingtoMySQLfromC+C+的APICh-forconnectingtoMySQLfromCh(C/C+interpreter)Ch(

20、C/C+interpreter)的API29資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型開放原始碼(2/4)-MaxDBMaxDBMaxDB資料庫源自於SAPDB()技術團隊自2003年第四季與MySQLAB公司簽署合約後，從7.5版之後，正式重新命名為MaxDB，並歸MySQLAB公司繼續開發和維護，網址可參考http:/提供的所有相關軟體，如表3-230資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型表3-2MaxSQL的相關軟體軟體類型名稱軟體說明後端資料庫伺服器MaxDBServer此為MaxDB伺服端的主要元件前端工具軟體DBMGUI此

21、軟體主要安裝於前端電腦中，提供遠端連線管理資料庫伺服器的工具SQLStudio此軟體提供圖形化界面，供使用者下查詢(Query)命令使用驅動程式(Drivers)與連接器(Connectors)ODBCDriver提供MaxDB的ODBC驅動程式JDBCDriver提供MaxDB的JDBC驅動程式APIsSQLDBCPerlPerl的APIPHPPHP的APIPythonPython的API31資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型開放原始碼(3/4)-PostgreSQLPostgreSQL提供多平臺的版本，包括Linux、UNIX(AIX,BSD,HP-UX

22、,SGIIRIX,MacOSX,Solaris,Tru64)和Windows完全符合交易(Transaction)的四個特性ACID(Atomicity、ConsistencyPreservation、Isolation和Durability)，且完全支援外來鍵(ForeignKey)、合倂(Join)、視界(Views)、預存程序(StoredProcedures)和觸發器(Trigger)，並包括美國標準局ANSI-SQL92和ANSI-SQL99的資料型態提供多種的原生程式介面，包括C/C+、Java、Perl、Python、Ruby、Tcl、ODBC參考網站資料http:/宣稱為企業級

23、的資料庫管理系統，並支援多版本並行控制技術(Multi-VersionConcurrencyControl，簡稱MVCC)、即時回復能力(Recovery)、資料表空間(Tablespace)、非同步複寫(AsynchronousReplication)、巢狀式交(NestedTransactions)、線上即時備份、查詢計劃器/最佳處理處(QueryPlanner/Optimizer)以及支援容錯(FaultTolerance)能力的Write-AheadLogging啟動後，預設值會於TCP/IPPort5432傾聽PostgreSQL的限制如表3-3所示，以及表3-4所列的相關軟體。32

24、資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型表3-3PostgreSQL的各種項目之限制值項目項目限制值限制值資料庫的大小無限資料表的大小32 Tera Bytes列的大小1.6 Tera Bytes欄位的大小1 Giga Bytes每個資料表的最多列數無限每個資料表的最大行數依據不同Column的資料型態，可從250至1600不等每個資料表的索引數無限33資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型表3-4PostgreSQL的相關軟體軟體類型名稱軟體說明後端資料庫伺服器PostgreSQLServer前端工具軟體pgAdminIII跨平台

25、的前端資料庫管理程式PhpPgAdminWeb-based的資料庫管理程式驅動程式(Drivers)與連接器(Connectors)DBDr:PgPerlDriverJDBCJDBCDriverlibpqxxC+APINpgsql.NetDataProviderODBCODBCDriverPgOleDbOLE-DBDriverAPIspgtclngNextgenerationinterfaceforTCLPgtclTclbindinglibraryPginATclinterfacetoPostgreSQLwritteninTcPyGreSQLPythoninterfaceforPostgreS

26、QLpsycopgAnotherPythoninterfaceforPostgreSQL34資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型開放原始碼(4/4)-FirebirdFirebirdFirebird自1981年，之後以不同名稱曾發展出不同產品的系統資料庫管理系統提供不同作業系統的版本，如Linux、Windows和不同的Unix提供多人並行處理能力（Concurrency）、高效能、並提供預存程序(StoredProcedures)和觸發器(Triggers)的功能啟動後，預設值會於TCP/IPPort3050傾聽參考網址http:/。基於Inprise公司（

27、也就是知名的Borland軟體公司）於2000年七月二十五日所釋放出來的關聯資料庫管理系統為基礎遵循InterBasePublicLicensev.1.0.的授權條款，再藉由C/C+之非商業程式設計師強化後，提供多種平臺的資料庫系統。而後所新開發出來的新原始碼則是遵循InitialDevelopersPublicLicense.(IDPL).以及原始的InterBase.，最後被修改為Mozilla.35資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型本章內容3-1關聯式資料模型的發展3-2關聯式資料庫簡介 3-33-3關聯式模型的基本介紹關聯式模型的基本介紹關聯式模型的

28、基本介紹關聯式模型的基本介紹3-4關聯式模型的完整性限制關聯式模型的完整性限制3-5關聯式模型的轉換與設計關聯式模型的轉換與設計3-6不當設計所引發的異動操作異常問題不當設計所引發的異動操作異常問題3-7鍵值屬性的介紹鍵值屬性的介紹3-8功能相依性功能相依性3-9正規化正規化3-10實體關聯圖的實例探討與設計實體關聯圖的實例探討與設計3-11案例資料之參考案例資料之參考36資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型3-3關聯式模型的基本介紹以資料表(Table)的方式來呈現此資料表在關聯式模型中稱之為關聯(Relation)或實體(Entity)關聯的縱向行，稱之為

29、行(Column)、欄位(Field)或稱為屬性(Attribute)橫向列，則稱之為列(Row)、紀錄(Record)、值組(Tuple)或稱為實例(Instance)37資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型員工員工代號姓名年齡出生日期居住區域854Andy2075/3/5Taipei581Candy3659/4/12Tainan542Jacky4055/9/9Taipei651Mary3065/10/2Taichung652Robin2768/12/20HsinChu屬性(行、欄位)值組(列、紀錄、實例)關聯名稱(資料表、實體)圖3-2關聯之基本介紹38資

30、料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型訂單訂單編號訂貨日期客戶產品代號產品資料產品代號產品名稱單價訂單明細訂單編號產品代號數量關聯綱要(RelationalSchema)是由數個關聯所組成的一個集合圖3-3關聯綱要(RelationalSchema)39資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型本章內容3-1關聯式資料模型的發展3-2關聯式資料庫簡介3-3關聯式模型的基本介紹 3-43-4關聯式模型的完整性限制關聯式模型的完整性限制關聯式模型的完整性限制關聯式模型的完整性限制3-5關聯式模型的轉換與設計關聯式模型的轉換與設計3-6不當設

31、計所引發的異動操作異常問題不當設計所引發的異動操作異常問題3-7鍵值屬性的介紹鍵值屬性的介紹3-8功能相依性功能相依性3-9正規化正規化3-10實體關聯圖的實例探討與設計實體關聯圖的實例探討與設計3-11案例資料之參考案例資料之參考40資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型3-4關聯式模型的完整性限制垃圾進、垃圾出(Garbage-InGarbage-Out)保證資料的正確性完整性限制(IntegrityConstraint)，限制使用者所輸入的資料倘若是不符合完整性，就必須將排除在外關聯的完整性(Integrity)，依據限制的範圍大小而言，可以從小而大做不同

32、的限制。鍵值屬性的限制的鍵值完整性限制(KeyIntegrityConstraint)一般屬性限制的領域完整性限制(DomainIntegrityConstraint)單一關聯的實體完整性限制(EntityIntegrityConstraint)兩個關聯間的參考完整性限制(ReferentialIntegrityConstraint)使用者自訂的完整性限制(User-DefinedIntegrityConstraint)。41資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型鍵值完整性限制關聯之中，鍵值具有不可為空值(NullValue)的限制每個值組中，倘若該值組的鍵值為

33、空值(NullValue)時，就必須限制該筆值組的輸入鍵值屬性主要的功能在於對該關聯中的值組(Tuple)做唯一的識別功能，倘若此鍵值屬性的值為空值，則該筆值組將失去唯一識別的功能，因此，鍵值屬性不可為空值(NullValue)42資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型領域完整性限制每一個屬性皆被有效的限制，對於不符合人們所期待的值，也該被限制輸入，造成無用的資料儲存在資料庫內一個屬性其中的值必須被限制在某一範圍或某一種資料型態，此種限制稱之為領域完整性限制(DomainIntegrityConstraint)例如在員工關聯中，有一個屬性為性別，則此屬性可以限制

34、該屬性值必須屬於領域男,女之中，除此領域之外的值，皆不可接受，此即為領域完整性限制(DomainIntegrityConstraint)43資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型實體完整性限制主要的考量在於關聯的整體性，對於同一筆值組而言，在同一個關聯之中，不應該會重複出現一次以上的值組存在於此關聯中如何判定是否同一筆資料內容呢？就是任何兩筆值組的鍵值屬性不可具有相同的屬性值在同一關聯中，鍵值屬性不可有重複值，此稱為實體完整性限制(EntityIntegrityConstraint)44資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型參考完

35、整性限制關聯與關聯之間的關聯性(Relationship)的完整性主要來自於兩個關聯之間不同鍵值屬性的參考關係被參考的關聯稱之為父關聯另一個參考父關聯的關聯稱之為子關聯由子關聯的外來鍵(ForeignKey，簡稱FK)參考父關聯的主要鍵(PrimaryKey，簡稱PK)所形成的一個關聯性(Relationship)此關聯性必須遵循父關聯中的主要鍵值存在，子關聯的外來鍵值方能存在，否則其值將會是空值(NullValue)45資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型使用者自訂的完整性限制使用者自訂的完整性限制(User-DefinedIntegrityConstrai

36、nt)，依據使用者的需求，額外新增的限制資料的輸入，以保持資料的完整性例如某學校的教師，必須同時具備教師證字號的屬性值，以及科系發聘的聘用書字號的屬性值，方可新增此筆教師資料，此種限制即屬於使用者自訂完整性限制的一種46資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型本章內容3-1關聯式資料模型的發展3-2關聯式資料庫簡介3-3關聯式模型的基本介紹3-4關聯式模型的完整性限制 3-53-5關聯式模型的轉換與設計關聯式模型的轉換與設計關聯式模型的轉換與設計關聯式模型的轉換與設計3-6不當設計所引發的異動操作異常問題不當設計所引發的異動操作異常問題3-7鍵值屬性的介紹鍵值屬性

37、的介紹3-8功能相依性功能相依性3-9正規化正規化3-10實體關聯圖的實例探討與設計實體關聯圖的實例探討與設計3-11案例資料之參考案例資料之參考47資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型3-5關聯式模型的轉換與設計概念式實體關聯圖(ConceptualEntityRelationshipDiagram，簡稱ERD)，著重在於資訊人員與不同領域中的專業人士或系統操作人員溝通使用，並能忠實無誤地將現況表現出來，並無關於實作上的模型概念的實體關聯圖的設計目的，就是透過該企業領域的專業人士或專家的口中得知企業文化，並藉由概念式實體關聯圖描繪出來，並進而與該專業人士來確

38、認是否與事實符合進而將此模型轉換成程式設計人員能真正實作的實際式實體關聯圖48資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型轉換的過程必須先將所有的實體以及鍵值呈現出如圖3-4之四個不同的實體，而實體之間的基數關係則會影響到轉換的結果圖3-4實體(或稱關聯綱要)49資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型概念式實體關聯圖的基數關係在概念式實體關聯圖的基數關係(Cardinality)可分為下列四種1：1：一對一關係1：N：一對多關係M：1：多對一關係M：N：多對多關係50資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型

39、1:N或M:1的處理方式實際式實體關聯圖中，大部份應該只有1:N或是M:1的基數關係，而此基數關係處理方式，只要將基數代表1的一方，將此方關聯中的主要鍵嵌入代表多的一方的關聯，成為外來鍵51資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型第一項需求的轉換(一對多型)第一項需求：一位學生(學號、姓名、地址、電話、生日、年齡)，可能會有多個電話號碼，以及會有監護人(姓名、關係、地址)，但不是每一位學生都必須有監護人，可視學生年齡是否已經成年，以及可能會有一到多位監護人。將學生的主要鍵學號嵌入監護人，成為監護人中的外來鍵倘若在一個家庭中的姓名不會重複，則可以在監護人中選擇學號、

40、姓名成為監護人的主要鍵52資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型第一項需求之ERD圖3-5第一項需求之ERD學生資料表的主要鍵學號學生資料表的主要鍵學號，嵌入此成為外來鍵53資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型第六、七項需求的轉換(一對多型)轉換成圖3-6之ERD，將科系的主要鍵科系代號嵌入課程中，形成課程的外來鍵。第六項需求：科目之間有可能檔修情形，也就是說，有些科目必須要先修過某些基礎科目之後，方可選修該門科目；而某一個科目也有可能會擋其他多個不同科目的情形，一個科目只會有一個先修科目。第七項需求：每科系可以開出很多不同的課

41、程讓學生來選修；但每一科系所開出的課程，雖然科目名稱有可能在不同科系之間會有相同的情形，但視為不同課程；換句話說，一個課程只會有一個科系開出，不會有多個科系開出完全相同的一門課程。54資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型第六、七項需求之ERD圖3-6第六、七項需求之ERD科系資料表的主要鍵科系代號科系資料表的主要鍵科系代號，嵌入此成為外來鍵55資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型1:1的處理方式通常在1：1的基數關係中，會將兩個關聯直接合併成一個關聯，在實作上而言會增加實作上的效率和系統實際在運作上的速度，但除了有三種情形，可

42、以讓1：1的基數關係存在；該關聯的屬性數超過所採用資料庫管理系統的上限限制在該關聯中，大部份值組之某部份屬性皆為空值時。例如某大學的學生基本資料中，大部份學生都未結婚，所以在配偶的屬性會有大部份值組都是呈現空值(NullValue)的情形如果此兩關聯的1：1關聯性並非永遠存在或並非永遠歸屬關係56資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型第三項需求的轉換(一對一型)第三項需求：每一個科系僅會有一個學生代表，參與該科系的科系會議，並且不需要將歷年的學生代表記錄，只要記錄目前的學生代表即可。由於一個科系會永遠存在，而學生會因為畢業或換人來擔任，所以科系與學生之間的關聯性

43、並非永遠存在和歸屬關係，所以轉換成圖3-7，將學生的主要鍵學號嵌入科系中，成為科系中的一個外來鍵。倘若此需求有必要將所有擔任過的學生代表記錄下來，也就是所有學生代表的歷程紀錄，則此關聯將不會是如此設計57資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型第三項需求之ERD圖3-7第三項需求之ERD58資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型M:N的轉換處理對於M:N的關係，不像是1:1關係，只是影響執行效率上的問題M:N在實作上是無法實作的，所以在兩個關聯之中的關係(Relationship)必須提升為一個關聯來看待，如圖3-8的轉換方式，必須

44、將M:N轉換成1:M和N:1的關係，中間會形成另一個關聯59資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型M:N的轉換MNMN11圖3-8M:N的轉換60資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型第一項需求的轉換(多對多型)第二項需求：學生必須歸屬在某一個科系(科系代號、科系名稱、位置)，也可以同時申請輔系或雙學位，也就是主副修關係。第四項需求：每位學生可以自由選修課程(課程代號、必選修別、學分數、科目名稱、先修科目)，但學生的選修結果必須記錄該名學生選修的成績。皆為M:N的模式，可將轉換成圖3-9，在學生與科系之間產生另一個主副系，並將學生

45、的主要鍵嵌入，科系的主要鍵也同時嵌入，形成主副修關聯中的兩個外來鍵，並選擇學號、科系代號為此關聯的主要鍵；另一個M:N的學生和課程之間亦產生出一個選課的關聯，同時分別將學生和課程的主要鍵嵌入選課中，並選擇學號、學年學期、科系代號、科目代號為此關聯的主要鍵。註此處的學年學期、科系代號、科目代號即為需求中的課程代號61資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型第二、四項需求之ERD圖3-9第二、四項需求之ERD62資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型轉換後之ERD(未經正規化)圖3-10轉換後之ERD(未經正規化)63資料庫系統理論與實

46、務陳祥輝著2007版權所有，翻印必究第三章關聯式模型本章內容3-1關聯式資料模型的發展3-2關聯式資料庫簡介3-3關聯式模型的基本介紹3-4關聯式模型的完整性限制3-5關聯式模型的轉換與設計 3-63-6不當設計所引發的異動操作異常問題不當設計所引發的異動操作異常問題不當設計所引發的異動操作異常問題不當設計所引發的異動操作異常問題3-7鍵值屬性的介紹鍵值屬性的介紹3-8功能相依性功能相依性3-9正規化正規化3-10實體關聯圖的實例探討與設計實體關聯圖的實例探討與設計3-11案例資料之參考案例資料之參考64資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型3-6不當設計所引發

47、的異動操作異常問題實體關聯圖的設計，對於同一個關聯，有可能會造成許多的問題，查詢操作和異動操作(包括新增、刪除和修改操作)之間的衝突同一個關聯之中，方便地查詢出所有的資料，則對於異動操作往往會出現不同的異常現象(新增異常、刪除異常和修改異常)沒有異動操作上的異常現象，卻又會造成在查詢操作上的麻煩和不方便，如圖3-11所示。為達到查詢和異動操作兩者之間的平衡點對於一個不當的關聯要透過適當的切割成為數個關聯，可以避免掉不同異動的異常要解決查詢上的不便，則可以使用數個相關的關聯(Relation)，和彼此相關之關聯中的關聯性(Relationship)，透過不同的合併方式，還原回原有的關聯模式或合併

48、出所需要的資料，以解決關聯因切割後所造成查詢上的不方便性65資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型查詢與異動之間的衝突與解決操作項目查詢操作異動操作查詢(Select)新增(Insert)刪除(Delete)修改(Update)衝突性方便造成異動操作的異常造成查詢操作的不方便性正常解決方式合併理論(Join)正規化(NormalForm)或稱為切割理論邏輯分層外部層(外部綱要)介面(Interface)概念層(概念綱要)66資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型異動操作的三個異常現象異動操作的異常新增異常(InsertionAno

49、maly)刪除異常(DeletionAnomaly)修改異常ModificationAnomaly)67資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型訂單編號訂貨日期客戶產品代號產品名稱單價數量000012006/06/08陳明希T0001紅茶1550000012006/06/08陳明希C0001咖啡2560000012006/06/08陳明希T0002青草茶20100000022006/07/15林阿德T0001紅茶1580000022006/07/15林阿德T0003烏龍茶20100000032006/12/20蔡淑媛T0003烏龍茶2080000032006/12

50、/20蔡淑媛C0001咖啡25120000032006/12/20蔡淑媛T0004奶茶20100000032006/12/20蔡淑媛T0005綠茶1560重複重複多筆產品多筆產品多筆產品同筆訂單同筆訂單同筆訂單圖3-12操作異常範例(a)原始資料原始資料單一個資料表的設計此單一資料表的設計以查詢資料而言非常方便，因為資料全在一個資料表內68資料庫系統理論與實務陳祥輝著2007版權所有，翻印必究第三章關聯式模型訂單編號訂貨日期客戶產品代號產品名稱單價數量000012006/06/08陳明希T0001紅茶1550000012006/06/08陳明希C0001咖啡2560000012006/06/0