Interacting With Data

Databases

Database Management System

DBMS Server

Store, manage, retrieve data

Applies database and security rules

Interprets SQL Queries

Computation, Replication/Backup

DBMS is a group of software applications designed to provide a variety of functions for Data and the applications that access the data.

Structure of a Data Table

Studentno Name Address Phone

11462534 White 9 White St 783-5643

44376789 Brown 6 Brown Rd 656-2222

23390765 Smith 2 Smith St 222-5858

Row (Record)

Column (Field)

Students

Information is added to data table on a Row by Row basis

Table names and Column names are case sensitive

Table Name

Databases

Two main types of Database structures used in this course. The Flat File model and the Relational Model.

Flat File – Based on a single table. Simple but inefficient for complex data

storage and retrieval.

Relational – multiple tables in single database More efficient, but harder to create and

organize. Has been the standard model for several

decades.

Database (Flat File) - Data Table

1) The Flat File data model relies on a single table to hold all data about a particular subject.

2) The data table can become very large and difficult to manage

3) The common Telephone Book is a classic example of a Flat-File database

4) Very few serious applications rely on this database model.

Flat-File Model

Studentno Name Address Phone

11462534 White 9 White St 783-5643 INT213 75 DCN286 65

44376789 Brown 6 Brown Rd 656-2222 IOS110 55 ULI101 72

23390765 Smith 2 Smith St 222-5858 IOS110 70 HWD101 80

Students

In complex databases, the Flat-File model cannot avoid duplication of Column data.

Searching, updating and deleting data becomes very difficult

Course1 Mark1 Course2 Mark2

Columns are duplicated, increasing table size

How Data Flows

Client Browser

ODBC Connection Interpreter (DSN) (Translates client request so DBMS understands what the client wants)

Database Management System

(Applies rules and searches for data)Data Tables (contains all information)

Recordsets (contains copy of information requested by client)

Search for Data (SQL query)

Create recordset based on data found

Sends and receives Query request by way of

Recordset Object

Sends Recordset request by way of Connection Object

How Data Flows

ODBC Connection Interpreter (DSN) (Translates client request so DBMS understands what to do)

• Open Database Connectivity provides a standard software interface to different database management systems (DBMS).

• Software applications can communicate with different DBMSs without altering their applications by using drivers provided by Database system vendors

• DSNs (data source name) are set up to use drivers, either by;

1) accessing the ODBC applet in the control panel or,

2) writing a DSN-less connection string (handy when you do not have access to the control panel or when the O/S does not have a control panel)

• A Connection Object is created which allows the application to connect to the database System

How Data Flows

Creating The Connection Object

• The Connection Object can be created either as a DSN or DSN-less connection

DSN Connection<%‘create the object (DSN already set in the control panel) that will connect to the database Set conn = CreateObject (“ADODB.Connection”)‘ open the connection conn.Open “INT213con”%>

DSN-less Connection<%‘create the object without DSN being setup in control panel Set conn = CreateObject (“ADODB.Connection”)‘ create the connection conn.ConnectionString="DRIVER={Microsoft Access Driver (*.mdb)};" &_ "DBQ=C:\Inetpub\wwwroot\INT213db.mdb“‘Open the connection conn.Open %>

Recordset Object

• Recordset Object sends a query to the database system using the Connection Object.

• The DBMS processes the query and returns the data requested in the form of Rows and Columns (just like an Excel spreadsheet).

The Recordset and the data it contains is a copy (snapshot) of the data from the data tables. It is held in RAM on the client system. This data can be displayed, altered, deleted, etc. Any changes are sent back to the server using the Recordset Object

• The actual data tables will not be affected by changes until the Recordset is written back to the Data Tables.

Recordsets (contains information requested

by client)

Recordset Object

'set SQL statement that will search for all data in the database Table you created in Access

sql = "SELECT * FROM students"

'Establish the recordset which will hold a copy of the retrieved data in memory

Set rs = Server.CreateObject("ADODB.RecordSet")

'Open the recordset using the Server connection

rs.Open sql, conn

Creating Recordsets

The Whole Thing - Together

<% Option Explicit %><%Dim conn, rs, sql, path, StuNo, StuPhone Set conn = Server.CreateObject("ADODB.Connection") ‘Open the connection conn.Open “INT213con”'set SQL statement that will search for all data in the database Table you created in Access sql = "SELECT * FROM students" 'Establish the recordset which will hold a copy of the retrieved data in memory Set rs = Server.CreateObject("ADODB.RecordSet") 'Open the recordset using the Server connection rs.Open sql, conn Place the data into variables before closing the Recordset.

StuNo = rs(“Studentno”) StuPhone = rs(“Phone”)'Close the connection to Server and Recordset

rs.close set rs = nothing conn.close set conn = nothing%>

SQL

Looking for Data in All the Right Places

Using the Students Table, lets see what some SQL queries can do

Select * From Students

Select * From Students Where Studentno <> 44376789

Select Phone, Name From Students Order By Studentno

Select * From Students Order by Name DESC

SQL is the language of Database Systems. It is extremely powerful, but the logic of its’ use is easy to understand. SQL is a course in itself, so we will only deal with very basic issues in this course.

SQL

Combining SQL with Form Data

Dynamic web sites require input from users who want specific data from a database.

To retrieve this data it will be necessary to combine an SQL statement with the Request object.

Data = “Select * from Students Where Name = ‘” & Request(”txtname”) & “’”

Note the Single quotes. They protect the double quotes that are added by the Text Box. The single quotes act as an extra set of double quotes.