For your convenience Apress has placed some of the front
matter material after the index. Please use the Bookmarks
and Contents at a Glance links to access them.
Download from Wow! eBook <www.wowebook.com>
v
Contents at a Glance
Foreword ......................................................................................................................xv
About the Author ...................................................................................................... xvii
About the Technical Reviewer .....................................................................................xix
Acknowledgments ....................................................................................................... xxi
Introduction ...............................................................................................................xxiii
Chapter 1: What Can Go Wrong ■ ....................................................................................1
Chapter 2: Guided Tour of the Development Process ■ ...................................................9
Chapter 3: Initial Requirements and Use Cases ■ .........................................................25
Chapter 4: Learning from the Data Model ■ .................................................................43
Chapter 5: Developing a Data Model ■ .........................................................................59
Chapter 6: Generalization and Specialization ■ ............................................................75
Chapter 7: From Data Model to Relational Database Design ■ .....................................93
Chapter 8: Normalization ■ .........................................................................................113
Chapter 9: More on Keys and Constraints ■ ...............................................................129
Chapter 10: Query Basics ■ ........................................................................................141
Chapter 11: User Interface ■ ......................................................................................157
Chapter 12: Other Implementations ■ ........................................................................169
Appendix ■ ..................................................................................................................189
Index ...........................................................................................................................221
xxiii
Introduction
Everyone keeps data. Big organizations spend millions to look after their payroll, customer, and transaction
data. e penalties for getting it wrong are severe: businesses may collapse, shareholders and customers lose
money, and for many organizations (airlines, health boards, energy companies), it is not exaggerating to say that
even personal safety may be put at risk. And then there are the lawsuits. e problems in successfully designing,
installing, and maintaining such large databases are the subject of numerous books on data management and
software engineering. However, many small databases are used within large organizations and also for small
businesses, clubs, and private concerns. When these go wrong, it doesn’t make the front page of the papers; but
the costs, often hidden, can be just as serious.
Where do we find these smaller electronic databases? Sports clubs will have membership information and
match results; small businesses might maintain their own customer data. Within large organizations, there will
also be a number of small projects to maintain data information that isn’t easily or conveniently managed by the
large system–wide databases. Researchers may keep their own experiment and survey results; groups will want
to manage their own rosters or keep track of equipment; departments may keep their own detailed accounts and
submit just a summary to the organization’s financial software.
Most of these small databases are set up by end users. ese are people whose main job is something other
than that of a computer professional. ey will typically be scientists, administrators, technicians, accountants, or
teachers, and many will have only modest skills when it comes to spreadsheet or database software.
e resulting databases often do not live up to expectations. Time and energy is expended to set up a few
tables in a database product such as Microsoft Access, or in setting up a spreadsheet in a product such as Excel.
Even more time is spent collecting and keying in data. But invariably (often within a short time frame) there is a
problem producing what seems to be a quite simple report or query. Often this is because the way the tables have
been set up makes the required result very awkward, if not impossible, to achieve.
Getting It Wrong
A database that does not fulfill expectations becomes a costly exercise in more ways than one. We clearly have the
cost of the time and eort expended on setting up an unsatisfactory application. However, a much more serious
problem is the inability to make the best use of valuable data. is is especially so for research data. Scientific
and social researchers may spend considerable money and many years designing experiments, hiring assistants,
and collecting and analyzing data, but often very little thought goes into storing it in an appropriately designed
database. Unfortunately, some quite simple mistakes in design can mean that much of the potential information
is lost. e immediate objective may be satisfied, but unforeseen uses of the data may be seriously compromised.
Next year’s grant opportunities are lost.
Another hidden cost comes from inaccuracies in the data. Poor database design allows what should be
avoidable inconsistencies to be present in the data. Poor handling of categories can cause summaries and reports
to be misleading or, to be blunt, wrong. In large organizations, the accumulated eects of each department’s
inaccurate summary information may go unnoticed.
■ IntroduCtIon
xxiv
Problems with a database are not necessarily caused by a lack of knowledge about the database product
itself (though this will eventually become a constraint) but are often the result of having chosen the wrong
attributes to group together in a particular table. is comes about for two main reasons:
e creator does not have a clear idea of what information the database is meant to be delivering in the short
and medium term
e creator does not have a clear model of the dierent classes of data and their relationships to each other
is book describes techniques for gaining a precise understanding of what a problem is about, how to
develop a conceptual model of the data involved, and how to translate that model into a database design. You’ll
learn to design better databases. You’ll avoid the cost of “getting it wrong.”
Create a Data Model
e chasm between having a basic idea of what your database needs to be able to do and designing the
appropriate tables is bridged by having a clear data model. Data modeling involves thinking very carefully about
the dierent sets or classes of data needed for a particular problem.
Here is a very simple textbook example: a small business might have customers, products, and orders. We
need to record a customer’s name. at clearly belongs with our set of customer data. What about address? Now,
does that mean the customer’s contact address (in which case it belongs to the customer data) or where we are
shipping the order (in which case it belongs with information about the order)? What about discount rate? Does
that belong with the customer (some are gold card customers), or the product (dinner sets are on special at the
moment), or the order (20% o orders over $400.00), or none of the above, or all of the above, or does it depend
on the boss’s mood?
Getting the correct answers to these questions is obviously vital if you are going to provide a useful database
for yourself or your client. It is no good heading up a column in your spreadsheet “Discount” before you have
a very precise understanding of exactly what a discount means in the context of the current problem. Data
modeling– diagrams provide very precise and easy–to–interpret documentation for answers to questions such as
those just posed. Even more importantly, the process of constructing a data model leads you to ask the questions
in the first place. It is this, more than anything else, that makes data modeling such a useful tool.
e data models we will be looking at in this book are small. ey may represent small problems in their
entirety, but more likely they will be small parts of larger problems. e emphasis will be on looking very carefully
at the relationships between a few classes of data and getting the detail right. is means using the first attempts
at the model to form questions for the user, to find the exceptions (before they find you), and then to make some
pragmatic decisions about how much of the detail is necessary to make a useful database. Without a good data
model, any database is pretty much doomed before it is started.
Data models are often represented visually using some sort of diagram. Diagrams allow you to take in a
large amount of information at a glance, giving you the ability to quickly get the gist of a database design without
having to read a lot of text. We will be using the class diagram notation from UML to represent our data models,
but many other notations are equally useful.
Database Implementation
Once you have a data model that supports your use cases (and all the other details that you have discovered along
the way), you know how big your problem is and the type of detail it will involve. You now have a good foundation
for designing a suitable application and undertaking the implementation.
Conceptually, the translation from data model to designing a database or spreadsheet is simple. In Chapters 7
through 9, we will look at how to design tables and relationships in a relational database (such as Microsoft Access),
which represent the information in the data model. In Chapter 12, we also look at how this might be done in an
object–oriented database or language (e.g., JADE, Visual Basic), and for problems with not too many classes of data,
how you might capture some of the information in a spreadsheet product such as Microsoft Excel.
- 1
- 2
前往页