Logical Database Design (LDD) in Information Systems: Phases, Framework, and Keywords, Lecture notes of Design

Download Logical Database Design (LDD) in Information Systems: Phases, Framework, and Keywords and more Lecture notes Design in PDF only on Docsity!

NBS

PUBLICATIONS u.o. L/epanment of Commerce National Bureau of Standards

Computer Science

and Technology

NAT'L INST^ OF^ STAND^ &^ TECH

AlllQb T7fll4b

NBS Special Publication 500-

Guide on

Logical Database Design

m hehe National Bureau of Standards' was established by an act of Congress on March (^) 3, 1901. The JH Bureau's^ overall^ goal^ is^ to^ strengthen^ and^ advance^ the^ nation's^ science^ and^ technology^ and^ facilitate their effective application for public benefit. To this end, the Bureau conducts research and provides: (^) (1) a basis for the nation's physical measurement system, (^) (2) scientific and technological services for industry and government, (^) (3) a technical basis for equity in trade, and (^) (4) technical services to promote public safety. The Bureau's technical work is performed by the National Measurement Laboratory, the National Engineering Laboratory, the Institute for Computer Sciences and Technology, and the Center for Materials Science.

The National Measurement Laboratory

Provides the national system of physical and chemical measurement; •^ Basic^ Standards^ coordinates the system with measurement systems of other nations and •^ Radiation^ Research furnishes essential services leading to accurate and uniform physical and •^ Chemical^ Physics chemical measurement throughout the Nation's scientific community, in-^ •^ Analytical^ Chemistry dustry, and commerce; provides advisory and research services to other Government agencies; conducts physical and chemical^ research;^ develops, produces, and distributes Standard^ Reference^ Materials;^ and^ provides calibration services. The^ Laboratory^ consists^ of^ the^ following^ centers:

The National Engineering Laboratory Provides technology and technical services to the public and private sectors to address national needs and to solve national problems; conducts research in engineering and applied science in support of these efforts; builds and main- tains competence in the necessary disciplines required to carry out this research and technical service; develops engineering data and measurement capabilities; provides engineering measurement traceability services; develops test methods and proposes engineering standards and code changes; develops and proposes new engineering practices; and develops and improves mechanisms to transfer results of its research to the ultimate user. The Laboratory consists of the following centers:

Applied Mathematics Electronics and Electrical Engineering^ Manufacturing Engineering Building Technology Fire Research Chemical Engineering^

The Institute for Computer Sciences and Technology

Conducts research (^) and provides scientific and technical services to aid Federal agencies in the selection, (^) acquisition, application, and use of com- puter technology (^) to improve effectiveness (^) and economy in Government operations in (^) accordance with Public Law 89-306 (^) (40 U.S.C. 759), relevant Executive Orders, and other directives; (^) carries out this mission by managing the Federal Information Processing Standards Program, developing (^) Federal ADP standards guidelines, (^) and managing (^) Federal participation in ADP voluntary standardization activities; provides (^) scientific and technological ad- visory services and assistance to Federal agencies; and provides the technical foundation for computer-related policies (^) of the Federal Government. The (^) In- stitute consists of the following centers:

Programming Science and Technology Computer Systems Engineering

The Center (^) for Materials Science Conducts research and provides measurements, data, standards, reference materials, quantitative understanding and other technical information funda- mental to the processing, structure, properties and performance of materials; addresses the scientific basis for new advanced materials technologies; plans research (^) around cross-country scientific themes such as nondestructive evaluation (^) and phase (^) diagram development; oversees Bureau-wide technical programs in nuclear reactor (^) radiation research and nondestructive evalua- tion; and broadly disseminates (^) generic technical information resulting from its programs. The Center consists of the following Divisions:

Inorganic Materials Fracture and Deformation^ Polymers Metallurgy Reactor Radiation

