Practical Web Database Design

by Auld, Chris

• ISBN13: 9781590591949
• ISBN10: 1590591941
• Format: Paperback
• Copyright: 2003-09-01
• Publisher: Apress

Summary

Let's cut straight to the chase - this book will take you from knowing what a database is, to knowing how to design one effectively so that it makes good use of storage space, and provides fast and efficient data access. From first principles to implementation, we take the woe from normalization and the worry from stored procedures. This book doesn't present platform-specific database implementations, but takes you through the steps you need to follow to ensure that your database does the job you want it to do, and does it well.

Table of Contents

Introduction	1	(3)
Support and Feedback	3	(2)
Database Introduction	5	(18)
What is a Database?	5	(3)
A Short History Lesson	8	(2)
Database Models	10	(7)
DBMS	17	(1)
Databases on the Web	18	(2)
Summary	20	(3)
Core Database Concepts	23	(30)
Introduction	23	(2)
But Is All This Theory Actually Useful?	24	(1)
What is a Data Model?	25	(2)
The Relational Model of Data	27	(15)
The Basics: Tables, Rows, and Columns	28	(3)
Domains and Data Types	31	(3)
Identifying Rows: Primary Keys	34	(2)
Linking Related Tables: Foreign Keys	36	(1)
Normalization	37	(5)
A Quick Recap	42	(1)
Data Integrity	42	(2)
Metadata and the Data Dictionary	44	(1)
Physical Data Access Methods	45	(4)
Sequential Access	46	(1)
Indexed Access	47	(1)
Direct Access	48	(1)
Fitting It All Together	49	(1)
Summary	50	(3)
Creating and Using Relational Databases with SQL	53	(48)
Introduction	53	(1)
A Note on the Examples	54	(1)
Structured Query Language	54	(3)
A Little History	55	(1)
Dialects	55	(1)
Syntax Components	56	(1)
Data Definition	57	(10)
Create Table	57	(6)
Drop Table	63	(1)
Alter Table	64	(2)
Create Index, Drop Index	66	(1)
Data Manipulation	67	(5)
Creating Data: Insert	67	(2)
Updating Data: Update	69	(1)
Deleting Data: Delete	70	(1)
The Where Clause	71	(1)
Reading Data from the Database	72	(22)
Querying in SQL: Select	73	(4)
Joins	77	(7)
Useful Reporting Features	84	(6)
Other Miscellaneous Useful Stuff	90	(4)
Techniques for Building Select Queries	94	(5)
A Detailed Example	95	(4)
Summary	99	(2)
Fundamentals of Database Design	101	(38)
Data Modeling	101	(2)
Why Bother Modeling?	102	(1)
Modeling Entities, Attributes, and Relationships	103	(2)
Entities	104	(1)
Attributes	104	(1)
Relationships	105	(1)
Common Structures	105	(11)
One-to-Many	106	(1)
Many-to-Many	107	(2)
One-to-Many Recursive	109	(3)
Many-to-Many Recursive	112	(1)
One-to-One	113	(3)
E-Commerce Example Database Diagram	116	(1)
Integrity Constraints	116	(15)
Entity Integrity	117	(4)
Attribute or Domain Integrity	121	(7)
Referential Integrity	128	(3)
Denormalizing for Performance	131	(1)
Using Case Tools for Modeling	132	(4)
Case Demonstration -- Visio 2002	132	(4)
Importance of Sample Data	136	(1)
Summary	137	(2)
Planning the Database	139	(16)
Gather Information	140	(6)
Store Owner	141	(3)
Customer	144	(1)
Payment Processing	145	(1)
Identify Entities and Attributes	146	(1)
Identify Entity Relationships	147	(4)
Product Relationships	147	(3)
Category Relationships	150	(1)
Customer Relationships	151	(1)
Prepare Conceptual Data Model	151	(1)
Summary	152	(3)
Implementing the Database	155	(22)
Target Database System	156	(1)
Naming Conventions	156	(3)
Table Names	157	(1)
Column Names	157	(2)
Associative Entities for Many-to-Many Relationships	159	(2)
Data Types and Nullability	161	(2)
Character Data Types	161	(1)
Numeric Data Types	161	(1)
Date Data Types	162	(1)
Nullability	162	(1)
Natural and Surrogate Primary Keys	163	(1)
Foreign Keys and Relational Integrity	164	(1)
Table and Column Definitions	165	(6)
Product	166	(1)
Category	167	(1)
ProductCategory	168	(1)
Customer	168	(1)
Orders	169	(1)
OrderItem	170	(1)
The Overall Database	170	(1)
Database Indexes	171	(1)
Sample DDL	172	(1)
Sample SQL	173	(2)
Search for Products	173	(1)
Add a Product to a Customer's Order (``add to cart'')	174	(1)
Summary	175	(2)
Database Security	177	(20)
Designing a Security Model	178	(3)
Identifying Your Target Audience	178	(1)
Choosing an Authentication Method	179	(1)
Identifying the Type of Data Access Needed	180	(1)
Defining Users and Groups	181	(2)
Using Strongly Typed Passwords	182	(1)
Encrypt Your Passwords	182	(1)
How to Secure Your Database	183	(8)
Table Permissions	183	(1)
Stored Procedure Permissions	184	(1)
View Permissions	184	(1)
Setting Permissions	185	(1)
Inline SQL vs. Stored Procedures	186	(5)
Using Views to Restrict Data Access	191	(1)
Network Security for Your RDBMS	192	(1)
Summary	193	(4)
Advanced Database Features	197	(28)
Advanced Queries	197	(8)
Sub-Queries	198	(7)
Stored Procedures and Triggers	205	(4)
Stored Procedures	206	(1)
Triggers	207	(2)
Physical Database Tuning	209	(4)
Query Plans, or, What's That RDBMS Thinking?	210	(3)
Indexes	213	(3)
Types of Index	215	(1)
Managing Concurrency	216	(6)
Locking	216	(1)
Transactions	217	(5)
Summary	222	(3)
Database Maintenance	225	(32)
Why a Developer Should Know This	226	(1)
Backup and Restore	226	(8)
Backing Up Your Database	227	(4)
Restoring Your Database	231	(3)
Modifying Tables	234	(12)
Changing Columns	235	(3)
Adding Columns	238	(1)
Dropping Columns	239	(4)
Adding Indexes	243	(3)
Reorganizing Data and Indexes	246	(3)
Dropping an Index	247	(1)
Drop Existing	248	(1)
Building Scripts for Production Implementation	249	(5)
Scripts to Populate Tables	249	(5)
When to Perform Database Maintenance	254	(1)
Summary	254	(3)
Appendix: Available Database Platforms	257	(24)
MySQL	257	(8)
Oracle	265	(6)
Microsoft SQL Server	271	(7)
Summary	278	(3)
Index	281