CHAPTER 7 LOGICAL DATABASE DESIGN L ogical database design is the process of deciding how to arrange the attributes of the entities in a given business environment into database structures, such as the tables of a relational database. The goal of logical database design is to create well structured tables that properly reflect the company’s business environment. The tables will be able to store data about the company’s entities in a non-redundant manner and foreign keys will be placed in the tables so that all the relationships among the entities will be supported. Physical database design, which will be treated in the next chapter, is the process of modifying the logical database design to improve performance.
OBJECTIVES ■ Describe the concept of logical database design. ■ Design relational databases by converting entity-relationship diagrams into relational tables. ■ Describe the data normalization process. ■ Perform the data normalization process.
■ Test tables for irregularities using the data normalization process. ■ Learn basic SQL commands to build data structures. ■ Learn basic SQL commands to manipulate data. CHAPTER OUTLINE Introduction Converting Entities in Ternary Converting E-R Diagrams into Relational Relationships Tables Designing the General Introduction Hardware Co.
Database Converting a Simple Entity Designing the Good Reading Converting Entities in Binary Bookstores Database Relationships Designing the World Music Converting Entities in Unary Association Database Relationships Designing the Lucky Rent-A-Car Database 158 C h a p t e r 7 Logical Database Design The Data Normalization Process Example: Lucky Rent-A-Car Introduction to the Data Testing Tables Converted from E-R Normalization Technique Diagrams Steps in the Data Normalization with Data Normalization Process Building the Data Structure with SQL Example: General Hardware Co. Manipulating the Data with SQL Example: Good Reading Bookstores Summary Example: World Music Association INTRODUCTION Historically, a number of techniques have been used for logical database design. In the 1970s, when the hierarchical and network approaches to database management were the only ones available, a technique known as data normalization was developed. While data normalization has some very useful features, it was difficult to apply in that environment.
Data normalization can also be used to design relational databases and, actually, is a better fit for relational databases than it was for the hierarchical and network databases. But, as the relational approach to database management and the entity-relationship approach to data modeling both blossomed in the 1980s, a very natural and pleasing approach to logical database design evolved in which rules were developed to convert E-R diagrams into relational tables. Optionally, the result of this process can then be tested with the data normalization technique. Thus, this chapter on the logical design of relational databases will proceed in three parts: first, the conversion of E-R diagrams into relational tables, then the data normalization technique, and finally the use of the data normalization technique to test the tables resulting from the E-R diagram conversions.
CONVERTING E-R DIAGRAMS INTO RELATIONAL TABLES Introduction Converting entity-relationship diagrams to relational tables is surprisingly straight- forward, with just a few simple rules to follow. Basically, each entity will convert to a table, plus each many-to-many relationship or associative entity will convert to a table. The only other issue is that during the conversion, certain rules must be followed to ensure that foreign keys appear in their proper places in the tables. We will demonstrate these techniques by methodically converting the E-R diagrams of Chapter 2 into relational tables.
Converting a Simple Entity Figure 7.1 repeats the simple entity box in Figure 2.2 shows a relational table that can store the data represented in the entity box. The table simply contains the attributes that were specified in the entity box. Notice that Salesperson Number is underlined to indicate that it is the unique identifier of the entity, and the primary key of the table. Clearly, the more interesting issues and rules come about when, as almost always happens, entities are involved in relationships with other entities.
Converting E-R Diagrams into Relational Tables 159 CONCEPTS 7-A E COLAB IN ACTION Ecolab is a $3-billion-plus developer interacting with customers for sales and service purposes. and marketer of cleaning, sanitizing, pest elimination, EcoNet also enables the standardization of processes and industrial maintenance and repair products and across the sales and service organizations within the services that was founded in 1923. Its customers include seven various North American business units. This is restaurants, hotels, hospitals, food and beverage plants, achieved by having one application get data from laundries, schools, and other retail and commercial different databases.
Headquartered in St. Paul, MN, Ecolab is truly The system is also used as a sales planning tool. a global company, operating directly in 70 countries and Using EcoNet, a salesperson can access such customer through distributors, licensees, and export operations in information as past and outstanding invoices, service an additional 100 countries. Its domestic and worldwide reports, and order status.
The salesperson can also use operations are supported by 20,000 employees and the system to place new orders. Being Web-based, Econet over 50 manufacturing and distribution facilities. A large can be accessed from a home or office PC, from a laptop percentage of the employees are sales and service at the customer location, and even through handheld individuals who work in a mobile, remote environment. In addition, customers can view their own data One of Ecolab’s applications with a significant through ‘‘My Ecolab.’’ database component is called ‘‘EcoNet.’’ EcoNet gives Implemented in 2002, EcoNet uses an interesting the large sales and service work force access to infor- mix of databases.
mation distributed across many databases. EcoNet pro- vides Ecolab’s North American sales and service people 1. The transactional data, including the last six month’s with a portal into pertinent information needed when orders, is held in a Computer Associates IDMS ‘‘Photo Courtesy of Ecolab’’ Printed by permission of Ecolab, Inc. All rights reserved., 370 Wabasha Street North, St.
160 C h a p t e r 7 Logical Database Design network-type database. EcoNet accesses this ‘‘up- 3. Summarized Sales tables and Key Performance to-the-minute’’ information using screen scrapping Indicators are also bridged to Microsoft SQL Server technology against the IBM mainframe computer relational databases. rather than migrating the data in real time to a relational DBMS.
Ecolab is continually looking for additional informa- 2. Completed transaction data is bridged nightly to a tion to add to the EcoNet application in order to provide data warehouse holding seven years of sales data in their sales and service people with valuable information IBM DB2 Unix. when interacting with customers. SALESPERSON PK Salesperson Number Salesperson Name Commission Percentage F I G U R E 7.1 Year of Hire The entity box from Figure 2.2 Conversion of an E-R diagram entity Salesperson Salesperson Commission Year box to a relational table Number Name Percentage of Hire Converting Entities in Binary Relationships One-to-One Binary Relationship Figure 7.3 repeats the one-to-one binary relation- ship of Figure 2.
There are three options for designing tables to represent this data, as shown in Figure 7.4a, the two entities are combined into one relational table. On the one hand, this is possible because the one-to-one relationship means that for one salesperson, there can only be one associated office and con- versely, for one office there can be only one salesperson. So a particular salesperson and office combination can fit together in one record, as shown in Figure 7. On the other hand, this design is not a good choice for two reasons.
One reason is that the very fact that salesperson and office were drawn in two different entity boxes in the E-R diagram of Figure 7.3 means that they are thought of separately in this business environment and thus should be kept separate in the database. The other reason is the modality of zero at the salesperson in Figure 7. Reading that diagram from right to left, it says that an office might have no one assigned to it. Thus, in the table in Figure 7.4a, there could be a few or possibly many record occurrences that have values for the office number, telephone, and size attributes but have the four attributes pertaining to salespersons empty or null! This could result in a lot of wasted storage space, but it is worse than that.
If Salesperson Number is declared Converting E-R Diagrams into Relational Tables 161 SALESPERSON OFFICE PK Salesperson PK Office Number Number Works in Salesperson Telephone Occupied by Name Size Commission Percentage F I G U R E 7.3 Year of Hire The one-to-one (1-1) binary relationship from Figure 2.4a to be the primary key of the table, this scenario would mean that there would be records with no primary key values, a situation which is clearly not allowed.4b is a better choice. There are separate tables for the salesperson and office entities. In order to record the relationship, i. which salesperson is assigned to which office, the Office Number attribute is placed as a foreign key in the SALESPERSON table.
This connects the salespersons with the offices to which SALESPERSON/OFFICE Salesperson Salesperson Commission Year of Office Number Name Percentage Hire Number Telephone Size a. One-to-one binary relationship converted to a single relational table. SALESPERSON Salesperson Salesperson Commission Year of Office Number Name Percentage Hire Number OFFICE Office Number Telephone Size b. One-to-one binary relationship converted to two relational tables, with the for- eign key in the SALESPERSON table.
SALESPERSON Salesperson Salesperson Commission Year of Number Name Percentage Hire OFFICE Office Salesperson Number Telephone Number Size F I G U R E 7.4 Conversion of an E-R diagram with two c. One-to-one binary relationship converted to two relational tables, with the for- entities in a one-to-one binary relationship eign key in the OFFICE table. into one or two relational tables 162 C h a p t e r 7 Logical Database Design they are assigned. Again, look at the modalities in the E-R diagram in Figure 7.
Reading from left to right, each salesperson is assigned to exactly one office (indicated by the two ‘‘ones’’ adjacent to the office entity). That translates directly into each record in the SALESPERSON table of Figure 7.4b having a value (and a single value, at that) for its Office Number foreign key attribute. That’s good! But what about the problem of unassigned offices mentioned in the previous paragraph? In Figure 7.4b, unassigned offices will each have a record in the OFFICE table, with Office Number as the primary key, which is fine. Their office numbers will simply not appear as foreign key values in the SALESPERSON table.
Finally, instead of placing Office Number as a foreign key in the SALESPERSON table, could you instead place Salesperson Number as a foreign key in the OFFICE table, Figure 7.4c? Recall that, reading the E-R diagram of Figure 7.3 from right to left, the modality of zero adjacent to the salesperson entity says that an office might be empty, i. it might not be assigned to any salesperson. But then, some or perhaps many records of the OFFICE table of Figure 7.4c would have no value or a null in their Salesperson Number foreign key attribute positions. Why bother having to deal with this situation when the design in Figure 7.4b avoids it? Certainly, it follows that if the modalities were reversed, meaning that the zero modality was adjacent to the office entity box and the one modality was adjacent to the salesperson entity box, then the design in Figure 7.4c would be the preferable one.
This would mean that every office must have a salesperson assigned to it but a salesperson may or may not be assigned to an office. Perhaps lots of the salespersons travel most of the time and don’t need offices. By the way, while we’re in ‘‘what if’’ mode, what if the modality was zero on both sides? Then there would be a judgment call to make between the designs of Figure 7.