Chapter 9 Database Design
Chapter 9
Database Design
Discussion Focus
What is the relationship between a database and an information system, and how does this
relationship have a bearing on database design?
An information system performs three sets of services:
• It provides for data collection, storage, and retrieval.
• It facilitates the transformation of data into information.
• It provides the tools and conditions to manage both data and information.
Basically, a database is a fact (data) repository that serves an information system. If the database is
designed poorly, one can hardly expect that the data/information transformation will be successful, nor
is it reasonable to expect efficient and capable management of data and information.
The transformation of data into information is accomplished through application programs. It is
impossible to produce good information from poor data; and, no matter how sophisticated the
application programs are, it is impossible to use good application programs to overcome the effects of
bad database design. In short: Good database design is the foundation of a successful information
system.
Database design must yield a database that:
• Does not fall prey to uncontrolled data duplication, thus preventing data anomalies and the
attendant lack of data integrity.
• Is efficient in its provision of data access.
• Serves the needs of the information system.
The last point deserves emphasis: even the best-designed database lacks value if it fails to meet
information system objectives. In short, good database designers must pay close attention to the
information system requirements.
Systems design and database design are usually tightly intertwined and are often performed in parallel.
Therefore, database and systems designers must cooperate and coordinate to yield the best possible
information system.
What is the relationship between the SDLC and the DBLC?
The SDLC traces the history (life cycle) of an information system. The DBLC traces the history (life
cycle) of a database system. Since we know that the database serves the information system, it is not
surprising that the two life cycles conform to the same basic phases.
Suggestion: Use Figure 9.8 as the basis for a discussion of the parallel activities.
What basic database design strategies exist, and how are such strategies executed?
, Chapter 9 Database Design
Suggestion: Use Figure 9.14 as the basis for this discussion.
There are two basic approaches to database design: top-down and bottom-up.
Top-down design begins by identifying the different entity types and the definition of each entity's
attributes. In other words, top-down design:
• starts by defining the required data sets and then
• defines the data elements for each of those data sets.
Bottom-up design:
• first defines the required attributes and then
• groups the attributes to form entities.
Although the two methodologies tend to be complementary, database designers who deal with small
databases with relatively few entities, attributes, and transactions tend to emphasize the bottom-up
approach. Database designers who deal with large, complex databases usually find that a primarily top-
down design approach is more appropriate.
In spite of the frequent arguments concerning the best design approach, perhaps the top-down vs.
bottom-up distinction is quite artificial. The text's note is worth repeating:
NOTE
Even if a generally top-down approach is selected, the normalization process that revises
existing table structures is (inevitably) a bottom-up technique. E-R models constitute a top-
down process even if the selection of attributes and entities may be described as bottom-up.
Since both the E-R model and normalization techniques form the basis for most designs, the
top-down vs. bottom-up debate may be based on a distinction without a difference.
Problem Solutions
1. The ABC Car Service & Repair Centers are owned by the SILENT car dealer; ABC services
and repairs only SILENT cars. Three ABC Car Service & Repair Centers provide service and
repair for the entire state.
Each of the three centers is independently managed and operated by a shop manager, a
receptionist, and at least eight mechanics. Each center maintains a fully stocked parts
inventory.
Each center also maintains a manual file system in which each car’s maintenance history is
kept: repairs made, parts used, costs, service dates, owner, and so on. Files are also kept to
track inventory, purchasing, billing, employees’ hours, and payroll.
You have been contacted by the manager of one of the centers to design and implement a
computerized system. Given the preceding information, do the following:
Chapter 9
Database Design
Discussion Focus
What is the relationship between a database and an information system, and how does this
relationship have a bearing on database design?
An information system performs three sets of services:
• It provides for data collection, storage, and retrieval.
• It facilitates the transformation of data into information.
• It provides the tools and conditions to manage both data and information.
Basically, a database is a fact (data) repository that serves an information system. If the database is
designed poorly, one can hardly expect that the data/information transformation will be successful, nor
is it reasonable to expect efficient and capable management of data and information.
The transformation of data into information is accomplished through application programs. It is
impossible to produce good information from poor data; and, no matter how sophisticated the
application programs are, it is impossible to use good application programs to overcome the effects of
bad database design. In short: Good database design is the foundation of a successful information
system.
Database design must yield a database that:
• Does not fall prey to uncontrolled data duplication, thus preventing data anomalies and the
attendant lack of data integrity.
• Is efficient in its provision of data access.
• Serves the needs of the information system.
The last point deserves emphasis: even the best-designed database lacks value if it fails to meet
information system objectives. In short, good database designers must pay close attention to the
information system requirements.
Systems design and database design are usually tightly intertwined and are often performed in parallel.
Therefore, database and systems designers must cooperate and coordinate to yield the best possible
information system.
What is the relationship between the SDLC and the DBLC?
The SDLC traces the history (life cycle) of an information system. The DBLC traces the history (life
cycle) of a database system. Since we know that the database serves the information system, it is not
surprising that the two life cycles conform to the same basic phases.
Suggestion: Use Figure 9.8 as the basis for a discussion of the parallel activities.
What basic database design strategies exist, and how are such strategies executed?
, Chapter 9 Database Design
Suggestion: Use Figure 9.14 as the basis for this discussion.
There are two basic approaches to database design: top-down and bottom-up.
Top-down design begins by identifying the different entity types and the definition of each entity's
attributes. In other words, top-down design:
• starts by defining the required data sets and then
• defines the data elements for each of those data sets.
Bottom-up design:
• first defines the required attributes and then
• groups the attributes to form entities.
Although the two methodologies tend to be complementary, database designers who deal with small
databases with relatively few entities, attributes, and transactions tend to emphasize the bottom-up
approach. Database designers who deal with large, complex databases usually find that a primarily top-
down design approach is more appropriate.
In spite of the frequent arguments concerning the best design approach, perhaps the top-down vs.
bottom-up distinction is quite artificial. The text's note is worth repeating:
NOTE
Even if a generally top-down approach is selected, the normalization process that revises
existing table structures is (inevitably) a bottom-up technique. E-R models constitute a top-
down process even if the selection of attributes and entities may be described as bottom-up.
Since both the E-R model and normalization techniques form the basis for most designs, the
top-down vs. bottom-up debate may be based on a distinction without a difference.
Problem Solutions
1. The ABC Car Service & Repair Centers are owned by the SILENT car dealer; ABC services
and repairs only SILENT cars. Three ABC Car Service & Repair Centers provide service and
repair for the entire state.
Each of the three centers is independently managed and operated by a shop manager, a
receptionist, and at least eight mechanics. Each center maintains a fully stocked parts
inventory.
Each center also maintains a manual file system in which each car’s maintenance history is
kept: repairs made, parts used, costs, service dates, owner, and so on. Files are also kept to
track inventory, purchasing, billing, employees’ hours, and payroll.
You have been contacted by the manager of one of the centers to design and implement a
computerized system. Given the preceding information, do the following:
