Transcript Chapter 3
Chapter 3-1 Chapter 3: Data Modeling Introduction An Overview of Databases Steps in Developing a Database Using Resources, Events and Agents Model Normalization Chapter 3-2 Introduction Uses of a modern AIS Systematically Provide Easy record data convenient and useful formats access to information Chapter 3-3 Data Stores-Specific Diagrams Included on both Flowcharts and DFDs There are also rules related to data stores. For example Can a customer have more than one address? Can an address belong to more than one customer? Chapter 3-4 What is a Database? Collection of organized data Used by many different computer applications Manipulated by database management systems (DBMS) Chapter 3-5 Significance of a Database Critical information Volume Distribution Privacy Irreplaceable data Need for accuracy Internet uses Chapter 3-6 Storing Data in Databases Data must be stored and organized systematically Three important concepts: Data hierarchy Record structures Database keys Chapter 3-7 Data Hierarchy Data organization in ascending order: Data field Record File Database Chapter 3-8 Record Structures Data fields in each record of a database table Structure is usually fixed Example Chapter 3-9 Database Keys Primary Key Unique to each record Foreign Keys Enable referencing of one or more records Matches primary key of related table Chapter 3-10 Records Combined Into Report Chapter 3-11 Additional Database Issues Administration Database Administrator Documentation Includes a variety of descriptions Structures, Contents, Security Features Data Dictionary Metadata Chapter 3-12 Data Dictionary Example Chapter 3-13 Additional Database Issues Data Integrity Data Integrity controls Designed by database developers Processing Accuracy and Completeness Transaction controls Ensures accurate transaction processing Chapter 3-14 Additional Database Issues Concurrency Concurrency controls Prevent multi-user access at same time Backup and Security Ability to recreate data Prevent unauthorized access View controls Chapter 3-15 Study Break #1 The part of the data hierarchy that represents one instance of an entity is a: A. Field B. Record C. File D. Database Chapter 3-16 REA(L) Model Resources Organization’s assets Events Activities associated with a business processes Agents People associated with business activities Location Chapter 3-17 Steps in Developing Databases with REA Identify Business and Economic Events Identify Entities Identify Relationships Among Entities Chapter 3-18 Steps in Developing Databases with REA Create Entity-Relationship Diagrams Identify Attributes of Entities Convert E-R Diagrams into Database Tables Chapter 3-19 Identify Events and Entities Types of Events Business Economic Types of Database Entities Entities Agents Resources Chapter 3-20 Entity Examples Chapter 3-21 Identify Relationships Among Entities Types of Relationships Direct relationship Indirect relationship Cardinalities Nature of relationships among entities Chapter 3-22 Cardinality Relationships Notations One-to-one (1:1) One-to-many (1:N) Many-to-many (N:M) Purpose Occurrence of one entity Associated with occurrence of one event of another entity Examples of each (1:1, 1:N, N:M) Chapter 3-23 Cardinality Relationships Chapter 3-24 Entity-Relationship Diagram Purpose Diagram entities Relationships among entities Structure Rectangles represent entities Connecting lines represent relationships Chapter 3-25 E-R Diagram Example Chapter 3-26 Relationship Tables Provide greater flexibility Need for Relationship Tables Many-to-many relationships Linking tables with foreign keys Chapter 3-27 Relationship Tables Chapter 3-28 Schematic of Database Tables Chapter 3-29 Chapter 3-30 Normalization Normalization Methodology ensuring attributes are stored in most appropriate tables Design promotes accuracy Avoids redundancy of data storage Levels First normal form Second normal form Third normal form Chapter 3-31 Unnormalized Data Chapter 3-32 First Normal Form In First Normal Form (1 NF) when: All data fields are singular Each attribute has one value Problems Data redundancy Insertion anomaly Deletion anomaly Chapter 3-33 First Normal Form Example Chapter 3-34 Anomalies Social License Security Last First Phone Plate Number Name Name Number State Number 123-45-6789 Curry Dorothy (916)358-4444 CA 123 MCD 123-45-6789 Curry Dorothy (916)358-4444 CA 123 MCD Ticket Number Date Code Fine 10151 10/15/2010 A $10 10152 10/16/2010 B $20 123-45-6789 Curry Dorothy (916)358-4448 134-56-7783 Mason Richard (916)663-7865 134-56-7783 Mason Richard (916)663-7865 CA CA CA 123 MCD 10121 11/12/2010 253 DAL 10231 10/23/2010 253 DAL 12051 12/5/2010 245-67-8901 Guy New (916)555-1212 CA 248 NEW 245-67-8902 Guy Old (916)555-1222 CA 249 OLD B C A Update: Dorothy gets a new phone $20 $50 $10 Insertion: New driver without ticket 12999 12/31/2010 C $50 Deletion: Officer does not appear on court date. Ticket information deleted (& Old Guy info too) Chapter 3-35 Second Normal Form In Second Normal Form (2 NF) when: It is in 1 NF All data items depend on primary record key (i.e., no partial dependencies) Benefits More efficient design Eliminates data redundancy Chapter 3-36 Second Normal Form Example Chapter 3-37 Third Normal Form In Third Normal Form (3 NF) when: It is in 2 NF Does not contain transitive dependencies Data field A does not determine data field B Ultimate Goal Create database in 3 NF Chapter 3-38 Third Normal Form Example Chapter 3-39 Study Break #5 A database is in third normal form (3NF) if it is second normal form and: A. All the data attributes in a record are well defined B. All the data attributes in a record depend on the record key C. The data contain to transitive dependencies D. The data can be stored in two or more separate tables Chapter 3-40