Conceptual Database Design

1
Conceptual Database Design
2
Chapter 14 - Objectives

• Describe 3 database design phases and major steps
  in each of the phases
• Describe the major steps in the conceptual design
  phase in detail
• Define how to document conceptual database
  design.
• Discuss the role of the end user in the design
  process.

3
Representation of a Database Application with
Multiple User Views
4
Critical Success Factors in Database Design

• Work interactively with users as much as
  possible.
• Follow a structured methodology throughout the
  data modeling process.
• Incorporate structural and integrity
  considerations into the data models.
• Use normalization and transaction validation
  techniques into the data modelling methodology.
• Use ER Diagrams to represent as much of the data
  models as possible.
• Use DBDL (Generic SQL) to document requirements
  that cant be expressed in a diagram
• Build a data dictionary to supplement the data
  model diagrams.
• Be willing to repeat steps.

5
Methodology Overview - Conceptual Database Design

• Step 1 Build conceptual data model
• Step 1.1 Identify entity types
• Step 1.2 Identify relationship types
• Step 1.3 Identify and associate attributes with
  entity or relationship types
• Step 1.4 Determine attribute domains
• Step 1.5 Determine candidate and primary key
  attributes
• Step 1.6 Consider use of enhanced modeling
  concepts (optional step)
• Step 1.7 Check model for redundancy
• Step 1.8 Validate conceptual model against user
  transactions
• Step 1.9 Review conceptual data model with user

6
Methodology Overview - Logical Database Design
for Relational Model

• Step 2 Build and validate local logical data
  model
• Step 2.1 Remove features not compatible with the
  relational model (optional step)
• Step 2.2 Derive relations for local logical data
  model
• Step 2.3 Validate relations using normalization
• Step 2.4 Validate relations against user
  transactions
• Step 2.5 Define integrity constraints
• Step 2.6 Review local logical data model with
  user

7
Methodology Overview - Logical Database Design
for Relational Model

• Step 3 Build and validate global logical data
  model
• Step 3.1 Merge local logical data models into
  global model
• Step 3.2 Validate global logical data model
• Step 3.3 Check for future growth
• Step 3.4 Review global logical data model with
  users

8
Methodology Overview - Physical Database Design
for Relational Databases

• Step 4 Translate global logical data model for
  target DBMS
• Step 4.1 Design base relations
• Step 4.2 Design representation of derived data
• Step 4.3 Design enterprise constraints
• Step 5 Design physical representation
• Step 5.1 Analyze transactions
• Step 5.2 Choose file organization
• Step 5.3 Choose indexes
• Step 5.4 Estimate disk space requirements

9
Methodology Overview - Physical Database Design
for Relational Databases

• Step 6 Design user views
• Step 7 Design security mechanisms
• Step 8 Consider the introduction of controlled
  redundancy
• Step 9 Monitor and tune the operational system

10
Conceptual Data Modeling
11
Step 1.1 Identify Entity Types

• PurposeTo identify the main entities that are
  required by the model.
• Look for nouns in user requirements documentation
  (e.g., Event tables)
• Fact-finding techniques should ensure data
  related requirements are uncovered
• Careful about terminology
• Homonym word can have several different
  meanings based on context schedule
• Synonym words that have same meaning branch
  and office
• Data Dictionary is a good idea to define standard
  terms and meanings

12
Step 1.2 Identify Relationship types

• Purpose To identify the important relationships
  that exist between the entity types that have
  been identified.
• Look for verbs/verb phrases
• Staff Manages Property
• Model only important relationships those we
  need to support specific requirements

13
Step 1.2 Identify Relationship types

• Most relationships are binary (2 entities) but
  may be complex relationships (3 or more entities)
  or unary
• Determine multiplicity of each relationship
• Ensure that each entity participates in at least
  one relationship

14
Step 1.3 Identify and associate attributes

• PurposeTo identify and associate attributes with
  the appropriate entity or relationship types and
  document the details of each attribute.
• Determine the facts to be maintained for each
  entity
• Identify
• simple and composite attributes
• single and Multi-valued attributes
• derived attributes

15
Step 1.4 Identify Attribute Domains

• Purpose To determine domains for the attributes
  in the local conceptual model and document the
  details of each domain.
• Domain for each attribute should specify
• Allowable set of values
• Sizes and formats of the attribute

16
Step 1.5 Determine candidate and primary key
attributes

• Purpose To identify the candidate key(s) for
  each entity and if there is more than one
  candidate key, to choose one to be the primary
  key.
• To be a candidate key, attribute value can never
  be NULL (missing)
• Guidelines choosing primary key candidate key
  with
• Smallest set of attributes
• Least likelihood of changing
• Fewest characters (efficiency)
• Smallest maximum value (numerics)
• Ease of use, from user perspective

17
Step 1.6 Consider use of enhanced modeling
concepts (optional step)

• Purpose To consider the use of enhanced
  modeling concepts, such as specialization /
  generalization.
• Rule of thumb use these techniques if they
  enhance clarity or simplify the model.

Owner
Generalization
Business
Private
18
Step 1.7 Check model for redundancy

• Purpose To check for the presence of any
  redundancy in the model.
• Focus on 11 relationships should entities be
  combined?
• Remove redundant relationships

19
Step 1.8 Validate conceptual model against user
transactions

• Purpose To ensure that the local conceptual
  model supports the transactions required
• Transactions use cases events
• Dry run the data model ensure information
  required to support each transaction supported by
  the model
• Often leads to rework that is OK the design
  process is iterative.

20
Step 1.9 Validate Model with Users

• Purpose To review the local conceptual data
  model with the user to ensure that the model is a
  true representation of the users view of the
  enterprise.
• Dont just wait until the conceptual model is
  complete have a set of check points where model
  is validated.
• Review includes diagram and supporting
  documentation

21
Document model in data dictionary
Entity
Relationship
Attribute
22
First-cut ER diagram for DreamHome
23
DreamHome ER Diagram with primary keys added
24
Using Pathways to Check that the Conceptual Model
Supports the User Transactions