lec09-norm精选.pdfVIP

Normalization Chapter 19 10/5/2011 EECS 484: Database Management Systems 1 Database Design: The Story so Far  Requirements Analysis  Data stored, operations, apps, …  Conceptual Database Design  Model high-level description of the data, constraints, ER model  Logical Database Design  Choose a DBMS and design a database schema  Schema Refinement  Normalize relations, avoid redundancy, anomalies …  Physical Database Design  Examine physical database structures like indices, restructure …  Security Design 10/5/2011 EECS 484: Database Management Systems 2 Redundancy Supplier Supplies Item name addr item desc price  What is a good relational schema?  Suppose we did the following… Name Item Desc Addr Price Redundant Acme Dynamite boom A2 $8 storage problem: Acme Paint blue A2 $10 Supplier Acme Flowers pink A2 $3 information stored once per item it supplies! 10/5/2011 EECS 484: Database Management Systems 3 Redundancy Name Item Desc Addr Price Acme Dynamite boom A2 $8 Acme Paint blue A2 $10 Acme Flowers pink A2 $3  Redundancy Problems: Better Schema:  Redundant storage S(name,addr)  A supplier supplies multiple items