410 likes | 528 Views
Chapter 8. The University Lab: Conceptual Design Verification, Logical Design, and Implementation Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel. In this chapter, you will learn:. How the Lab Management System modules are defined and refined
E N D
Chapter 8 The University Lab: Conceptual Design Verification, Logical Design, and Implementation Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel
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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Entities Identified Table 8.2 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Completion of Conceptual Design • Refine module definition • Entities • Attributes • Normalization process • Discover new entities • Revise attributes Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Lab Management System Module E-R Segment Figure 8.1 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
USER Entity Table 8.3 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
LOG Entity Table 8.4 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
LAB_ASSISTANT Entity Table 8.5 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
WORK_SCHEDULE Entity Table 8.7 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
HOURS_WORKED Entity Table 8.8 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
RESERVATION Entity Table 8.9 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Revised RESERVATION Entity Table 8.10 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
RES_SLOT (Weak) Entity Table 8.11 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Inventory Management Module E-R Segment Figure 8.9 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
INV_Type Entity Table 8.13 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
ITEM Entity Table 8.14 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
STORAGE Entity Table 8.15 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
LOCATION Entity Table 8.16 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
REPAIR Entity Table 8.17 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
VENDOR Entity Table 8.18 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
ORDER Entity Table 8.19 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
ORDER_ITEM Entity Table 8.20 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
WITHDRAW Entity Revision Figure 8.19 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
WITHDRAW Entity and Revision Table 8.21 Table 8.22 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
WD_ITEM (Weak) Entity Table 8.23 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
CHECK_OUT Design Revision Figure 8.22 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
CHECK_OUT Entity Table 8.24 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
CHECK_OUT_ITEM (Weak) Entity Table 8.25 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Inventory Transaction Process Figure 8.25 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
INV_TRANS Entity Table 8.26 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
TR_ITEM (Weak) Entity Table 8.27 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Revised University Computer Lab ERD Figure 8.28 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Physical Design • Defines specific storage or access methods used by database • Includes estimate of storage space • Characteristics are function of DBMS and operating systems Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
Testing and Evaluation • Determine how well database meets goals • Ongoing process • Considerations • Performance measures • Security • Backup and recovery procedures Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel
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 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel