Contents – detailed

1. How to develop a database application

1.1 How to develop a database application – what this book contains

1.1.1 The database application system life cycle

1.1.2 Entity modelling and normalization

1.1.3 Database design in Access

1.1.4 Query Design in Access

1.1.5 Access Forms, Macros and Reports

1.1.6 Database Programming in Visual Basic

1.2 Visual Basic and Access

1.3 Versions of Visual Basic and Access

1.4 Compatibility between versions of Visual Basic and

Access

1.5 Exercises

2. Entity modelling and normalization

2.1 Introduction

2.2 Entity modelling

2.2.1 Entity types and relationships

2.2.2 Replacing M:N relationships with new entity

types

2.2.3 Replacing relationships involving more than

two entity types with a new entity type

2.2.4 Parallel relationships

2.2.5 Mutually exclusive relationships

2.2.6 Unnecessary relationships

2.3 The Relational model

2.3.1 Selecting an initial set of attributes

2.3.2 Terminology

2.3.3 Primary keys

2.3.4 Foreign keys

2.4 Normalization

2.4.1 First normal form

2.4.2 Second normal form

2.4.3 Third normal form

2.4.4 Fourth normal form

2.4.5 Fifth normal form

2.5 Exercises

3. Access database design

3.1 Introduction

3.2 Organizing an application

3.3 The facilities of Access

3.4 Creating a database

3.5 Creating tables

3.6 Creating relationships

3.7 Exercises

4. Access query design using Query Design view

4.1 Introduction

4.2 Developing queries using the Access Query Design view

4.3 Exercises

5. Access query design using SQL – DDL and DML statements

5.1 Introduction to SQL

5.2 Versions of SQL: Jet and ANSI

5.3 Categories of SQL statement

5.4 How we describe the syntax of SQL statements

5.5 CREATE TABLE

5.6 ALTER TABLE

5.7 DROP TABLE

5.8 CREATE INDEX

5.9 DROP INDEX

5.10 INSERT INTO

5.11 UPDATE

5.12 DELETE

5.13 Exercises

6. Access query design using SQL – the SELECT statement – single tables and joins

6.1 Introduction

6.2 SELECT with a single table

6.2.1 Selecting columns

6.2.2 DISTINCT

6.2.3 WHERE

6.2.4 LIKE

6.2.5 BETWEEN

6.2.6 IN

6.2.7 AND, OR and NOT

6.2.8 GROUP BY

6.2.9 HAVING

6.2.10 ORDER BY

6.2.11 TOP

6.3 SELECT with multiple tables

6.3.1 SELECT with joined tables

6.3.2 INNER JOIN

6.3.3 Greater than join

6.3.4 Self join

6.3.5 Joining more than two tables

6.3.6 LEFT JOIN and RIGHT JOIN

6.3.7 Combining INNER JOINs and LEFT JOINs

6.3.8 Jet’s join nesting rule

6.4 Exercises

7. Access query design using SQL – the SELECT statement – further features

7.1 Introduction

7.2 SELECT with subqueries

7.2.1 Simple Subqueries and their Join equivalents

7.2.2 Subquery with Join in the Outer SELECT

7.2.3 Where a Join must be used instead of a subquery

7.2.4 Joins in the subquery

7.2.5 Returning more than one column from a subquery

7.2.6 ANY and ALL

7.2.7 Replacing ANY and ALL

7.2.8 Where a subquery seems essential

7.2.9 Correlated subqueries

7.2.10 EXISTS and NOT EXISTS

7.3 UNION queries

7.3.1 Simple UNION examples

7.3.2 Column homogeneity

7.3.3 Different field names

7.3.4 UNION of recordsets with different numbers of columns

7.3.5 Simulating an INTERSECT

7.3.6 Combinations of UNION and

(simulated) INTERSECT

7.3.7 Simulating a MINUS

7.4 Queries built on queries – Views

7.4.1 Simple views

7.4.2 Summary views

7.4.3 Views based on joins

7.4.4 Joining views

7.4.5 Views may contain duplicates

7.4.6 Updating and views

7.4.7 Views as programmers’ stepping-stones

7.5 SELECT INTO

7.6 CROSSTAB queries

7.7 Exercises

8. Access Forms, Macros and Reports

8.1 Introduction

8.2 Access Forms

8.2.1 AutoForms

8.2.2 Filtering, Sorting and Finding

8.2.3 Form Wizard: Creating a 1:N form

8.2.4 Adding a Combo box to an Access form

8.2.5 Design view: Creating forms manually

8.3 Access Macros

8.3.1 Linking Access forms via Macro code

8.3.2 Using Conditions in Access Macros

8.4 Access Reports

8.4.1 Report showing data from two linked tables

8.4.2 Report containing a Graph

8.5 Exercises

9. Visual Basic database programming using the Data Control

9.1 Introduction

9.2 Linking an Access database to Visual Basic using the

Data Control

9.3 Adding, Deleting and Changing records

9.4 Error handling

9.5 Error prevention

9.6 A FindFirst button

9.7 Adding a Menu

9.8 DBGrid with a single table

9.9 DBGrid with linked tables in a 1:N form

9.10 Using the ADO Data Control – single table updates

9.11 Using the ADO Data Control – linked tables in a

1:N form

9.12 Exercises

10. Visual Basic database programming using Data Access Objects (DAO)

10.1 Introduction

10.2 DAO commands (Jet 2.5/3.0 and above)

10.3 Displaying data in a VB Grid

10.4 Editing data in a VB Grid

10.5 Editing data in a VB Grid: two forms

10.6 Displaying data from a 1:N relationship using a VB

Grid

10.7 Producing Reports

10.8 Printing a Report (using Print#) displaying data from

one table

10.9 Printing a Report (using Print#) displaying data from

three related tables

10.10 Printing a Report (using the Printer object) displaying

data from three related tables

10.11 Listing the printers available

10.12 Exercises

11. Visual Basic database programming using ActiveX Data Objects (ADO)

11.1 The ADO approach

11.2 Displaying data in a List Box: ADO and DAO

compared

11.3 Creating and using a Data Source Name (DSN)

11.4 Updating a single table using ADO

11.5 The ADO Recordset Open command - Cursors and Locking

11.6 Find buttons

11.7 Exercises

12. Visual Basic database programming using Access Modules

12.1 Introduction

12.2 Access Modules

12.3 Form Modules and Standard Modules

12.4 Example: Database filtering on more than one criterion

12.5 Example: Transaction processing

12.6 Exercises

13. The Internet, WWW, and ASP

13.1 Introduction

13.2 Why put your database on the web?

13.3 Browsers

13.4 Creating web pages

13.5 Organizing web pages into a website

13.6 Your development environment

13.7 The web client/server dialogue - non-ASP

13.8 The web client/server dialogue - ASP

13.9 Client side and server side scripting

13.10 Alternative web application development approaches

13.11 Java

13.12 VBScript and JavaScript

13.13 CGI

13.14 ISAPI and Java Servlets

13.15 Exercises

14. Creating web pages using HTML

14.1 Introduction

14.2 How to develop web pages using HTML

14.3 How to upload your web pages onto the internet

14.3.1 Windows based FTP packages

14.3.2 DOS FTP

14.4 A single web page showing many of HTML’s features

14.4.1 Structure of the web page

14.4.2 Head tags

14.4.3 Comments

14.4.4 Body tags

14.4.5 Text

14.4.6 Positioning

14.4.7 Lists

14.4.8 Lines

14.4.9 Images

14.4.10 Tables

14.5