Get documents about "Database Systems: Design, Implementation, and Management
Document Sample
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
