Inside relational databases with examples in Access / Mark Whitehorn and Bill Marklyn.

Format:

Book

Author/Creator:

Whitehorn, Mark, 1953-

Contributor:

Marklyn, Bill, 1960-

John G. Hartman Memorial Library Fund.

Language:

English

Subjects (All):

Microsoft Access.

Relational databases.

Physical Description:

xiv, 371 pages ; 24 cm

Place of Publication:

[London] : Springer, [2007]

Summary:

Inside Relational Databases was first published in 1997 and, rather to our surprise, rapidly reached the status of a classic work in the database field. Translated into three other languages and sold all over the world, it has helped thousands of people to understand the relational model that underpins all modern databases.

Inside Relational Databases has never been about how to use a particular database engine (Access, SQL Server, MySQL, whatever). Instead, it's about the underlying way in which relational databases work. However it is very convenient if the book illustrates the relational model using the reader's favorite product - such as Access. So, for the new edition we are producing several versions of the same book, each version based on a different database engine. The version in your hand is based around Access (hence the title). There will be different versions based around other engines.

You shouldn't buy this book if you are looking for a book about how to use Access. You should buy this book if you have created databases but they don't seem to work very well. Perhaps you: can't retrieve the information that you want. have to type in the same information over and over again. type in data and it appears to go missing. ask questions and get answers that you know are wrong. can use Access but you don't know exactly what to do with it. know that a relational database lets you create multiple tables in the database but you are uncertain why this is to your advantage. Or perhaps you hear words in connection with databases like: normalization, redundant data, transaction, functional dependency, data dictionary, concurrency, inner join, meta-data, locking, union, ER modeling, data integrity. You haven't got the faintest idea what they mean and there is no one you can ask.

Contents:

Who are we? 1

What is a database? 2

Databases vs. Database Management Systems 3

Relational Database Management Systems 3

Some ground rules 7

Downloading files from the website 8

We don't have problems... 9

Outroduction 9

Part 1 A simple, single-table database 11

Queries/Views 14

Reports 15

Rows & columns - records & fields 18

Building a table 22

Types of data 23

Meaningful operations 24

Excluding certain errors 26

Making storage more efficient 26

Making data recall more rapid 28

Field size 28

General notes on table design 29

Chapter 4 Queries/Views 36

Queries usually find subsets of the data 36

Queries, answer tables and base tables finally defined properly and closure mentioned briefly 37

Summarizing data 42

Other useful queries 42

Graphical querying tools 43

SQL and Views 44

Multiple forms per table 48

Text boxes can be made read only 49

Text boxes don't have to present data from just one field 49

It isn't necessary for each field in a table to appear on the form 51

Controlling data entry 51

Use of forms can be controlled 51

Forms can be web pages 51

Chapter 6 Reports 54

Part 2 A multi-table database 59

Chapter 9 Serious problems with single tables 62

Redundant data 63

Typographical errors 63

Modifying data 64

Chapter 10 Multiple tables cure serious problems 67

Redundant data 69

Typographical errors 72

Modifying data 72

Chapter 11 Making multiple tables work together 73

Databases are designed to model the real world 74

Chapter 12 Getting the data into the correct tables 75

Not normalization (and not ER modeling either) 77

Object identification 78

Chapter 13 Relationships in the real world 81

One-to-many 81

One-to-one 82

Many-to-many 82

None 82

Mapping real world relationships to tables 83

Chapter 14 How are relationships modeled? 84

Primary keys 86

Foreign keys 91

Summary so far 92

Joins 93

General lessons about joins 106

Chapter 15 Revisiting the big four - the synergy begins 112

Closure 112

Queries (and a bit on forms) 116

Reports 124

Chapter 16 Integrity 127

Data integrity - is it worth the effort? 127

Types of data integrity error (and some cures) 128

Declarative and procedural referential integrity 134

Nulls in foreign keys 139

These options in context 142

Other integrity issues 143

Integrity - where should you set it? 143

Part 3 Database Design & Architecture 147

Chapter 18 Database design 149

Designing databases - user, logical and physical models 149

The Logical model - overview 151

More about the logical model 152

CASE tools 154

Summary so far 158

The final big advantage of CASE tools 158

More about the differences between the Logical and Physical models 160

Reality check 162

Normalization can help 162

Reverse engineering 163

Methodologies 164

Summary of design models 164

Chapter 19 The seven layers of wisdom 165

The seven layers of wisdom 165

Chapter 20 Database architecture 168

Default Architecture in Access 168

Access - PC front end - data on file server 168

Client-server (or two-tier) architecture 171

Three-tier architecture (also known as multi-tier) 173

Web-based applications 174

Choosing a database architecture 176

Part 4 Related database topics 179

Chapter 21 What exactly is a relational database? 181

Do multiple tables a relational database make? 181

Chapter 22 Triggers and stored procedures 183

Triggers 183

Stored procedures 187

Summary - triggers and stored procedures 189

Chapter 23 Transactions, logs, backup, locking and concurrency 190

Transactions 190

Logs 191

Locking 197

Concurrency 199

Row locking and page locking 199

Access and the features described in this chapter 200

Answers from earlier 200

Chapter 24 Codd's rules 201

Codd's rules 201

Economy vs. readability 201

A little background 202

The rules themselves 202

Chapter 25 Normalization 215

A first look at normalization 215

First normal form (first level of normalization): 1NF 216

Second normal form (second level of normalization): 2NF 218

Third normal form (third level of normalization): 3NF 220

Summary so far 221

Adding some definitions 222

Chapter 26 More about normalization 233

Higher normal forms 233

Normalization doesn't automatically remove all redundancy 237

Chapter 27 The system tables 244

Chapter 28 More on queries: data manipulation 246

Relational operators 246

Chapter 29 SQL 258

SELECT and FROM 261

DISTINCT 262

WHERE 262

Conditions 263

ORDER BY 267

Wildcards 270

Sub-queries 271

Built-in functions 272

GROUP BY - collecting information 276

GROUP BY...HAVING - collecting specific information 282

Working with multiple tables 285

Inner (Natural) joins 290

Outer joins 291

UNION 293

SELECT summary 296

INSERT 297

UPDATE 300

DELETE 302

A question (and a free SQL diagnostic tool) 303

Chapter 30 Domains 307

Chapter 31 What does null mean? 309

Chapter 32 Primary keys 313

Candidate keys 315

Part 5 Speeding up your database 317

Chapter 33 Hardware considerations 319

CPUs 320

Memory 320

Disks 322

Data volume vs. disk capacity 322

Don't put all your eggs in one basket 323

Chapter 34 Indexing 324

Indexing techniques 324

Applying indexes - which fields/columns should be indexed? 333

Intelligent use of indexes 337

Chapter 35 More on optimization 338

Query optimization 338

Update statistics 339

Query analysis 340

Writing good SQL code 342

Chapter 36 Denormalization 344

Mirroring tables 345

Splitting tables 346

Redundant data 348

Repeating groups (breaking 1NF) 349

Derived columns 351

Appendix 1 GUIs, macros and control languages 353

Creating a very simple user interface 353

Other languages - SQL 362.

Notes:

Includes index.

Local Notes:

Acquired for the Penn Libraries with assistance from the John G. Hartman Memorial Library Fund.

ISBN:

1846283949

9781846283949

OCLC:

70987390

Publisher Number:

9781846283949 (pbk.)