'Headquarters and Laboratories at Gaithersburg, MD, unless otherwise noted; mailing address Gaithersburg, MD 20899. ^Some divisions^ within^ the center are located at Boulder,^ CO 80303. ^Located (^) at Boulder, CO, with some elements at Gaithersburg, MD.

Reports on Computer Science and Technology

The National Bureau of Standards has a special responsibility withm the Federal Government for computer science and technology activities. The programs of the NBS Institute for Computer Sciences and Technology are designed to provide ADP standards, guidelines, and technical advisory services to improve the effectiveness of computer utilization in the Federal sector, and to perform appropriate research and development efforts as foundation for such activities and programs. This publication series will report these NBS efforts^ to the^ Federal computer community as well as to interested specialists in^ the^ academic and private sectors. Those wishing to receive notices of publications^ in^ this^ series^ should^ complete^ and^ return^ the^ form at the end of this publication.

Library of Congress Catalog Card Number: 85-

National Bureau of Standards Special Publication 500-

Natl. Bur. Stand. (U.S.), Spec. Publ. 500-122, 115 pages (Feb. 1985)

CODEN: XNBSAV

U.S. GOVERNMENT PRINTING OFFICE WASHINGTON: 1985 For sale by the SuDerinlendeni ot Documents, U S. Government Printing Ottice, Washington, DC 20402

TABLE OF CONTENTS

LIST OF FIGURES

• 1. INTRODUCTION Page
     • 1.1 What Is Logical Database Design? - 1.1.1 LDD's Relation to Other Life Cycle Phases - 1.1.2 Characteristics of LDD
  • 1.2 An Ideal Logical Database Design Methodology - 1.2.1 LDD Practices - 1.2.2 Data Dictionary System
    • 1.3 Intended Audience for this Guide
    • 1.4 Purpose of this Guide
    • 1.5 Assumptions
    • 1.6 Scope of this Guide
    • 1.7 Structure of this Guide
• 2. THE FRAMEWORK THAT SUPPORTS LDD
     • 2.1 The Role of LDD in the Life Cycle - 2.1.1 Needs Analysis
       • 2.1.2 Requirements Analysis
       • 2.1.3 Logical Database Design
       • 2.1.4 Physical Database Design
     • 2.2 Detailed Framework for LDD
       • 2.2.1 LDD Information Requirements
       • 2.2.2 LDD Phases
       • 2.2.3 Strategies for LDD Development
         • 2.2.4 Summary of LDD Features
• 3. PROJECT ORGANIZATION
     • 3.1 Functional Roles Needed for LDD
     • 3.2 Training Required for LDD
     • 3.3 Project Planning and Management Requirements
• 4. LOCAL INFORMATION -FLOW MODELING
     • 4.1 Information Used to Develop the LIM
     • 4.2 Functions of the LIM
     • 4.3 Procedure for Developing the LIM - 4.3.1 Review Need for Analysis - 4.3.2 Determine Subsystems - 4.3.3 Plan Development of the LIM - 4.3.4 Develop LIM - 4.3.5 Develop Workload With Respect to LIMs
• 5. GLOBAL IN FORMAT I ON -FLOW MODELING
     • 5.1 Information Used to Develop the GIM
     • 5.2 Functions of the GIM
     • 5.3 Procedure for Developing the GIM - 5.3.1 Verify the LIMs - 5.3.2 Consolidate LIMs - System (AIS) 5.3.3 Refine Boundary of Automated Information - 5.3.4 Produce GIM
  • 6. CONCEPTUAL SCHEMA DESIGN
       • 6.1 Information Used to Develop the CS
    • 6.2 Functions of the CS
    • 6.3 Procedure for Developing the CS - 6.3.1 List Entities and Identifiers - 6.3.2 Generate Relationships among Entities - 6.3.3 Add Connectivity to Relationships - 6.3.4 Add Attributes to Entities - 6.3.5 Develop Additional Data Characteristics - 6.3.6 Normalize the Collection
