Chapter 6 Database Management

							                                                                6
                                Chapter 6



         Database Design 2:
         Design Methodology



Concepts of Database Management, 4th Edition, Pratt & Adamski       1
                                     Objectives                  6
 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
 Concepts of Database Management, 4th Edition, Pratt & Adamski       2
                                                                 6
                                     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
 Concepts of Database Management, 4th Edition, Pratt & Adamski       3
                                     Objectives                  6

 Review existing documents to obtain
 information from users prior to beginning
 the database design process
 Discussspecial issues related to
 implementing one-to-one relationships
 and many-to-many relationships involving
 more than two entities
 Discuss entity subtypes and their
 relationships to nulls
      how to avoid potential problems
 Learn
 when merging third normal form relations
 Concepts of Database Management, 4th Edition, Pratt & Adamski       4
                                                                   6
                              Database Design
 User       Views
     Requirements necessary to support a particular
      user’s operations
 Information-level                          Design Methodology
     Represent user view as collection of tables
     Normalize these tables
     Identify all keys
     Merge the result into design




   Concepts of Database Management, 4th Edition, Pratt & Adamski       5
                                                                  6
                  Represent User View as
                   Collection of Tables

Step 1:                 Determine entities involved and
                        create separate table for each
                        type
Step 2:                 Determine primary key for each
                        table
Step 3:                 Determine properties for each
                        entities
Step 4:                 Determine relationships among
                        entities
  Concepts of Database Management, 4th Edition, Pratt & Adamski       6
                                                                  6
                      Normalize the Tables


 Represent                  all keys
      Primary, alternate, secondary, foreign
 Database                Design Language (DBDL)
      Mechanism for representing tables and keys




  Concepts of Database Management, 4th Edition, Pratt & Adamski       7
                                                                6
                              DBDL Notation

 Table name followed by columns in
   parentheses
         Primary key column(s) underlined
 AK identifies alternate keys
 SK identifies secondary keys
 FK identifies foreign keys




Concepts of Database Management, 4th Edition, Pratt & Adamski       8
                                                                6
        Entity-Relationship Diagrams
                  Figure 6.2




Concepts of Database Management, 4th Edition, Pratt & Adamski       9
                                                                6
 Merge the Result into the Design
            Figure 6.3




Concepts of Database Management, 4th Edition, Pratt & Adamski       10
                                                                 6
                      User View Examples



View #1: Sales Rep View


Rep (RepNum, LastName, FirstName, Street,
City, State, Zip, Commission, Rate)




 Concepts of Database Management, 4th Edition, Pratt & Adamski       11
                                                                  6
            User View Examples (con’t.)
                    Figure 6.5




View #2: Customer View


  Concepts of Database Management, 4th Edition, Pratt & Adamski       12
                                                                  6
            User View Examples (con’t.)
                    Figure 6.6




View #3: Part View

  Concepts of Database Management, 4th Edition, Pratt & Adamski       13
                                                                    6
              User View Examples (con’t.)
                      Figure 6.8




View #4: Order View

    Concepts of Database Management, 4th Edition, Pratt & Adamski       14
                                                                 6
     Second Set User View Examples



View #1: Publisher View


Publisher (PublisherCode, PublisherName, City)
      SK PublisherName




 Concepts of Database Management, 4th Edition, Pratt & Adamski       15
                                                                 6
     Second Set User View Examples
                (con’t.)

View #2: Branch View

Publisher (PublisherCode, PublisherName, City)
      SK PublisherName

Branch (BranchNum, BranchName,
BranchLocation, NumEmployees)
      SK BranchName



 Concepts of Database Management, 4th Edition, Pratt & Adamski       16
                                                                   6
                   Second Set User
                View Examples (con’t.)
                     Figure 6.11




View #3: Book View
   Concepts of Database Management, 4th Edition, Pratt & Adamski       17
                                                                     6
                         Second Set User
                      View Examples (con’t.)
                           Figure 6.12




View #4: Author View
     Concepts of Database Management, 4th Edition, Pratt & Adamski       18
                                                                     6
                          Second Set User
                       View Examples (con’t.)
                            Figure 6.13




View #5 and View #6: Inventory View
     Concepts of Database Management, 4th Edition, Pratt & Adamski       19
                                                                  6
                    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




  Concepts of Database Management, 4th Edition, Pratt & Adamski       20
                                                                6
         ERD Relationship Alternative
                 Figure 6.14




Concepts of Database Management, 4th Edition, Pratt & Adamski       21
                                                                6
                   Symbols for Columns
                       Figure 6.15




Concepts of Database Management, 4th Edition, Pratt & Adamski       22
                                                                6
                          Composite Entity
                            Figure 6.16




Concepts of Database Management, 4th Edition, Pratt & Adamski       23
                                                                6
                      Crow’s Foot Symbol
                          Figure 6.17




Concepts of Database Management, 4th Edition, Pratt & Adamski       24
                                                                6
          Representing Cardinality
                Figure 6.18




Concepts of Database Management, 4th Edition, Pratt & Adamski       25
                                                                 6
                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




 Concepts of Database Management, 4th Edition, Pratt & Adamski       26
                                                                 6
                                 Survey Form


 Used to collect information from users
 Must contain particular elements
      Entity information
      Attribute information
      Relationships
      Functional dependencies
      Processing information




 Concepts of Database Management, 4th Edition, Pratt & Adamski       27
                                                                  6
                        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



  Concepts of Database Management, 4th Edition, Pratt & Adamski       28
    1:1 Relationship Considerations                             6
               Figure 6.25




     Include primary key of
     each table as foreign
     key in the other
Concepts of Database Management, 4th Edition, Pratt & Adamski       29
         1:1 Relationship Considerations                             6
                    Figure 6.26




Implementation when
information does not
match.
     Concepts of Database Management, 4th Edition, Pratt & Adamski       30
          1:1 Relationship Considerations                            6
                     Figure 6.27




Implemented in a single
table.



     Concepts of Database Management, 4th Edition, Pratt & Adamski       31
         1:1 Relationship Considerations                             6
                    Figure 6.28




1:1 relationship implemented by
including primary key of one table
as foreign key (and alternative key)
in the other.
     Concepts of Database Management, 4th Edition, Pratt & Adamski       32
       M:M Relationship Considerations                               6
                 Figure 6.29




Sample Sales Data
     Concepts of Database Management, 4th Edition, Pratt & Adamski       33
       M:M Relationship Considerations                               6
                 Figure 6.30




Result obtained by splitting
Sales table into three tables

     Concepts of Database Management, 4th Edition, Pratt & Adamski       34
  M:M Relationship Considerations                               6
            Figure 6.31




  Result obtained by joining three tables--2 rows
  are in error. Must be converted to 4NF.
Concepts of Database Management, 4th Edition, Pratt & Adamski       35
                                                                                    6
              Table Split to Avoid Nulls
                     Figure 6.32




                                                                Nulls are absence
                                                                of values
Concepts of Database Management, 4th Edition, Pratt & Adamski                           36
                            Entity Subtypes                     6
                              Figure 6.34




Concepts of Database Management, 4th Edition, Pratt & Adamski       37
                                                                6
                   Student Table Split to
                       Avoid Nulls
                       Figure 6.36




Concepts of Database Management, 4th Edition, Pratt & Adamski       38
                                                                6
                  Two Entity Subtypes—
                  Incomplete Categories
                       Figure 6.37




Concepts of Database Management, 4th Edition, Pratt & Adamski       39
                                                                6
                  Two Entity Subtypes—
                   Complete Categories
                       Figure 6.38




Concepts of Database Management, 4th Edition, Pratt & Adamski       40