Chapter 8In this chapter, you will learn:Completing Conceptual and Logical DesignEntities IdentifiedCompletion of Conceptual DesignLab Management System Module E-R SegmentUSER EntityLOG EntityLAB_ASSISTANT EntityWORK_SCHEDULE EntityHOURS_WORKED EntityRESERVATION EntityRevised RESERVATION EntityRES_SLOT (Weak) EntityInventory Management Module E-R SegmentINV_Type EntityITEM EntitySTORAGE EntityLOCATION EntityREPAIR EntityVENDOR EntityORDER EntityORDER_ITEM EntityWITHDRAW Entity RevisionWITHDRAW Entity and RevisionWD_ITEM (Weak) EntityCHECK_OUT Design RevisionCHECK_OUT EntityCHECK_OUT_ITEM (Weak) EntityE-R Model VerificationInventory Management Reporting ProblemsInventory Management Reporting Problems SolutionInventory Transaction ProcessINV_TRANS EntityTR_ITEM (Weak) EntityRevised University Computer Lab ERDLogical DesignPhysical DesignImplementationTesting and EvaluationOperation8Chapter 8The University Lab: Conceptual Design Verification, Logical Design, and ImplementationDatabase Systems: Design, Implementation, and Management, Fifth Edition, Rob and CoronelDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel28In 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 operationDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel38Completing 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 SystemDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel48Entities IdentifiedTable 8.2Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel58Completion of Conceptual Design• Refine module definition–Entities–Attributes•Normalization process–Discover new entities–Revise attributesDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel68Lab Management System Module E-R SegmentFigure 8.1Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel78USER EntityTable 8.3Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel88LOG EntityTable 8.4Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel98LAB_ASSISTANT EntityTable 8.5Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel108WORK_SCHEDULE EntityTable 8.7Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel118HOURS_WORKED EntityTable 8.8Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel128RESERVATION EntityTable 8.9Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel138Revised RESERVATION EntityTable 8.10Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel148RES_SLOT (Weak) EntityTable 8.11Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel158Inventory Management Module E-R SegmentFigure 8.9Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel168INV_Type EntityTable 8.13Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel178ITEM EntityTable 8.14Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel188STORAGE EntityTable 8.15Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel198LOCATION EntityTable 8.16Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel208REPAIR EntityTable 8.17Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel218VENDOR EntityTable 8.18Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel228ORDER EntityTable 8.19Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel238ORDER_ITEM EntityTable 8.20Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel248WITHDRAW Entity RevisionFigure 8.19Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel258WITHDRAW Entity and RevisionTable 8.21Table 8.22Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel268WD_ITEM (Weak) EntityTable 8.23Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel278CHECK_OUT Design RevisionFigure 8.22Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel288CHECK_OUT EntityTable 8.24Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel298CHECK_OUT_ITEM (Weak) EntityTable 8.25Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel308E-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 requirementDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel318Inventory 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 modelDatabase Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel328Inventory Management Reporting Problems Solution•Create new entity as common movement entry point•INV_TRANS created–Standardizes inventory module interfaces–Facilitates control