• 7. EXTERNAL SCHEMA MODELING
     • 7.1 Information Used to Develop the ES
     • 7.2 Functions of the ES
     • 7.3 Procedure for Developing the ES
  • 7.3.1 Extract an ES from the CS
  • 7.3.2 Develop Workload With Respect to ESs
  • 7.3.3 Add Local Constraints to the ES
• 8. CONCLUSIONS
• 9. ACKNOWLEDGMENTS
• 10. REFERENCES AND SELECTED READINGS
  • 1 - Information Systems Life Cycle FIGURES DESCRIPTION PAGE
  • 2 - Diagram of the Four LDD Phases
• 3 - Local Information-Flow Modeling (LIM) Procedure
  • 4 - Example of a LIM
  • 5 - Global Information-Flow Modeling (GIM) Procedure
• 6 - Example of a GIM ,
• 7 - Conceptual Schema (CS) Design Procedure
  • 8 - Example of an E-R Diagram
  • 9 - Alternate Notation for an E-R Diagram
  • 10 - Replacing a Relationship with an Entity
  • 11 - Example of an E-R Diagram with Connectivity
  • 12 - Example of an E-R-A Diagram
  • 13 - External Schema (ES) Modeling Procedure

Guide on Logical Database Design

Eli zabeth

Margaret W.

David K.

Joan M.

N. Fong

Henderson

Jefferson

Sullivan

This report discusses an iterative methodolo-

gy for^ Logical^ Database^ Design.^ The^ inethodology

includes four phases: Local Information-flow

Modeling, Global Information-flow Modeling, Con-

ceptual Schema Design, and External Schema Model-

ing. These phases are intended to make maximum

use of available information and user expertise,

including the use of a previous Needs Analysis,

and to prepare a firm foundation for physical da-

tabase design and system implementation. The

methodology recommends analysis fran different

points of view—organization, function, and event — in order to ensure that the logical data-

base design accurately reflects the requirements

of the entire population of future users. The

methodology also recommends computer support from

a data dictionary system, in order to conveniently

and accurately handle the volume and complexity of

design documentation and analysis. The report

places the methodology in the context of the com-

plete system life cycle. An appendix of illustra-

tions shows examples of how the four phases^ of^ the

methodology can be implemented.

Key words: data dictionary system; data dictionary

system standard; data management; data model;^ da-

tabase design; database management system, DBMS;

Entity-Relationship-Attribute Model; Information

Resource Dictionary System, IRDS ; logical database

design.

Figure 1 shows LDD's place in the life cycle and dep-

icts the functional and data activities that can be per-

formed in parallel. LDD can be performed in parallel to the

phases of Requirements Analysis, Systems Specification, and

Systems Design. The synchronized performance of these

phases will assist in providing the information needed for a

good LDD and will result in speeding the systems development

process

.

By taking a brief overview of the development of an in-

formation system, we can see how LDD is used. The life cy-

cle of an information system should consist of the following

phases

:

1. Needs Analysis

Also known as Enterprise Analysis, this phase is con-

ducted before other work on the systems development

project begins. Its purpose is to establish the con-

text and boundaries of the systems development ef-

fort, and provide the focus, scope, priorities, and

initial requirements for the target system.

2. Requirements Analysis

The results of the Needs Analysis are carried further

in this phase, which provides both the functional and

the data requirements for the system under develop-

ment. Requirements analysis is performed in parallel

to the LDD and Systems Specification phases. Proto-

typing may be performed during this phase to refine

requi rements

.

3. Systems^ Specification

During this phase, the functional information provid-

ed by requirements analysis is used to^ produce

specifications for: input^ and^ output^ reports^ that^ are

both external and internal to the system; the func-

tions, processes, and procedures of^ operational^ sub-

systems; and decision support capabilities.

4. Logical Database Design

This phase is performed concurrently with the phases

of Requirements Analysis, Systems Specification,^ and

Systems Design. During this phase, the data require-

ments provided by the Needs Analysis and Requirements

Analysis phases are used to perform the following

iterative data modeling and design activities:

A. Local and Global Information-flow Modeling

