资料库基本概念.ppt

資料庫基本概念 資料庫基本概念 Dept. of Information Management OCIT February, 2002 Informal design guidelines for relational schemas Informal measures of quality for relation schema design Semantics of the attributes Reducing the redundant values in tuples Reducing the null values in tuples Disallowing spurious tuples Semantics of the relational attributes A certain meaning is associated with the attributes. The easier it is to explain the semantics of the relation, the better will be the relation schema design. Guideline 1: Do not combine attributes from multiple entity types and relationship types into a single relation. Redundant information in tuples and update anomalies Grouping attributes into relation schemas has a significant effect on storage space. Update anomalies Insertion Deletion Modification Guideline 2: No update anomalies occur in the relation. Both schemas suffer from update anomalies Null values in tuples How to account for nulls when aggregate operations such as COUNT or SUM are applied Nulls can have multiple interpretations: Not applying to the tuples Unknown values Known but absent values Guideline 3: Avoid placing attributes with nulls in a base relation. If nulls are unavoidable, make sure that they apply in exceptional cases only 正規化 (Normalization) a process for assigning attributes to entities to reduce data redundancies and to help eliminate the data anomalies. Normalization works through a series of stages called normal forms: First normal form (1NF) Second normal form (2NF) Third normal form (3NF) The highest level of normalization is not always desirable. 正規化 First normal form (1NF) Disallow multivalued and composite attributes. Disallow relations within relations. That is, the domains of attributes must include only atomic values. 第一正規化 (1NF) 1. 必須為row-column的二維式table 2. table的每一筆資料（row）只描述一件事情 3. 每一欄位只含有單一事物的特性（欄位的唯一性） 4. 每一筆row的欄位內只允許存放單一值 5. 每個欄位名稱必須是獨一無二的 6. 沒有任何兩筆資料是相同的 7. row或欄位的先後順予是無關緊要的 第一階正規化 Second normal form (2NF) X ? Y is a f