| |
| |
Preface | |
| |
| |
| |
Introduction | |
| |
| |
Who are we? | |
| |
| |
What is a database? | |
| |
| |
Databases vs. Database Management Systems | |
| |
| |
Relational Database Management Systems | |
| |
| |
Why this book? | |
| |
| |
Who should read this book? | |
| |
| |
Organization of the book | |
| |
| |
Some ground rules | |
| |
| |
Downloading files from the website | |
| |
| |
Acknowledgements | |
| |
| |
We don't have problems... | |
| |
| |
Outroduction | |
| |
| |
| |
A simple, single-table database | |
| |
| |
| |
Introduction to Part 1 | |
| |
| |
Tables | |
| |
| |
Queries/Views | |
| |
| |
Forms | |
| |
| |
Reports | |
| |
| |
| |
Tables | |
| |
| |
Rows & columns - records & fields | |
| |
| |
Building a table | |
| |
| |
Types of data | |
| |
| |
Meaningful operations | |
| |
| |
Excluding certain errors | |
| |
| |
Making storage more efficient | |
| |
| |
Making data recall more rapid | |
| |
| |
Field size | |
| |
| |
General notes on table design | |
| |
| |
| |
Queries/Views | |
| |
| |
Queries usually find subsets of the data | |
| |
| |
Queries, answer tables and base tables finally defined properly and closure mentioned briefly | |
| |
| |
Summarizing data | |
| |
| |
Other useful queries | |
| |
| |
Graphical querying tools | |
| |
| |
SQL and Views | |
| |
| |
| |
Forms | |
| |
| |
Multiple forms per table | |
| |
| |
Text boxes can be made read only | |
| |
| |
Text boxes don't have to present data from just one field | |
| |
| |
It isn't necessary for each field in a table to appear on the form | |
| |
| |
Controlling data entry | |
| |
| |
Use of forms can be controlled | |
| |
| |
Forms can be web pages | |
| |
| |
Summary | |
| |
| |
| |
Reports | |
| |
| |
| |
Summary of Part 1 | |
| |
| |
| |
A multi-table database | |
| |
| |
| |
Introduction to Part 2 | |
| |
| |
| |
Serious problems with single tables | |
| |
| |
Redundant data | |
| |
| |
Typographical errors | |
| |
| |
Modifying data | |
| |
| |
Summary | |
| |
| |
| |
Multiple tables cure serious problems | |
| |
| |
Redundant data | |
| |
| |
Typographical errors | |
| |
| |
Modifying data | |
| |
| |
| |
Making multiple tables work together | |
| |
| |
Databases are designed to model the real world | |
| |
| |
| |
Getting the data into the correct tables | |
| |
| |
Not normalization (and not ER modeling either) | |
| |
| |
Object identification | |
| |
| |
| |
Relationships in the real world | |
| |
| |
One-to-many | |
| |
| |
One-to-one | |
| |
| |
Many-to-many | |
| |
| |
None | |
| |
| |
Mapping real world relationships to tables | |
| |
| |
| |
How are relationships modeled? | |
| |
| |
Primary keys | |
| |
| |
Foreign keys | |
| |
| |
Summary so far | |
| |
| |
Joins | |
| |
| |
General lessons about joins | |
| |
| |
| |
Revisiting the big four - the synergy begins | |
| |
| |
Closure | |
| |
| |
Tables | |
| |
| |
Queries (and a bit on forms) | |
| |
| |
Forms | |
| |
| |
Reports | |
| |
| |
| |
Integrity | |
| |
| |
Data integrity - is it worth the effort? | |
| |
| |
Types of data integrity error (and some cures) | |
| |
| |
Declarative and procedural referential integrity | |
| |
| |
Nulls in foreign keys | |
| |
| |
These options in context | |
| |
| |
Other integrity issues | |
| |
| |
Integrity - where should you set it? | |
| |
| |
| |
Summary of Part 2 | |
| |
| |
| |
Database Design & Architecture | |
| |
| |
| |
Database design | |
| |
| |
Designing databases - user, logical and physical models | |
| |
| |
The Logical model - overview | |
| |
| |
More about the logical model | |
| |
| |
CASE tools | |
| |
| |
Summary so far | |
| |
| |
The final big advantage of CASE tools | |
| |
| |
More about the differences between the Logical and Physical models | |
| |
| |
Reality check | |
| |
| |
Normalization can help | |
| |
| |
Reverse engineering | |
| |
| |
Methodologies | |
| |
| |
Summary of design models | |
| |
| |
| |
The seven layers of wisdom | |
| |
| |
The seven layers of wisdom | |
| |
| |
| |
Database architecture | |
| |
| |
Default Architecture in Access | |
| |
| |
Access - PC front end - data on file server | |
| |
| |
Client-server (or two-tier) architecture | |
| |
| |
Three-tier architecture (also known as multi-tier) | |
| |
| |
Web-based applications | |
| |
| |
Choosing a database architecture | |
| |
| |
What comes next | |
| |
| |
| |
Related database topics | |
| |
| |
| |
What exactly is a relational database? | |
| |
| |
Do multiple tables a relational database make? | |
| |
| |
| |
Triggers and stored procedures | |
| |
| |
Triggers | |
| |
| |
Stored procedures | |
| |
| |
Summary - triggers and stored procedures | |
| |
| |
| |
Transactions, logs, backup, locking and concurrency | |
| |
| |
Transactions | |
| |
| |
Logs | |
| |
| |
Locking | |
| |
| |
Concurrency | |
| |
| |
Row locking and page locking | |
| |
| |
Access and the features described in this chapter | |
| |
| |
Answers from earlier | |
| |
| |
| |
Codd's rules | |
| |
| |
Codd's rules | |
| |
| |
Economy vs. readability | |
| |
| |
A little background | |
| |
| |
The rules themselves | |
| |
| |
Summary | |
| |
| |
| |
Normalization | |
| |
| |
A first look at normalization | |
| |
| |
First normal form (first level of normalization): 1NF | |
| |
| |
Second normal form (second level of normalization): 2NF | |
| |
| |
Third normal form (third level of normalization): 3NF | |
| |
| |
Summary so far | |
| |
| |
Adding some definitions | |
| |
| |
Summary (again) | |
| |
| |
| |
More about normalization | |
| |
| |
Higher normal forms | |
| |
| |
Normalization doesn't automatically remove all redundancy | |
| |
| |
Summary | |
| |
| |
| |
The system tables | |
| |
| |
| |
More on queries: data manipulation | |
| |
| |
Relational operators | |
| |
| |
Summary | |
| |
| |
| |
SQL | |
| |
| |
SELECT and FROM | |
| |
| |
DISTINCT | |
| |
| |
WHERE | |
| |
| |
Conditions | |
| |
| |
ORDER BY | |
| |
| |
Wildcards | |
| |
| |
Sub-queries | |
| |
| |
Built-in functions | |
| |
| |
GROUP BY - collecting information | |
| |
| |
GROUP BY...HAVING - collecting specific information | |
| |
| |
Working with multiple tables | |
| |
| |
Inner (Natural) joins | |
| |
| |
Outer joins | |
| |
| |
UNION | |
| |
| |
SELECT summary | |
| |
| |
INSERT | |
| |
| |
UPDATE | |
| |
| |
DELETE | |
| |
| |
A question (and a free SQL diagnostic tool) | |
| |
| |
Summary | |
| |
| |
| |
Domains | |
| |
| |
| |
What does null mean? | |
| |
| |
| |
Primary keys | |
| |
| |
Candidate keys | |
| |
| |
| |
Speeding up your database | |
| |
| |
| |
Hardware considerations | |
| |
| |
CPUs | |
| |
| |
Memory | |
| |
| |
Disks | |
| |
| |
Data volume vs. disk capacity | |
| |
| |
Don't put all your eggs in one basket | |
| |
| |
| |
Indexing | |
| |
| |
Indexing techniques | |
| |
| |
Applying indexes - which fields/columns should be indexed? | |
| |
| |
Intelligent use of indexes | |
| |
| |
| |
More on optimization | |
| |
| |
Query optimization | |
| |
| |
Update statistics | |
| |
| |
Query analysis | |
| |
| |
Writing good SQL code | |
| |
| |
| |
Denormalization | |
| |
| |
Mirroring tables | |
| |
| |
Splitting tables | |
| |
| |
Redundant data | |
| |
| |
Repeating groups (breaking 1NF) | |
| |
| |
Derived columns | |
| |
| |
Summary | |
| |
| |
| |
GUIs, macros and control languages | |
| |
| |
Creating a very simple user interface | |
| |
| |
Other languages - SQL | |
| |
| |
Index | |