Database Design - PowerPoint PPT Presentation

1 / 18
About This Presentation
Title:

Database Design

Description:

Employees in company work for a department and work on project. Projects are associated with a department ... Department controls a number of projects ... – PowerPoint PPT presentation

Number of Views:34
Avg rating:3.0/5.0
Slides: 19
Provided by: susanv5
Category:
Tags: database | design

less

Transcript and Presenter's Notes

Title: Database Design


1
  • Database Design
  • Chapter 12.2

2
Database design steps
  • Requirement collections and analysis
  • Conceptual database design
  • Choice of a DBMS
  • Data model mapping
  • Physical DB design
  • DB system implementation
  • In-class database

3
1. Requirement collection analysis
  • What do you want from your DBMS?
  • Major application areas and user groups
    identified
  • Interviews, questionnaires collected
  • Documentation analyzed
  • Operating environment and planned use studied
  • Description of typical operations to data
  • E.g. queries, updates, etc.

4
2. Conceptual DB design
  • Conceptual schema design
  • Big picture data organization
  • Provides DBMS independent understanding
  • Stable description
  • Tool for users
  • Conceptual DB model used to represent conceptual
    schema
  • Results are a diagram based on conceptual DB
    model, e.g. ER, EER, UML diagram

5

Conceptual DB design contd
  • Approaches to Conceptual Schema Design
  • One shot
  • All requirements merged before design
  • Reconcile differences

6

Conceptual DB design contd
  • View integrated
  • Every user group define own schema
  • Merged to provide global conceptual schema
  • Requires data integration
  • Identify and resolve
  • name synonym, homonym
  • Type set vs. attributes
  • Domain char vs. int
  • Constraints key
  • Merge views
  • Remove redundancies

7

Conceptual DB design contd
  • View integration strategies (fig. 12.6)
  • Binary Ladder
  • N-ary integration
  • Binary balanced
  • mixed

8

Conceptual DB design contd
  • Transaction design
  • Big picture data usage
  • Identifies characteristics of
  • Retrieval, update, mixed
  • Ensures schema includes all info required
  • Conceptual DB model used to represent conceptual
    schema
  • Results are a diagram based on conceptual DB
    model, e.g. ER/EER, UML diagram

9
3. Choice of a DBMS
  • What model and what software?
  • Choosing a data model Relational,
    Object-oriented, object-relational
  • Choosing a specific DBMS based on model
  • Relational Access, Ingres (open source)
  • Object-Relational (support SQL1999) Oracle,
    SQL-Server, DB2, CA-Ingres, Sybase, PostgreSQL
    (open source)
  • Object-oriented ObjectStore, Versant (VSNT),
    Objectivity/DB, (commercial version of ORION)

10
4. Data model mapping
  • Creating the final blueprint
  • Create conceptual schema and external schema in
    data model
  • System independent mapping (ER to relational)
  • Tailoring schema to specific DBMS

11
5. Physical DB design
  • Internal, low-level details
  • Choose storage structures for files and access
    paths
  • Based on analysis of queries, transactions, etc.
  • Indexing, clustering related records, hashing,
    etc.

12
6. DB system implementation
  • Final steps of design process
  • Create DDL for DB in DBMS
  • Load the data into the DB

13
Mini-world is company DB initial requirements
  • Company has departments and department managers
  • Employees in company work for a department and
    work on project
  • Projects are associated with a department
  • Employee has name, SSN, address, salary, sex,
    birth date
  • For each project keep track of number of hours
    worked on project
  • Maintain information about dependents for
    insurance

14
Company DB requirements
  • Company is organized into departments
  • Each department has a unique name, unique number
    and an employee who manages the department
  • Keep track of the start date when employee began
    managing department
  • Department has several locations

15
Company DB requirements
  • Department controls a number of projects
  • Each project has a unique name, unique number and
    a single location

16
Company DB requirements
  • Each employee has a name, SSN, address, salary,
    sex, and birth date
  • Employee is assigned to one department but works
    on several projects which are not necessarily
    controlled by the same department
  • Keep track of the number of hours per week an
    employee works on each project
  • Keep track of supervisor of each employee

17
Company DB requirements
  • Want to keep track of the dependents of each
    employee for insurance
  • Keep each dependents first name, sex, birth
    date, relationship to employee

18
Missing information in requirements
  • Planned use? User groups?
  • Typical operations?
Write a Comment
User Comments (0)
About PowerShow.com