An Example of Logical Database Design

This chapter develops a global conceptual schema and a set of SQL tables for a relational database, given the requirements specification for a retail store database. The example illustrates the database life cycle steps of conceptual data modeling, global schema design, transformation to SQL tables, and normalization of those tables. The example given in the chapter presents a management of a large retail store who need a database to keep track of sales activities. The requirements analysis for this database leads to the six entities and their unique identifiers shown in the chapter. For physical design (access methods, etc.) it is necessary to determine what kind of processing needs to be done on the data. The requirements analysis should determine if there will be substantial database growth; what time frame that growth will take place over; and whether the frequency and type of queries and updates will change, as well. The first step is to develop a conceptual data model diagram and a set of functional dependencies (FDs) to correspond to each of the assertions given. Figures present the diagram for the entity–relationship (ER) model and show the equivalent diagram for the Unified Modeling Language (UML). Normally, the conceptual data model is developed without knowing all the FDs, but in this example the nonkey attributes are omitted so that the entire database can be represented with only a few statements and FDs. The results of this analysis, relative to each of the assertions are given.