The Database Life Cycle

Figure 6.3

The Database Life Cycle

The Database Initial Study
Overall Purpose of the Initial Study:
Analyze the company situation. Define problems and constraints. Define objectives. Define scope and boundaries.

Figure 6.4

The Database Life Cycle

Analyze the Company Situation

What is the organizations general operating environment, and what is its mission within that environment? What is the organizations structure?
Define Problems and Constraints

How does the existing system function? What input does the system require? What documents does the system generate? How is the system output used? By Whom? What are the operational relationships among business units? What are the limits and constraints imposed on the system?

The Database Life Cycle

Define the Objective
What is the proposed systems initial objective? Will the system interface with other existing or future systems in the company? Will the system share the data with other systems or users?

Define Scope and Boundaries

Scope -- What is the extent of the design based on operational requirements? Boundaries -- What are the limits?

Budget
Hardware and software

Figure 6.6

The Database Life Cycle

Conceptual Design
Data modeling is used to create an abstract database structure that represents real-world objects. The design must be software- and hardware-independent.

Minimal data rule:

All that is needed is there, and all that is there is needed.

Four Steps:
Data analysis and requirements Entity relationship modeling and normalization Data model verification Distributed database design

The Database Life Cycle

Data analysis and requirements
Designers efforts are focused on

Information needs. Information users. Information sources. Information constitution.

Sources of information for the designer

Developing and gathering end user data views Direct observation of the current system: existing and desired output Interface with the systems design group

The designer must identify the companys business rules and analyze their impacts.

The Database Life Cycle and Normalization Entity Relationship Modeling

Table 6.2 Developing the Conceptual Model Using E-R Diagrams

A Composite Entity

Figure 6.7

E-R Modeling Is An Iterative Process Based On Many Activities

Figure 6.8

Conceptual Design Tools And Information Sources

Figure 6.9

The Database Life Cycle

Entity Relationship Modeling and Normalization
Define entities, attributes, primary keys, and foreign keys. Make decisions about adding new primary key attributes in order to satisfy end user and/or processing requirements.

Make decisions about the treatment of multivalued attributes.

Make decisions about adding derived attributes to satisfy processing requirements.

The Database Life Cycle

Make decisions about the placement of foreign keys in 1:1 relationships. Avoid unnecessary ternary relationships. Draw the corresponding E-R diagram. Normalize the data model. Include all the data element definitions in the data dictionary. Make decisions about standard naming conventions.

The Database Life Cycle

Entity Relationship Modeling and Normalization
Some Good Naming Conventions: Use descriptive entity and attribute names wherever possible. Composite entities usually are assigned a name that is descriptive of the relationships they represent. An attribute name should be descriptive and it should contain a prefix that helps identify the table in which it is found.

The Database Life Cycle

Data Model Verification
Purposes of close review of entities and attributes The emergence of the attribute details may lead to a revision of the entities themselves.
The focus on attribute details can provide clues about the nature of the relationships as they are defined by the primary and foreign keys. To satisfy processing and/or end user requirements, it might be useful to create a new primary key to replace an existing primary key.

Unless the entity details are precisely defined, it is difficult to evaluate the extent of the designs normalization.

The Database Life Cycle

Data Model Verification
Advantages of the Modular Approach The modules can be delegated to design groups, greatly speeding up the development work. The modules simplify the design work. The modules can be prototyped quickly. Implementation and applications programming trouble spots can be identified more readily. Even if the entire system cant be brought on line quickly, the implementation of one or more modules will demonstrate that progress is being made and that at least part of the system is ready to begin serving the end users.

The E-R Model Verification Process

Table 6.3

Iterative E-R Model Verification Process

Figure 6.10

The Database Life Cycle

During the E-R model verification process, the DB designer must:
Ensure the modules cohesivity -- the strength of the relationships found among the modules entities. Analyze each modules relationships with other modules to address module coupling -the extent to which modules are independent of one another.

Processes may be classified according to

The Database Life Cycle

Distributed Database Design
Design portion of a database may reside in different physical locations.

If the database process is to be distributed across the system, the designer must also develop the data distribution and allocation strategies for the database.

