Database Solutions: A step-by-step guide to building databases

About

Features

• Step-by-step instructions on designing and building databases
• Hints and tips for success
• Comprehensive guidance on avoiding pitfalls and traps
• How to create data models using the new design notation, UML (with appendices mapping to ChenÒ³ notation and the CrowÒ³ Feet notation)
• Two full length, coded example databases written on Microsoft Access 97 and Oracle 8
• Evaluation copy of RationalÒ³ UML visual modelling tool, Rose.
• Shortcuts: 15 sample data models to adapt to your needs, chosen from 7 common business areas, plus SQL scripts for each on the CD-ROM



Description

• Copyright 2000
• Dimensions: 7-3/8x9-1/4
• Pages: 448
• Edition: 1st

• Book
• ISBN-10: 0-201-67476-9
• ISBN-13: 978-0-201-67476-7

Database Solutions:A Step-by-Step Guide to Building Databases by Thomas Connolly & Carolyn Begg

Do 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?

Recipe for disaster:

• Take one RDMS
  
• Follow instructions on packet
  

Recipe for success:

• Take one RDMS n any of the major commercial products will do***Oracle***Informix***SQL Server***Access***Paradox
  
• Add one thorough reading of Database Solutions if you are an inexperienced database designer, or one recap of the methodology if you are an old hand
  
• Use the design and implementation frameworks to plan your timetable
  
• Measure out a common data model that fits your requirements and adapt as necessary
  
• Weigh up advice and tips from the authors - experienced professionals
  

Make life easy. Look in this book and CD-ROM for:

• Step-by-step instructions on designing and building databases
  
• Advice and tips on building a successful database
  
• Comprehensive guidance on avoiding pitfalls and traps
  
• How to create data models using the new design notation, UML (with appendices mapping to Chen's notation and the Crow's Feet notation)
  
• Shortcuts: 15 sample data models to adapt to your needs, chosen from 7 common business areas, plus SQL scripts for each
  
• Two full length, coded example databases written on Microsoft Access 97 and Oracle 8
  
• Evaluation copy of Rational's UML visual modelling tool, Rose.
  

0201674769B04062001



Sample Content

Table of Contents

Preface.

I. BACKGROUND.

1. Introduction.

Examples of the Use of Database Systems.

Database Approach.

The Database.

The Database Management System (DBMS).

Views.

Components of the DBMS Environment.

DBMS Architectures.

Functions of a DBMS.

Database Design.

Advantages and Disadvantages of DBMSs.

2. The Relational Model.

What Is a Data Model?

Terminology.

Relational Data Structure.

Properties of Relational Tables.

Relational Keys.

Representing Relational Databases.

Relational Integrity.

Nulls.

Entity Integrity.

Referential Integrity.

Business Rules.

Relational Languages.

3. The Database Application Lifecycle.

The Software Crisis.

The Information Systems Lifecycle.

The Database Application Lifecycle.

Database Planning.

System Definition.

User Views.

Requirements Collection and Analysis.

Database Design.

DBMS Selection.

Application Design.

Transaction Design.

User Interface Design.

Prototyping.

Implementation.

Data Conversion and Loading.

Testing.

Operational Maintenance.

II. DATABASE ANALYSIS AND DESIGN TECHNIQUES.

4. Fact-Finding.

When Are Fact-Finding Techniques Used?

What Facts Are Collected?

Fact-Finding Techniques.

Examining Documentation.

Interviewing.

Observing the Business in Operation.

Research.

Questionnaires.

The Stayhome Case Study.

<

Preface

Preface

Background

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 we’re trying to represent while ignoring implementation details;

a physical database design phase, in which we decide how we’re 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 company’s specific requirements. Some of the models we provide cover the following common business areas:

Customer order entry

Inventory control

Asset management

Project management

Course management

Human resource management

Payroll 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:

Chen’s notation, consisting of rectangles representing entities and diamonds representing relationships, with lines linking the rectangles and diamonds;

Crow’s Feet notation, again consisting of rectangles representing entities and lines between entities representing relationships, with a crow’s 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: Rumbaugh’s OMT modeling, Booch’s Object-Oriented Analysis and Design, and Jacobson’s 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;

database practitioners;

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 we’ve 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: Chen’s notation and the Crow’s 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 — you’ll 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 we’re discussing.

Accompanying CD-ROM

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:

thomas.connolly@paisley.ac.uk

Acknowledgments

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

0201674769P04062001



Updates

Submit Errata

