WIXLIB

Teorey.book Page xiii Saturday, July 16, 2005 12:57 PMContentsChapter 9CASE Tools for Logical Database Design 1879.1 Introduction to the CASE Tools 1889.2 Key Capabilities to Watch For 1919.3 The Basics 1929.4 Generating a Database from a Design 1969.5 Database Support 1999.6 Collaborative Support 2009.7 Distributed Development 2019.8 Application Life Cycle Tooling Integration 2029.9 Design Compliance Checking 2049.10 Reporting 2069.11 Modeling a Data Warehouse 2079.12 Semi-Structured Data, XML 2099.13 Summary 2119.14 Literature Summary 211Appendix: The Basics of s to Selected ExercisesAbout the AuthorsIndex265263259xiii

Teorey.book Page 1 Saturday, July 16, 2005 12:57 PM1IntroductionDatabase technology has evolved rapidly in the three decades sincethe rise and eventual dominance of relational database systems.While many specialized database systems (object-oriented, spatial, multimedia, etc.) have found substantial user communities in the scienceand engineering fields, relational systems remain the dominant databasetechnology for business enterprises.Relational database design has evolved from an art to a science thathas been made partially implementable as a set of software design aids.Many of these design aids have appeared as the database component ofcomputer-aided software engineering (CASE) tools, and many of themoffer interactive modeling capability using a simplified data modelingapproach. Logical design—that is, the structure of basic data relationships and their definition in a particular database system—is largely thedomain of application designers. These designers can work effectivelywith tools such as ERwin Data Modeler or Rational Rose with UML, aswell as with a purely manual approach. Physical design, the creation ofefficient data storage and retrieval mechanisms on the computing platform being used, is typically the domain of the database administrator(DBA). Today’s DBAs have a variety of vendor-supplied tools available tohelp design the most efficient databases. This book is devoted to thelogical design methodologies and tools most popular for relationaldatabases today. Physical design methodologies and tools are covered ina separate book.1

Teorey.book Page 2 Saturday, July 16, 2005 12:57 PM2CHAPTER 1IntroductionIn this chapter, we review the basic concepts of database management and introduce the role of data modeling and database design inthe database life cycle.1.1 Data and Database ManagementThe basic component of a file in a file system is a data item, which is thesmallest named unit of data that has meaning in the real world—forexample, last name, first name, street address, ID number, or politicalparty. A group of related data items treated as a single unit by an application is called a record. Examples of types of records are order, salesperson,customer, product, and department. A file is a collection of records of asingle type. Database systems have built upon and expanded these definitions: In a relational database, a data item is called a column orattribute; a record is called a row or tuple; and a file is called a table.A database is a more complex object; it is a collection of interrelatedstored data that serves the needs of multiple users within one or moreorganizations, that is, interrelated collections of many different types oftables. The motivations for using databases rather than files includegreater availability to a diverse set of users, integration of data for easieraccess to and updating of complex transactions, and less redundancy ofdata.A database management system (DBMS) is a generalized software system for manipulating databases. A DBMS supports a logical view(schema, subschema); physical view (access methods, data clustering);data definition language; data manipulation language; and importantutilities, such as transaction management and concurrency control, dataintegrity, crash recovery, and security. Relational database systems, thedominant type of systems for well-formatted business databases, alsoprovide a greater degree of data independence than the earlier hierarchical and network (CODASYL) database management systems. Data independence is the ability to make changes in either the logical or physicalstructure of the database without requiring reprogramming of application programs. It also makes database conversion and reorganizationmuch easier. Relational DBMSs provide a much higher degree of dataindependence than previous systems; they are the focus of our discussion on data modeling.

Teorey.book Page 3 Saturday, July 16, 2005 12:57 PM1.2 The Database Life Cycle1.2 The Database Life CycleThe database life cycle incorporates the basic steps involved in designinga global schema of the logical database, allocating data across a computer network, and defining local DBMS-specific schemas. Once thedesign is completed, the life cycle continues with database implementation and maintenance. This chapter contains an overview of the database life cycle, as shown in Figure 1.1. In succeeding chapters, we willfocus on the database design process from the modeling of requirementsthrough logical design (steps I and II below). The result of each step ofthe life cycle is illustrated with a series of diagrams in Figure 1.2. Eachdiagram shows a possible form of the output of each step, so the readercan see the progression of the design process from an idea to actual database implementation. These forms are discussed in much more detail inChapters 2 through 6.I.Requirements analysis. The database requirements are determined by interviewing both the producers and users of data andusing the information to produce a formal requirements specification. That specification includes the data required for processing, the natural data relationships, and the software platform forthe database implementation. As an example, Figure 1.2 (step I)shows the concepts of products, customers, salespersons, andorders being formulated in the mind of the end user during theinterview process.II. Logical design. The global schema, a conceptual data model diagram that shows all the data and their relationships, is developed using techniques such as ER or UML. The data modelconstructs must ultimately be transformed into normalized (global) relations, or tables. The global schema development methodology is the same for either a distributed or centralizeddatabase.a.Conceptual data modeling. The data requirements are analyzedand modeled using an ER or UML diagram that includes, forexample, semantics for optional relationships, ternary relationships, supertypes, and subtypes (categories). Processingrequirements are typically specified using natural language3