The following are defined: data flows throughout

the system; information models for each applica-

tion (i.e., local) and for the entire system

(i.e., global); and, data classifications,^ re-

quirements, and sources for the subsystems^ in-

cluding those for decision support. The LDD

data modeling activities correspond to^ the^ func-

tional specification activities of to the Sys-

tems Specification phase.

B. Conceptual and External Schemas

The following are defined: data structures for

system-wide (i.e., conceptual) and application-

oriented (i.e., external) views of the system;

user views of the databases including those pro-

viding decision support capabilities; and logi-

cal database schema designs and constraints.

LDD schema design activities correspond to the

functional design activities of the Systems

Design phase.

5. Systems^ Design

This phase delineates: the functional control flows

using the data flows from LDD; high level and de-

tailed system architectures; the software structure

design; and the module external design (i.e., the

design for interfaces among modules of code)

.

6. Physical Database Design

This phase produces physical data flows and the de-

tailed internal schema for the specific^ hardware,

software, and database implementations to be used, in

order to balance maximum data storage efficiency,

data retrieval performance, and data update perfor-

mance. Physical database design is performed in

parallel to the Implementation phase.

7. Implementation

This phase produces: logic definition for programs;

module design; internal data definitions; coding;

testing and debugging; acceptance testing; and

conversion from the old system to the new one.

8, Operation and Maintenance

During this phase the information system performs to

serve the users^ information needs and to collect

data about the system's ongoing operation. Program-

mers and analysts continue to debug the system and

modify it to support changing users' needs. Database

designers continue to maintain database effectiveness

and efficiency during system modifications and data

changes. When modifications to the system are no

longer adequate to support user needs, the current

system should evolve to a new target system^ and^ the

cycle will begin again.

As this description of the information system's life

cycle shows, LDD plays a major role in development. LDD

greatly enhances the performance of the Quality Assurance

(QA) process, which would be ongoing from the Systems

Specification and LDD phases through the Operation and

Maintenance phase. Because LDD emphasizes the iterative ap-

proach, QA will have many opportunities to check the results

of one iteration against the results of other iterations.

Since LDD is performed in parallel to^ the^ Requirements

Analysis, Systems Specification, and Systems Design phases,

QA will^ be able to compare both the interim and final

results of concurrent phases to resolve any difficulties

sooner than through the traditional approach. The automated

Data Dictionary System (DDS) , described in Section 1.2.2,

should be used during Requirements Analysis and LDD to pro-

vide immediate, shared access to data requirements and data-

base designs, and to support the QA process.

1.1.2 Characteristics of LDD.

The potential benefits^ of^ LDD^ to^ the^ development^ life

cycle can only be gained, however, through a good quality

LDD. For LDD to perform its role well, the results of the

logical design process must have certain characteristics. A

LDD should be:

o Independent of the hardware and software environ-

ment, so that the design can be implemented in a

variety of environments and so the design will

remain relevant even if the hardware and software

selected to support the information system eventual-

ly change.

o Independent of the implementation data model or the

Database Management System (DBMS) in use, so that

the design will apply to any present or future data

model or data inanagement system, which would not

necessarily be a DBMS.

o Comprehensive in representing present and future ap-

plications so that all known, anticipated, and prob-

able needs^ can be included or considered in the

design, to avoid costly system alterations in the

future

.

o Able to satisfy the information requirements of the

entire organization, encompassing all possible ap-

plications rather than being limited to one or two;

this way the information system will have the capa-

city to be an organizational resource, not just the

resource of one department or application area.

A good LDD should also fulfill a set of precise techni-

cal goals to provide a firm foundation for:

o Maintainability and reusability, achieved through

the use of modularity in the database design.

o Robustness, allowing both the design and the system

to be adaptable to hardware and software changes.

o Security, controlled through compar tmentalization in

the database design which will limit specified types

of data access to designated personnel or organiza-

tional units.

o Update and storage efficiency, achieved through con-

trolled redundancy that limits the number^ of^ places

