Chapter 11 - Conceptual, Logical and Physical Database Design PDF

Preview

Summary

Conceptual, Logical and Physical Database Design...

Description

CHAPTER 11 Conceptual, Logical and Physical Database Design IN THIS CHAPTER, YOU WILL LEARN: ■ About the three stages of database design: conceptual, logical and physical ■ How to design a conceptual model to represent the business and its key functional areas

■ How the conceptual model can be transformed into a logically equivalent set of relations

■ How to translate the logical data model into a set of specific DBMS table specifications

■ About different types of file organization ■ How indexes can be applied to improve data access and retrieval ■ How to estimate data storage requirements

PREVIEW In Chapter 10, you learnt about the Database Life Cycle. The most critical phase of this cycle was that of the actual database design. It is essential that the data characteristics that have been captured in the database initial study are used to build a database model which accurately reflects the user requirements and the needs of the business. Such is the importance of database design; it is broken down into three distinct stages Conceptual database design where we create the conceptual representation of the database by producing a data model which identifies the relevant entities and relationships within our system Logical database design where we design relations based on each entity and define integrity rules to ensure there is no redundant relationships within our database Physical database design is where the physical database is implemented in the target DBMS. In this stage we have to consider how each relation is stored and how the data is accessed.

CHAPTER 11 Conceptual, Logical and Physical Database Design

563

Figure 11.1 shows the procedural flow of these stages and the steps within each which need to be taken.

FIGURE 11.1

The three stages of database design

• • • •

• • • • •

• • • • • •

Data analysis and requirements Entity relationsip modelling and normalization Data model verifi cation Distributed database design

Creating the logical data model Validating the logical data model using normalization Assigning and validating integrity constraints Merging logical models constructed for different parts for the database together Reviewing the logical data model with the user

Translate each relation identifi ed in the logicaldata model into tables Determine a suitable fi le organization Define indexes Defi ne user views Estimate data storage requirements Detemine database security for users

These three stages of database design are not totally intuitive and obvious. There is not one quick or automated method for tackling each stage. A well-designed database takes a considerable amount of time and effort to envisage, build and refine. It cannot be stressed enough that if the time is taken to design your databases properly, then it will provide a solid foundation in which to build a complete system. One of E.F. Codd’s requirements when designing a relational database management system was that the design should maintain logical and physical data independence. The separation of these two stages is very important. Logical design is concerned with what the database looks like to the user. Physical design is concerned with how the logical design maps to the physical storage of the database in secondary storage. Codd’s rules on relational database design stated that:

■ if the logical structure of the database should change, then the way the user views the database should not change (logical data independence)

■ if the physical methods (hardware, storage, etc.) of storing and retrieving data change, then the user interface should not be affected in anyway (physical data independence). In this chapter you will learn about the steps required to complete conceptual, logical and physical database design using a number of examples.

11

564

PART IV Database Design

11.1 CONCEPTUAL DESIGN In the conceptual design stage, data modelling is used to create an abstract database structure that represents real-world objects in the most realistic way possible. The conceptual model must embody a clear understanding of the business and its functional areas. At this level of abstraction, the type of hardware and/or database model to be used might not yet have been identified. Therefore, the design must be software and hardware independent so the system can be set up within any hardware and software platform chosen later. Keep in mind the following minimal data rule: All that is needed is there, and all that is there is needed. In other words, make sure that all data needed are in the model and that all data in the model are needed. All data elements required by the database transactions must be defined in the model, and all data elements defined in the model must be used by at least one database transaction. However, as you apply the minimal data rule, avoid an excessive short-term bias. Focus not only on the immediate data needs of the business, but also on the future data needs. Thus, the database design must leave room for future modifications and additions, ensuring that the business’s investment in information resources will endure. As you re-examine Figure 11.1, note that conceptual design requires four steps, each of which will be examined in the next sections:

■ Data analysis and requirements ■ Entity relationship modelling and normalization ■ Data model verification ■ Distributed database design

11.1.1 Data Analysis and Requirements

11

The first step in conceptual design is to discover the characteristics of the data elements. An effective database is an information factory that produces key ingredients for successful decision making. Appropriate data element characteristics are those that can be transformed into appropriate information. Therefore, the designer’s efforts are focused on:

■ Information needs. What kind of information is needed – that is, what output (reports and queries) must be generated by the system, what information does the current system generate, and to what extent is that information adequate?

■ Information users. Who will use the information? How is the information to be used? What are the different end-user data views?

■ Information sources. Where is the information to be found? How is the information to be extracted once it is found?

■ Information constitution. What data elements are needed to produce the information? What are the data attributes? What relationships exist among the data? What is the data volume? How frequently are the data used? What data transformations are to be used to generate the required information?