The Database Life Cycle

Database Software Selection
Common factors affecting the decision:
Cost -- Purchase, maintenance, operational, license, installation, training, and conversion costs. DBMS features and tools. Underlying model. Portability -- Platforms, systems, and languages.

DBMS hardware requirements.

The Database Life Cycle

Logical Design
Logical design translates the conceptual design into the internal model for a selected DBMS. It includes mapping of all objects in the model to the specific constructs used by the selected database software. For a relational DBMS, the logical design includes the design of tables, indexes, views, transactions, access authorities, and so on.

A Simple Conceptual Model

Figure 6.11

PROF_ID

Is a valid professor identification number. Type: numeric Range: low value = 1,000 high value =2,000 Display format: 9999 Length: 4 Is a valid professor last name. Type: character Display format: XXXXXXXXXXXXXXX Length: 15 Is a valid phone number. Type: character Display format: 999-999-9999 Length: 12 Is a valid class code. Type: numeric Range: low value = 1,000 Display format: 9999 Length: 4

PROF_LNAME

PROF_PHONE

CLASS_CODE

high value =1,999

CLASS_SECTION

Is a valid is a valid class section number. Type: numeric Range: low value = 10 high value = 99 Display format: 99 Length: 2 Is a valid day code. Type: character Valid entries: MWF, TTh, M, T, W, Th, F Display format: XXX Length: 3 Is a valid time. Type: character Display format: 99:99 (24-hour clock) Display range: 00:01 to 24:00 Length: 5

CLASS_DAYS

CLASS_TIME

A Sample Table Layout

Table 6.4

The Database Life Cycle

Physical Design
Physical design is the process of selecting the data storage and data access characteristics of the database. It affects not only the location of the data in the storage device(s) but also the performance. The storage characteristics are a function of:
The types of devices supported by the hardware.

The Database Life Cycle

Implementation and Loading
Create the database storage group. Create the database within the storage group. Assign the rights to use the database to a database administrator. Create the table space(s) within the database. Create the table(s) within the table space(s).

Assign access rights to the table spaces and the tables within specified table spaces.
Load the data.

Physical Organization of a DB2 Database Environment

Figure 6.12

Figure 6.13

The Database Life Cycle

Physical Design Issues
Performance Security
Physical security Password security Access rights Audit trails Data encryption Diskless workstations

Backup and Recovery Integrity

The Need for Concurrency Control

Table 6.5

The Database Life Cycle

Testing and Evaluation
The testing and evaluation phase occurs in parallel with application programming.

Programmers use database tools (e.g., report generators, screen painters, and menu generators) to prototype the applications during the coding of the programs.
Options to enhance the system if the implementation fails.

The Database Life Cycle

Operation
Once the database has passed the evaluation stage, it is considered to be operational. The beginning of the operational phase invariably starts the process of system evolution.

The Database Life Cycle

Maintenance and Evolution
Preventive maintenance Corrective maintenance Adaptive maintenance Assignment and maintenance of access permissions Generation of database access statistics Periodic security audits based on the system-generated statistics Periodic system-usage summaries for internal billing or budgeting purposes.

A Special Note about Database Design Strategies

Two Classical Approaches to Database Design:

Top-down design starts by identifying the data sets, and then defines the data elements for each of these sets. Bottom-up design first identifies the data elements (items), and then groups them together in data sets.

Top-Down Versus Bottom-Up Design Sequencing

Figure 6.14

Centralized vs Decentralized Design Two Different Database Design

Philosophies:
Centralized design It is productive when the data component is composed of a relatively small number of objects and procedures.

Centralized vs Decentralized Design

Two Different Database Design Philosophies:
Decentralized design It may be used when the data component of the system has a considerable number of entities and complex relations on which very complex operations are performed. (Figure 6.16)

Aggregation problems must be addressed: (Figure 6.17)

Synonyms and homonyms.

Figure 6.16

Figure 6.17

The Database Life Cycle

The Database Initial Study
Overall Purpose of the Initial Study:
Analyze the company situation. Define problems and constraints. Define objectives. Define scope and boundaries.