Summary

We've covered a lot of ground in this chapter and yet barely touched upon the subject of databases and ActiveX Data Objects (ADO). However, we have learned enough to create an effective database. We've also learned enough about the database language, SQL, to retrieve data from a database, insert data into it, change data in it, and delete data from it. We also now have enough knowledge of ADO to be able to access a server-side database from a web page.

We learned that databases consist of one or more tables and that each table consists of one or more rows called records.
A record consists of one or more fields. Each field holds information and has a particular data type, such as numbers, text, or dates.
When designing tables, we need to ensure that we don't duplicate information. A table should contain fields that have some sort of logical relationship between them. For example, store customer data in one table, and order data in another.
Some fields have data that is unique to that record. We can use these fields to search for a particular record. We can define fields that are unique and likely to be used for searching as primary keys. Defining a primary key indexes the field and helps speed up data retrieval.
Relationships allow us to specify that the data in one table is reliant on the existence of data in another. For example, it shouldn't be possible to have orders in the orders table for stock that does not exist in the stock table.
We took a brief tour of Structured Query Language (SQL), which provides a means of retrieving, inserting, updating, and deleting data in a database.
To retrieve a record or group of records we use SQL's select statement. This allows us to specify which fields we require. It also allows us to define criteria for defining which records we want using the where clause.
The SQL update statement allows a record or group of records already existing in a database to have their field values changed. Adding new records involves the insert statement, and deleting records involves the delete statement.
We looked at how to access a server-side database using script running server-side. To do this requires the use of components installed on the server. We looked at one such group of components called ActiveX Data Objects.
A connection to the database needs to be made inside the web page before we can start selecting or changing data.
Two technologies provide the means to connect to a database: ODBC and OLE DB. With OLE DB we just need to specify the OLE driver and the location of the database. Before we can use ODBC, we need to set up an ODBC data source on the server-computer.
The ADO Connection object provides a means of connecting to a database. Once a connection is made it can be reused again and again within the same page; doing so speeds up database access.
The ADO Connection object provides the Execute() method, which enables SQL to be run against the database. If we are executing a query that inserts, deletes, or updates data, we need to call the Execute() method passing the SQL.
If our SQL is a select query that returns records, we need to use an ADO Recordset object to hold the results of the Execute() method. Alternatively if we are only making one database access in a page, we can use the Open() method of a Recordset object to connect to the database and retrieve records.
Finally, we changed the trivia quiz to generate the questions and check the answers by using information stored in a database