Physical File and Database Design

1
Physical File and Database Design

• The following information is required
• Normalized relations, including volume estimates
• Definitions of each attribute
• Descriptions of where and when data are used,
  entered, retrieved, deleted, and updated
  (including frequencies)
• Expectations or requirements for response time
  and data integrity
• Descriptions of the technologies used for
  implementing the files and database

2
Designing Fields

• Field
• Smallest unit of named application data
  recognized by system software
• Attributes from relations will be represented as
  fields
• Data Type
• A coding scheme recognized by system software for
  representing organizational data
• Choosing data types
• Four objectives
• Minimize storage space
• Represent all possible values of the field
• Improve data integrity of the field
• Support all data manipulations desired on the
  field
• Calculated fields
• A field that can be derived from other database
  fields

3
Methods of Controlling Data Integrity

• Default Value
• A value a field will assume unless an explicit
  value is entered for that field
• Range Control
• Limits range of values that can be entered into
  field
• Referential Integrity
• An integrity constraint specifying that the value
  (or existence) of an attribute in one relation
  depends on the value (or existence) of the same
  attribute in another relation
• Null Value
• A special field value, distinct from 0, blank, or
  any other value, that indicates that the value
  for the field is missing or otherwise unknown

4
(No Transcript)
5
Designing Physical Tables

• Relational database is a set of related tables
• Physical Table
• A named set of rows and columns that specifies
  the fields in each row of the table
• Design Goals
• Efficient use of secondary storage (disk space)
• Disks are divided into units that can be read in
  one machine operation.
• Space is used most efficiently when the physical
  length of a table row divides close to evenly
  with storage unit.
• Efficient data processing
• Data are most efficiently processed when stored
  next to each other in secondary memory.

6
Denormalization

• The process of splitting or combining normalized
  relations into physical tables based on affinity
  of use of rows and fields
• Partitioning
• Capability to split a table into separate
  sections
• Oracle 9i implements three types
• Range
• Hash
• Composite
• Optimizes certain operations at the expense of
  others

7
When to Denormalize

• Three common situations where denormalization may
  be used
• Two entities with a one-to-one relationship
• A many-to-many relationship with nonkey
  attributes
• Reference data

8
(No Transcript)
9
(No Transcript)
10
(No Transcript)
11
Designing Physical Tables

• File Organization
• A technique for physically arranging the records
  of a file
• Objectives for choosing file organization
• Fast data retrieval
• High throughput for processing transactions
• Efficient use of storage space
• Protection from failures or data loss
• Minimizing need for reorganization
• Accommodating growth
• Security from unauthorized use

12.11
12
Sequential File Organization
A file organization in which rows are stored in a
sequence according to primary key value
13
Indexed File Organization

• A file organization in which rows are stored
  either sequentially or nonsequentially and an
  index is created that allows software to locate
  individual rows
• Index A table used to determine the location of
  rows in a file that satisfy some condition

14
Guidelines for Choosing Indexes

• Specify a unique index for the primary key of
  each table.
• Specify an index for foreign keys.
• Specify an index for nonkey fields that are
  referenced in qualification, sorting and grouping
  commands for the purpose of retrieving data.

15
Hashed File Organization

• A file organization in which the address for each
  row is determined using an algorithm

16
(No Transcript)
17
Summary

• In this chapter you learned how to
• Define key database design terms.
• Explain the role of database design in the IS
  development process.
• Transform E-R or class diagrams into normalized
  relations
• Merge normalized relations from separate user
  views into a consolidated set of well-structured
  relations.
• Choose storage formats for fields.
• Translate well-structured relations into database
  tables.
• Explain when to use different types of file
  organizations.
• Describe the purpose and appropriate use of
  indexes.

18
The Process of Coding, Testing and Installation

• Coding
• Physical design specifications are turned into
  working computer code.
• Testing
• Tests are performed using various strategies.
• Testing can be performed in parallel with coding.
• Installation
• The current system is replaced by the new system.

19
Deliverables
20
The Process of Documenting the System, Training
Users, and Supporting Users

