Database Systems: Design, Implementation, and Management by Q51OE9

VIEWS: 194 PAGES: 40

									                                                7




               Chapter 7


The University Lab: Conceptual
           Design

Database Systems: Design, Implementation, and
 Management, Fifth Edition, Rob and Coronel
                                                                                     7



                 In this chapter, you will learn:
         • How to develop a database initial study
         • How to write a description of operations
         • How to write business rules on which the
           database design is based
         • How to translate the business rules into ERD
           segments
         • How to put the ERD segments together to create
           the initial ERD


Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       2
                                                                                     7

                            Database Initial Study
    • Detailed description of organization’s current and
      proposed superscript database system
      environments
           – Organizational
                  •   Objectives
                  •   Structure
                  •   Operations
                  •   Problems
           – System
                  •   Objectives
                  •   Scope and boundaries
                  •   Information sources and users
                  •   End-user requirements
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       3
                                                                                     7

                     UCL Database Initial Study
             • Users
                    –   Assistant Dean, College of Business
                    –   Computer Lab Director
                    –   Computer Lab Assistants
                    –   Computer Lab Secretary
                    –   Computer Lab Graduate Assistants
             • Objectives
                    – Provide users with controlled access to UCL
                      assets
                    – Guide users working with assets and provide
                      problem-solving services

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       4
                                                                                                  7

               UCL’s Organizational Structure




                                                                                     Figure 7.1
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                    5
                                                                                           7

               UCL Description of Operations
          • Inventory, storage, order management
                 – Classifications: hardware, software, literature,
                   supplies
                 – Serialized vs. nonserialized items




                                                                                     Table 7.2


Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel               6
                                                                                     7

    UCL Description of Operations (con’t.)
    • Equipment maintenance and repair management
           – Information kept in Bad Equipment Log and Hardware
             Returned for Service Log
    • Equipment check-out and check-in management
           – Form completed when equipment checked out
           – Notice sent for late equipment
    • Lab assistant payroll management
           – Time sheets used to track assistants
           – Hourly basis for a fourteen day period
    • Lab reservation management
           – Faculty member completes reservation form with date, time,
             department, and course number
    • Computer lab access management
           – Lab users sign users’ log and leave University ID card
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       7
                                                                                     7

                  Common Problems and
                Constraints in Current System
       • Never up to date and error-ridden
       • Too much data duplication and inconsistency
       • Does not generate useful information
       • Does not allow ad hoc queries
       • CLD spends too much time processing data
         manually
       • Lack of computerization makes inventory
         management difficult


Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       8
                                                                                     7


                                  Specific Problems
       • Inventory/Storage/Order Management
              – No access to crucial inventory management data
                for CLD
              – UCL needs available stock figures & average use
                of supplies
              – CLD does not know item location
       • Equipment maintenance and repair management
              – CLD cannot generate history for equipment
              – CLD determine the status of items subject to
                maintenance procedures

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       9
                                                                                     7
                      Specific Problems (con’t.)
          • Equipment check-out/check-in management
                 – CLD lacks information about lab assets
          • Lab assistant payroll management
                 – CLD spends too much time summarizing hours
                   worked LAs
                 – CLD cannot estimate work loads
          • Lab reservations management
                 – Manual reservation system inadequate
                 – No statistical information for scheduling lab
                   reservations
          • Computer lab access management
                 – User log not properly maintained
                 – Certain items not returned
                 – Security problems
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       10
                                                                                     7

                               Constraints for UCL
             • Time Frame
                    – Operational within 3 months
             • Hardware and Software
                    – Use existing UCL hardware and software
             • Distributed Aspects and Expandability
                    – Operate in multiuser environment
                    – Independent of existing administrative systems
             • Cost
                    –   Development costs must be minimal
                    –   Use no more than two additional terminals
                    –   Operate without additional personnel
                    –   $9,500 is available
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       11
                                                                                     7

                                  Objectives for UCL
                 • General System Objectives
                       – Improve operational efficiency
                       – Provide useful information for planning,
                         control, and security
                 • Specific Objectives
                       – Inventory/Storage/Order Management
                              •   Better purchase order control
                              •   Monitor supply item stock
                              •   Control inventory by type and item
                              •   Quick information about item location
                              •   Timely information about use of supplies and
                                  generate stats to aid in future purchases
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       12
                                                                                     7


         Specific Objectives for UCL (con’t.)
   • Equipment maintenance and repair management
          – Monitor maintenance histories
          – Track items returned to vendor
   • Equipment check-out/check-in management
          – Tracks items checked out
          – Monitor item check-out time
          – Generate usage statistics
   • Lab assistant payroll management
          – Scheduling and work loads
          – Work summaries
Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       13
                                                                                     7




         Specific Objectives for UCL (con’t.)
             • Lab reservations management
                    – Decrease reservation processing time
                    – Produce reservation schedules
                    – Generate statistical summaries
             • Computer lab access management
                    – Control users and lab resources
                    – Reduce sign-in time
                    – Provide peak use information for scheduling

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       14
                                                                                     7

                           Scope and Boundaries
             • What will be system’s extent?
                    – UCL portion of organizational chart
                    – Independent of other systems
             • What operational areas are covered by system?
                    – Limited to six areas addressed earlier
             • What design/ implementation strategy to use?
                    – Organization into system modules
             • What modules should be included in system?
                    – Lab management
                    – Inventory management
             • How do modules interface?
                    – Through CHECK_OUT module

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       15
                                                                                     7

                          Modules and Interfaces




              Figure 7.2




             Table 7.4


Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       16
                                                                                     7


                   Conceptual Design:
              General Systems Requirements
                 •    Easy to use
                 •    Provide security measures
                 •    Fully integrated
                 •    Able to access system concurrently
                 •    Perform various functions
                       – Personnel, inventory, order, maintenance,
                         reservation, Check_out, and access
                 • Input driven by output

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       17
                                                                                          7

                      Conceptual Design Phase
                    Confirmation of good data sources




                                                                         From Table 7.5



Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel            18
                                                                                     7




Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       19
                                                                                                  7




