Chapter 8 The University Lab: Conceptual Design Verification, Logical Design, and Implementation

1
Chapter 8The University Lab Conceptual Design
Verification, Logical Design, and Implementation
Database Systems Design, Implementation, and
Management 4th Edition Peter Rob Carlos Coronel
2
Completing the Conceptual and Logical Database
Design

• Issues to Be Addressed
• Entity relationship modeling and normalization
• Data model verification
• Logical design
• Physical design
• Implementation
• Testing and evaluation
• Operation
• Two UCL System Modules
• Lab Management System
• Inventory Management System

3
The University Lab Systems Design Organization
Table 8.1
4
The Lab Management System Modules E-R Segment
Figure 8.1
5
The USER Entity
Table 8.2
6
Sample USER Data
Figure 8.2
7
The LOG Entity
Table 8.3
8
The LOG Sample Data
Figure 8.3
9
The LAB_ASSISTANT Entity
Table 8.4
10
Selected LAB_ASSISTANT Records
Figure 8.4
11
Table 8.5 The Lab Assistant Work Scheduling Sheet
12
The WORK_SCHEDULE Entity
Table 8.6
13
Sample WORK_SCHEDULE Data
Figure 8.5
14
Table 8.7 The HOURS_WORKED Entity
Figure 8.6 Sample Data in the HOURS_WORKED Table
15
The RESERVATION Entity
Table 8.8
16
Table 8.9 The Revised RESERVATION Entity
Figure 8.7 The RESERVATION Sample Data
17
Table 8.10 The RES_SLOT (Weak) Entity
Figure 8.8 The RES_SLOT Sample Data
18
Figure 8.9 The Inventory Management Modules E-R
Segment
19
An Inventory Classification Hierarchy
Table 8.11
20
The INV_TYPE Classification Hierarchy As A Tree
Diagram
Figure 8.10
21
The INV_TYPE Entity
Table 8.12
22
The INV_TYPE Sample Data
Figure 8.11
23
The ITEM Entity
Table 8.13
24
The STORAGE Entity
Table 8.14
25
Figure 8.13 The STORAGE Sample Data
Figure 8.12 Sample ITEM Data
26
Table 8.15 The LOCATION Entity
Figure 8.14 The LOCATION Sample Data
27
Table 8.16 The REPAIR Entity
28
The REPAIR Sample Data
Figure 8.15
29
The VENDOR Entity
Table 8.17
30
The VENDOR Sample Data
Figure 8.16
31
Table 8.18 The ORDER Entity
32
Sample ORDER Data
Figure 8.17
33
The ORDER_ITEM Entity
Table 8.19
34
The ORDER_ITEM Sample Data
Figure 8.18
35
The WITHDRAW Entitys Revision Process
Figure 8.19
36
The WITHDRAW Entity
Table 8.20
37
Table 8.21 The Revised WITHDRAW Entity
Figure 8.20 The WITHDRAW Sample Data
38
Table 8.22 The WD_ITEM (Weak) Entity
Figure 8.21 The WD_ITEM Sample Data
39
The CHECK_OUT Design Revision Process
Figure 8.22
40
Table 8.23 The CHECK_OUT Entity
Figure 8.23 The CHECK_OUT Sample Data
41
Table 8.24 The CHECK_OUT_ITEM (Weak) Entity
Figure 8.24 The CHECK_OUT_ITEM Sample Data
42
Chapter 8The University Lab Conceptual Design
Verification, Logical Design, and Implementation
Database Systems Design, Implementation, and
Management 4th Edition Peter Rob Carlos Coronel
43
E-R Model Verification

• Review of the Conceptual Design Process
• Entity sets, attributes, and domains
• Composite attributes
• Multivalued attributes
• Primary keys
• Foreign keys
• Derived attributes
• Composite entities

44
E-R Model Verification

• The verification process is used to established
  that
• The design properly reflects the end user or
  application views of the database.
• All database transactions are defined and modeled
  to ensure that the implementation of the design
  will support all transaction-processing
  requirements.
• The database design is capable of meeting all
  output requirements.
• All required input screens and data entry forms
  are supported.
• The design is sufficiently flexible to support
  expected enhancements and modifications.

45
E-R Model Verification

• Tasks of E-R Model Verification
• Identifying the central entity
• Identifying each module and its components
• Identifying each module transaction requirement

46
E-R Model Verification

• Problems with the Inventory Management Module
• The Inventory module generates three reports, one
  of which is an inventory movement report. But the
  inventory movements are spread across several
  different entities. Such a spread makes it
  difficult to generate the output and reduces
  system performance.
• An items quantity on hand is updated with an
  inventory movement that can represent a purchase,
  withdraw, check-out, check-in, or inventory
  adjustment. Yet only the withdrawals and
  check-outs are represented in the model.

