Key Definitions The data storage function manages how

  • Slides: 28
Download presentation
Key Definitions The data storage function manages how data is stored and handled by

Key Definitions The data storage function manages how data is stored and handled by programs that run the system? Goals of the data storage design function are ending up with efficient data retrieval (good response time) and having users able to retrieve the information they need. 1 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Data Storage Formats Slide 2 Power. Point Presentation for Dennis & Haley Wixom, Systems

Data Storage Formats Slide 2 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Types of Data Storage Format Files are electronic lists of data optimized to perform

Types of Data Storage Format Files are electronic lists of data optimized to perform a particular transaction A database is a collection of groupings of information the relate to each other in some way. A Database Management System (DBMS) is software that creates and manipulates these databases. 3 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Customer Order Database 4 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis

Customer Order Database 4 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

File Attributes Typically organized sequentially Pointers Linked Lists 5 Power. Point Presentation for Dennis

File Attributes Typically organized sequentially Pointers Linked Lists 5 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

File Types Master files Transaction files Look-up files Audit files History files 6 Power.

File Types Master files Transaction files Look-up files Audit files History files 6 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Hierarchical Database Example 7 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis

Hierarchical Database Example 7 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Network Database Example 8 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis

Network Database Example 8 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Relational Database Concepts Keys Referential integrity SQL Joining tables 9 Power. Point Presentation for

Relational Database Concepts Keys Referential integrity SQL Joining tables 9 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Object Database Example 10 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis

Object Database Example 10 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Multi-dimensional Database Example 11 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis

Multi-dimensional Database Example 11 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Optimizing Data Storage Slide 12 Power. Point Presentation for Dennis & Haley Wixom, Systems

Optimizing Data Storage Slide 12 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Dimensions of Data Storage Optimization Storage efficiency (minimizing storage space) Speed of access (minimizing

Dimensions of Data Storage Optimization Storage efficiency (minimizing storage space) Speed of access (minimizing time to retrieve desired information) 13 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Storage Efficiency Minimize null values Reduce update anomalies 14 Power. Point Presentation for Dennis

Storage Efficiency Minimize null values Reduce update anomalies 14 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Optimizing Data Storage 15 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis

Optimizing Data Storage 15 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Normalization Steps 16 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and

Normalization Steps 16 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

First Normal Form (1 NF) 17 Power. Point Presentation for Dennis & Haley Wixom,

First Normal Form (1 NF) 17 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Second Normal Form (2 NF) 18 Power. Point Presentation for Dennis & Haley Wixom,

Second Normal Form (2 NF) 18 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Third Normal Form (3 NF) 19 Power. Point Presentation for Dennis & Haley Wixom,

Third Normal Form (3 NF) 19 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Normalized Logical Data Model 20 Power. Point Presentation for Dennis & Haley Wixom, Systems

Normalized Logical Data Model 20 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Optimizing Access Speed Denormalization Clustering Intra-file Inter-file Indexing 21 Power. Point Presentation for Dennis

Optimizing Access Speed Denormalization Clustering Intra-file Inter-file Indexing 21 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Payment Type Index 22 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis

Payment Type Index 22 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Guidelines for Creating Indexes Use indexes sparingly for transaction systems Use many indexes to

Guidelines for Creating Indexes Use indexes sparingly for transaction systems Use many indexes to increase response times in decision support systems For each table Create a unique index based on the primary key Create an index based on the foreign key Create an index for fields used frequently for grouping, sorting, or criteria 23 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Oracle Index Editor 24 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis

Oracle Index Editor 24 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Estimating Data Storage Size Field Average Size (Characters) Order number Date Cust ID Last

Estimating Data Storage Size Field Average Size (Characters) Order number Date Cust ID Last name First name State Amount Tax rate Record Size Overhead (30%) Total Record Size 25 8 7 4 13 9 2 49 14. 7 63. 7 Initial Table Size Initial Table Volume Growth/Month Table volume @ 3 years 50, 000 3, 185, 000 1, 000 5, 478, 200 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Data Storage Size Estimator 26 Power. Point Presentation for Dennis & Haley Wixom, Systems

Data Storage Size Estimator 26 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

27 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright

27 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.

Summary Files are electronic lists of data generally of five types: master, look-up, transaction,

Summary Files are electronic lists of data generally of five types: master, look-up, transaction, audit, and history. A database is a collection of groupings of information and a DBMS is software that creates and manipulates these. There a number of methods for optimizing data access speed and data storage efficiency, though the designers may have to make tradeoffs between these goals. 28 Power. Point Presentation for Dennis & Haley Wixom, Systems Analysis and Design Copyright 2000 © John Wiley & Sons, Inc. All rights reserved.