DatabaseManagementSystems.ppt

Database Management Systems Chapter 3 Data Normalization Objectives Why is database design important? What is a table and how do you choose keys? What are the fundamental rules of database normalization? How do you begin analyzing a form to create normalized tables? How do you create a design in first normal form? What is second normal form? What is third normal form? What problems exist beyond third normal form? How do business rules change the database design? What problems arise when converting a class diagram to normalized tables? What tables are needed for the Sally’s Pet Store? How do you combine tables from multiple forms and many developers? How do you record the details for all of the columns and tables? Why Normalization? Need standardized data definition Advantages of DBMS require careful design Define data correctly and the rest is much easier It especially makes it easier to expand database later Method applies to most models and most DBMS Similar to Entity-Relationship Similar to Objects (without inheritance and methods) Goal: Define tables carefully Save space Minimize redundancy Protect data Definitions Keys Primary key Every table (object) must have a primary key Uniquely identifies a row (one-to-one) Concatenated (or composite) key Multiple columns needed for primary key Identify repeating relationships (1 : M or M : N) Key columns are underlined First step Collect user documents Identify possible keys: unique or repeating relationships Notation Identifying Key Columns Surrogate Keys Real world keys sometimes cause problems in a database. Example: Customer Avoid phone numbers: people may not notify you when numbers change. Avoid SSN (privacy and most businesses are not authorized to ask for verification, so you could end up with duplicate values) Often best to let the DBMS generate unique values Access: AutoNumber SQL Server: Identity Oracle: Sequences (but require additional programming) Drawback: Numbers are not related to any business data, so