Database Design

1
Database Design

• Database Life Cycle

2
Systems Development Life Cycle
3
Database Role in Information Systems

• Major component
• Requires thorough elaboration
• Affects application structure
• Reflects information needs of functional area
• SDLC inherently links to the database lifecycle

4
Database Life Cycle (DBLC)
5
Database Initial Study

• Analyze company situation
• Operating environment
• Organizational structure
• Define problems and constraints
• Look for the source of problems
• Define objectives
• Define scope and boundaries

6
System Definition

• Describes the scope and boundaries of the
  database application and major user views
• User view defines what is required of a database
  application from the perspective of a particular
  group of users
• Required data
• Required transactions

7
Database Design

• Most Critical DBLC phase
• Make sure final product meets requirements
• Focus on data requirements
• Subphases
• Conceptual design
• DBMS software selection
• Logical design
• Physical design

8
Conceptual Design

• Data modeling creates abstract data structure to
  represent real-world items
• High level of abstraction
• Four steps
• Requirements and data analysis
• Entity relationship modeling
• Data model verification
• Distributed database design (if distributed
  database is developed)

9
DBMS Software Selection

• DBMS software selection is critical
• Advantages and disadvantages
• Factors affecting purchasing decision
• Cost
• DBMS features and tools
• Underlying model
• Portability
• DBMS hardware requirements

10
Logical Database Design

• Translate conceptual design into logical
  (internal) schema
• Map objects in the conceptual model to specific
  DBMS constructs
• Design components
• Tables
• Views
• Access authorities
• Others

11
Normalization of Logical Schema

• Business rules help to formulate functional
  dependencies
• Tables do not display data redundancy
• Logical schema is validated against users
  transactions
• Denormalization can be applied

12
Physical Database Design

• Specifications for database implementation on
  secondary storage.
• Expected workloads are analyzed
• Logical devices are assigned
• Adding or removing indexes
• Clustering tables

13
Design Security Measures

• System security
• Authentication
• Data security
• Access to base relations
• Access to views

14
Implementation and Loading

• Implement specifications from physical design to
  create storage constructs
• Create a database within the storage constructs
• Implement specifications from logical design to
  build tables and other object
• Populate tables with data

15
Testing and Evaluation

• Database is tested for performance, integrity,
  concurrent access, and security constraints
• Done in parallel with application programming
• Actions taken if tests fail
• Fine-tuning based on reference manuals
• Modification of physical design
• Modification of logical design

16
Operation, Maintenance and Evaluation

• Preventative maintenance
• Corrective maintenance
• Adaptive maintenance
• Adding new users
• Generation of database access statistics to
  monitor performance
• Periodic security audits based on
  system-generated statistics
• Periodic system usage-summaries

17
DB Design Strategy Notes

• Top-down
• Identify data sets
• Define data elements
• Bottom-up
• Identify data elements
• Group them into data sets based on business rules
• Example 3NF Synthesis