Transcript Comp1503 Introduction to E

Comp2513
Database and E-Commerce
Daniel L. Silver, Ph.D.
Objectives
To introduce the basic concepts of database
and DBMS
 To describe the relational database model
 To discuss the Stuctured Query Language
 To define ODBC and JDBC
 To outline the role that database plays in ECommerce
 Reference: portions of Chapter 6

2002
Daniel L. Silver
2
Outline
Databases and DBMS
 Relation database
 Structured Query Language (SQL)
 ODBC and JDBC

2002
Daniel L. Silver
3
What is a Database?
Database - A collection of data, structured
in a well defined format, accessed by
multiple applications using standard
commands, ensuring integrity of access
 A Database can contain many records and
the equivalent of many files each containing
many records

2002
Daniel L. Silver
4
Database Management System

DBMS – a collection of software that
facilitates and optimizes database I/O for
applications
– Flexible access to data - independent of
physical storage
– Rapid response to ad hoc queries
– Access by multiple applications in various ways
– Ensures data integrity
– Elimination of redundant data
2002
Daniel L. Silver
5
Relational Database
Different databases have different ways of
organizing and representing data – referred
to as a data model
 The relational data model – data is placed in
tables where rows represent records and
columns represent fields
 Tables have no predefined relation to one
another, instead data can be dynamically
related

2002
Daniel L. Silver
6
Relation DBMS - RDBMS

Major Commercial RDBMS vendors:
–
–
–
–
–

IBM (DB2)
Oracle
MS (Access, SQL Server)
INGRES (RTI)
Informix
Freely Available RDBMS:
– PostgreSQL
– MySQL
2002
Daniel L. Silver
7
An Example: ERD
ERD =
Entity
Relationship
Diagram
2002
Daniel L. Silver
8
An Example: Relational Tables
PROJECT
pnum pdesc psd
1 E911 Apr
2 CAPC Aug
EMPLOYEE
ped
Nov
Dec
eid
9902
0103
ASSIGNMENT
aid ………………..
apnum aeid alnum
1 9902
Hfx1
1 0103
Hfx1
2 0103
Yrm2
2002
ename
Ritter, Tex
Nasium, Jim
LOCATION
abd
May
May
Sep
aed
Oct
Aug
Oct
Daniel L. Silver
lnum laddress
Hfx1 1234 Barrington St.
Yrm2 56 Front St.
9
SQL- Structured Query Language
Data within a DBMS is manipulated via a
4GL or by a specific application program
using a DBMS access language
 SQL is data definition and manipulation
langauge for relational databases
 SQL has become an international standard

2002
Daniel L. Silver
10
SQL Basics

