The purpose of this week is to present a detailed description of the concepts and techniques used to translate a conceptual data model into a form necessary for database design. You will be introduced to the relational data model—the most common notation used for representing detailed data requirements necessary for database design. Concepts of the relational data model, normalization principles for creating relational models with desirable properties, a process for combining different relational data models into a consolidated one, and how to translate an entity-relationship data model into a relational data model are presented. This week will provide you a transition from typical systems analysis to data analysis methodologies, often discussed in a database course. TFigure 9.2 in our text (page 275) shows the relationship between Data Modeling and the systems development life cycle. In Logical Design, there is a process called normalization. Please describe what this is in your own words.
NOTE: Please read the textbook before attempting this question. Normalization is not making the database normal. There is more to this process
Figure 9-2 shows that database modeling and design activities occur in all phases of the systems development process. In this chapter we discuss methods that help you finalize logical and physical database designs during the design phase. In logical database design you use a process called normalization, which is a way to build a data model that has the properties of simplicity, nonredundancy, and minimal maintenance.
In most situations, many physical database design decisions are implicit or eliminated when you choose the data-management technologies to use with the application. We concentrate on those decisions you will make most frequently and use Microsoft Access to illustrate the range of physical database design parameters you must manage. The interested reader is referred to Hoffer, Ramesh, and Topi (2011) for a more thorough treatment of techniques for logical and physical database design.
Four steps are key to logical database modeling and design:
- 1. Develop a logical data model for each known user interface (form and report) for the application, using normalization principles.
- 2. Combine normalized data requirements from all user interfaces into one consolidated logical database model; this step is called view integration.
- 3. Translate the conceptual E-R data model for the application, developed without explicit consideration of specific user interfaces, into normalized data requirements.
- 4. Compare the consolidated logical database design with the translated E-R model and produce, through view integration, one final logical database model for the application.
.During physical database design, you use the results of these four key logical database design steps. You also consider definitions of each attribute; descriptions
274275of where and when data are entered, retrieved, deleted, and updated; expectations for response time and data integrity; and descriptions of the file and database technologies to be used. These inputs allow you to make key physical database design decisions, including the following:
FIGURE 9-2 Relationship between data modeling and the systems development life cycle.
- 1. Choosing the storage format (called data type) for each attribute from the logical database model; the format is chosen to minimize storage space and to maximize data quality. Data type involves choosing length, coding scheme, number of decimal places, minimum and maximum values, and potentially many other parameters for each attribute.
- 2. Grouping attributes from the logical database model into physical records (in general, this is called selecting a stored record, or data structure).
- 3. Arranging related records in secondary memory (hard disks and magnetic tapes) so that individual and groups of records can be stored, retrieved, and updated rapidly (called file organizations). You should also consider protecting data and recovering data after errors are found.
- 4. Selecting media and structures for storing data to make access more efficient. The choice of media affects the utility of different file organizations. The primary structure used today to make access to data more rapid is key indexes, on unique and nonunique keys.