47
E-R Model Verification

• Solution
• Creating a new entity to record all inventory
  movement that is, an inventory entity is needed.
• Creation of the new entity INV_TRANS serves two
  purposes as a common entry points
• It standardizes the inventory modules interface
  with other modules.
• It facilitates control and generation of required
  outputs, such as the inventory movement report.

48
Figure 8.25 The Inventory Transaction Process
49
(No Transcript)
50
(No Transcript)
51
The TR_ITEM (Weak) Entity
Table 8.26
52
The TR_ITEM Sample Data
Figure 8.27
53
(No Transcript)
54
Logical Design

• The logical design translates the conceptual
  model to match the format expected of the DBMS
  that is used to implement the system.
• It sets the stage for creating the relational
  table structures, indexes, and views.

55
Logical Design

• Tables
• Example of SQL to create the STORAGE table
• CREATE TABLE STORAGE (LOC_ID CHAR(12) NOT
  NULL,ITEM_ID CHAR(10) NOT NULL,STOR_QTY NUMBER,
  PRIMARY KEY (LOC_ID, ITEM_ID),FOREIGN KEY
  (LOC_ID) REFERENCES LOCATION ON DELETE
  RESTRICT ON UPDATE RESTRICT,FOREIGN KEY
  (ITEM_ID) REFERENCES ITEM ON DELETE CASCADE
  ON UPDATE CASCADE)

56
(No Transcript)
57
Logical Design

• Indexes
• Indexes are created to enhance operational speed
  and to enable us to produce logically ordered
  output sequences.
• CREATE UNIQUE INDEX LA_DEXON LAB_ASSISTANT
  (LA_ID)
• CREATE UNIQUE INDEX WS_DEXON WORK_SCHEDULE
  (SCHED_SEMESTER, LA_ID, SCHED_WEEKDAY,
  SCHED_TIME_IN)

58
Logical Design

• Views
• Views are often used for security purposes.
  However, views are also used to streamline the
  systems processing requirements.
• CREATE VIEW LA_SCHED ASSELECT LA_ID, LA_NAME,
  SCHED_WEEKDAY, SCHED_TIME_IN, SCHED_TIME_OUTWHERE
  SCHED_SEMESTER FALL99

59
Physical Design

• Physical design requires the definition of
  specific storage or access methods that will be
  used by the database.
• It must include an estimate of the space required
  to store the database.
• Physical storage characteristics are a function
  of the DBMS and the operating systems being used.

60
Table 8.27 Fixed Space Claimed By OS/2 DBM V1.2
Per Database
Table 8.28 Physical Storage Requirements The
USER Table
61
Implementation

• Database administrator (DBA), who controls the
  database management function, must define the
  standards and procedures required to interact
  with the database.
• The implementation plan includes
• Formal definitions of the processes and
  standards.
• Chronology of the required activities.
• Development of adequate documentation standards.
• Identification of responsibilities for continued
  development and maintenance.

62
Implementation

• Database Creation
• Tables, indexes, and views are created.
• Storage space and access methods are implemented.
• Database Loading and Conversion
• Data are entered one table at a time or by
  copying the data from existing databases or
  files.
• Processes may require data to be loaded in a
  specific order.

63
Implementation

• System Procedures
• System procedures describe the steps required to
  manage, access, and maintain the database system.
  
• Procedures must be established to
• Test and evaluate the database.
• Fine-tune the database.
• Ensure database security and integrity.
• Back up and recover the database.
• Access and use the database system.

64
Testing and Evaluation

• The purpose of testing and evaluation is to
  determine how well the database meets its goals.
• Testing and evaluation should be an ongoing
  process.
• Testing and evaluation should consider
• Performance measures.
• Security measures.
• Backup and recovery procedures.

65
Operation

• Database operation is an ongoing venture that
  includes all the DBAs administrative and
  technical functions designed to ensure the
  databases continuity.
• Operational Database
• An operational database provides all necessary
  support for the systems daily operations and
  maintains all appropriate operational procedures.
• Operational Procedures
• Database operational procedures are written
  documents in which the activities of the daily
  database operations are described.
• Managing the Database Maintenance and
  EvolutionThe DBA is responsible for coordinating
  all operational and managerial aspects of the new
  DBMS environment.

66
Operation

• DBAs Responsibilities
• Monitoring and fine-tuning the database
• Planning for and allocating resources for changes
  and enhancements
• Planning for and allocating resources for
  periodic system upgrades
• Providing preventive and corrective maintenance
• Providing end user management services
• Performing periodic security audits
• Performing necessary training
• Establishing and enforcing database standards
• Marketing the database to the organizations
  users
• Obtaining funding for database operations,
  upgrades, and enhancements
• Ensuring completion of database projects within
  time and budget constraints