Practical Database Design Methodology and Use of UML Diagrams

Chapter 10

Practical Database Design Methodology and Use of UML Diagrams

_In this chapter we move from the database design principles that were presented in Chapters 7 through 9 to examine some of the more practical aspects of database design. We have already described material that is relevant to the design of actual databases for practical real-world applications. This material includes Chapters 7 and 8 on database conceptual modeling; Chapters 3 through 6 on the relational model, the SQL language, and relational algebra and calculus; and Chapter 9 on mapping a high-level conceptual ER or EER schema into a relational schema. We will present additional relevant materials in later chapters, including an overview of programming techniques for relational systems (RDBMSs) in Chapters 13 and 14, and data dependency theory and relational normalization algorithms in Chapters 15 and 16.

The overall database design activity has to undergo a systematic process called the design methodology, whether the target database is managed by an RDBMS, an object database management system (ODBMS, see Chapter 11), an object-relational database management system (ORDBMS, see Chapter 11), or some other type of database management system. Various design methodologies are provided in the database design tools currently supplied by vendors. Popular tools include Oracle Designer and related products in Oracle Developer Suite by Oracle, ERwin and related products by CA, PowerBuilder and PowerDesigner by Sybase, and ER/Studio and related products by Embarcadero Technologies, among many others. Our goal in this chapter is to discuss not one specific methodology but rather data-base design in a broader context, as it is undertaken in large organizations for the design and implementation of applications catering to hundreds or thousands of users.

Generally, the design of small databases with perhaps up to 20 users need not be very complicated. But for medium-sized or large databases that serve several diverse application groups, each with dozens or hundreds of users, a systematic approach to the overall database design activity becomes necessary. The sheer size of a populated database does not reflect the complexity of the design; it is the database schema that is the more important focus of database design. Any database with a schema that includes more than 20 entity types and a similar number of relationship types requires a careful design methodology.

Using the term large database for databases with several dozen gigabytes of data and a schema with more than 30 or 40 distinct entity types, we can cover a wide array of databases used in government, industry, and financial and commercial institutions. Service sector industries, including banking, hotels, airlines, insurance, utilities, and communications, use databases for their day-to-day operations 24 hours a day, 7 days a week—known in the industry as 24 by 7 operations. Application systems for these databases are called transaction processing systems due to the large transaction volumes and rates that are required. In this chapter we will concentrate on the database design for such medium- and large-scale databases where transaction processing dominates.

This chapter has a variety of objectives. Section 10.1 discusses the information system life cycle within organizations with a particular emphasis on the database system. Section 10.2 highlights the phases of a database design methodology within the organizational context. Section 10.3 introduces some types of UML diagrams and gives details on the notations that are particularly helpful in collecting requirements and performing conceptual and logical design of databases. An illustrative partial example of designing a university database is presented. Section 10.4 introduces the popular software development tool called Rational Rose, which uses UML diagrams as its main specification technique. Features of Rational Rose specific to database requirements modeling and schema design are highlighted. Section 10.5 briefly dis-cusses automated database design tools. Section 10.6 summarizes the chapter.