Practical Database Design for the Web

312 Pages·2002·16.231 MB·English

by  Chris Auld, Allan Kent, Rudy Limeback, Nigel Stanger, Thearon Willis (auth.)

Summary of Contents
lntroduction 1
Chapter 1: Database lntroduction 5
Chapter 2: Core Database Concepts 23
Chapter 3: Creating and Using Relational Databases with SQL 53
Chapter 4: Fundamentals of Database Design 101
Chapter 5: Planning the Database 139
Chapter 6: lmplementing the Database 155
Chapter 7: Database Security 177
Chapter 8: Advanced Database Feature 197
Chapter 9: Database Maintenance 225
Appendix: Available Database Plattarms 257
Index 281
Practical Database Design for the Web
Chris Auld
Allan Kent
Rudy Limeback
Nigel Stanger
Thearon Willis
© 2002 Apress
Originally published by glasshaus in 2002
ISBN 978-1-59059-194-9 ISBN 978-1-4302-5377-8 (eBook)
DOI 10.1007/978-1-4302-5377-8
Practical Database Design for the Web
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or
transmitted in any form or by any means, without the prior written permission of the publisher, except
in the case of brief quotations embodied in critical articles or reviews.
The authors and publisher have made every effort in the preparation of this book to ensure the
accuracy of the information. However, the information contained in this book is sold without warranty,
either express or implied. Neither the authors, glasshaus nor its dealers or distributors will be held
liable for any damages caused or alleged to be caused either directly or indirectly by this book.
Cover Image
The Cover Image was provided by Raphael J. Wiszowaty
Raphael J. Wiszowaty was born in Poland in 1978 and immigrated to the United States in 1994
tagether with his family. His interest in art and design began when he was still in elementary school.
He developed a serious vision of becoming an artist and designer when he joined Gallery 37 in
Chicago in 1996. His five year training involved working with different media: painting, sculpture,
graphic design, animation, video, and sound.
Currently he is a student at Columbia College Chicago, with already completed concentration in
Graphie Design. He will graduate with honors and BFA in lnteractive Multimedia in June of 2003. lf
you would like to find out more about Raphael's work, please feel free to visit his site at:
www.multimus.com
~shaus
lobor-Nvlng clovlc:esl<>< web pn>leulonalo
© 2002 glasshaus
Trademark Acknowledgements
glasshaus has endeavered to provide trademark information about all the companies and products
mentioned in this book by the appropriate use of capitals. However, glasshaus cannot guarantee the
accuracy of this information.
Credits
Authors Commissioning Editor
Chris Auld Chris Mills
Allan Kent
Rudy Urneback Publisher
Nigel Stanger Viv Emery
Thearon Willis
Managing Editor
Techincal Reviewers Liz Toy
Robin Dewson
Damien Foggon Project Manager
Dan Maharry Sophie Edwards
Dan O'Reilly
David Schultz Graphie Editors
Alex Shiell Rache! Taylor
Rick Stones Pip Wonson
Proof Reader Cover
Agnes Wiggers Dawn Chellingworth
Technical Editors lndexer
Mark Waterhouse Bill Johncocks
Chris Mills
About the Authors
Chris Auld
Chris Auld is Chief Architect at New Zealand based software firm eMedia.
Chris divides his time between architecting large systems, building mobile
applications, and running areund like a headless chicken evangelising all
that is "new" and "only just released". Chris is a Microsoft platform
specialist and a self-professed XML geek - he even has the car number
plate to prove it! Chris is a graduate of Otago University in Law and
Information Science.
ln his "spare" time, he collects University Degrees and DJs Dark
Progressive House music, and partakes in silly ritualistic adventure sport
activities such as kitesurfing, whitewater kayaking and mountain biking.
I would like to thank: my Mum and Dad for getting me to where I am and for supporting
me in where I am going; my darfing girltriend for enduring my late nights in front of the
computer screen. I also need to thank the glasshaus crew for their encouragement and
support, despite my being on the other side of the world with 12 hours time difference.
Allan Kent
Allan has been programming seriously for the last nine years and other
than a single blemish when he achieved a diploma in Cobol
programming, is entirely self-taught. He runs his own company where
they try to make a living out of making a Iot of noise and playing Quake.
When that doesn't work they make a Iot of noise while doing development
and design for an ad agency. Allan lives in Cape Town, South Africa with
his girltriend and four cats.
Rudy Limeback
Rudy Urneback is a web and SQL consultant in Toronto Canada. Rudy
started as a Cobol programmer in 1971, turning to database design with
IDMS soon after. Subsequent positions included database software sales,
data administration and management, data warehousing, and data
modeling. Rudy began using SQL with DB2 in 1987, and has also worked
with SQL Server, Access, Oracle, and MySQL. His interest in the Internet
started in 1996 with a personal web site. Rudy was one of the founders of
the http://evolt.org/ Web developer community and remains active on its
discussion Iist, helping web developers use SQL for dynamic sites. Rudy is
also a regular contributor on several database forums. Visit http://rudy.ca/tor
more information or just to say hello.
Nigel Stanger
Nigel is a lecturer in the Department of Information Science at the University
of Otago School of Business in Dunedin, New Zealand. He has almost
fifteen years experience with database systems, mainly relational, but also
object and other types. He teaches both introductory and advanced
database courses, covering everything from database theory and SOL to
database tuning and data warehouses.
He has research interests in database system performance (especially in
distributed and web-based environments), wireless mobile databases and
XML-based systems. His PhD thesis was on translating between data
modeling representations. He's also recently been looking at spam filtering
using neural networks.
He's a keen mountain biker and goes on at least one major cycle tour every year. He also enjoy car
trialing and recently helped set part of the course for an event. He has been a Mac user since 1985,
defiantly cheerful in a sea of Windows machines.
Thearon Willis
Thearon is a Senior Systems Engineer with over 20 years programming
experience. He currently develops Intranet applications, XML Web services
and server-side and client-side utilities using Visual Basic .NET, ASP,
DHTML, XML, JavaScript, VBScript, VB COM Components and SOL
Server. He lives with his wife Margie and daughter Stephanie in the
Raleigh, North Carolina area.
As always, I want to thank my wife Margie and my daughter Stephanie for
the patience they have shown while I write another book. Without their Iove
and support, none of this would be possible.

