Database Design Conceptual Data Modeling - PowerPoint PPT Presentation

Loading...

PPT – Database Design Conceptual Data Modeling PowerPoint presentation | free to view - id: 8c116-ZDc1Z



Loading


The Adobe Flash plugin is needed to view this content

Get the plugin now

View by Category
About This Presentation
Title:

Database Design Conceptual Data Modeling

Description:

How to validate resultant conceptual model to ensure it is a ... in the E-R model corresponds to a ... unique identifier (will become a key) attributes ... – PowerPoint PPT presentation

Number of Views:62
Avg rating:3.0/5.0
Slides: 39
Provided by: isabellebi
Category:

less

Write a Comment
User Comments (0)
Transcript and Presenter's Notes

Title: Database Design Conceptual Data Modeling


1
Database DesignConceptual Data Modeling
2
Objectives
  • Purpose of a design methodology.
  • Database design has three main phases
    conceptual, logical, and physical design.
  • How to decompose the scope of the design into
    specific users views of the enterprise.
  • How to use ER modeling to build a local
    conceptual data model based on information given
    in a view of the enterprise.

3
Objectives
  • How to validate resultant conceptual model to
    ensure it is a true and accurate representation
    of a view of the enterprise.
  • How to document process of conceptual database
    design.
  • End-users play an integral role throughout
    process of conceptual database design.

4
Acknowledgments
  • These slides have been adapted from Thomas
    Connolly and Carolyn Begg

5
Design Methodology
  • Structured approach that uses procedures,
    techniques, tools, and documentation aids to
    support and facilitate the process of design.
  • Database design methodology has 3 main phases
  • Conceptual database design
  • Logical database design
  • Physical database design.

6
Preliminary Phase Database Initial Study
  • Purposes
  • Analyze company situation
  • Operating environment
  • Organizational structure
  • Define problems and constraints
  • Define objectives
  • Define scope and boundaries

7
Business Rules
  • What are business rules, what is their source,
    and why are they crucial?
  • Business rules are precise statements, derived
    from a detailed description of the organization's
    operations, that define one or more of the
    following modeling components
  • entities - in the E-R model corresponds to a
    table
  • relationships are associations between
    entities
  • attributes are characteristics of entities
  • connectivities are used to describe the
    relationship classification
  • cardinalities express the specific number of
    entity occurrences associated with one occurrence
    of the related entity
  • constraints limitations on the type of data
    accepted

8
Business Rules
  • Examples of business rules are
  • An invoice contains one or more invoice lines,
    but each invoice line is associated with a single
    invoice.
  • A store employs many employees, but each employee
    is employed by only one store.
  • A college has many departments, but each
    department belongs to a single college. (This
    business rule reflects a university that has
    multiple colleges such as Business, Liberal Arts,
    Education, Engineering, etc.)
  • A driver may be assigned to drive many different
    vehicles, and each vehicle can be driven by many
    drivers. (Note Keep in mind that this business
    rule reflects the assignment of drivers during
    some period of time.)
  • A client may sign many contracts, but each
    contract is signed by only one client.

9
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

10
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 programs
  • Procedures

11
The Information System
  • System Analysis
  • Establishes need and extent of an information
    system
  • System 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

12
Database Lifecycle (DBLC)
Figure 6.3
13
Initial Study Activities
14
Use case diagram
15
Conceptual/Logical Database Design
  • Conceptual database design
  • Process of constructing a model of information
    used in an enterprise, independent of all
    physical considerations.
  • Logical database design
  • Process of constructing a model of information
    used in an enterprise based on a specific data
    model (e.g. relational), but independent of a
    particular DBMS and other physical
    considerations.

16
Physical Database Design
  • Process of producing a description of the
    implementation of the database on secondary
    storage it describes the base relations, file
    organizations, and indexes design used to achieve
    efficient access to the data, and any associated
    integrity constraints and security measures.

17
Critical Success Factors in Database Design
  • Work interactively with users as much as
    possible.
  • Follow a structured methodology throughout the
    data modeling process.
  • Employ a data-driven approach.
  • Incorporate structural and integrity
    considerations into the data models.
  • Combine conceptualization, normalization, and
    transaction validation techniques into the data
    modeling methodology.

18
Critical Success Factors in Database Design
  • Use diagrams to represent as much of the data
    models as possible.
  • Use a Database Design Language (DBDL) to
    represent additional data semantics.
  • Build a data dictionary to supplement the data
    model diagrams.
  • Be willing to repeat steps.

19
Methodology Overview - Conceptual Database Design
  • Step 1 Build local conceptual data model for
    each user view
  • 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 unique identifier (will
    become a key) attributes
  • Step 1.6 Consider use of enhanced modeling
    concepts (optional step)
  • Step 1.7 Check model for redundancy
  • Step 1.8 Validate local conceptual model
    against user transactions
  • Step 1.9 Review local conceptual data model
    with user

20
Class diagram
21
Methodology Overview - Logical Database Design
for Relational Model
  • Step 2 Build and validate local logical data
    model for each view
  • 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

22
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
    against the conceptual data
    model
  • Step 3.3 Check for future growth
  • Step 3.4 Review global logical data model with
    users

23
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

24
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

25
Step 1 Build Local Conceptual Data Model for Each
View
  • To build a local conceptual data model of an
    enterprise for each specific view.
  • Step 1.1 Identify entity types
  • To identify the main entity types that are
    required by the view.
  • Step 1.2 Identify relationship types
  • To identify the important relationships that
    exist between the entity types that have been
    identified.

26
Step 1 Build Local Conceptual Data Model for Each
View
  • Step 1.3 Identify and associate attributes with
    entity or relationship types
  • To identify and associate attributes with the
    appropriate entity or relationship types and
    document the details of each attribute.
  • Step 1.4 Determine attribute domains
  • To determine domains for the attributes in the
    local conceptual model and document the details
    of each domain.

27
Step 1 Build Local Conceptual Data Model for Each
View
  • Step 1.5 Determine candidate and unique
    identifier attributes
  • To identify the candidate key(s) for each entity
    and if there is more than one candidate key, to
    choose one to be the unique identifier.
  • Step 1.6 Consider use of enhanced modeling
    concepts (optional step)
  • To consider the use of enhanced modeling
    concepts, such as specialization /
    generalization, aggregation, and composition.

28
Step 1 Build Local Conceptual Data Model for Each
View
  • Step 1.7 Check model for redundancy
  • To check for the presence of any redundancy in
    the model.
  • Step 1.8 Validate local conceptual model against
    user transactions
  • To ensure that the local conceptual model
    supports the transactions required by the view.
  • Step1.9 Review local conceptual data model with
    user
  • 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.

29
Extract from Data Dictionary for Staff View of
DreamHome Showing Description of Entities
30
First-cut ER diagram for Staff View of DreamHome
31
Extract from Data Dictionary for Staff View of
DreamHome Showing Description of Relationships
32
Extract from Data Dictionary for Staff View of
DreamHome Showing Description of Attributes
33
ER Diagram for Staff View of DreamHome with
Unique Identifiers Added
34
Revised ER Diagram for Staff View of DreamHome
with Specialization / Generalization
35
Example of a Non-Redundant Relationship FatherOf
36
Using Pathways to Check that the Conceptual Model
Supports the User Transactions
37
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

38
E-R Modeling is Iterative
About PowerShow.com