Database Design Procedures

1
Database Design Procedures

• Yong Choi
• School of Business
• CSUB

2
(No Transcript)
3
Phase 1 Database Initial Study

• Purposes
• Analyze company situation
• Operating environment
• Organizational structure
• Define problems and constraints
• Define objectives
• Define scope and boundaries

4
Initial Study Activities
5
Phase 2 Database Design

• Most Critical DBLC phase
• Makes sure final product meets requirements
• Focus on data requirements
• Subphases
• Create conceptual design
• DBMS software selection
• Create logical design
• Create physical design

6
Two Views of Data
7
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

8
Data analysis and Requirements

• Focus on
• Information needs
• What output (reports and queries) must be
  generated by the system? What information dose
  the current system generate and to what extent is
  that information adequate?
• Information users
• Who will use the information? How is the
  information to be used? What are the different
  end-user views?

9
Data analysis and Requirements

• Focus on
• Information sources
• Where is the information to be found? How is the
  information to be extracted once it is found?
• Information constitution
• What data elements are needed to produce the
  information? What are the data attributes? What
  relationships exist among the data? What is the
  data volume How frequently are the data used?
  What data transformations are to be used to
  generate the required information?

10
Data analysis and Requirements

• Data sources
• Developing and gathering end-user data views
• Direct observation of current system
• Interfacing with systems design group
• Draft business rules based on a Draft ER model

11
E-R Modeling is Iterative
12
Concept Design Tools and Sources
13
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

14
E-R Model Verification Process
15
Iterative Process of Verification
16
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

17
Top-Down vs. Bottom-Up