Table of Contents
lntroduction 1
Support and Feedback ......................................................................................3
1: Database lntroduction 5
What is a Database? ........................................................................................5
A Short History Lesson ....................................................................................8
Database Models ............................................................................................1 0
DBMS .............................................................................................................. 17
Databases on the Web .................................................................................... 18
Summary ........................................................................................................ 20
2: Core Database Concepts 23
lntroduction ...................................................................................................... 23
But ls All This Theory Actually Useful? ......................................................................2 4
What is a Data Model? .................................................................................... 25
The Relational Model of Data ..........................................................................2 7
The Basics: Tables, Rows, and Columns .................................................................. 28
Domains and Data Types ..........................................................................................3 1
ldentifying Rows: Primary Keys ................................................................................3 4
Linking Related Tables: Foreign Keys ........................................................................ 36
Normalization ............................................................................................................3 7
A Quick Recap ..........................................................................................................4 2
Data lntegrity ..................................................................................................4 2
Metadata and the Data Dictionary ................................................................. .44
Physical Data Access Methods ..................................................................... .45
Sequential Access .....................................................................................................4 6
lndexed Access ..........................................................................................................4 7
Direct Access ............................................................................................................4 8
Fitting lt All Tagether ........................................................................................4 9
Summary ........................................................................................................5 0
3: Creating and Using Relational Databases with SQL 53
lntroduction ......................................................................................................5 3
A Note on the Examples ............................................................................................5 4
Structured Query Language ............................................................................5 4
A Little History ............................................................................................................5 5
Dialects ......................................................................................................................5 5
Syntax Components ..................................................................................................5 6
Data Definition ................................................................................................5 7
CREATE TABLE ................. ,. .....................................................................................5 7
DROP TABLE ............................................................................................................6 3
ALTER TABLE ............................................................................................................6 4
CREATE INDEX, DROP INDEX ................................................................................6 6
ii