Notes
Slide Show
Outline
1
Chapter 6
  • Database Design 2:
  • Design Methodology
2
Objectives
  • Discuss the general process and goals of database design


  • Define user views and explain their function


  • Define database design language and use it to document database designs


  • Create an entity-relationship diagram to visually represent a database design


  • Present a methodology for database design at the information level and view examples illustrating this methodology
3
Objectives

  • Explain the physical-level design process


  • Examine some alternative approaches to entity-relationship diagrams


  • Discuss top-down and bottom-up approaches to database design and examine the advantages and disadvantages of both methods


  • Use a survey form to obtain information from users prior to beginning the database design process


4
Database Design
  • User Views
    • Requirements necessary to support a particular user’s operations
5
Information-level Design Methodology
6
Normalize the Tables
  • Represent all keys
    • Primary, alternate, secondary, foreign
  • Database Design Language (DBDL)
    • Mechanism for representing tables and keys
    • Table name followed by columns in parentheses
      • Primary key column(s) underlined
      • AK identifies alternate keys
      • SK identifies secondary keys
      • FK identifies foreign keys
7
Entity-Relationship Diagrams
Figure 6.2
8
Merge the Result into the Design
Figure 6.3
9
Physical-Level Design
  • Undertaken after information-level design completion
  • Most DBMSs support primary, candidate, secondary, and foreign keys
  • DB programmers must include logic to ensure the uniqueness of primary keys and enforce other conditions
10
ERD Relationship Alternative
Figure 6.14
11
 
12
 
13
 
14
 
15
Top-Down vs. Bottom-Up
  • Bottom-up
    • Design starts at low level
    • Specific user requirements drive design process
  • Top-down
    • Begins with general database that models overall enterprise
    • Refines the model until design is achieved
16
Survey Form
  • Used to collect information from users
  • Must contain particular elements
    • Entity information
    • Attribute information
    • Relationships
    • Functional dependencies
    • Processing information
17
Existing Documents
  • Aid in collecting user requirements
  • Collect information similar to that collected with survey forms
    • Entity information
    • Attribute information
    • Relationships
    • Functional dependencies
    • Processing information
18
1:1 Relationship Considerations Figure 6.25
19
1:1 Relationship Considerations Figure 6.26
20
1:1 Relationship Considerations Figure 6.27
21
1:1 Relationship Considerations Figure 6.28
22
M:M Relationship Considerations Figure 6.29
23
M:M Relationship Considerations Figure 6.30
24
M:M Relationship Considerations Figure 6.31
25
Table Split to Avoid Nulls
Figure 6.32
26
Entity Subtypes
Figure 6.34
27
Student Table Split to
Avoid Nulls
Figure 6.36
28
Two Entity Subtypes—
Incomplete Categories
Figure 6.37
29
Two Entity Subtypes—
Complete Categories
Figure 6.38