• Two audiences for final documentation
• Information systems personnel who will maintain
  the system throughout its productive life
• People who will use the system as part of their
  daily lives
• User Training
• Application-specific
• General for operating system and off-the-shelf
  software

21
Software Application Testing

• A master test plan is developed during the
  analysis phase.
• During the design phase, unit, system and
  integration test plans are developed.
• The actual testing is done during implementation.
• Test plans provide improved communication among
  all parties involved in testing.

22
(No Transcript)
23
Test Classification

• Manual vs. Automated
• Static (syntax only) vs. Dynamic (execution)

24
Manual Testing Techniques

• Inspection
• A testing technique in which participants examine
  program code for predictable language-specific
  errors
• Walkthrough
• A peer group review of any product created during
  the systems development process also called a
  structured walkthrough
• Desk Checking
• A testing technique in which the program code is
  sequentially executed manually by the reviewer

25
Automated Testing Techniques

• Syntax Checking
• The compiler is run against the source code to
  identify syntax errors.
• Unit Testing
• Each module is tested alone in an attempt to
  discover any errors in its code, also called
  module testing.
• Integration Testing
• The process of bringing together all of the
  modules that a program comprises for testing
  purposes. Modules are typically integrated in a
  top-down, incremental fashion.

26
Test Cases

• Test case a scenario of transactions, queries or
  navigation paths
• Can represent either
• Typical system use
• Critical system use
• Abnormal system use
• Test cases and results should be thoroughly
  documented so they can be repeated for each
  revision of an application.

27
Test Cases (cont.)

• Test cases are usually developed by analysts.
• Test cases should not be created by the
  programmers.
• Separate people should program and test in order
  to ensure objectivity.
• Programmers use symbolic debuggers to isolate
  causes for errors.

28
User Acceptance Testing

• Actual users test a completed information system.
• End result is the users final acceptance of the
  system.
• Alpha testing use simulated data
• Beta testing use real data in real user
  environment

29
Types of Alpha Tests

• Recovery testing
• Forces software (or environment) to fail in order
  to verify that recovery is properly performed
• Security testing
• Verifies that protection mechanisms built into
  the system will protect it from improper
  penetration
• Stress testing
• Tries to break the system
• Performance testing
• Determines how the system performs on the range
  of possible environments in which it may be used

30
Installation

• The organizational process of changing over from
  the current information system to a new one
• Four installation strategies
• Direct Installation
• Parallel Installation
• Single-location installation
• Phased Installation

17.30
31
(No Transcript)
32
The Process of Maintaining Information Systems

• Process of returning to the beginning of the SDLC
  and repeating development steps focusing on
  system change until the change is implemented
• Maintenance is the longest phase in the SDLC
• Four major activities
• Obtaining maintenance requests
• Transforming requests into changes
• Designing changes
• Implementing changes

33
Maintenance is like a mini-SDLC
34
Types of System Maintenance

• Corrective maintenance
• Changes made to a system to repair flaws in its
  design, coding, or implementation
• Adaptive maintenance
• Changes made to a system to evolve its
  functionality to changing business needs or
  technologies
• Perfective maintenance
• Changes made to a system to add new features or
  to improve performance
• Preventive maintenance
• Changes made to a system to avoid possible future
  problems

35
By far, most maintenance is corrective, and
therefore urgent and non-value adding.
36
The Cost of Maintenance

• Many organizations allocate eighty percent of
  information systems budget to maintenance
• Factors that influence system maintainability
• Latent defects
• Number of customers for a given system
• Quality of system documentation
• Maintenance personnel
• Tools
• Well-structured programs

37
A well-documented system is easier to understand,
and therefore easier to maintain.
38
Conducting System MaintenanceManaging Maintenance

• Number of people working in maintenance has
  surpassed number working in development.
• Three possible organizational structures
• Separate
• Maintenance group consists of different personnel
  than development group.
• Combined
• Developers also maintain systems.
• Functional
• Maintenance personnel work within the functional
  business unit.

39
Advantages and Disadvantages