Solutions
TABLE OF CONTENTS
Answers to Review Questions..................................................................................................1
Answers to Problems................................................................................................................ 6
ANSWERS TO REVIEW QUESTIONS
1. What factors relevant to database design are revealed during the initial study phase?
Answer: The database initial study phase yields the information required to determine an
organization’s needs, as well as the factors that influence data generation, collection, and
processing. Students must understand that this phase is generally concurrent with the
planning phase of the SDLC and that, therefore, several of the initial study activities are
common to both.
The most important discovery of the initial study phase is the set of the company’s objectives.
Once the designer has a clear understanding of the company’s main goals and its mission,
(s)he can use this as the guide to making all subsequent decisions concerning the analysis,
design, and implementation of the database and the information system.
The initial study phase also establishes the company’s organizational structure; the
description of operations, problems and constraints, and alternate solutions; system
objectives; and the proposed system scope and boundaries.
The organizational structure and the description of operations are interdependent because
operations are usually a function of the company’s organizational structure. The
determination of structure and operations allows the designer to analyze the existing system
and to describe a set of problems, constraints, and possible solutions.
Naturally, the designer must find a feasible solution within the existing constraints. In most
cases, the best solution is not necessarily the most feasible one. The constraints also force
the designer to narrow the focus on very specific problems that must be solved.
In short, the combination of all the factors we have just discussed help the designer to put
together a set of realistic, achievable, and measurable system objectives within the system’s
required scope and boundaries.
2. Why is the organizational structure relevant to the database designer?
Answer: The delivery of information must be timely, it must reach the right people, and the
delivered information must be accurate. Since the proper use of timely and accurate
information is the key factor in the success of any system, the reports and queries drawn from
the database must reach the key decision makers within the organization. Clearly,
understanding the organization structure helps the designer to define the organization’s lines
of communication and to establish reporting requirements.
3. What is the difference between the database design scope and its boundaries? Why is the scope
and boundary statement so important to the database designer?
, Answer: The system’s boundaries are the limits imposed on the database design by external
constraints such as available budget and time, the current level of technology, and end-user
resistance to change. The scope of a database defines the extent of the database design
coverage and reflects a conscious decision to include some things and exclude others. Note
that the existence of boundaries usually has an effect on the system’s scope.
For legal and practical design reasons, the designer cannot afford to work on an unbounded
system. If the system’s limits have not been adequately defined, the designer may be legally
required to expand the system indefinitely. Moreover, an unbounded system will not contain
the built-in constraints that make its use practical in a real-world environment. For example, a
completely unbounded system will never be completed, nor may it ever be ready for
reasonable use. Even a system with an “optimistic” set of bounds may drag the design out
over many years and may cost too much. Keep in mind that company managers almost
invariably want least-cost solutions to specific problems.
4. What business rule(s) and relationships can be described for the ERD shown in
Figure QB.4?
FIGURE QB.4 The ERD for Question 4
Answer: The business rules and relationships are summarized in Table QB.4.
, Table QB.4 Business Rules and Relationships Summary
Business rules Relationships
A supplier supplies many parts. many to many
Each part is supplied by many suppliers. PART - SUPPLIER
A part is used in many products. many to many
Each product is composed of many parts. PRODUCT - PART
A product is bought by many customers. many to many
Each customer buys many products. PRODUCT - CUSTOMER
Note that the ERD in Figure QB.4 uses the PART_PROD, PROD_VEND, and PROD_CUST
entities to convert the M:N relationships to a series of 1:M relationships. Also, note the use of
two composite entities:
The PART_VEND entity’s composite PK is VEND_ID + PART_CODE.
The PART_PROD entity’s composite PK is PART_CODE + PROD_CODE.
The use of these composite PKs means that the relationship between PART and
PART_VEND is strong, as is the relationship between VENDOR and PART_VEND. These
strong relationships are indicated through the use of a solid relationship line.
No PK has been indicated for the PROD_CUST entity, but the existence of weak
relationships—note the dashed relationship lines—lets you assume that the PROD_CUST
entity’s PK is not a composite one. In this case, a revision of the ERD might include the
establishment of a composite PK (PROD_CODE + CUST_NUM) for the PROD_CUST entity.
(If you are using Microsoft Visio Professional, declaring the relationships between
CUSTOMER and PROD_CUST and between PRODUCT and PROD_CUST to be strong will
automatically generate the composite PK, PROD_CODE + CUST_NUM.)
5. Write the connectivity and cardinality for each of the entities shown in Question 4.
Answer: We have indicated the connectivities and cardinalities in Figure QB.5. (The Crow’s
Foot ERD combines the connectivity and cardinality depiction through the use of the
relationship symbols. Therefore, the use of text boxes—we have created those with the Visio
text tool—to indicate connectivities and cardinalities is technically redundant.)
, FIGURE QB.5 Connectivities and Cardinalities
Figure QB.5’s connectivities and cardinalities are reflected in the business rules:
One part can be supplied by one or more suppliers, and a supplier can supply many
parts.
A product is made up of several parts, and a part can be a component of different
products.
A product can be bought by several customers, and a customer can purchase
several products.
6. What is a module, and what role does a module play within the system?
Answer: A module is a separate and independent collection of application programs that
covers a given operational area within an information system. A module accomplishes a
specific system function, and it is, therefore, a component of the overall system. For example,
a system designed for a retail company may be composed of the modules shown in Figure
QB.6.
TABLE OF CONTENTS
Answers to Review Questions..................................................................................................1
Answers to Problems................................................................................................................ 6
ANSWERS TO REVIEW QUESTIONS
1. What factors relevant to database design are revealed during the initial study phase?
Answer: The database initial study phase yields the information required to determine an
organization’s needs, as well as the factors that influence data generation, collection, and
processing. Students must understand that this phase is generally concurrent with the
planning phase of the SDLC and that, therefore, several of the initial study activities are
common to both.
The most important discovery of the initial study phase is the set of the company’s objectives.
Once the designer has a clear understanding of the company’s main goals and its mission,
(s)he can use this as the guide to making all subsequent decisions concerning the analysis,
design, and implementation of the database and the information system.
The initial study phase also establishes the company’s organizational structure; the
description of operations, problems and constraints, and alternate solutions; system
objectives; and the proposed system scope and boundaries.
The organizational structure and the description of operations are interdependent because
operations are usually a function of the company’s organizational structure. The
determination of structure and operations allows the designer to analyze the existing system
and to describe a set of problems, constraints, and possible solutions.
Naturally, the designer must find a feasible solution within the existing constraints. In most
cases, the best solution is not necessarily the most feasible one. The constraints also force
the designer to narrow the focus on very specific problems that must be solved.
In short, the combination of all the factors we have just discussed help the designer to put
together a set of realistic, achievable, and measurable system objectives within the system’s
required scope and boundaries.
2. Why is the organizational structure relevant to the database designer?
Answer: The delivery of information must be timely, it must reach the right people, and the
delivered information must be accurate. Since the proper use of timely and accurate
information is the key factor in the success of any system, the reports and queries drawn from
the database must reach the key decision makers within the organization. Clearly,
understanding the organization structure helps the designer to define the organization’s lines
of communication and to establish reporting requirements.
3. What is the difference between the database design scope and its boundaries? Why is the scope
and boundary statement so important to the database designer?
, Answer: The system’s boundaries are the limits imposed on the database design by external
constraints such as available budget and time, the current level of technology, and end-user
resistance to change. The scope of a database defines the extent of the database design
coverage and reflects a conscious decision to include some things and exclude others. Note
that the existence of boundaries usually has an effect on the system’s scope.
For legal and practical design reasons, the designer cannot afford to work on an unbounded
system. If the system’s limits have not been adequately defined, the designer may be legally
required to expand the system indefinitely. Moreover, an unbounded system will not contain
the built-in constraints that make its use practical in a real-world environment. For example, a
completely unbounded system will never be completed, nor may it ever be ready for
reasonable use. Even a system with an “optimistic” set of bounds may drag the design out
over many years and may cost too much. Keep in mind that company managers almost
invariably want least-cost solutions to specific problems.
4. What business rule(s) and relationships can be described for the ERD shown in
Figure QB.4?
FIGURE QB.4 The ERD for Question 4
Answer: The business rules and relationships are summarized in Table QB.4.
, Table QB.4 Business Rules and Relationships Summary
Business rules Relationships
A supplier supplies many parts. many to many
Each part is supplied by many suppliers. PART - SUPPLIER
A part is used in many products. many to many
Each product is composed of many parts. PRODUCT - PART
A product is bought by many customers. many to many
Each customer buys many products. PRODUCT - CUSTOMER
Note that the ERD in Figure QB.4 uses the PART_PROD, PROD_VEND, and PROD_CUST
entities to convert the M:N relationships to a series of 1:M relationships. Also, note the use of
two composite entities:
The PART_VEND entity’s composite PK is VEND_ID + PART_CODE.
The PART_PROD entity’s composite PK is PART_CODE + PROD_CODE.
The use of these composite PKs means that the relationship between PART and
PART_VEND is strong, as is the relationship between VENDOR and PART_VEND. These
strong relationships are indicated through the use of a solid relationship line.
No PK has been indicated for the PROD_CUST entity, but the existence of weak
relationships—note the dashed relationship lines—lets you assume that the PROD_CUST
entity’s PK is not a composite one. In this case, a revision of the ERD might include the
establishment of a composite PK (PROD_CODE + CUST_NUM) for the PROD_CUST entity.
(If you are using Microsoft Visio Professional, declaring the relationships between
CUSTOMER and PROD_CUST and between PRODUCT and PROD_CUST to be strong will
automatically generate the composite PK, PROD_CODE + CUST_NUM.)
5. Write the connectivity and cardinality for each of the entities shown in Question 4.
Answer: We have indicated the connectivities and cardinalities in Figure QB.5. (The Crow’s
Foot ERD combines the connectivity and cardinality depiction through the use of the
relationship symbols. Therefore, the use of text boxes—we have created those with the Visio
text tool—to indicate connectivities and cardinalities is technically redundant.)
, FIGURE QB.5 Connectivities and Cardinalities
Figure QB.5’s connectivities and cardinalities are reflected in the business rules:
One part can be supplied by one or more suppliers, and a supplier can supply many
parts.
A product is made up of several parts, and a part can be a component of different
products.
A product can be bought by several customers, and a customer can purchase
several products.
6. What is a module, and what role does a module play within the system?
Answer: A module is a separate and independent collection of application programs that
covers a given operational area within an information system. A module accomplishes a
specific system function, and it is, therefore, a component of the overall system. For example,
a system designed for a retail company may be composed of the modules shown in Figure
QB.6.
