Home > Store > Data

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

Register your product to gain access to bonus material or receive a coupon.

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

Book

  • This product currently is not for sale.
Not for Sale

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.

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.

Entities.

Relationships.

Degree of a Relationship.

Recursive Relationships.

Attributes.

Simple and Composite Attributes.

Single-Valued and Multi-Valued Attributes.

Derived Attributes.

Keys.

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.

Fan Traps.

Chasm Traps.

6. Normalization.

Introduction.

Data Redundancy and Update Anomalies.

Insertion Anomalies.

Deletion Anomalies.

Modification 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 Entities.

Identify Relationships.

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.

Specialization/Generalization.

Superclasses and Subclasses.

Superclass/Subclass Relationships.

Attribute Inheritance.

Specialization Process.

Generalization Process.

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.

Analyze Transactions.

Choose File Organizations.

Choose Indexes.

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.

SQL.

QBE (Query-by-Example).

Sample Queries for Stayhome.

V. SECOND WORKED EXAMPLE.

18. Perfect Pets — Logical Database Design.

Perfect Pets.

Data Requirements.

Transaction Requirements.

Using the Logical Database Design Methodology.

19. Perfect Pets — Physical Database Design.

Using the Physical Database Design Methodology.

What Next?

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.
Appendix C: File Organizations and Indexes.

Basic Concepts.

Heap Files.

Ordered Files.

Hash Files.

Indexes.

Types of Indexes.

Secondary Indexes.

Multilevel Indexes.

B+-Trees.

Appendix D: Common Data Models.

Customer Order Entry.

Inventory Control.

Asset Management.

Project Management.

Course Management.

Human Resource Management.

Payroll Management.

Vehicle Rentals.

Student Accommodation.

Client Transportation.

Publisher Printing.

County Library.

Real Estate Rentals.

Travel Agent.

Student Results.

Glossary.
References.
Index. 0201674769T04062001

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

More Information

ONE MONTH ACCESS!

WITH PURCHASE


Get unlimited 30-day access to thousands of Books & Training Videos about technology, professional development and digital media If you continue your subscription after your 30-day trial, you can receive 30% off a monthly subscription to the Safari Library for up to 12 months.