CREATE TABLE – creates a table and
defines its fields (columns), e.g.:
CREATE TABLE PROJECT
(pnum integer NOT NULL.
pdesc character NOT NULL.
PRIMARY KEY (pnum);
ALTER TABLE – delete or add fields
 DROP TABLE – delete an entire table

2002
Daniel L. Silver
11
SQL Basics
INSERT INTO – places values into a table
 UPDATE – changes values in a table
 DELETE FROM – removes records in table
 SELECT – columns from a table, general
format:

SELECT <colname>, <colname> FROM
<tablename> WHERE <condition>
2002
Daniel L. Silver
12
An Example: Relational Tables
PROJECT
pnum pdesc psd
1 E911 Apr
2 CAPC Aug
EMPLOYEE
ped
Nov
Dec
eid
9902
0103
ASSIGNMENT
aid ………………..
apnum aeid alnum
1 9902
Hfx1
1 0103
Hfx1
2 0103
Yrm2
2002
ename
Ritter, Tex
Nasium, Jim
LOCATION
abd
May
May
Sep
aed
Oct
Aug
Oct
Daniel L. Silver
lnum laddress
Hfx1 1234 Barrington St.
Yrm2 56 Front St.
13
SQL Select Example
Find all projects in which Jim Nasium is
involved …
Set qename = “Nasium, Jim”
SELECT pnum, pdesc
FROM employee, assignment, project
WHERE ename = qename AND
assignment.aeid = employee.eid AND
project.pnum = assignment.apnum
Returns:
2002
pnum pdesc
1 E911
2 CAPC
Daniel L. Silver
14
Our E-Commerce Mall DBMS
We are using PostgreSQL (postgres)
 Freely available off the web
 What is PostgreSQL?
 The PostgreSQL page.

2002
Daniel L. Silver
15
Our E-Commerce Mall DB
Consists of 3 tables created and managed by
PostgreSQL
 Categories

–
–
–
–
–
2002
category_id
category_name
description
image
parent
int
char 50
text
char 100
int (null if a store)
Daniel L. Silver
16
Our E-Commerce Mall DB

Products
–
–
–
–
–
–
–
2002
product_id
product_name
sku
description
image
price
category
int
char 50
char 50
text
char 100
real
int
Daniel L. Silver
17
Our E-Commerce Mall DB

Product_category
– product_id
– category_id

int
int
Used to display a product in more than one
category
2002
Daniel L. Silver
18
SQL and Our E-Commere
Mall Database
From index.jsp, a java bean is used to query
our E-Commerce Mall DB to get the
categories for a particular store:
(“SELECT category_id, category_name, description, image
FROM category WHERE parent = ?");
pstmt.setInt(1,getId());
2002
Daniel L. Silver
19
SQL and Our E-Commere
Mall Database
DETAILS:
From index.jsp, the following use of a java bean gets the categories of a store:
List categories = store.getCategories();
The getCategories method in turn uses another bean to get all categories:
category.getChildren()
The getChildren method makes the SQL query via a JDBC request:
conn = StoreDatabase.getConnection();
pstmt = conn.prepareStatement("SELECT category_id,category_name,description,image
FROM category WHERE parent = ?");
pstmt.setInt(1,getId());
rs = pstmt.executeQuery();
2002
Daniel L. Silver
20
SQL and Our E-Commere
Mall Database
From category.jsp, a java bean is used to
query our E-Commerce Mall DB to get
the products of a particular category:
("SELECT product_id, product_name, sku,description, image,price
FROM product WHERE category = ?" +
" OR EXISTS (SELECT * FROM product_category
WHERE product_category.category_id = ?" +
" AND product_category.product_id = product.product_id)");
pstmt.setInt(1,category.getId()); pstmt.setInt(2,category.getId());
2002
Daniel L. Silver
21
SQL and Our E-Commere
Mall Database
DETAILS:
From category.jsp, a java bean is used to get the categories of a store:
List products = Product.getProducts(category);
The getProducts method makes the SQL query via a JDBC request:
conn = StoreDatabase.getConnection();
pstmt = conn.prepareStatement("SELECT product_id,product_name,sku,description,
image,price
FROM product
WHERE category = ?" +
" OR EXISTS (SELECT * FROM product_category
WHERE product_category.category_id = ?" +
" AND product_category.product_id = product.product_id)");
pstmt.setInt(1,category.getId());
pstmt.setInt(2,category.getId());
rs = pstmt.executeQuery();
2002
Daniel L. Silver
22
ODBC
Open Database Connectivity is a widely
accepted application programming interface
(API) for database access developed by a
consortium led by MicroSoft
 ODBC is a combination of ODBC API
function calls and the SQL language

2002
Daniel L. Silver
23
ODBC
Originally, a proprietary language was used
to talk to each DBMS
 A program required unique code to interact
with Access, DB2 and Oracle databases

Program with
3 different sets
of API calls
and SQL
Access
DBMS
Access DB
DB2
DBMS
DB2 DB
Oracle DB
2002
Daniel L. Silver
Oracle
DBMS
24
ODBC




ODBC abstracts away specific DBMS
The application issues ODBS API calls
ODBC Manager interfaces with the target DBMS
ODBC driver must be installed for each DBMS
Program with
ODBC API
calls and SQL
Access
DBMS
ODBC
Manager
DB2
DBMS
Access DB
DB2 DB
Oracle DB
2002
Daniel L. Silver
Oracle
DBMS
25
JDBC
Java Database Connectivity
 JDBC is a trademark of SUN Microsystems
 Standard API that lets you access virtually
any tabular data source from Java programs

– relational databases, flat files, spreadsheet files

JDBC builds on and reinforces the style and
virtues of Java (easy to use)
2002
Daniel L. Silver
26
JDBC Facilitates DB I/O
(The following examples are taken from IndexServlet.java)

Connect to DB and establish a session:
conn = StoreDatabase.getConnection();
… within getConnection …
Connection conn =
DriverManager.getConnection(“jdbc:postgresql://raven.acadiau.ca/
2513DB”, “storexx_uid”, “storexx_pwd”);

Creates an object for issuing SQL statements (commands)
to the connection:
pstmt = conn.prepareStatement("SELECT category_id,category_name,
description,image FROM category WHERE parent = ?");

Two JDBC statement methods:
– executeQuery() – used for issuing SELECT queries
– executeUpdate() – used for issuing DB inserts, updates and deletes
2002
Daniel L. Silver
27
JDBC Facilitates DB I/O
(The following examples are taken from IndexServlet.java)

The executeQuery() method returns a ResultSet object that contains the
results of the query operation on the DB:
conn = StoreDatabase.getConnection();
pstmt = conn.prepareStatement("SELECT category_id,category_name,
description, image FROM category WHERE parent = ?");
pstmt.setInt(1,getId());
rs = pstmt.executeQuery();

This “rs” object can be explored row by row:
while (rs.next()) {
int childId = rs.getInt("category_id");
String childName = rs.getString("category_name");
String childDesc = rs.getString("description");
String childImage = rs.getString("image");
…
}
2002
Daniel L. Silver
28
JDBC Facilitates DB I/O
DETAILS of getChildren:
List children = new Vector();
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
try {
conn = StoreDatabase.getConnection();
pstmt = conn.prepareStatement("SELECT category_id,category_name,description,image
FROM category WHERE parent = ?");
pstmt.setInt(1,getId());
rs = pstmt.executeQuery();
while (rs.next()) {
int childId = rs.getInt("category_id");
String childName = rs.getString("category_name");
String childDesc = rs.getString("description");
String childImage = rs.getString("image");
Category child = new Category(childId,childName,childDesc,childImage,this);
children.add(child);
child.isNew = false;
}
…
return children;
2002
Daniel L. Silver
29
JSPs simplify JDBC (WS/DB2)


Java Server Pages can make use of pre-written
code to facilitate DB access
To connect:
<jsp:dbconnect id="conn" url="jdbc:db2:demomall"
driver="COM.ibm.db2.jdbc.app.DB2Driver"
userid="db2user" passwd="db2pass">
</jsp:dbconnect>

To execute a query:
<jsp:dbquery connection="conn" id="catalog">
SELECT …
</jsp:dbquery>

To get the result (in this case a category name):
<jsp:getProperty name="catalog" property=“name" />
2002
Daniel L. Silver
30
JSPs simplify JDBC
Portion of code within index.jsp that accesses
the Mall DB when displaying the categories
at the top of the page:
<a class="catLink"
href="category.jsp?id=<jsp:getProperty name="category" property="id" />"
onMouseOver="hiliteCell(<%=i%>)“
onMouseOut="unhiliteCell(<%=i%>)">
<jsp:getProperty name="category" property="name" />
</a>
2002
Daniel L. Silver
31
JSPs simplify JDBC
DETAILS:
<%
int spaceWidth = 600 - (categories.size() * 110);
int i = 0;
for (Iterator it = categories.iterator(); it.hasNext();) {
Category c = (Category) it.next();
pageContext.setAttribute("category",c);
%>
<td width="110"><table border="0" cellspacing="0" cellpadding="0"
width="110"><tr><td align="center"><font face="Verdana, Arial, Helvetica, sansserif" size="2">
<a class="catLink"
href="category.jsp?id=<jsp:getProperty name="category" property="id" />"
onMouseOver="hiliteCell(<%=i%>)"
onMouseOut="unhiliteCell(<%=i%>)"><jsp:getProperty name="category"
property="name" /></a>
</font></td></tr></table></td>
<%
i++;
}
%>
2002
Daniel L. Silver
32
The Role of Database
in E-Commerce




Database is used within E-Commerce to provide
dynamic ad hoc information on-demand to users
Store administrators use databases to set up categories
and products in a secure and reliable manner
E-Commerce applications can be written with only the
logical structure of data and not its physical storage
The database holds the content of a page and it also can
hold the presentation of that content such that the same
data can be presented in different ways to different
customers
2002
Daniel L. Silver
33
Major Architectural Components
of the Web
Bank
Server
Dedicated
Client 1
Browser
Client 2
HTTP
TCP/IP
Server A
Internet
HTTP
Server
Browser
URL
Server B
2002
App.
Server
Database
Server
index.html prog.class
Bank
Server
Daniel L. Silver
34
THE END
[email protected]
SQL and WebSphere
From index.jsp, the following gets the various
category names (cgname) and reference numbers
for a specified merchant (cgmenbr):
SELECT cgryrel.crccgnbr, category.cgname, category.cgrfnbr
FROM cgryrel, category
WHERE crmenbr = <%= request.getParameter("cgmenbr") %> AND
(cgryrel.crpcgnbr IS NULL) AND
category.cgmenbr = <%= request.getParameter("cgmenbr") %> AND
category.cgrfnbr = crccgnbr
2002
Daniel L. Silver
36
SQL and WebSphere
From index.jsp, the following gets the various
category names (cgname) and reference numbers
for a specified merchant (cgmenbr):
SELECT cgryrel.crccgnbr, category.cgname, category.cgrfnbr
FROM cgryrel, category
WHERE crmenbr = <%= request.getParameter("cgmenbr") %> AND
(cgryrel.crpcgnbr IS NULL) AND
category.cgmenbr = <%= request.getParameter("cgmenbr") %> AND
category.cgrfnbr = crccgnbr
2002
Daniel L. Silver
37
SQL and WebSphere
From product.jsp, the following gets the various
product category names (cgname) for specified
merchant (cgmenbr) and category reference number
(cgrfnbr):
SELECT category.cgname
FROM category
WHERE cgmenbr = <%=request.getParameter("cgmenbr") %> AND
cgrfnbr = <%= request.getParameter("cgrfnbr") %>
2002
Daniel L. Silver
38
SQL and WebSphere
From productDisplay.jsp, the following gets various
product values for display for specified merchant
(prmenbr) and product number (prrfnbr):
SELECT product.prsdesc, product.prnbr, product.prfull,
product.prthmb,prodprcs.ppprc, prodprcs.ppcur,
product.prldesc1,product.prldesc2, product.prldesc3,
product.prwght, product.prwmeas,
product.prheight,product.prlngth, product.prwidth,
product.prsmeas
FROM product, prodprcs
WHERE ppprnbr = prrfnbr AND
prmenbr = <%= request.getParameter("prmenbr") %> AND
prrfnbr = <%= request.getParameter("prrfnbr") %>
2002
Daniel L. Silver
39
JDBC Facilitates WS DB I/O
(The following examples are taken from IndexServlet.java)

Connect to DB and establish a session:
Connection myConnection =
DriverManager.getConnection("jdbc:db2:demomall",
"db2user", "db2pass");

Create an object for issuing SQL statements
(commands):
Statement statement = myConnection.createStatement();

Two JDBC statement methods:
– executeQuery() – used for issuing SELECT queries
– executeUpdate() – used for issuing DB inserts, updates
and deletes
2002
Daniel L. Silver
40