Chapter 9 Introduction to Database Concepts DatabaseDriven Web

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 Oracle 9 i 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 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 Oracle 9 i 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 Auto. Number 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 • Oracle 9 i is a client/server database • On the server side, a process listens for incoming user requests and commands • On the client side, Oracle 9 i provides utilities for executing SQL commands and designing and creating custom applications, as well as specific applications for supporting tasks • All Oracle 9 i 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 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 Ole. Db. Data. Adapter 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 column 1, column 2, . . . 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. column 1, table. column 2, . . . FROM table 1, table 2 WHERE table 1. joincolumn = table 2. 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 (column 1_name, column 2_name, . . . ) VALUES (column 1_value, column 2_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 column 1 = new_value 1, column 2 = new_value 2, . . . 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