1 / 41

Conceptual and Logical Design in Database Systems Management

In this chapter, you will explore the nuances of lab management system modules, defining and refining their attributes and domains. Learn how database transactions are identified and designed within the system, and understand the concurrent use of verification processes that employ modeling and normalization techniques to eliminate data redundancies. The chapter reviews the essential steps for database implementation, testing, evaluation, and operation, emphasizing the importance of thorough design verification to ensure that all user requirements and entities are accurately represented.

ulric-logan
Download Presentation

Conceptual and Logical Design in Database Systems Management

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 8 The University Lab: Conceptual Design Verification, Logical Design, and Implementation Database Systems: Design, Implementation, and Management, Fifth Edition, Rob and Coronel

  2. 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

  3. 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

  4. Entities Identified Table 8.2 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  5. 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

  6. Lab Management System Module E-R Segment Figure 8.1 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  7. USER Entity Table 8.3 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  8. LOG Entity Table 8.4 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  9. LAB_ASSISTANT Entity Table 8.5 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  10. WORK_SCHEDULE Entity Table 8.7 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  11. HOURS_WORKED Entity Table 8.8 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  12. RESERVATION Entity Table 8.9 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  13. Revised RESERVATION Entity Table 8.10 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  14. RES_SLOT (Weak) Entity Table 8.11 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  15. Inventory Management Module E-R Segment Figure 8.9 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  16. INV_Type Entity Table 8.13 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  17. ITEM Entity Table 8.14 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  18. STORAGE Entity Table 8.15 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  19. LOCATION Entity Table 8.16 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  20. REPAIR Entity Table 8.17 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  21. VENDOR Entity Table 8.18 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  22. ORDER Entity Table 8.19 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  23. ORDER_ITEM Entity Table 8.20 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  24. WITHDRAW Entity Revision Figure 8.19 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  25. WITHDRAW Entity and Revision Table 8.21 Table 8.22 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  26. WD_ITEM (Weak) Entity Table 8.23 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  27. CHECK_OUT Design Revision Figure 8.22 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  28. CHECK_OUT Entity Table 8.24 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  29. CHECK_OUT_ITEM (Weak) Entity Table 8.25 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  30. 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

  31. 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

  32. 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

  33. Inventory Transaction Process Figure 8.25 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  34. INV_TRANS Entity Table 8.26 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  35. TR_ITEM (Weak) Entity Table 8.27 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  36. Revised University Computer Lab ERD Figure 8.28 Database Systems: Design, Implementation, & Management, 5th Edition, Rob & Coronel

  37. 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

  38. 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

  39. 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

  40. 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

  41. 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

More Related