Database Planning, Design,

1
Chapter 9

• Database Planning, Design,
• and Administration
• Transparencies

2
Chapter 9 - Objectives

• The main stages of the information systems
  lifecycle.
• The relationship between the database application
  and information systems lifecycles.
• The main stages of the database application
  lifecycle.

2
2
3
Chapter 9 - Objectives

• The main phases of database design conceptual,
  logical, and physical design.
• The benefits of Computer-Aided Software
  Engineering (CASE) tools.
• The types of criteria used to evaluate a DBMS.

3
4
Chapter 9 - Objectives

• How to evaluate and select a DBMS.
• The distinction between data administration and
  database administration.
• The purpose and tasks associated with data
  administration and database administration.

4
5
Software Crisis

• Last few decades have seen the proliferation of
  software applications.
• Many applications required constant maintenance
  involving
• correcting faults
• implementing new user requirements
• modifying the software to run on new or upgraded
  platforms

5
6
Software Crisis

• As a result, many major software projects were
• late
• over budget
• unreliable
• difficult to maintain
• performed poorly
• Also called software depression.

6
7
Software Crisis

• Study on software projects in 1996 in the UK
• 80 - 90 do not meet their performance goals.
• About 80 of systems are delivered late and over
  budget.
• Around 40 of developments fail or are abandoned.
  
• Under 40 fully address training and skills
  requirements.
• Less than 25 properly integrate business and
  technology objectives.
• Just 10 - 20 meet all their success criteria.

7
8
Software Development Lifecycle

• Major reasons for failure of software projects
• lack of complete requirements specification
• lack of appropriate development methodology
• poor decomposition of design into manageable
  components
• Solution - a structured approach to development
  of software called Information System Development
  Lifecycle.

8
9
Information System (IS)

• Resources that enable the collection, management,
  control, and dissemination of information
  throughout an organization.
• Components of IS include
• Database
• Database software
• Application software
• Computer hardware including storage media
• Personnel using and developing the system

9
10
Information System Development Lifecycle

• Database is a fundamental component of an IS.
• IS lifecycle is linked to the lifecycle of the
  database system that supports it.
• Stages include planning, requirements collection
  and analysis, design (including database design),
  prototyping, implementation, testing, conversion,
  and operational maintenance.

10
11
Database Application Lifecycle
11
12
Database Application Lifecycle

• Database planning
• System definition
• Requirements collection and analysis
• Database design
• DBMS selection (optional)

12
13
Database Application Lifecycle

• Application design
• Prototyping (optional)
• Implementation
• Data conversion and loading
• Testing
• Operational maintenance

13
14
Database Planning

• The management activities that allow the stages
  of the database application to be realized as
  efficiently and effectively, as possible.
• Identifies work to be done the resources with
  which to do it and the money to pay for it all.
• Integrated with the overall IS strategy of the
  organization.

14
15
Example - Corporate Data Model
15
16
System Definition

• The scope and boundaries of the database
  application including its major application areas
  and user groups.

16
17
Requirements Collection and Analysis

• The process of collecting and analyzing
  information about the part of the organization
  that is to be supported by the database
  application, and using this information to
  identify the users requirements of the new
  system.

17
18
Database Design

• The process of creating a design for a database
  that will support the enterprises operations and
  objectives.

18
19
Database Design

• Major aims
• Represent data and relationships between data
  required by all major application areas and user
  groups.
• Provide data model that supports any transactions
  required on the data.
• Specify a minimal design that is appropriately
  structured to achieve the stated performance
  requirements for the system such as response
  times.

19
20
DBMS Selection

• The selection of an appropriate DBMS to support
  the database application.
• Undertaken at any time prior to logical design
  provided sufficient information is available
  regarding system requirements.

21
21
Application Design

• The design of the user interface and the
  application programs that use and process the
  database.
• Database and application design are parallel
  activities.

22
22
Prototyping

• Building a working model of a database
  application.
• Purpose
• To identify features of a system that work well,
  or are inadequate
• To suggest improvements or even new features
• To clarify the users requirements
• To evaluate the feasibility of a particular
  system design.

23
23
Prototype Development Method Stages
24
24
System Development Approach

• Understanding the system requirements before
  design is critical to the success of creating
  todays complex system.
• A system development approach based on modeling
  and prototyping has proven very effective in
  understanding the system requirements.