Conceptual Design
Phase: User Needs
                                                                                     Figure 7.3

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                    20
                                                                                     7




Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       21
                                                                                            7
                              Conceptual Design
                             Phase: Initial Entities




                                                                                     Table 7.8

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel               22
                                                                                     7




Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       23
                                                                                          7

                                    Business Rule 1




                                                                                     Figure 7.4


Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel            24
                                                                                     7
                                    Business Rule 2




                                            Figure 7.5

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       25
                                                                                                  7
                                      Business Rule 3




                                                                                     Figure 7.6

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                    26
                                                                                                  7

                                    Business Rule 4




                                                                                     Figure 7.7

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                    27
                                                                                     7

                                    Business Rule 5




Figure 7.8




Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       28
                                                                                                  7

                                    Business Rule 6




                                                                                     Figure 7.9

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                    29
                                                                                     7


                                    Business Rule 7
Figure 7.10




Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel       30
                                                                                               7
                                    Business Rule 8




                                                                                     Figure 7.11

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                 31
                                                                                            7
                                    Business Rule 9




                                                                                     Figure 7.12

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                 32
                                                                                                   7

                                   Business Rule 10




                                                                                     Figure 7.13

Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                     33
                                                                                          7
                                   Business Rule 11




                                                                                     Figure 7.14


Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel            34
                                                                                            7

                                   Business Rule 12




                                                                                     Figure 7.15



Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                 35
                                                                                                   7
                                   Business Rule 13




                                                                                     Figure 7.16


Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                     36
                                                                                               7
                                   Business Rule 14




                                                                                     Figure 7.17


Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                 37
                                                                                             7

                                   Business Rule 15




                                                                                     Figure 7.18




Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                 38
                                                                                                  7
         UCL Entities Considering Business
                       Rules



                                                                                              Composite
                                                      Weak




                                                                                              Weak



                                                                                     Table 7.10



Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel                    39
                                                                                            7




                                                                                      Initial
                                                                                     ERD for
                                                                                       UCL




Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel              40

								
To top