Database Design
In this lesson, you will learn:
That successful database design must reflect the
information system of which the database is a part
That successful information systems are subject to frequent
evaluation and revision within a framework known as the
Systems Development Life Cycle (SDLC)
That, within the information system, the most successful
databases are subject to frequent evaluation and revision
within a framework known as the Database Life Cycle
(DBLC)
How to conduct evaluation and revision within the SDLC
and DBLC frameworks
What database design strategies exist: top-down vs.
bottom-up design and centralized vs. decentralized design
Changing Data into Information
Data
Raw facts stored in databases
Need additional processing to become useful
Information
Required by decision maker
Data processed and presented in a meaningful
form
Transformation
The Information System
Database
Carefully designed and constructed repository of facts
Part of an information system
Information System
Provides data collection, storage, and retrieval
Facilitates data transformation
Components include:
People
Hardware
Software
Database(s)
Application
Procedures
programs
The Information System (Cont.)
System Analysis
Establishes need and extent of an information system
Systems development
Process of creating information system
Database development
Process of database design and implementation
Creation of database models
Implementation
Creating storage structure
Loading data into database
Providing for data management
Systems Development Life Cycle
Database Lifecycle (DBLC)
Phase 1: Database Initial Study
Purposes
Analyze company situation
Operating environment
Organizational structure
Define problems and constraints
Define objectives
Define scope and boundaries
Initial Study Activities
Phase 2: Database Design
Most Critical DBLC phase
Makes sure final product meets requirements
Focus on data requirements
Sub phases
Create conceptual design
DBMS software selection
Create logical design
Create physical design
Two Views of Data
I. Conceptual Design
Data modeling creates abstract data
structure to represent real-world items
High level of abstraction
Four steps
Data analysis and requirements
Entity relationship modeling and normalization
Data model verification
Distributed database design
Data analysis and Requirements
Focus on:
Information needs
Information users
Information sources
Information constitution
Data sources
Developing and gathering end-user data views
Direct observation of current system
Interfacing with systems design group
Business rules
Entity Relationship
Modeling and Normalization
E-R Modeling is Iterative
Concept Design: Tools and
Sources
Data Model Verification
E-R model is verified against proposed system
processes
End user views and required transactions
Access paths, security, concurrency control
Business-imposed data requirements and constraints
Reveals additional entity and attribute details
Define major components as modules
Cohesivity
Coupling
E-R Model Verification Process
Iterative Process of Verification
Distributed Database Design
Design portions in different physical
locations
Development of data distribution and
allocation strategies
II. DBMS Software Selection
DBMS software selection is critical
Advantages and disadvantages need study
Factors affecting purchasing decision
Cost
DBMS features and tools
Underlying model
Portability
DBMS hardware requirements
III. Logical Design
Translates conceptual design into internal model
Maps objects in model to specific DBMS
constructs
Design components
Tables
Indexes
Views
Transactions
Access authorities
Others
IV. Physical Design
Selection of data storage and access
characteristics
Very technical
More important in older hierarchical and network
models
Becomes more complex for distributed systems
Designers favor software that hides physical
details
Physical Organization
Phase 3: Implementation and
Loading
Creation of special storage-related constructs
to house end-user tables
Data loaded into tables
Other issues
Performance
Security
Backup and recovery
Integrity
Company standards
Concurrency controls
Phase 4: Testing and Evaluation
Database is tested and fine-tuned 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
Upgrade or change DBMS software or hardware
Phase 5: Operation
Database considered operational
Starts process of system evaluation
Unforeseen problems may surface
Demand for change is constant
Phase 6: Maintenance and
Evaluation
Preventative maintenance
Corrective maintenance
Adaptive maintenance
Assignment of access permissions
Generation of database access statistics to
monitor performance
Periodic security audits based on systemgenerated statistics
Periodic system usage-summaries
DB Design Strategy Notes
Top-down
1) Identify data sets
2) Define data elements
Bottom-up
1) Identify data elements
2) Group them into data sets
Top-Down vs. Bottom-Up
Centralized vs. Decentralized
Design
Centralized design
Typical of simple databases
Conducted by single person or small team
Decentralized design
Larger numbers of entities and complex
relations
Spread across multiple sites
Developed by teams
Decentralized Design
People Who Deal With Databases
DB Implementers:
build DBMS software e.g. at IBM, Oracle
End Users:
store and use data in a DBMS
Application Programmers:
develop packages facilitating data access for end
users
People Who Deal With Databases
DB Administrators (DBA):
Interact with system users to understand what data
is to be stored & how it will be used
Ensure unauthorized data access not permitted
Ensure if system fails, users can continue accessing
uncorrupted data
Restore data to consistent state
End