• Modeling and rapid prototyping approach replaces
  the traditional waterfall approach.

25
Two Pronged approach to Building A System
Modeling and Prototyping

• Modeling
• Purposes To present the essential
  characteristics of the
• System.
• Characteristics Model is created based on
  observation of
• the real world or approximation based on system
  goals. (a
• simplified representation of a complex reality)
• Approach Models are used to segment a complex
  system
• into successively simpler components that can be
  easily
• understood.

26
Two Pronged approach to Building A System
Modeling and Prototyping

• Prototyping
• Purposes To validate and refine the model of a
  system.
• Characteristics Prototyping is a discipline of
  interactive
• experimentation to stimulate user feedback.
• Approach A prototype is a materialization of
  modeling
• process by building rapidly and simulating the
  essential
• aspects of the system.

27
Types of Prototyping

• Evaluative (mock-up) prototyping (Blue print
  approach)
• Use Visualization demonstration inspection
• Advantages. Reduce risk low cost resolve
  uncertainty
• Early fast flexible
• How Focuses on a relatively small number of
  questions to avoid becoming too complex.
• Generally thought of a throw-away.

28
Types of Prototyping

• Straw man (exploratory) prototyping (scale model
  approach)
• Use Experiment validating and refining the
  conceptual as well as logical data model.
• Advantages. Used to improve design discover
  things about a proposed system that would
  otherwise not be revealed.
• How Evaluate the impact on work flows
  validate ease of use.

29
Types of Prototyping

• Evolution prototyping (real world model)
• Use Production use.
• Advantages. Part of production exercise.
• How Explore functionality of subsystem and
  system probe technical feasibility the
  performance and the integrity of the system.

30
Stages of Prototyping

• Planning
• Initial analysis and design
• Construction
• Tryout
• Evaluation
• Disposal

31
Dimensions of Prototyping

• Relationships of any prototyping to its eventual
  system
• are characterized along four dimensions
• Focus for example, a prototype may focus on the
  functionality, user interface, system
  integration, reliability, and performance.
• Scope Is a measure of how much of the eventual
  system the prototype represents.

32
Dimensions of Prototyping

• Depth is a measure of how deeply it represents
  the behavior of the eventual system. For ex., a
  shallow prototype of a message system might
  display canned messages, where a deeper
  prototype might actually perform communications
  to provide a more realistic surrogate for the
  eventual system.
• Scale Is a measure of how its size or
  performance compares with that of the eventual
  system.

33
Implementation

• The physical realization of the database and
  application designs.
• Use DDL of DBMS to create database schemas and
  empty database files.
• Use DDL to create any specified user views.
• Use 3GL or 4GL to create application programs.
  Parts of these programs are the database
  transactions, created using DML of DBMS possibly
  embedded in a host programming language.

25
34
Data Conversion and Loading

• Transferring any existing data into the new
  database and converting any existing applications
  to run on the new database.
• Only required when a new database system is
  replacing an old system.
• DBMS normally have a utility that loads existing
  files into the new database.
• Where applicable, it may be possible to convert
  and use application programs from the old system
  for use by the new system.

26
35
Testing

• The process of executing the application programs
  with the intent of finding errors.
• Use carefully planned test strategies and
  realistic data.
• Testing cannot show the absence of faults it can
  show only that software faults are present.
• Demonstrates that database and application
  programs appear to be working according to
  requirements.

27
36
Operational Maintenance

• The process of monitoring and maintaining the
  system following installation.
• Monitoring the performance of the system.
• If performance falls, may require tuning or
  reorganization of the database.
• Maintaining and upgrading the database
  application (when required).
• Incorporating new requirements into the database
  application.

28
37
Overview of Database Design

• Main aims of data modeling
• To assist in the understanding of the meaning
  (semantics) of the data.
• To facilitate communication about requirements.
• A data model facilitates understanding
• Each users perspective of the data.
• Nature of data itself, independent of physical
  representations.
• The use of data across applications.

29
38
Overview of Database Design - Criteria for Data
Model
30
39
Conceptual Database Design

• The process of constructing a model of the
  information used in an enterprise, independent of
  all physical considerations.
• Data model is built using the information in
  users requirements specification.
• Source of information for the logical design
  phase.

31
40
Logical Database Design