CHAPTER 11 Conceptual, Logical and Physical Database Design

565

The designer obtains the answers to those questions from different sources so (s)he can compile the necessary information. Note these sources:

■ Developing and gathering end-user data views. The database designer and the end user(s) interact to jointly develop a precise description of end-user data views. In turn, the end-user data views are used to help identify the database’s main data elements.

■ Directly observing the current system: existing and desired output. The end user usually has an existing system in place (manual or computer-based). The designer reviews the existing system to identify the data and their characteristics. The designer examines the input forms and files (tables) to discover the data type and volume. If the end user already has an automated system in place, the designer carefully examines the current and desired reports to describe the data required to support the reports.

■ Interfacing with the systems design group. The database design process is part of the Systems Development Life Cycle (SDLC). In some cases, the systems analyst in charge of designing the new system will also develop the conceptual database model. (This is usually true in a microcomputer environment.) In other cases, the database design is considered part of the database administrator’s job. The presence of a database administrator (DBA) usually implies the existence of a formal data-processing department. The DBA designs the database according to the specifications created by the systems analyst. To develop an accurate data model, the designer must have a thorough understanding of the company’s data types and their extent and uses. But data do not by themselves yield the required understanding of the total business. From a database point of view, the collection of data becomes meaningful only when business rules are defi ned. Remember from Chapter 2, Data Models, that a business rule is a brief and precise narrative description of a policy, procedure or principle within a specific organization’s environment. Business rules, derived from a detailed description of an organization’s operations, help to create and enforce actions within that organization’s environment. When business rules are written properly, they define entities, attributes, relationships, multiplicities and constraints. To be effective, business rules must be easy to understand and they must be widely disseminated to ensure that every person in the organization shares a common interpretation of the rules. Using simple language, business rules describe the main and distinguishing characteristics of the data as viewed by the company. Examples of business rules are as follows:

■ A customer may make many payments on account. ■ Each payment on account is credited to only one customer. ■ A customer may generate many invoices. ■ Each invoice is generated by only one customer. Given their critical role in database design, business rules must not be established casually. Poorly defined or inaccurate business rules lead to database designs and implementations that fail to meet the needs of the organization’s end users. Ideally, business rules are derived from a formal description of operations. As its name implies, a description of operations is a document that provides a precise, detailed, up-to-date and thoroughly reviewed description of the activities that define an organization’s operating environment. To the database designer, the operating environment is both the data sources and the data users. Naturally, the organization’s operating environment is dependent on the organization’s mission. For example, the operating environment of a university would be quite different from that of a steel manufacturer, an airline or a nursing home. Yet no matter how different the organizations may be, the data analysis and requirements component of the database design process is enhanced when the data environment and data use are described accurately and precisely within a description of operations.

11

566

PART IV Database Design

In a business environment, the main sources of information for the description of operations – and, therefore, of business rules – are company managers, policy makers, department managers and written documentation such as company procedures, standards and operations manuals. A faster and more direct source of business rules is direct interviews with end users. Unfortunately, because perceptions differ, the end user can be a less reliable source when it comes to specifying business rules. For example, a maintenance department mechanic may believe that any mechanic can initiate a maintenance procedure, when actually only mechanics with inspection authorization should perform such a task. Such a distinction may seem trivial, but it has major legal consequences. Although end users are crucial contributors to the development of business rules, it pays to verify end-user perceptions. Often interviews with several people who perform the same job yield very different perceptions of what their job components are. While such a discovery may point to ‘management problems’, that general diagnosis does not help the database designer. Given the discovery of such problems, the database designer’s job is to reconcile the differences and verify the results of the reconciliation to ensure that the business rules are appropriate and accurate. Knowing the business rules enables the designer to understand fully how the business works and what role the data plays within company operations. Consequently, the designer must identify the company’s business rules and analyze their impact on the nature, role and scope of data. Business rules yield several important benefits in the design of new systems: They help standardize the company’s view of data.

■ They constitute a communications tool between users and designers. ■ They allow the designer to understand the nature, role and scope of the data. ■ They allow the designer to understand business processes. ■ They allow the designer to develop appropriate relationship participation rules and foreign key constraints. (See Chapter 5, Data Modelling with Entity Relationship Diagrams.) The last point is especially noteworthy: whether a given relationship is mandatory or optional is usually a function of the applicable business rule.

11

Example Data Analysis and Requirements for a DVD Rental Store To illustrate the first stage of the conceptual design process, let us now consider an example based on a DVD rental store. Within this store movie titles are classified according to their type: comedy, family, documentary, action and new release. Each type contains many possible titles, and most titles within a type are available in multiple copies. For example, note the summary presented in Table 11.1

