Transcript chap09

Chapter 9
Introduction to Database
Concepts
Database-Driven Web Sites, Second Edition
1
Objectives
In this chapter, you will:
• Learn what a relational database is, become
familiar with the structure of a relational
database, and understand relational database
concepts and terms
• Understand the differences between personal and
client/server databases, and become familiar with
the Oracle9i client/server database
Database-Driven Web Sites, Second Edition
2
Objectives
In this chapter, you will:
• Explore the structure of the Clearwater Traders
and Northwoods University relational databases
• Understand how Web forms communicate with
databases and learn how to create a data
connection in Visual Studio .NET
• Write SQL queries to retrieve records from a
single database table, and learn how to sort and
filter retrieved values
Database-Driven Web Sites, Second Edition
3
Objectives
In this chapter, you will:
• Write SQL queries that join multiple database
tables
• Write SQL queries to perform operations on
groups of data values
• Write SQL queries to insert, update, and delete
data records
Database-Driven Web Sites, Second Edition
4
Overview of Relational Databases
• A database stores data in a central location
• A database strives to eliminate redundant data in
order to reduce the possibility of inconsistent
data
• In a database system, a single application called
the database management system (DBMS)
performs all routine data handling operations
Database-Driven Web Sites, Second Edition
5
Overview of Relational Databases
• Most modern databases are relational databases,
which store data in a tabular format
• A relational database organizes data in tables, or
matrixes with columns and rows
Database-Driven Web Sites, Second Edition
6
Overview of Relational Databases
• Entity: an object about which data is stored
• Relational database: different tables store data
about different entities
• Relationships:
– Connect information about different entities
– Links that show how different records are related
• Relationships among records in different tables
are established through key fields
Database-Driven Web Sites, Second Edition
7
Primary Keys
• Primary key: a field in a relational database table
whose value must be unique for each record, and
which serves to identify the record
• Every record must have a primary key, and the
primary key cannot be NULL
• NULL means that a value is indeterminate or
undefined
Database-Driven Web Sites, Second Edition
8
Candidate Keys
• Candidate key:
– Field that could be used as the primary key
– Should be a numeric field that is unique for each
record and does not change
• Good candidate key choices include identification
numbers, such as product stock-keeping units
(SKUs), book ISBN numbers, and student
identification numbers
Database-Driven Web Sites, Second Edition
9
Surrogate Keys
• Surrogate key:
– Field that the database designer creates to be the
record’s primary key identifier
– Has no real relationship to the record to which it is
assigned, other than to identify the record uniquely
• Usually, developers configure the database to
generate surrogate key values automatically
Database-Driven Web Sites, Second Edition
10
Surrogate Keys
• In an Oracle9i database, surrogate key values can
be automatically generated using a sequence
• Sequences: sequential lists of numbers that the
database generates automatically and that
guarantee that each primary key value will be
unique
• In an Access database, surrogate key values can
be automatically generated using the
AutoNumber data type
Database-Driven Web Sites, Second Edition
11
Foreign Keys
• Foreign key: a field in a table that is a primary
key in another table
• The foreign key creates a relationship between
the two tables
Database-Driven Web Sites, Second Edition
12
Foreign Keys
Database-Driven Web Sites, Second Edition
13
Composite Keys
• The combination of fields to create a unique
primary key is called a composite key
Database-Driven Web Sites, Second Edition
14
Composite Keys
Database-Driven Web Sites, Second Edition
15
Database Management Systems
• Database management system (DBMS):
– Provides the common functions for managing a
database
– Consists of a database engine, which manages the
physical storage and data retrieval
– Provides software for creating database
applications, which provide the interface that
allows users to interact with the database
Database-Driven Web Sites, Second Edition
16
Personal Database Management
Systems
• Personal database: DBMS that is primarily for
creating single-user database applications, which
are applications that only one person uses at one
time
• With a personal database, the database engine
and the database applications run on the same
workstation, and appear to the user as a single
integrated application
Database-Driven Web Sites, Second Edition
17
Personal Database Management
Systems
• Organizations sometimes use personal
databases to create simple multi-user database
applications that multiple people use at the same
time
• Personal databases such as Microsoft Access
support small multi-user database applications
• They do this by storing the database application
files on a file server and then transmitting the
files or the parts of files containing the desired
data to various users across a network
Database-Driven Web Sites, Second Edition
18
Personal Database Management
Systems
Figure 9-8 shows how a personal database is used for a multiuser application
Database-Driven Web Sites, Second Edition
19
Client/Server Database Management
Systems
• Client/server databases:
– Take advantage of distributed processing and
networked computers by distributing processing
across multiple computers
– The DBMS server process runs on one workstation,
and the database applications run on separate client
workstations across the network
• When the server DBMS process receives a data
request, it retrieves the data from the database,
performs the requested functions on the data, and
sends only the requested data back to the client
Database-Driven Web Sites, Second Edition
20
Client/Server Database Management
Systems
Figure 9-9 shows the client/server database architecture
Database-Driven Web Sites, Second Edition
21
Client/Server Database Management
Systems
• Oracle9i is a client/server database
• On the server side, a process listens for incoming
user requests and commands
• On the client side, Oracle9i provides utilities for
executing SQL commands and designing and
creating custom applications, as well as specific
applications for supporting tasks
• All Oracle9i server- and client-side programs use
SQL*Net
Database-Driven Web Sites, Second Edition
22
The Case Study Databases
• The following principles should be followed when
creating database tables:
– To avoid creating tables that contain redundant
data, related items that describe a single entity
should be grouped together in a common table
– Tables that duplicate values many times in
different rows should not be created
– When a database programmer creates a database
and inserts data values, he or she must specify the
data type for each column
Database-Driven Web Sites, Second Edition
23
Retrieving Database Data Using Visual
Studio .NET
• To retrieve and manipulate data in a database, a
data connection must be created
• Data connection: a communication path between
a Web application and a data source, such as a
database
Database-Driven Web Sites, Second Edition
24
How Web Forms Communicate with
Data Sources
• Application program interfaces (APIs): establish
rules for how programs interact and share data
• ODBC (Open Database Connectivity): an API that
specifies how program commands connect to
databases and communicate with databases
• ODBC driver: translates database application
program commands to a format that the database
understands
• Driver: a program that translates commands
between different programs
Database-Driven Web Sites, Second Edition
25
How Web Forms Communicate with
Data Sources
• Visual Studio .NET provides ADO.NET, which is a
set of built-in procedures and object models that
translate commands between application
programs and ODBC drivers
• An OLE DB (Object Linking and Embedding
Database) driver translates ADO.NET commands
into the commands that specific ODBC drivers
expect
Database-Driven Web Sites, Second Edition
26
How Web Forms Communicate with
Data Sources
Figure 9-14 shows how Web forms interact with databases
using ADO.NET and ODBC
Database-Driven Web Sites, Second Edition
27
Creating a Data Connection in Visual
Studio .NET
• The first step in creating a Web form that displays
database data is to make a data connection
• When a new data connection is created, the
connection exists in the Visual Studio .NET IDE,
and is available to any Web application project
that is open
Database-Driven Web Sites, Second Edition
28
Creating a Data Connection in Visual
Studio .NET
• To create and manage data connections in
Visual Studio .NET IDE, the Server Explorer is
used
• The Server Explorer window is a server
management console
Database-Driven Web Sites, Second Edition
29
Creating a Data Connection in Visual
Studio .NET
• In a database, a view is similar to a table, except
that it shows a different aspect of the table, such
as a subset of the table fields
• A stored procedure is a program that is stored in
the database and which other users can execute
• Stored procedures are used to retrieve data
values and manipulate them using program
commands
• Data records can be viewed, inserted, updated,
and deleted using Visual Studio .NET
Database-Driven Web Sites, Second Edition
30
Writing SQL Queries to Retrieve Data
from a Single Database Table
• To create Web pages that allow users to
manipulate database data, program commands
that can interact with relational databases must
be written
• These program commands often use query
languages to retrieve existing database data and
insert, update, and delete data values
• Structured Query Language (SQL): the primary
query language for relational databases
Database-Driven Web Sites, Second Edition
31
Writing SQL Queries to Retrieve Data
from a Single Database Table
• SQL commands that retrieve database data are
called queries
• SQL commands that insert, update, or delete
database data are called action queries
• To create database queries in Visual Studio .NET,
a Web form component called an
OleDbDataAdapter is created
• Visual Studio .NET provides the Query Builder,
which is a graphical environment that allows
developers to visually select the database tables
and table fields from which to retrieve values
Database-Driven Web Sites, Second Edition
32
Creating a Data Adapter
• Data adapter:
– A Web form component that retrieves database
data in a Web form
– Retrieves specific data values from a data source
such as a database, and places the values in a data
set
• Data set: a structure similar to an array or a
collection, except that it stores data retrieved
from a database
Database-Driven Web Sites, Second Edition
33
Using Query Builder to Create SQL
Queries That Retrieve Data from a
Single Database Table
• To use Query Builder to create queries that
retrieve data from a single database table,
developers must:
– Select the database table from which to retrieve
data
– Specify the table columns that the query retrieves
– Specify a sort order for the retrieved data values
– Specify search conditions to filter the retrieved
values
Database-Driven Web Sites, Second Edition
34
Using Query Builder to Create SQL
Queries That Retrieve Data from a
Single Database Table
• The basic syntax for a SQL query that retrieves
data from a single database table is:
SELECT column1, column2, ...
FROM table
Database-Driven Web Sites, Second Edition
35
Finishing the Data Adapter
• When a new data adapter is created using the
Data Adapter Configuration Wizard, the Wizard
automatically creates action queries based on the
data adapter’s SQL query
• To configure the data adapter so it does not
create action queries, a developer would open the
Advanced SQL Generation Options dialog box
and clear the Generate Insert, Update and Delete
statements check box
Database-Driven Web Sites, Second Edition
36
Creating SQL Queries That Retrieve
Data From Multiple Tables
• One of the strengths of SQL is its ability to join,
or combine, data from multiple database tables
using foreign key references
• The general syntax of a SELECT query that joins
two tables is:
SELECT table.column1, table.column2, ...
FROM table1, table2
WHERE table1.joincolumn = table2.joincolumn
[AND search_condition(s)]
Database-Driven Web Sites, Second Edition
37
Creating SQL Queries That Retrieve
Data From Multiple Tables
• To make it easier to identify the tables in a
multiple-table query, it is helpful to create a query
design diagram, such as the one shown in Figure
9-23
Database-Driven Web Sites, Second Edition
38
SQL Group Functions
• SQL group function: performs an operation on a
group of retrieved records and returns a single
result, such as a column sum
• A group function is used in a SQL query by listing
the function name, followed by the column name
on which to perform the calculation in
parentheses
Database-Driven Web Sites, Second Edition
39
Using the GROUP BY Clause to Group
Related Records
• If a query retrieves multiple records, and one of
the columns has duplicate values, the output can
be grouped by the column with duplicate values
and group functions can be applied to the
grouped data
• The GROUP BY clause has the following syntax:
GROUP BY fieldname
• The fieldname parameter is the name of the
column on which you want to group the values
Database-Driven Web Sites, Second Edition
40
Inserting, Updating, and Deleting
Database Data
• As users interact with data-based Web pages,
they enter input values and make selections that
may generate action queries
• Action queries involve operations that change the
database by inserting, updating, or deleting data
values
Database-Driven Web Sites, Second Edition
41
Inserting Database Data
• The SQL INSERT action query is used to add new
data records to database tables
• The basic syntax of an INSERT action query for
inserting a value for each table field is:
INSERT INTO table (column1_name,
column2_name, ...)
VALUES (column1_value, column2_value, ...)
• The values in the VALUES list must appear in the
same order as the column names in the INSERT
INTO list
Database-Driven Web Sites, Second Edition
42
Updating Database Records
• To update an existing database record, an
UPDATE action query is used
• A search condition to identify the row to update is
also specified
• The general syntax of an UPDATE action query is:
UPDATE tablename
SET column1 = new_value1, column2 =
new_value2, ...
WHERE search_condition
Database-Driven Web Sites, Second Edition
43
Updating Database Records
• In the UPDATE action query, the WHERE clause is
used to specify a search condition to make the
command update specific records
• The general syntax for a search condition is:
WHERE column_name comparison_operator
search_expression
• Multiple records in a table can be updated using a
single UPDATE command that has an inexact
search condition that matches multiple records
Database-Driven Web Sites, Second Edition
44
Deleting Existing Database Records
• The DELETE action query is used to remove
records from database tables
• The general syntax for the DELETE action query
is:
DELETE FROM tablename
WHERE search_condition
• Records from only one table can be deleted at a
time using a single DELETE action query
Database-Driven Web Sites, Second Edition
45
Summary
• Most modern databases are relational databases,
which store data in a tabular format
• Relational databases store data about different
entities in separate tables
• A primary key is a field that uniquely identifies a
specific record in a database table
• Personal database systems are best suited for
single-user database applications, which usually
are stored on a single user’s desktop computer
Database-Driven Web Sites, Second Edition
46
Summary
• Client/server databases divide the database into a
server process that runs on a network server and
user application processes that run on individual
client workstations
• Web forms that interact with databases contain
SQL commands to retrieve, insert, update, and
delete data
• The Query Builder can be used to create SQL
queries that retrieve data from a single database
table, to sort retrieved data values, and to filter
data values using search conditions
Database-Driven Web Sites, Second Edition
47