Teorey.book Page 4 Saturday, July 16, 2005 12:57 PM4IntroductionCHAPTER 1Information requirementsDetermine requirementsLogical design[multiple views]ModelIntegrate views[single view]Transform to SQL tablesNormalizePhysical designSelect indexes[special ent[else]Monitor and detect changing requirements[defunct]Figure 1.1The database life cycleexpressions or SQL commands, along with the frequency ofoccurrence. Figure 1.2 [step II(a)] shows a possible ER modelrepresentation of the product/customer database in the mindof the end user.

Teorey.book Page 5 Saturday, July 16, 2005 12:57 PM1.2 The Database Life CycleStep I Requirements Analysis (reality)SalespersonsProductsOrdersCustomersStep II Logical designStep II(a) Conceptual data iewNserved-bysold-by1salespersonNStep II(b) View integrationCustomerviewcustomerIntegrationof retailsalesperson’sand customer’sviewsFigure -by1salesperson1fills-outproductLife cycle results, step-by-stepb.View integration. Usually, when the design is large and morethan one person is involved in requirements analysis, multiple views of data and relationships result. To eliminate redundancy and inconsistency from the model, these views musteventually be “rationalized” (resolving inconsistencies due tovariance in taxonomy, context, or perception) and then consolidated into a single global view. View integration requiresthe use of ER semantic tools such as identification of synonyms, aggregation, and generalization. In Figure 1.2 [step5

Teorey.book Page 6 Saturday, July 16, 2005 12:57 PM6CHAPTER 1IntroductionII(b)], two possible views of the product/customer databaseare merged into a single global view based on common datafor customer and order. View integration is also important forapplication integration.c.Transformation of the conceptual data model to SQL tables. Basedon a categorization of data modeling constructs and a set ofmapping rules, each relationship and its associated entitiesare transformed into a set of DBMS-specific candidate relational tables. We will show these transformations in standard SQL in Chapter 5. Redundant tables are eliminated aspart of this process. In our example, the tables in step II(c) ofFigure 1.2 are the result of transformation of the integratedER model in step II(b).d.Normalization of tables. Functional dependencies (FDs) arederived from the conceptual data model diagram and thesemantics of data relationships in the requirements analysis.They represent the dependencies among data elements thatare unique identifiers (keys) of entities. Additional FDs thatrepresent the dependencies among key and nonkey attributeswithin entities can be derived from the requirements specification. Candidate relational tables associated with all derivedFDs are normalized (i.e., modified by decomposing or splitting tables into smaller tables) using standard techniques.Finally, redundancies in the data in normalized candidatetables are analyzed further for possible elimination, with theconstraint that data integrity must be preserved. An exampleof normalization of the Salesperson table into the newSalesperson and SalesVacations tables is shown in Figure1.2 from step II(c) to step II(d).We note here that database tool vendors tend to use theterm logical model to refer to the conceptual data model, andthey use the term physical model to refer to the DBMS-specificimplementation model (e.g., SQL tables). Note also that manyconceptual data models are obtained not from scratch, butfrom the process of reverse engineering from an existing DBMSspecific schema [Silberschatz, Korth, and Sudarshan, 2002].III. Physical design. The physical design step involves the selection of indexes (access methods), partitioning, and clustering ofdata. The logical design methodology in step II simplifies theapproach to designing large relational databases by reducing the

Teorey.book Page 7 Saturday, July 16, 2005 12:57 PM1.2 The Database Life CycleStep II(c) Transformation of the conceptual model to SQL nameqty-in-stockcreate table customer(cust no integer,cust name char(15),cust addr char(30),sales name char(15),prod no integer,primary key (cust no),foreign key (sales name)references salespersonforeign key (prod no)references -noorder-noprod-noStep II(d) Normalization of SQL tablesDecomposition of tables and removal of update eptjob-leveljob-levelvacation-daysStep III Physical designIndexingClusteringPartitioningMaterialized viewsDenormalizationFigure 1.2(continued)number of data dependencies that need to be analyzed. This isaccomplished by inserting conceptual data modeling and integration steps [steps II(a) and II(b) of Figure 1.2] into the tradi-7

Teorey.book Page 8 Saturday, July 16, 2005 12:57 PM8CHAPTER 1Introductiontional relational design approach. The objective of these steps isan accurate representation of reality. Data integrity is preservedthrough normalization of the candidate tables created when theconceptual data model is transformed into a relational model.The purpose of physical design is to optimize performance asclosely as possible.As part of the physical design, the global schema can sometimes be refined in limited ways to reflect processing (query andtransaction) requirements if there are obvious, large gains to bemade in efficiency. This is called denormalization. It consists ofselecting dominant processes on the basis of high frequency,high volume, or explicit priority; defining simple extensions totables that will improve query performance; evaluating total costfor query, update, and storage; and considering the side effects,such as possible loss of integrity. This is particularly importantfor Online Analytical Processing (OLAP) applications.IV. Database implementation, monitoring, and modification. Once the design is completed, the database can be createdthrough implementation of the formal schema using the datadefinition language (DDL) of a DBMS. Then the data manipulation language (DML) can be used to query and update the database, as well as to set up indexes and establish constraints, suchas referential integrity. The language SQL contains both DDLand DML constructs; for example, the create table command represents DDL, and the select command represents DML.As the database begins operation, monitoring indicateswhether performance requirements are being met. If they arenot being satisfied, modifications should be made to improveperformance. Other modifications may be necessary whenrequirements change or when the end users’ expectationsincrease with good performance. Thus, the life cycle continueswith monitoring, redesign, and modifications. In the next twochapters we look first at the basic data modeling concepts andthen—starting in Chapter 4—we apply these concepts to thedatabase design process.1.3 Conceptual Data ModelingConceptual data modeling is the driving component of logical databasedesign. Let us take a look at how this component came about, and why

Teorey.book Page 9 Saturday, July 16, 2005 12:57 PM1.3 Conceptual Data Modelingit is important. Schema diagrams were formalized in the 1960s byCharles Bachman. He used rectangles to denote record types anddirected arrows from one record type to another to denote a one-tomany relationship among instances of records of the two types. Theentity-relationship (ER) approach for conceptual data modeling, one ofthe two approaches emphasized in this book and described in detail inChapter 2, was first presented in 1976 by Peter Chen. The Chen form ofthe ER model uses rectangles to specify entities, which are somewhatanalogous to records. It also uses diamond-shaped objects to representthe various types of relationships, which are differentiated by numbersor letters placed on the lines connecting the diamonds to the rectangles.The Unified Modeling Language (UML) was introduced in 1997 byGrady Booch and James Rumbaugh and has become a standard graphical language for specifying and documenting large-scale software systems. The data modeling component of UML (now UML-2) has a greatdeal of similarity with the ER model and will be presented in detail inChapter 3. We will use both the ER model and UML to illustrate the datamodeling and logical database design examples throughout this book.In conceptual data modeling, the overriding emphasis is on simplicity and readability. The goal of conceptual schema design, where the ERand UML approaches are most useful, is to capture real-world datarequirements in a simple and meaningful way that is understandable byboth the database designer and the end user. The end user is the personresponsible for accessing the database and executing queries and updatesthrough the use of DBMS software, and therefore has a vested interest inthe database design process.The ER model has two levels of definition—one that is quite simpleand another that is considerably more complex. The simple level is theone used by most current design tools. It is quite helpful to the databasedesigner who must communicate with end users about their data requirements. At this level you simply describe, in diagram form, the entities,attributes, and relationships that occur in the system to be conceptualized, using semantics that are definable in a data dictionary. Specializedconstructs, such as “weak” entities or mandatory/optional existencenotation, are also usually included in the simple form. But very little elseis included, to avoid cluttering up the ER diagram while the designer’sand end user’s understandings of the model are being reconciled.An example of a simple form of ER model using the Chen notation isshown in Figure 1.3. In this example, we want to keep track of videotapes and customers in a video store. Videos and customers are represented as entities Video and Customer, and the relationship “rents”9

Teorey.book Page 10 Saturday, July 16, 2005 12:57 PM10CHAPTER teVideovideo-idcopy-notitleFigure 1.3A simple form of ER model using the Chen notationshows a many-to-many association between them. Both Video and Customer entities have a few attributes that describe their characteristics,and the relationship “rents” has an attribute due date that representsthe date that a particular video rented by a specific customer must bereturned.From the database practitioner’s standpoint, the simple form of theER model (or UML) is the preferred form for both data modeling and enduser verification. It is easy to learn and applicable to a wide variety ofdesign problems that might be encountered in industry and small businesses. As we will demonstrate, the simple form can be easily translatedinto SQL data definitions, and thus it has an immediate use as an aid fordatabase implementation.The complex level of ER model definition includes concepts that gowell beyond the simple model. It includes concepts from the semanticmodels of artificial intelligence and from competing conceptual datamodels. Data modeling at this level helps the database designer capturemore semantics without having to resort to narrative explanations. It isalso useful to the database application programmer, because certainintegrity constraints defined in the ER model relate directly to code—code that checks range limits on data values and null values, for example. However, such detail in very large data model diagrams actuallydetracts from end user under

Data Structures Hanan Samet Joe Celko’s SQL Programming Style Joe Celko Data Mining, Second Edition: Concepts and Techniques Ian Witten and Eibe Frank Fuzzy Modeling and Genetic Algorithms for Data Mining and Exploration Earl Cox Data Modeling Essentials, Third Edition Gra