• The process of constructing a model of the
  information used in an enterprise based on a
  specific data model (e.g. relational), but
  independent of a particular DBMS and other
  physical considerations.
• The conceptual data model is refined and mapped
  on to a logical data model.

32
41
Database Design

• A logical model that represents multiple user
  views of an organization is called a global
  logical data model.
• There are two major approaches to merging user
  views.
• Centralized
• View integration

33
42
Database Design - Merging User Views

• Centralized approach
• Merge separate user requirements that represent
  distinct user views into a single set of user
  requirements, and then build the global logical
  data model.
• View integration approach
• Merge separate local logical data models that
  represent distinct user views into one global
  logical data model.

34
43
Physical Database Design

• The process of producing a description of the
  implementation of the database on secondary
  storage.
• Describes the storage structures and access
  methods used to achieve efficient access to the
  data.
• Tailored to a specific DBMS system.

35
44
ANSI-SPARC Architecture and Database Design Phases
36
45
Application Design

• Includes two important activities
• Transaction design
• User interface design

37
46
Application Design - Transaction Design

• Transaction
• An action or series of actions, carried out by a
  single user or application program, which
  accesses or changes the content of the database.
• Purpose to define and document the high-level
  characteristics of the transactions required on
  the database system.

38
47
Application Design - Transaction Design

• Important characteristics of transactions include
• Data to be used by the transaction
• Functional characteristics of the transaction
• Output of the transaction
• Importance to the users
• Expected rate of usage
• Three main types of transactions retrieval,
  update and mixed.

39
48
Transaction Analysis

• A Transaction is a collection of database
  operations grouped into a unit of work that is
  either completely executed or completely
  abandoned
•  
• TM (Transaction Monitors) are a class of
  transaction-processing applications that were
  originally designed to manage very large numbers
  of simultaneous transactions against mainframe
  database mgt systems.
•  
• MTS (Microsoft Transaction Server) brings the
  robustness and scalability to the client-server
  arena.

49
Transaction Analysis

• Transactions can be classified as either implicit
  or explicit. Implicit transactions are single SQL
  statements that execute as an atomic unit.
  Explicit transactions are groupings of SQL
  statements surrounded by transaction delimiters
  Begin Transaction, Commit Transaction, Rollback
  Transaction.

50
Application Design - User Interface Design
Guidelines

• Meaningful title
• Comprehensible instructions
• Logical grouping and sequencing of fields
• Visually appealing layout of the form/report
• Familiar field labels
• Consistent terminology and abbreviations
• Consistent use of color

40
51
Application Design - User Interface Design
Guidelines

• Visible space and boundaries for data-entry
  fields
• Convenient cursor movement
• Error correction for individual characters and
  entire fields
• Error messages for unacceptable values
• Optional fields marked clearly
• Explanatory messages for fields
• Completion signal

41
52
Computer-Aided Software Engineering (CASE) Tools

• Purpose - To support the efficient and effective
  development of database applications.
• CASE support may include
• A data dictionary to store information about the
  database applications data.
• Design tools to support data analysis.
• Tools to develop the corporate, conceptual, and
  logical data models.
• Tools to enable the prototyping of applications.

42
53
Computer-Aided Software Engineering (CASE) Tools

• Divided into three categories upper-CASE,
  lower-CASE, and integrated-CASE.
• Can provide the following benefits
• Standards
• Integration
• Support for standard methods
• Consistency
• Automation

43
54
Database Application Lifecycle - CASE Tools
44
55
DBMS Selection

• Define Terms of Reference of study
• Shortlist two or three products
• Evaluate products
• Recommend selection and produce report

45
56
DBMS Evaluation Features
46
57
DBMS Evaluation Features
47
58
DBMS Evaluation Features
48
59
DBMS Evaluation Features
49
60
Example - Evaluation of DBMS Product
50
61
Database Application Lifecycle - DA and DBA Major
and Minor Roles
51
62
Data Administration

• The management of the data resource, which
  includes database planning, development and
  maintenance of standards, policies and
  procedures, and conceptual and logical database
  design.

52
63
Data Administration Tasks
53
64
Database Administration

• The management of the physical realization of a
  database application, which includes physical
  database design and implementation, setting
  security and integrity controls, monitoring
  system performance, and reorganizing the
  database, as necessary.

54
65
Database Administration Tasks
55
66
DA and DBA Main Task Differences.
56