Mapping Problem Domain Objects to RDBMS Schema

Transcript Mapping Problem Domain Objects to RDBMS Schema

Systems Analysis and Design

Alan Dennis, Barbara Wixom, and David Tegarden John Wiley & Sons, Inc.

All rights reserved. Reproduction or translation of this work beyond that permitted in Section 117 of the 1976 United States Copyright Act without the express written permission of the copyright owner is unlawful. Request for further information should be addressed to the Permissions Department, John Wiley & Sons, Inc. The purchaser may make back-up copies for his/her own use only and not for redistribution or resale. The Publisher assumes no responsibility for errors, omissions, or damages, caused by the use of these programs or from the use of the information contained herein. Slide 2 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Object Persistence Design

Key Definitions

Object persistence performance.

involves the selection of a storage format and optimization for Four basic formats used for object persistence are: files, OO databases, object-relational, and relational databases .

OBJECT PERSISTENCE FORMATS

Files

Sequential Access Data stored in order based on a particular attribute Typically efficient for reports using all or most of the file’s data Random Access Data stored in unordered fashion Typically efficient for finding individual records Slide 6 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Types of Files

Master files – store core data Look-up files – reference information tat is used during validation processing (valid codes, etc) Transaction files – temporary files of data that will update a master file Audit files – trace changes made to files History files – contain archived data Slide 7 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Relational Databases

Primary key Foreign key Referential integrity Structured Query Language (SQL) Tables Joining tables Objects must be converted so they can be stored in a table Slide 8 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Customer Order File

Object-Relational Databases

Relational databases extended to handle the storage of objects Use of user-defined data types Extended SQL Inheritance tends to be language dependent Slide 10 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Relational Database Example

Object-Oriented Databases

Two approaches Adding persistence extensions to OO languages Separate database management systems  Extents      Object ID assigned Some inheritance Repeating groups or multivalued attributes Mainly support multimedia applications Sharp learning curve Slide 12 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Selecting an Object Persistence Format

MAPPING PROBLEM DOMAIN OBJECTS TO OBJECT PERSISTENCE FORMATS

The Process of Analysis

Identify a problem Analyze the problem The conceptualization is of the problem domain Analysis becomes models Models are iterated into the system design Slide 15 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Process of Design

Design strives to map the problem domain to the technological solution In UML, classes build the foundation Slide 16 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Example Class Diagram

Problem Domain to Design

How do you move from the problem domain to the design of the technology?

Recognize that other classes are needed to facilitate implementation of the design Slide 20 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Layers of Classes

FOUNDATION typically available with development language SYSTEM ARCHITECTURE HUMAN-COMPUTER INTERACTION DATA MANAGEMENT PROBLEM DOMAIN Slide 21 how the software will execute User interface (web pages for example) deals with persistence how objects will be stored and retrieved environment view PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping for Persistence

Focus is on identifying what must be persisted (stored, etc).

Mapping those classes from the problem domain to the data management domain Slide 22 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Initial Points to Consider

Adding primary and foreign keys Unless they add too much overhead Data management functionality only in classes at data management layer May add overhead, but aids in portability and reuse Slide 23 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Appointment System Problem Domain and Data Management Layers

Factoring Out Multiple Inheritance Effect Add attribute of Object Id for subclass

Slide 25

Copy attributes and methods of ‘extra’ subclass to all data management subclasses

Mapping Problem Domain Objects to ORDBMS Schema

Problem domain classes are mapped to data management classes and then to database tables Slide 26 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping Problem Domain Objects to ORDBMS Schema -- Example

Mapping to OORDMS Handling attributes

Map single-valued attributes to Columns in the table Set of access methods in the data management class Slide 28 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping to OORDMS Handling Methods

Methods (and derived attributes) can be mapped to stored procedures or problem modules Slide 29 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping to OORDMS Handling Relationships

Map simple relationships and aggregation to a column that stores an Object ID (ie similar to a foreign key) Slide 30 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping to OORDMS Other major steps

Multi-valued attributes are mapped to columns that contain a set of values Repeating groups of attributes can be used to create a new table Map many-to-many using a combination of the 2 steps above Deal with generalization and inheritance Slide 31 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping Problem Domain Objects to ORDBMS Schema -- Rules

Mapping Problem Domain Objects to RDBMS Schema

Problem domain classes are mapped to data management classes and then to database tables (same as OORDBMS) Slide 33 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping Problem Domain Objects to RDBMS Schema -

Map single-valued attributes to Columns in the table Set of access methods in the data management class Methods (and derived attributes) can be mapped to stored procedures or problem modules Slide 34 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping to RDMS Handling Relationships

Map simple relationships and aggregation to a column that stores a foreign key for the related tables Slide 35 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping to RDMS Other major steps

Multi-valued attributes are mapped to new tables Repeating groups of attributes can be used to create a new table and often a new problem domain class Map many-to-many to a new table that relates the 2 original tables Deal with generalization and inheritance Slide 36 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Mapping Problem Domain Objects to RDBMS Schema -- Rules

Which to use?

Generic customer information?

Pictures, video segments, etc?

Temporary data?

Data needed to process a web page that you do not want to store on the server?

OPTIMIZING RDBMS-BASED OBJECT STORAGE

Dimensions of Data Storage Optimization

Storage efficiency storage space) (minimizing Speed of access time to retrieve desired information) (minimizing Slide 41 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Optimizing Storage Efficiency

Reduce redundant data Limit null values Multiple possible interpretations can lead to mistakes A well-formed logical data model does not contain redundancy or many null values Slide 42 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

The Steps of Normalization

Optimizing Access Speed

Denormalization Clustering Intra-file Inter-file Indexing Slide 44 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Payment Type Index

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 Slide 46 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Steps for Estimating Size of a DB

Calculate raw data needs 1.

Calculate overhead as percentage of each record Past experience, vendor recommendations, etc.

Record number of records to be initially loaded and expected monthly growth Slide 47 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

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

8 7 4 13 9 2 4 2

14.7

63.7

Initial Table Size 50,000 Initial Table Volume 3,185,000 Growth/Month Table volume @ 3 years 1,000 5,478,200 Slide 48 PowerPoint Presentation for Dennis, Wixom & Tegardem Systems Analysis and Design Copyright 2001 © John Wiley & Sons, Inc. All rights reserved.

Expanding the Domain

The Object Data Management Group is working on standardization of the OO database features. Check them out at:

http://www.odmg.org