CS457 Database Design

1

• CS457 Database Design
• Chapter 12.2

2

• Complete Academic misconduct policy

3
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

4
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.

5
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

6

Conceptual DB design contd

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

7

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

8

Conceptual DB design contd

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

9

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

10
3. Choice of a DBMS

• What model and what software?
• Choosing a data model Relational,
  Object-oriented, object-relational,
• extended relational
• Choosing a specific DBMS based on model
• Relational Access, Oracle, Ingres, SQL server,
  Sybase
• Object-oriented Gemstone, Object Store, Poet

11
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

12
5. Physical DB design

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

13
6. DB system implementation

• Final steps of design process
• Create DDL for DB in DBMS
• Load the data into the DB