8 Chapter 8 The University Lab Conceptual Design

8 Chapter 8 The University Lab: Conceptual Design Verification, Logical Design, and Implementation Hachim Haddouti and Rob & Coronel, Ch 7

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, Ch 7 2

8 Completing Conceptual and Logical Design Hachim Haddouti and Rob & Coronel, Ch 7 3

Entities Identified Hachim Haddouti and Rob & Coronel, Ch 7 8 4

8 Completion of Conceptual Design • Refine module definition – Entities – Attributes • Normalization process – Discover new entities – Revise attributes Hachim Haddouti and Rob & Coronel, Ch 7 5

Lab Management System Module E-R Segment Hachim Haddouti and Rob & Coronel, Ch 7 8 6

8 USER Entity Hachim Haddouti and Rob & Coronel, Ch 7 7

8 LOG Entity Hachim Haddouti and Rob & Coronel, Ch 7 8

LAB_ASSISTANT Entity Hachim Haddouti and Rob & Coronel, Ch 7 8 9

8 WORK_SCHEDULE Entity Table 8. 7 Hachim Haddouti and Rob & Coronel, Ch 7 10

8 HOURS_WORKED Entity Hachim Haddouti and Rob & Coronel, Ch 7 11

8 RESERVATION Entity Table 8. 9 Hachim Haddouti and Rob & Coronel, Ch 7 12

8 Revised RESERVATION Entity Hachim Haddouti and Rob & Coronel, Ch 7 13

8 RES_SLOT (Weak) Entity Hachim Haddouti and Rob & Coronel, Ch 7 14

Inventory Management Module E-R Segment Hachim Haddouti and Rob & Coronel, Ch 7 8 15

8 INV_Type Entity Hachim Haddouti and Rob & Coronel, Ch 7 16

ITEM Entity Hachim Haddouti and Rob & Coronel, Ch 7 8 17

8 STORAGE Entity Hachim Haddouti and Rob & Coronel, Ch 7 18

8 LOCATION Entity Hachim Haddouti and Rob & Coronel, Ch 7 19

REPAIR Entity Hachim Haddouti and Rob & Coronel, Ch 7 8 20

8 VENDOR Entity Hachim Haddouti and Rob & Coronel, Ch 7 21

8 ORDER Entity Hachim Haddouti and Rob & Coronel, Ch 7 22

8 ORDER_ITEM Entity Hachim Haddouti and Rob & Coronel, Ch 7 23

WITHDRAW Entity Revision Hachim Haddouti and Rob & Coronel, Ch 7 8 24

WITHDRAW Entity and Revision Hachim Haddouti and Rob & Coronel, Ch 7 8 25

8 WD_ITEM (Weak) Entity Hachim Haddouti and Rob & Coronel, Ch 7 26

CHECK_OUT Design Revision Hachim Haddouti and Rob & Coronel, Ch 7 8 27

8 CHECK_OUT Entity Hachim Haddouti and Rob & Coronel, Ch 7 28

8 CHECK_OUT_ITEM (Weak) Entity Hachim Haddouti and Rob & Coronel, Ch 7 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, Ch 7 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, Ch 7 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, Ch 7 32

8 Inventory Transaction Process Figure 8. 25 Hachim Haddouti and Rob & Coronel, Ch 7 33

8 INV_TRANS Entity Table 8. 26 Hachim Haddouti and Rob & Coronel, Ch 7 34

8 TR_ITEM (Weak) Entity Hachim Haddouti and Rob & Coronel, Ch 7 35

8 Revised University Computer Lab ERD Hachim Haddouti and Rob & Coronel, Ch 7 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, Ch 7 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, Ch 7 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, Ch 7 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, Ch 7 40

8 Operation • Provides support for daily operations • Maintains operational procedures • Database maintenance and evolution – DBA performs technical and managerial duties to ensure properation of database to support organizational mission Hachim Haddouti and Rob & Coronel, Ch 7 41