where the same data will be stored.

o Retrieval efficiency, so that data can be organized

to be readily accessible by system users.

o Consistency and integrity, achieved^ through^ several

measures including data integrity constraints and

controlled redundancy.

If done^ correctly, logical^ database^ design^ for^ a^ com-

plex information system is a massive undertaking. The

short-term cost of LDD is great, but the long-term benefits

of better information and greater flexibility provide sub-

stantial savings over the system's life cycle.

4. A^ mode^ of^ notation (i.e., graphic or symbolic) to

describe and build a detailed conceptual model of the

data and functions under study.

5. A specification language (e.g., the language used by

a Data Dictionary System) to specify information re-

quirements and the LDD design in a consistent, unam-

biguous (^) manner

.

6. An automated tool such as a Data Dictionary System,

capable of supporting the documentation and analysis

of LDD complexity, especially for large systems

development projects. This tool should be used to

assist in: (a) describing the conceptual model; (b)

describing the data needed to support the functions

of the conceptual model; (c) performing completeness

and consistency checking of the conceptual model and

the data needed to support the functions of the con-

ceptual model [AFIF84]

.

1.2.2 Data Dictionary System.

A Data Dictionary System (DDS) is a computer software

system used to record, store, protect, and analyze descrip-

tions of an organization's information resources, including

data and programs. It provides analysts, designers, and

managers with convenient, controlled access to the summary

and detailed descriptions needed to plan, design, implement,

operate, and modify their information systems. The DDS also

provides end-users with the data descriptions that they need

to formulate ad hoc queries. Equally important, it provides

a common language, or framework, for establishing and^ en-

forcing standards and controls throughout an organization.

The data dictionary (DD) is the data that is^ organized

and managed by the Data Dictionary System. The DD is a

resource that will be of great value long after a logical

database design is completed. The data dictionary^ can^ pro-

vide support for information about all aspects of system

development to be stored, updated, and accessed throughout

the system's life cycle.

The term Information Resource Dictionary System^ (IRDS)

is beginning to replace the term Data Dictionary System due

to recognition of the flexibility and power of the software

[ANSI84, FIPS80, KONI84]. This paper uses the terms Data

Dictionary System (DDS) and data dictionary (DD) to conform

to the current practice of software vendors.

1.3 Intended Audience for this Guide

This guide is intended primarily^ to^ provide^ information

and guidance to: Data Administrators (DAs) and Database Ad-

ministrators (DBAs) in leading their LDD projects; Applica-

tions Administrators (AAs) and application specialists^ in

the types of data and data validation that LDD will require;

and, end-users and systems analysts in how they can^ best

contribute to the LDD project to maximize its benefits.

1.4 Purpose of this Guide

This guide provides a coherent plan of action that^ will

allow management and database designers to direct and^ per-

form the database design successfully. The LDD plan^ offered

here is sufficiently general to be compatible with existing

tools and techniques in use for database design. By defin-

ing a methodology that provides a more stable view of the

relationships among data items, this guide can be used to

increase the effectiveness of an inform.ation system over its

life cycle.

When the LDD approach described here is used, particu-

larly if used with the assistance of a Data Dictionary Sys-

tem, an increase in clear communication can result among the

end-users, systems analysts, designers, and the applications

programmers who will actually code and implement the system.

By providing a detailed and unambiguous description of the

system^s information requirements in relation to the users^

perspectives, LDD offers a bridge between the end-users and

the physical database designers and applications program-

mers.

This guide describes a methodology to be used in optim-

izing the flexibility and integrity of an information sys-

tem. Flexibility will be ensured through the identification

of the least changing characteristics of the system, which

give a stable foundation upon which to build the information

system. Data integrity will be optimized through the cen-

tralized control, completeness, and consistency that a qual-

ity LDD will provide. The information system that results

from these LDD procedures will perform better over the

system's life cycle because it will address current and

probable future needs more completely and will allow re-

quirements changes to be incorporated more effectively.