Database Systems Design_ Implementation_ and Management

Document Sample
Database Systems Design_ Implementation_ and Management Powered By Docstoc
					                                       8




           Chapter 8

  The University Lab: Conceptual
Design Verification, Logical Design,
       and Implementation

           Hachim Haddouti
                                                           8

       In this chapter, you will learn:
• How the Lab Management System modules are defined
  and refined
• How attributes and domains are identified and
  defined for each of the entities defined in the initial E-R
  model
• How the database transactions are identified and
  defined within the system modules
• That the design verification process uses modeling
  and normalization techniques concurrently to find and
  eliminate data redundancies
• Review the steps of database implementation
• Review the steps of database testing and evaluation
• Review the steps of database operation
         Hachim Haddouti and Rob & Coronel, Ch7                 2
                                                           8
          Completing Conceptual
            and Logical Design
• Detail Matters!
   – Tasks
      •   Entity relationship modeling and normalization
      •   Data model verification
      •   Logical design
      •   Physical design
      •   Implementation
      •   Testing and Evaluation
      •   Operation
   – Primary modules
      • Lab Management System
      • Inventory Management System
      Hachim Haddouti and Rob & Coronel, Ch7                   3
                                         8
              Entities Identified




Hachim Haddouti and Rob & Coronel, Ch7       4
                                            8




Completion of Conceptual Design
          •     Refine module definition
                – Entities
                – Attributes
          • Normalization process
                – Discover new entities
                – Revise attributes




   Hachim Haddouti and Rob & Coronel, Ch7       5
  Lab Management System                  8
    Module E-R Segment




Hachim Haddouti and Rob & Coronel, Ch7       6
                                         8

                     USER Entity




Hachim Haddouti and Rob & Coronel, Ch7       7
                                         8


                      LOG Entity




Hachim Haddouti and Rob & Coronel, Ch7       8
                                         8
    LAB_ASSISTANT Entity




Hachim Haddouti and Rob & Coronel, Ch7       9
                                                8


 WORK_SCHEDULE Entity




                                         Table 8.7



Hachim Haddouti and Rob & Coronel, Ch7               10
                                         8


   HOURS_WORKED Entity




Hachim Haddouti and Rob & Coronel, Ch7       11
                                               8


       RESERVATION Entity




                                         Table 8.9

Hachim Haddouti and Rob & Coronel, Ch7               12
                                          8


Revised RESERVATION Entity




 Hachim Haddouti and Rob & Coronel, Ch7       13
                                         8


  RES_SLOT (Weak) Entity




Hachim Haddouti and Rob & Coronel, Ch7       14
     Inventory Management                8
      Module E-R Segment




Hachim Haddouti and Rob & Coronel, Ch7       15
                                         8

               INV_Type Entity




Hachim Haddouti and Rob & Coronel, Ch7       16
                                         8
                     ITEM Entity




Hachim Haddouti and Rob & Coronel, Ch7       17
                                         8


              STORAGE Entity




Hachim Haddouti and Rob & Coronel, Ch7       18
                                         8


             LOCATION Entity




Hachim Haddouti and Rob & Coronel, Ch7       19
                                         8
                 REPAIR Entity




Hachim Haddouti and Rob & Coronel, Ch7       20
                                         8

                VENDOR Entity




Hachim Haddouti and Rob & Coronel, Ch7       21
                                         8
                 ORDER Entity




Hachim Haddouti and Rob & Coronel, Ch7       22
                                         8


         ORDER_ITEM Entity




Hachim Haddouti and Rob & Coronel, Ch7       23
                                         8
WITHDRAW Entity Revision




Hachim Haddouti and Rob & Coronel, Ch7       24
                                             8
WITHDRAW Entity and Revision




    Hachim Haddouti and Rob & Coronel, Ch7       25
                                         8


    WD_ITEM (Weak) Entity




Hachim Haddouti and Rob & Coronel, Ch7       26
                                           8
CHECK_OUT Design Revision




  Hachim Haddouti and Rob & Coronel, Ch7       27
                                         8

          CHECK_OUT Entity




Hachim Haddouti and Rob & Coronel, Ch7       28
                                           8


CHECK_OUT_ITEM (Weak) Entity




  Hachim Haddouti and Rob & Coronel, Ch7       29
                                                  8

           E-R Model Verification
• Establishes
   – Design reflects end user views of database
   – Database transactions defined and modeled so
     design supports related requirements
   – Design meets output requirements
   – Design supports required input screens and data
     entry forms
   – Design flexible to support future enhancements
• Verification identifies
   – Central entity
   – Each module and its components
   – Each module transaction requirement
     Hachim Haddouti and Rob & Coronel, Ch7            30
                                                   8
         Inventory Management
           Reporting Problems
• Generates three reports; one is inventory
  movement report
   – Inventory movements spread across different
     entities
   – Difficult to generate output and reduces
     performance
• Item “quantity on hand” updated with different
  inventory movements
   – Purchase, withdraw, check-out, check-in, or
     inventory adjustment
   – Only withdrawals and check-outs represented in
     model
    Hachim Haddouti and Rob & Coronel, Ch7             31
                                                     8



     Inventory Management
   Reporting Problems Solution
• Create new entity as common movement entry
  point
• INV_TRANS created
  – Standardizes inventory module interfaces
  – Facilitates control and generation of required
    outputs




    Hachim Haddouti and Rob & Coronel, Ch7               32
                                                       8

Inventory Transaction Process




                                         Figure 8.25

Hachim Haddouti and Rob & Coronel, Ch7                     33
                                                8
              INV_TRANS Entity




                                         Table 8.26
Hachim Haddouti and Rob & Coronel, Ch7                34
                                         8


     TR_ITEM (Weak) Entity




Hachim Haddouti and Rob & Coronel, Ch7       35
                                               8
Revised University Computer Lab ERD




      Hachim Haddouti and Rob & Coronel, Ch7       36
                                                  8


                      Logical Design
• Translates conceptual model to format for
  selected DBMS
• Sets stage for creating table structures, indexes,
  and views
• Table structures can be created with CREATE
  TABLE SQL commands
• Views created with CREATE VIEW SQL
  Commands
• Indexes created with CREATE INDEX SQL
  Commands

      Hachim Haddouti and Rob & Coronel, Ch7           37
                                               8




                   Physical Design
• Defines specific storage or access methods
  used by database
• Includes estimate of storage space
• Characteristics are function of DBMS and
  operating systems




    Hachim Haddouti and Rob & Coronel, Ch7         38
                                                   8
                    Implementation
• Database administrator (DBA)
  – Controls database management function
  – Defines standards and procedures required to
    interact with the database
  – Adopts appropriate plan
• Plan elements
  – Definitions of processes and standards
  – Chronology of required activities
      • Database creation
      • Loading and Conversion
  – Documentation standards
  – Responsibilities for continued development and
    maintenance
     Hachim Haddouti and Rob & Coronel, Ch7            39
                                             8




         Testing and Evaluation
• Determine how well database meets goals
• Ongoing process
• Considerations
  – Performance measures
  – Security
  – Backup and recovery procedures




    Hachim Haddouti and Rob & Coronel, Ch7       40
                                                  8



                            Operation
• Provides support for daily operations
• Maintains operational procedures
• Database maintenance and evolution
   – DBA performs technical and managerial duties to
     ensure proper operation of database to support
     organizational mission




     Hachim Haddouti and Rob & Coronel, Ch7            41

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:20
posted:9/23/2012
language:English
pages:41