TABLE 11.1

The DVD rental type and title relationship Type

Title

Copy

Family

Chronicles of Narnia Chronicles of Narnia Toy Story Toy Story Toy Story

1 2 1 2 3

Comedy

Simpsons Simpsons Simpsons

1 2 3

Action

Lord of the Rings Lord of the Rings

1 2

566

PART IV Database Design

In a business environment, the main sources of information for the description of operations – and, therefore, of business rules – are company managers, policy makers, department managers and written documentation such as company procedures, standards and operations manuals. A faster and more direct source of business rules is direct interviews with end users. Unfortunately, because perceptions differ, the end user can be a less reliable source when it comes to specifying business rules. For example, a maintenance department mechanic may believe that any mechanic can initiate a maintenance procedure, when actually only mechanics with inspection authorization should perform such a task. Such a distinction may seem trivial, but it has major legal consequences. Although end users are crucial contributors to the development of business rules, it pays to verify end-user perceptions. Often interviews with several people who perform the same job yield very different perceptions of what their job components are. While such a discovery may point to ‘management problems’, that general diagnosis does not help the database designer. Given the discovery of such problems, the database designer’s job is to reconcile the differences and verify the results of the reconciliation to ensure that the business rules are appropriate and accurate. Knowing the business rules enables the designer to understand fully how the business works and what role the data plays within company operations. Consequently, the designer must identify the company’s business rules and analyze their impact on the nature, role and scope of data. Business rules yield several important benefits in the design of new systems: They help standardize the company’s view of data.

■ They constitute a communications tool between users and designers. ■ They allow the designer to understand the nature, role and scope of the data. ■ They allow the designer to understand business processes. ■ They allow the designer to develop appropriate relationship participation rules and foreign key constraints. (See Chapter 5, Data Modelling with Entity Relationship Diagrams.) The last point is especially noteworthy: whether a given relationship is mandatory or optional is usually a function of the applicable business rule.

11

Example Data Analysis and Requirements for a DVD Rental Store To illustrate the first stage of the conceptual design process, let us now consider an example based on a DVD rental store. Within this store movie titles are classified according to their type: comedy, family, documentary, action and new release. Each type contains many possible titles, and most titles within a type are available in multiple copies. For example, note the summary presented in Table 11.1

TABLE 11.1

The DVD rental type and title relationship Type

Title

Copy

Family

Chronicles of Narnia Chronicles of Narnia Toy Story Toy Story Toy Story

1 2 1 2 3

Comedy

Simpsons Simpsons Simpsons

1 2 3

Action

Lord of the Rings Lord of the Rings

1 2

CHAPTER 11 Conceptual, Logical and Physical Database Design

567

You have been asked to produce a database for this store and have been provided with the following set of business rules from the manager:

■ The movie type classification is standard; not all types are necessarily in stock. ■ The movie list is updated as necessary; however, a movie on that list might not be ordered if the DVD shop owner decides that it the movie is not desirable for some reason.

■ The DVD rental shop does not necessarily order movies from the entire vendor list; some vendors on the vendor list are merely potential vendors from whom movies may be ordered in the future.

■ Movies classified as new releases are reclassified to an appropriate type after they have been in stock for more than 30 days.

■ The video shop manager wants to have an end-of-period (week, month, year) report for the number of rentals by type.

■ If a customer requests a title, the shop assistant must be able to find it quickly. When a customer selects one or more titles, an invoice is written. Each invoice may thus contain charges for one or more titles. All customers pay in cash.

■ When the customer checks out a title, a record is kept of the checkout date and time and the expected return date and time. Upon the return of rented titles, the shop assistant must be able to check quickly whether the return is late and to assess the appropriate late return fee.

■ The DVD store owner wants to be able to generate periodic revenue reports by title and by type. The owner also wants to be able to generate periodic inventory reports and to keep track of titles on order.

■ The DVD store owner, who employs two (salaried) full-time and three (hourly) part-time employees, wants to keep track of all employee work time and payroll data. Part-time employees must arrange entries in a work schedule, while all employees sign in and out on a work log.

NOTE When capturing the requirements, the description of operations not only establishes the operational aspects of the business; it also establishes some specific system objectives we have listed next.

As you start to think about designing this database, remember that transaction and information requirements help drive the design by defining required entities, relationships and attributes. Also, keep in mind that the description provided by the problem leaves many possibilities for design differences. For example, consider the EMPLOYEE classification as full-time or part-time. If there are few distinguishing characteristics between the two, the situation may be handled by using an ...