COMP255
Chapter 9
Database Design
1
Learning Objectives
●
Describe the role of database design as the foundation of a successful
information system
●
Describe the five phases in the Systems Development Life Cycle (SDLC)
●
Design databases using the six phases in the Database Life Cycle (DBLC)
framework
●
Conduct evaluation and revision within the SDLC and DBLC frameworks
●
Distinguish between top-down and bottom-up approaches in database design
●
Distinguish between centralized and decentralized conceptual database
design
2
Ultimate Goal
●
Understanding where databases fit into the
world
●
Start with some definitions
– Information system
– Systems analysis
– Systems development
3
Information System
●
A system that provides for data collection,
storage, and retrieval; facilitates the
transformation of data into information; and
manages both data and information. An
information system is composed of hardware,
the DBMS and other software, database(s),
people, and procedures.
4
More Definitions
●
Systems analysis
– The process that establishes the need for an information
system and its extent
●
Systems development
– The process of creating an information system
●
Database development
– Process of database design and its implementation
5
Information System
6
Performance Factors of an IS
●
Database design and implementation
●
Application design and implementation
●
Administrative procedures
7
SDLC
●
Systems Development Life Cycle
– Traces the history of an information system
– Provides the big picture within which database
design and application development can be
mapped out and evaluated
8
Traditional SDLC
9
Phases of the SDLC
●
Planning
– Yields a general overview of the company and its
objectives
●
Analysis
– Problems defined during planning phase are
examined in greater detail
10
Phases of the SDLC
●
Detailed systems design
– Designer completes the design of the system’s
processes
●
Implementation
– Hardware, DBMS software, and application programs
are installed, and the database design is
implemented
11
Phases of the SDLC
●
Maintenance
– Corrective, adaptive, and perfective
12
More on SDLC
●
Traditional SDLC ●
Agile Methods
– Called the waterfall – Based on iterations
– Only move to next – Do smaller SDLC
phase when previous steps
phase is complete – Lower cost of change
– High cost of change
13
Database Life Cycle
●
Similar to the SDLC but adapted to databases
●
Part of the SDLC for information systems with
databases
14
DBLC
15
Phases of the DBLC
●
Database initial study
– Define problems, constraints, objectives, scope,
and boundaries
●
Database design
– Making sure that the final product meets user and
system requirements
16
Phases of the DBLC
●
Implementation and loading
– DBMS is installed, database is created, and data is
loaded or converted
●
Testing and evaluation
– Database is tested, fine-tuned, and evaluated
17
Phases of the DBLC
●
Operation
– Problems are identified and solutions implemented
●
Maintenance and evolution
– Preventative, corrective, adaptive, etc.
18
SDLC and DBLC
19
Phase 2: Database Design
20
Conceptual Design
●
Goal
– Design a database independent of database software and physical
details
●
Conceptual data model
– Describes main data entities, attributes, relationships, and constrains
– Designed as software and hardware independent
●
Minimum data rule:
– All that is needed is there, and all that is there is needed
21
Steps in Conceptual Design
●
Data analysis and requirements
– Designers efforts are focused
– Information needs, users, sources and constitution
– Answers obtained from a variety of sources
●
Developing and gathering end-user data views
●
Directly observing current system: existing and desired output
●
Interfacing with the systems design group
22
Steps in Conceptual Design
●
Entity relationship modeling and normalization
– All objects (entities, attributes, relations, views, and
so on) are defined in a data dictionary, which is
used in tandem with the normalization process
23
Steps in Conceptual Design
●
Data model verification
– Verified against proposed system processes
– Run through a series of tests
24
Steps in Conceptual Design
●
Distributed database design
– Portions of database may reside in different
physical locations
– Database fragment: subset of a database stored at
a given location
– Ensures database integrity, security, and
performance
25
DBMS Selection
●
Factors that affect the purchasing
– Cost
– DBMS features and tools
– Underlying model
– Portability
– DBMS hardware requirements
– Support
26
Logical Design
●
Goal
– Design an enterprise-wide database that is based on
a specific data model but independent of physical-
level details
●
Requires that all objects in the conceptual model
be mapped to the specific constructs used by the
selected database model
27
Steps in Logical Design
●
Map the conceptual model to the logical model
– Map strong entities
– Map supertype/subtype relationships
– Map weak entities
– Map binary relationships
– Map higher-degree relationships
28
Steps in Logical Design
●
Validate the logical model
– Using normalization
– Integrity constraints
– Against the user requirements
29
Physical Design
●
Process of data storage organization and data access
characteristics of the database; ensures integrity, security,
and performance
●
Domain of the database administrator
●
Define data storage organization
●
Define integrity and security measures
●
Determine performance measures
30
Design Strategies
31
Centralized Design
●
A process by which all database design decisions are carried
out centrally by a small group of people. Suitable in a top-
down design approach when the problem domain is relatively
small, as in a single unit or department in an organization.
32
Decentralized Design
●
A process in which conceptual
design models subsets of an
organization’s database
requirements, which are then
aggregated into a complete
design. Such modular designs
are typical of complex systems
with a relatively large number
of objects and procedures.
33