Transcript Database Systems: Design, Implementation, and Management

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
Entities Identified
Hachim Haddouti and Rob & Coronel, Ch7
8
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
Module E-R Segment
Hachim Haddouti and Rob & Coronel, Ch7
8
6
8
USER Entity
Hachim Haddouti and Rob & Coronel, Ch7
7
8
LOG Entity
Hachim Haddouti and Rob & Coronel, Ch7
8
LAB_ASSISTANT Entity
Hachim Haddouti and Rob & Coronel, Ch7
8
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
Module E-R Segment
Hachim Haddouti and Rob & Coronel, Ch7
8
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
WITHDRAW Entity Revision
Hachim Haddouti and Rob & Coronel, Ch7
8
24
WITHDRAW Entity and Revision
Hachim Haddouti and Rob & Coronel, Ch7
8
25
8
WD_ITEM (Weak) Entity
Hachim Haddouti and Rob & Coronel, Ch7
26
CHECK_OUT Design Revision
Hachim Haddouti and Rob & Coronel, Ch7
8
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