Comp1503 Introduction to E-Commerce Technology

converseoncologistInternet και Εφαρμογές Web

7 Αυγ 2012 (πριν από 5 χρόνια και 1 μήνα)

320 εμφανίσεις

Comp2513


Database and E
-
Commerce

Daniel L. Silver, Ph.D.

2002

Daniel L. Silver

2

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 E
-
Commerce


Reference: portions of Chapter 6


2002

Daniel L. Silver

3

Outline



Databases and DBMS


Relation database


Structured Query Language (SQL)


ODBC and JDBC


2002

Daniel L. Silver

4

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

5

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

6

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

7

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

8

An Example: ERD


ERD =


Entity


Relationship


Diagram

2002

Daniel L. Silver

9

An Example: Relational Tables

pnum

pdesc

psd

ped

1
E911

Apr

Nov


2
CAPC

Aug

Dec


eid


ename

9902

Ritter, Tex

0103

Nasium, Jim

aid ………………..

apnum

aeid

alnum

abd

aed

1
9902

Hfx1

May

Oct

1

0103

Hfx1

May

Aug

2

0103

Yrm2

Sep

Oct

lnum

laddress

Hfx1

1234 Barrington St.

Yrm2

56 Front St.

PROJECT

EMPLOYEE

ASSIGNMENT

LOCATION

2002

Daniel L. Silver

10

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

11

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

12

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

13

An Example: Relational Tables

pnum

pdesc

psd

ped

1
E911

Apr

Nov


2
CAPC

Aug

Dec


eid


ename

9902

Ritter, Tex

0103

Nasium, Jim

aid ………………..

apnum

aeid

alnum

abd

aed

1
9902

Hfx1

May

Oct

1

0103

Hfx1

May

Aug

2

0103

Yrm2

Sep

Oct

lnum

laddress

Hfx1

1234 Barrington St.

Yrm2

56 Front St.

PROJECT

EMPLOYEE

ASSIGNMENT

LOCATION

2002

Daniel L. Silver

14

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:


pnum

pdesc

1
E911

2
CAPC


2002

Daniel L. Silver

15

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

16

Our E
-
Commerce Mall DB


Consists of 3 tables created and managed by
PostgreSQL


Categories


category_id


int


category_name

char 50


description


text


image



char 100


parent



int (null if a store)


2002

Daniel L. Silver

17

Our E
-
Commerce Mall DB


Products


product_id


int


product_name

char 50


sku



char 50


description


text


image



char 100


price



real


category


int

2002

Daniel L. Silver

18

Our E
-
Commerce Mall DB


Product_category


product_id


int


category_id


int


Used to display a product in more than one
category

2002

Daniel L. Silver

19

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

20

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

21

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

22

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

23

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

24

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

Oracle DB

Oracle

DBMS

DB2 DB

DB2

DBMS

Access DB

Access

DBMS

2002

Daniel L. Silver

25

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




ODBC

Manager

Oracle DB

Oracle

DBMS

DB2 DB

DB2

DBMS

Access DB

Access

DBMS

Program with

ODBC API

calls and SQL

2002

Daniel L. Silver

26

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

27

JDBC Facilitates DB I/O


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

(The following examples are taken from IndexServlet.java)

2002

Daniel L. Silver

28

JDBC Facilitates DB I/O


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");






}


(The following examples are taken from IndexServlet.java)

2002

Daniel L. Silver

29

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

30

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

31

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

32

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, sans
-
serif" 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

33

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

34

Major Architectural Components
of the Web

Internet

Browser

Database

Server

Client 1

Server A

Server B

Bank

Server

URL

HTTP

TCP/IP

Browser

Client 2

HTTP

Server

App.

Server

index.html

Bank

Server

Dedicated

prog.class

THE END


danny.silver@acadiau.ca

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 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

38

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

39

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

40

JDBC Facilitates WS DB I/O


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

(The following examples are taken from IndexServlet.java)