Chapter 3 1 Chapter 3 Data Modeling Introduction

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 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 Ø 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 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 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 (3 NF) 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