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