Transcript Introduction to Databases PowerPoint

Interacting With Data
Databases
Database Management System
Store, manage,
retrieve data
Applies database
and security rules
DBMS Server
Computation,
Replication/Backup
Interprets SQL
Queries
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
Column
(Field)
Row
(Record)
Students
Table Name
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
Information is added to data table on a Row by Row basis
Table names and Column names are case sensitive
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)
2)
3)
4)
The Flat File data model relies on a single table to hold all
data about a particular subject.
The data table can become very large and difficult to manage
The common Telephone Book is a classic example of a FlatFile database
Very few serious applications rely on this database model.
Flat-File Model
Students
Columns are duplicated,
increasing table size
Studentno
Name
Address
Phone
Course1
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
Mark1 Course2 Mark2
In complex databases, the Flat-File model cannot avoid duplication of
Column data.
Searching, updating and deleting data becomes very difficult
How Data Flows
ODBC Connection Interpreter (DSN)
(Translates client request so DBMS understands what the client wants)
Client Browser
Sends Recordset
request by way of
Connection Object
Sends and receives
Query request by way of
Recordset Object
Database Management System
Data Tables
(contains all information)
(Applies rules and searches for data)
Search for Data (SQL query)
Create recordset based
on data found
Recordsets
(contains copy of information requested by client)
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
Recordsets
(contains information requested by client)
• 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.
Recordset Object
Creating Recordsets
'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
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.