Appendix C The University Lab: Q Q Q Q
Conceptual Design Verification, Logical Design, and Implementation
Q Q Q Q Q Q
Appendix C
The University Lab: Conceptual Design,
Verification, Logical Design, and
Implementation
Discussion Focus Q
How is a database design verified, and why is such verification necessary?
Q Q Q Q Q Q Q Q Q Q Q
Use our detailed answer to question 1 to focus class discussion on database design verification. Stress that
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
the verification process uses the initial ER model as a communication tool.
Q Q Q Q Q Q Q Q Q Q Q Q
The designer may begin the verification process by describing the organization's operations to its end user
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
s, basing the detailed description on the initial ER model. Next, explain how the operations will be support
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ed by the database design. Stress that the design must support the end-
Q Q Q Q Q Q Q Q Q Q Q Q
user application views, outputs, and inputs. Points to be addressed include such questions as:
Q Q Q Q Q Q Q Q Q Q Q Q Q
• Is the description accurate? If not, what aspects of the description must be corrected?
Q Q Q Q Q Q Q Q Q Q Q Q Q
• Does the model support the end-user requirements? If not, what aspects of the end-
Q Q Q Q Q Q Q Q Q Q Q Q Q
userrequirements have not been addressed or have been addressed inadequately?
Q Q Q Q Q Q Q Q Q Q
Keep in mind that even a model that perfectly addresses all initially determined end user requirementsis
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
Qlikely to need adjustments as those end users begin to understand the ramifications of the database desig
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
n's capabilities. In many cases, the end users may learn what the organization's processes and procedures
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
actually are, thus leading to new requirements and the perception of new opportunities. Thedatabase des
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
igner must keep such likely developments in mind, especially if (s)he works as a database design consultan
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
t. (Anticipation of such developments must be factored into the contract negotiations for consulting fees.)
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
1
, Appendix C The University Lab: Q Q Q Q
Conceptual Design Verification, Logical Design, and Implementation
Q Q Q Q Q Q
Discuss the role of the system modules.
Q Q Q Q Q Q
The use of system modules can hardly be overemphasized in a database design environment. Stresst
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
hese module characteristics and features:
Q Q Q Q
• Modules represent subsets of the database model: Smaller "pieces" are more easilyundersto
Q Q Q Q Q Q Q Q Q Q Q Q
od.
• Modules are self- Q Q
contained and accomplish a specific system function; if such a system function must be mo
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
dified, other functions remain unaffected.
Q Q Q Q
• Modules fit into a modular database design, which is more easily modified and adapted tone
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
w circumstances. Because modification efforts are focused on a database subset, productivi
Q Q Q Q Q Q Q Q Q Q Q
ty of both designers and application developers is likely to be enhanced.
Q Q Q Q Q Q Q Q Q Q Q
Module interfaces must be clear if the modules are expected to work well within the overall system.
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
Answers to Review Questions Q Q Q
1. Why must a conceptual model be verified? What steps are involved in the verification process?
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
The verification of a conceptual model is crucial to a successful database design. The verification proc
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ess allows the designer to check the accuracy of the database design by:
Q Q Q Q Q Q Q Q Q Q Q Q
• Re-examining data and data transformations. Q Q Q Q
• Enabling the designer to evaluate the design efficiency relative to the end user's andsy
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
stem's design goals. Q Q
Keep in mind that, to a large extent, the best design is the one that serves the end-
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
user requirements best. For example, a design that works well for a manufacturing firm may not fit th
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
e needs of a marketing research firm, and vice versa.
Q Q Q Q Q Q Q Q Q
The verification process helps the designer to avoid implementation problems later by:
Q Q Q Q Q Q Q Q Q Q Q
• Validating the model's entities. (Remember the minimal data rule.)
Q Q Q Q Q Q Q Q
• Confirming entity relationships and eliminating duplicate, unnecessary, or improperlyd
Q Q Q Q Q Q Q Q Q
efined relationships. Q
• Eliminating data redundancies. Q Q
• Improving the model's semantic precision to better represent real-world operations.
Q Q Q Q Q Q Q Q Q
• Confirming that all user requirements (processing, performance, or security) are met.
Q Q Q Q Q Q Q Q Q Q
2
, Appendix C The University Lab: Q Q Q Q
Conceptual Design Verification, Logical Design, and Implementation
Q Q Q Q Q Q
Verification is a continuous activity in any database design. The database design process is evolutiona
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ry in nature: It requires the continuous evaluation of the developing model by examining the effect of
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
adding new entities and by confirming that any design changes enhance the model's accuracy.
Q Q Q Q Q Q Q Q Q Q Q Q Q
The verification process requires the following steps:
Q Q Q Q Q Q
1. Identify the database's central entity. Q Q Q Q
The central entity is the most important entity in our database, and most of the othere
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ntities depend on it. Q Q Q
2. Identify and define each module and its components. Q Q Q Q Q Q Q
The designer divides the database model into smaller sets that reflect the data needs ofp
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
articular systems modules such as inventory, orders, payroll, etc.
Q Q Q Q Q Q Q Q
3. Identify and define each of the module's processes. Q Q Q Q Q Q Q
Specifically, this step requires the identification and definition of the database transactions
Q Q Q Q Q Q Q Q Q Q Q
that represent the module's real-world operations.
Q Q Q Q Q
4. Verify each of the transactions against the database.
Q Q Q Q Q Q Q
2. What steps must be completed before the database design is fully implemented? (Make sure thaty
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ou list the steps in the correct sequence and discuss each step briefly.)
Q Q Q Q Q Q Q Q Q Q Q Q
The DBLC, discussed in detail in Chapter 9, “Database Design,” constitutes a database's history, tracin
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
g it from its conceptual design to its implementation and operation. We highly recommend that the d
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
atabase designer follow the DBLC's steps carefully in order to ensure that the database willproperly m
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
eet all user and system requirements.
Q Q Q Q Q
Before a database can be successfully implemented, the following steps must be completed:
Q Q Q Q Q Q Q Q Q Q Q Q
1. Define the conceptual model's components: entities, attributes, domains, and relationships.
Q Q Q Q Q Q Q Q Q
2. Normalize the database to ensure that all transitive dependencies are eliminated and thate
Q Q Q Q Q Q Q Q Q Q Q Q Q
ach entity's attributes are solely dependent on its key attribute(s).
Q Q Q Q Q Q Q Q Q
3
, Appendix C The University Lab: Q Q Q Q
Conceptual Design Verification, Logical Design, and Implementation Q Q Q Q Q Q
3. Verify the conceptual model to ensure that the proposed database will meet the system'str
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ansaction requirements and that the end-user and systems requirements will be met.
Q Q Q Q Q Q Q Q Q Q Q
The verification process will probably delete and/or create entities, attributes, andrelatio
Q Q Q Q Q Q Q Q Q Q Q
nships. It may also refine existing entities, attributes, and relationships.
Q Q Q Q Q Q Q Q Q
4. Create the logical design which requires the definition of the table structures, using aspecific
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
DBMS (relational, network or hierarchical). Logical design also includes, if necessary, appropri
Q Q Q Q Q Q Q Q Q Q Q
ate indexes and views.
Q Q Q
5. Create the physical design to define access paths, including space allocation, storage group cr
Q Q Q Q Q Q Q Q Q Q Q Q Q
eation, table spaces, and any other physical storage characteristic that is dependent onthe
Q Q Q Q Q Q Q Q Q Q Q Q Q
hardware and software to be used in the system's implementation.
Q Q Q Q Q Q Q Q Q Q
6. Implement the design. Somehow, this last step seems to suffer from planning neglect, to the
Q Q QQQ Q Q Q Q Q Q Q Q Q Q Q Q
detriment of the system's operation. Implementation, operation, and maintenance plans mu
Q Q Q Q Q Q Q Q Q Q
st (at least) include careful definition and description of the activities required to implement t
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
he database design:
Q Q
• loading and conversion Q Q
• definition of database standards Q Q Q
• system and procedures documentation: security, backup, and recovery
Q Q Q Q Q Q Q
• operational procedures to be followed by users Q Q Q Q Q Q
• a detailed training plan
Q Q Q
• identification of responsibilities for operation and maintenance. Q Q Q Q Q Q
3. What major factors should be addressed when database system performance is evaluated?Discuss
Q Q Q Q Q Q Q Q Q Q Q Q
each factor briefly.
Q Q Q
Database systems performance refers to the system's ability to retrieve information within a reasona
Q Q Q Q Q Q Q Q Q Q Q Q Q
ble amount of time and at a reasonable cost. Keeping in mind that "reasonable" means different thing
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
s to different people, we must address at least these important performance factors:
Q Q Q Q Q Q Q Q Q Q Q Q
• Concurrent users Q
For any given system, the more users connected to the system, the longer the dataret
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
rieval time. Q
• Resource limits Q
The fewer resources that are available to the user, the longer the access queues will be.
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
• Communication speeds Q
Lower communication speeds mean longer response times.
Q Q Q Q Q Q
• Query response time Q Q
Queries must be tuned to provide optimum query response time. (See Appendix C, “Database
Q Q Q Q Q Q Q Q Q Q Q Q Q
Performance Tuning.”) Lack of query response tuning means slow response times. Dependin
Q Q Q Q Q Q Q Q Q Q Q Q
g on how good the design and the program code are, the query response time can vary from
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
minutes to hours for the same query. Q Q Q Q Q Q
4
Conceptual Design Verification, Logical Design, and Implementation
Q Q Q Q Q Q
Appendix C
The University Lab: Conceptual Design,
Verification, Logical Design, and
Implementation
Discussion Focus Q
How is a database design verified, and why is such verification necessary?
Q Q Q Q Q Q Q Q Q Q Q
Use our detailed answer to question 1 to focus class discussion on database design verification. Stress that
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
the verification process uses the initial ER model as a communication tool.
Q Q Q Q Q Q Q Q Q Q Q Q
The designer may begin the verification process by describing the organization's operations to its end user
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
s, basing the detailed description on the initial ER model. Next, explain how the operations will be support
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ed by the database design. Stress that the design must support the end-
Q Q Q Q Q Q Q Q Q Q Q Q
user application views, outputs, and inputs. Points to be addressed include such questions as:
Q Q Q Q Q Q Q Q Q Q Q Q Q
• Is the description accurate? If not, what aspects of the description must be corrected?
Q Q Q Q Q Q Q Q Q Q Q Q Q
• Does the model support the end-user requirements? If not, what aspects of the end-
Q Q Q Q Q Q Q Q Q Q Q Q Q
userrequirements have not been addressed or have been addressed inadequately?
Q Q Q Q Q Q Q Q Q Q
Keep in mind that even a model that perfectly addresses all initially determined end user requirementsis
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
Qlikely to need adjustments as those end users begin to understand the ramifications of the database desig
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
n's capabilities. In many cases, the end users may learn what the organization's processes and procedures
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
actually are, thus leading to new requirements and the perception of new opportunities. Thedatabase des
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
igner must keep such likely developments in mind, especially if (s)he works as a database design consultan
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
t. (Anticipation of such developments must be factored into the contract negotiations for consulting fees.)
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
1
, Appendix C The University Lab: Q Q Q Q
Conceptual Design Verification, Logical Design, and Implementation
Q Q Q Q Q Q
Discuss the role of the system modules.
Q Q Q Q Q Q
The use of system modules can hardly be overemphasized in a database design environment. Stresst
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
hese module characteristics and features:
Q Q Q Q
• Modules represent subsets of the database model: Smaller "pieces" are more easilyundersto
Q Q Q Q Q Q Q Q Q Q Q Q
od.
• Modules are self- Q Q
contained and accomplish a specific system function; if such a system function must be mo
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
dified, other functions remain unaffected.
Q Q Q Q
• Modules fit into a modular database design, which is more easily modified and adapted tone
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
w circumstances. Because modification efforts are focused on a database subset, productivi
Q Q Q Q Q Q Q Q Q Q Q
ty of both designers and application developers is likely to be enhanced.
Q Q Q Q Q Q Q Q Q Q Q
Module interfaces must be clear if the modules are expected to work well within the overall system.
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
Answers to Review Questions Q Q Q
1. Why must a conceptual model be verified? What steps are involved in the verification process?
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
The verification of a conceptual model is crucial to a successful database design. The verification proc
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ess allows the designer to check the accuracy of the database design by:
Q Q Q Q Q Q Q Q Q Q Q Q
• Re-examining data and data transformations. Q Q Q Q
• Enabling the designer to evaluate the design efficiency relative to the end user's andsy
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
stem's design goals. Q Q
Keep in mind that, to a large extent, the best design is the one that serves the end-
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
user requirements best. For example, a design that works well for a manufacturing firm may not fit th
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
e needs of a marketing research firm, and vice versa.
Q Q Q Q Q Q Q Q Q
The verification process helps the designer to avoid implementation problems later by:
Q Q Q Q Q Q Q Q Q Q Q
• Validating the model's entities. (Remember the minimal data rule.)
Q Q Q Q Q Q Q Q
• Confirming entity relationships and eliminating duplicate, unnecessary, or improperlyd
Q Q Q Q Q Q Q Q Q
efined relationships. Q
• Eliminating data redundancies. Q Q
• Improving the model's semantic precision to better represent real-world operations.
Q Q Q Q Q Q Q Q Q
• Confirming that all user requirements (processing, performance, or security) are met.
Q Q Q Q Q Q Q Q Q Q
2
, Appendix C The University Lab: Q Q Q Q
Conceptual Design Verification, Logical Design, and Implementation
Q Q Q Q Q Q
Verification is a continuous activity in any database design. The database design process is evolutiona
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ry in nature: It requires the continuous evaluation of the developing model by examining the effect of
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
adding new entities and by confirming that any design changes enhance the model's accuracy.
Q Q Q Q Q Q Q Q Q Q Q Q Q
The verification process requires the following steps:
Q Q Q Q Q Q
1. Identify the database's central entity. Q Q Q Q
The central entity is the most important entity in our database, and most of the othere
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ntities depend on it. Q Q Q
2. Identify and define each module and its components. Q Q Q Q Q Q Q
The designer divides the database model into smaller sets that reflect the data needs ofp
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
articular systems modules such as inventory, orders, payroll, etc.
Q Q Q Q Q Q Q Q
3. Identify and define each of the module's processes. Q Q Q Q Q Q Q
Specifically, this step requires the identification and definition of the database transactions
Q Q Q Q Q Q Q Q Q Q Q
that represent the module's real-world operations.
Q Q Q Q Q
4. Verify each of the transactions against the database.
Q Q Q Q Q Q Q
2. What steps must be completed before the database design is fully implemented? (Make sure thaty
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ou list the steps in the correct sequence and discuss each step briefly.)
Q Q Q Q Q Q Q Q Q Q Q Q
The DBLC, discussed in detail in Chapter 9, “Database Design,” constitutes a database's history, tracin
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
g it from its conceptual design to its implementation and operation. We highly recommend that the d
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
atabase designer follow the DBLC's steps carefully in order to ensure that the database willproperly m
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
eet all user and system requirements.
Q Q Q Q Q
Before a database can be successfully implemented, the following steps must be completed:
Q Q Q Q Q Q Q Q Q Q Q Q
1. Define the conceptual model's components: entities, attributes, domains, and relationships.
Q Q Q Q Q Q Q Q Q
2. Normalize the database to ensure that all transitive dependencies are eliminated and thate
Q Q Q Q Q Q Q Q Q Q Q Q Q
ach entity's attributes are solely dependent on its key attribute(s).
Q Q Q Q Q Q Q Q Q
3
, Appendix C The University Lab: Q Q Q Q
Conceptual Design Verification, Logical Design, and Implementation Q Q Q Q Q Q
3. Verify the conceptual model to ensure that the proposed database will meet the system'str
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
ansaction requirements and that the end-user and systems requirements will be met.
Q Q Q Q Q Q Q Q Q Q Q
The verification process will probably delete and/or create entities, attributes, andrelatio
Q Q Q Q Q Q Q Q Q Q Q
nships. It may also refine existing entities, attributes, and relationships.
Q Q Q Q Q Q Q Q Q
4. Create the logical design which requires the definition of the table structures, using aspecific
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
DBMS (relational, network or hierarchical). Logical design also includes, if necessary, appropri
Q Q Q Q Q Q Q Q Q Q Q
ate indexes and views.
Q Q Q
5. Create the physical design to define access paths, including space allocation, storage group cr
Q Q Q Q Q Q Q Q Q Q Q Q Q
eation, table spaces, and any other physical storage characteristic that is dependent onthe
Q Q Q Q Q Q Q Q Q Q Q Q Q
hardware and software to be used in the system's implementation.
Q Q Q Q Q Q Q Q Q Q
6. Implement the design. Somehow, this last step seems to suffer from planning neglect, to the
Q Q QQQ Q Q Q Q Q Q Q Q Q Q Q Q
detriment of the system's operation. Implementation, operation, and maintenance plans mu
Q Q Q Q Q Q Q Q Q Q
st (at least) include careful definition and description of the activities required to implement t
Q Q Q Q Q Q Q Q Q Q Q Q Q Q
he database design:
Q Q
• loading and conversion Q Q
• definition of database standards Q Q Q
• system and procedures documentation: security, backup, and recovery
Q Q Q Q Q Q Q
• operational procedures to be followed by users Q Q Q Q Q Q
• a detailed training plan
Q Q Q
• identification of responsibilities for operation and maintenance. Q Q Q Q Q Q
3. What major factors should be addressed when database system performance is evaluated?Discuss
Q Q Q Q Q Q Q Q Q Q Q Q
each factor briefly.
Q Q Q
Database systems performance refers to the system's ability to retrieve information within a reasona
Q Q Q Q Q Q Q Q Q Q Q Q Q
ble amount of time and at a reasonable cost. Keeping in mind that "reasonable" means different thing
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
s to different people, we must address at least these important performance factors:
Q Q Q Q Q Q Q Q Q Q Q Q
• Concurrent users Q
For any given system, the more users connected to the system, the longer the dataret
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
rieval time. Q
• Resource limits Q
The fewer resources that are available to the user, the longer the access queues will be.
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
• Communication speeds Q
Lower communication speeds mean longer response times.
Q Q Q Q Q Q
• Query response time Q Q
Queries must be tuned to provide optimum query response time. (See Appendix C, “Database
Q Q Q Q Q Q Q Q Q Q Q Q Q
Performance Tuning.”) Lack of query response tuning means slow response times. Dependin
Q Q Q Q Q Q Q Q Q Q Q Q
g on how good the design and the program code are, the query response time can vary from
Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q Q
minutes to hours for the same query. Q Q Q Q Q Q
4
