| |
| |
| 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 | |