Database Solutions:A Step-by-Step Guide to Building Databases by Thomas Connolly & Carolyn BeggDo you carry the world on your shoulders? Does every request for a new business system from Senior Management land on your desk? Are you responsible for designing and creating the databases that keep your business running? Would you like to make the design and build process smoother, quicker and more reliable?
I. BACKGROUND.1. Introduction.
Examples of the Use of Database Systems.
The Database Management System (DBMS).
Components of the DBMS Environment.
Functions of a DBMS.
Advantages and Disadvantages of DBMSs.2. The Relational Model.
What Is a Data Model?
Relational Data Structure.
Properties of Relational Tables.
Representing Relational Databases.
Relational Languages.3. The Database Application Lifecycle.
The Software Crisis.
The Information Systems Lifecycle.
The Database Application Lifecycle.
Requirements Collection and Analysis.
User Interface Design.
Data Conversion and Loading.
II. DATABASE ANALYSIS AND DESIGN TECHNIQUES.4. Fact-Finding.
When Are Fact-Finding Techniques Used?
What Facts Are Collected?
Observing the Business in Operation.
The Stayhome Case Study.
The Stayhome Case Study — An Overview.
The Stayhome Case Study — Database Planning.
The Stayhome Case Study — System Definition.
The Stayhome Case Study — Requirements Collection and Analysis.
The Stayhome Case Study — Database Design.5. Entity-Relationship Modeling.
Degree of a Relationship.
Simple and Composite Attributes.
Single-Valued and Multi-Valued Attributes.
Strong and Weak Entities.
Multiplicity Constraints on Relationships.
One-To-One (1:1) Relationships.
One-To-Many (1:*) Relationships.
Many-To-Many (*:*) Relationships.
Multiplicity for Non-Binary Relationships.
Cardinality and Participation Constraints.
Attributes on Relationships.
Design Problems with ER Models.
Chasm Traps.6. Normalization.
Data Redundancy and Update Anomalies.
First Normal Form (1nf).
Second Normal Form (2nf).
Third Normal Form (3nf).
III. LOGICAL DATABASE DESIGN.7. Overview of the Methodology.
Introduction To the Database Design Methodology.
What Is a Database Design Methodology?
What Are the Aims of a Database Design Methodology?
Why Build Data Models?
Critical Success Factors in Database Design.
Overview of the Database Design Methodology.8. Logical Database Design — Step 1.
Build Local Logical Data Model for Each View.
Identify and Associate Attributes with Entities or Relationships.
Determine Attribute Domains.
Determine Candidate and Primary Key Attributes.
Specialize/Generalize Entities (Optional Step).
Remove Features Not Compatible with the Relational Model.
Check That Model Supports User Transactions.9. Logical Database Design — Step 2.
Create and Check Tables for Each Local Logical Data Model.
Create Tables for Local Logical Data Model.
Check Table Structures Using Normalization.
Check Tables Support User Transactions.
Define Integrity Constraints.
Review Local Logical Data Model with Users.10. Logical Database Design — Step 3.
The Business View of Stayhome.
Users' Requirements Specification.
Local Logical Data Model.
Build and Check Global Logical Data Model.
Merge Local Logical Data Models into Global Model.
Check Global Logical Data Model.
Check for Future Growth.
Review Global Logical Data Model with Users.11. Advanced Modeling Techniques.
Superclasses and Subclasses.
Constraints on Superclass/Subclass Relationships.
Creating Tables To Represent Specialization/Generalization.
IV. PHYSICAL DATABASE DESIGN.12. Physical Database Design — Step 4.
Comparison of Logical and Physical Database Design.
Overview of the Physical Database Design Methodology.
Translate Global Logical Data Model for Target DBMS.
Design Base Tables for Target DBMS.
Design Business Rules for Target DBMS.13. Physical Database Design — Step 5.
Understanding System Resources.
Design Physical Representation.
Choose File Organizations.
File Organizations and Indexes for Stayhome with Microsoft Access 97.
Guidelines for Choosing Indexes.
Indexes for Stayhome.14. Physical Database Design — Step 6.
Consider the Introduction of Controlled Redundancy.
Consider Derived Data.
Consider Duplicating Columns or Joining Tables Together.15. Physical Database Design — Step 7.
Design Security Mechanisms.
Design User Views.
Design Access Rules.16. Physical Database Design — Step 8.
Monitor and Tune the Operational System.17. Sample Stayhome Queries Using SQL and QBE.
Introduction To Microsoft SQL and QBE.
Sample Queries for Stayhome.
V. SECOND WORKED EXAMPLE.18. Perfect Pets — Logical Database Design.
Using the Logical Database Design Methodology.19. Perfect Pets — Physical Database Design.
Using the Physical Database Design Methodology.
APPENDICES.Appendix A: Alternative Data Modeling Notations.
ER Modeling Using the Chen Notation.
ER Modeling Using the Crow's Feet Notation.Appendix B: Summary of the Database Design Methodology.
Types of Indexes.
B+-Trees.Appendix D: Common Data Models.
Customer Order Entry.
Human Resource Management.
Real Estate Rentals.
The database is now the underlying framework of the information system and has fundamentally changed the way many companies and individuals work. The developments in this technology over the past few years have produced database systems that are more powerful and more intuitive to use, and users are creating databases and applications without the necessary knowledge to produce an effective and efficient system. Looking at the literature, we found many excellent books that examine a part of the database development life-cycle. However, we found very few that covered analysis, design, and implementation and described the development process in a simple-to-understand way that could be used by both technical and non-technical readers.
Our original concept therefore was to provide a book primarily for the business community that explained as clearly as possible how to analyze, design, and implement a database. This would cover both simple databases consisting of a few tables and large databases containing tens to hundreds of tables. During the initial reviews that we carried out, it became clear that the book would also be useful for the academic community and provide a very simple and clear presentation of a database design methodology that would complement a more extensive recommended textbook, such as our own book Database Systems.
The methodology we present in this book for relational Database Management Systems (DBMSs) the predominant system for business applications at present has been tried and tested over the years in both industrial and academic environments. The methodology is divided into two phases:
a logical database design phase, in which we develop a model of what were trying to represent while ignoring implementation details;
a physical database design phase, in which we decide how were going to realize the implementation in the target DBMS, such as Access, Paradox, Oracle, Informix, or SQL Server.
We present each phase as a series of simple-to-follow steps. For the inexperienced designer, we expect that the steps will be followed in the order described, and guidelines are provided throughout to help with this process. For the experienced designer, the methodology can be less prescriptive, acting more as a framework or checklist.
Helping to understand database design
To help you use the methodology and understand the important issues, we provide a comprehensive worked example that is integrated through the book based on a video rental company called StayHome. To reinforce the methodology we work through a second case study in Chapters 18 and 19 based on a veterinary clinic called Perfect Pets.
To help you further, we have included additional database solutions in Appendix D (with corresponding SQL scripts included on the accompanying CD-ROM). Each solution has a small introduction, which you may like to read and then try to produce the database design yourself before looking at our sample solution.
Common data models
As well as providing you with additional experience of designing databases, Appendix D also provides you with many common data models that you may find useful. In fact, it has been estimated that one-third of a data model consists of common constructs that are applicable to most companies and the remaining two-thirds are either industry-specific or company-specific. Thus, most database design work consists of re-creating constructs that have already been produced many times before in other companies. The models featured may not represent your company exactly, but they may provide a starting point from which you can develop a more suitable model that matches your companys specific requirements. Some of the models we provide cover the following common business areas:
Customer order entry
Human resource management
UML (Unified Modeling Language)
Increasingly, companies are standardizing the way in which they model data by selecting a particular approach to data modeling and using it throughout their database development projects. A popular high-level data model used in logical database design, and the one we use in this book, is based on the concepts of the Entity-Relationship (ER) model. Currently there is no standard notation for an ER model. Most books that cover database design for relational DBMSs tend to use one of two conventional notations:
Chens notation, consisting of rectangles representing entities and diamonds representing relationships, with lines linking the rectangles and diamonds;
Crows Feet notation, again consisting of rectangles representing entities and lines between entities representing relationships, with a crows foot at the end of a line representing a one-to-many relationship.
Both notations are well supported by current CASE tools. However, they can be quite cumbersome to use and a bit difficult to explain. In this book, we instead use the class diagram notation from the latest object-oriented modeling language called UML (Unified Modeling Language). UML is a notation that combines elements from the three major strands of object-oriented design: Rumbaughs OMT modeling, Boochs Object-Oriented Analysis and Design, and Jacobsons Objectory. It is anticipated that UML will become a standard, and the Object Management Group (OMG) has adopted UML as the standard notation for object methods.
We believe you will find this notation easier to understand and use. To help, we provide an evaluation copy of the Rational Rose visual modeling tool on the accompanying CD-ROM. This tool supports UML and will allow you to create your ER diagrams.
Showing how to implement a design
We believe it is important to show you how to convert a database design into a physical implementation. In this book, we show how to implement the first case study (the video rental company called StayHome) in the Microsoft Access 97 DBMS. In contrast, we show how to implement the database design for the second case study (the veterinary clinic called Perfect Pets) in the Oracle 8 DBMS.
Who should read this book?
Who should read this book? We have tried to write this book in a self-
contained way. The exception to this is physical database design, where you need to have a good understanding of how the target DBMS operates. Our intended audience is anyone who needs to develop a database, including but not limited to the following:
information modelers and database designers;
database application designers and implementers;
data and database administrators;
Information Systems, Business IT, and Computing Science professors specializing in database design;
database students, both undergraduate, advanced undergraduate, and graduate;
anyone wishing to design and develop a database application.
Structure of this book
We have divided the book into five parts and a set of four appendices:
Part 1 Background. We provide an introduction to DBMSs and the relational model in Chapters 1 and 2. We also provide an overview of the database application lifecycle in Chapter 3.
Part 2 Database analysis and design techniques. We discuss techniques for database analysis in Chapter 4 and show how to use some of these techniques to analyze the requirements for the video rental company StayHome. We show how to draw Entity-Relationship (ER) diagrams using UML in Chapter 5 and how to apply the rules of normalization in Chapter 6. ER models and normalization are important techniques that are used in the methodology we describe in Part 3.
Part 3 Logical database design methodology. We describe a step-by-step approach for logical database design. In Step 1, we create a local logical data model for each view of the company. In Step 2, we map each model to a set of database tables, and in Step 3 we merge the local data models together to provide a global model of the company.
Part 4 Physical database design methodology. We describe a step-by-step approach for physical database design. In Step 4, we design a set of base tables for the target DBMS. In Step 5, we choose file organizations and indexes. In Step 6, we consider the introduction of controlled redundancy to achieve improved performance. In Step 7, we design the security measures that will protect the data from unauthorized access. Finally, in Step 8 we monitor and tune the operational system. As weve just mentioned, we show you how to implement the design for the StayHome database application in Microsoft Access 97.
Part 5 Second worked example. In Chapters 18 and 19, we work through a second case study for the veterinary clinic Perfect Pets. We show you how to implement the design for the Perfect Pets database application in Oracle 8.
Appendices. Appendix A examines the two main alternative ER notations: Chens notation and the Crows Feet notation. Appendix B provides a summary of the methodology as a quick reference guide. Appendix C provides some background information on file organization and storage structures that may help you understand some aspects of the physical database design methodology presented in Part 3. Appendix D provides a set of 15 common data models.
To make the book as readable as possible, we have adopted the following style and structure:
A set of objectives for each chapter, clearly highlighted at the start of the chapter.
A summary at the end of each chapter covering the main points introduced.
Each important concept that is introduced is clearly defined and highlighted by placing the definition in a box.
A series of notes and tips youll see these throughout the book with an icon adjacent to highlight them.
Diagrams are liberally used throughout to support and clarify concepts.
A very practical orientation. Each chapter contains many worked examples to illustrate the points covered.
A glossary at the end of the book, which you may find useful as a quick reference guide. We also tend to use the margins to give you a reference to the section of the book that defines a concept were discussing.
The accompanying CD-ROM contains the following aids to help you with this book:
(1) An evaluation copy of the visual modeling tool, Rational Rose.
(2) An implementation of the StayHome database application in Microsoft Access 97.
(3) An SQL script to create an implementation of the Perfect Pets database application. This script can be used to create a database in many relational DBMSs, such as Oracle, Informix, and SQL Server.
(4) An SQL script for each common data model defined in Appendix D to create the corresponding set of base tables for the database application. Once again, these scripts can be used to create a database in many relational DBMSs.
Corrections and suggestions
As this type of textbook is so vulnerable to errors, disagreements, omissions, and confusion, your input is solicited for future reprints and editions. Comments, corrections, and constructive suggestions should be sent to Addison-Wesley at Pearson Education, or by electronic mail to:
This book is the outcome of many years of our work in industry, research, and academia. It is therefore difficult to name all the people who have directly or indirectly helped us in our efforts. For those people we are about to omit, we apologize now. However, special thanks and apologies must first go to our families, who over the years have been neglected while we have been writing our books.
We would like to thank Sally Mortimore, our editor, Alison Birtwell, publishing coordinator, Susan Harrison, production editor, and Annette Abel, copyeditor, for their marvelous help, advice, and encouragement. We would also like to thank the reviewers of this book, who contributed their comments, suggestions, and advice. In particular, we would like to mention Stuart Anderson, Andy Osborn, and Willie Favero.
We would also like to thank our secretaries Lyndonne MacLeod and June Blackburn, for their help and support during the years.
Thomas M. Connolly
Carolyn E. Begg
Glasgow, September 1999