Transcript Database Systems - Seneca - School of Information

Database Design – Lecture 18
Client/Server, Data
Warehouse and E-Commerce
Database Design
Lecture Objectives



Client/Server
Data Warehouse
E-Commerce
2
Client/Server
A computing model/architecture
Functionality is distributed between servers
and clients





Client is the TP (transaction processor)
Server is the DP (data processor)
Data can be distributed on more than one
physical site (location) but not necessarily
3
Client/Server
If data is distributed then the following
characteristics are required:



Location of data must be transparent to the user
(I.e. database must have DDBMS
characteristics)
Data can be accessed and manipulated by the
user at any time and in any way (I.e. data
requested is processed by the server but the
formatting and presentation are done on the
client side)
4
Client/Server
If data is distributed then the following
characteristics are required:


Processing of data is distributed amongst
multiple computers (I.e. data can reside at
multiple locations, the DDBMS server will locate
it and assemble it and send it back to client)
5
Client/Server
Database Design Issues



No significant issues – conceptual modeling used
to form the basis for the database design
Placement of the data is critical to ensure a
balance across this architecture
6
Client/Server - Tiers
2-Tier


Client requests services directly from the server
only
3-Tier


Client requests hare handled by intermediate
servers, which co-ordinate the clent request with
subordinate servers

i.e. Client – Database Middleware – Database Server
7
Client/Server - Tiers
Multi-Tier


Client request are handled by intermediate
servers, which co-ordinate the client requests
with subordinate servers

i.e. Client – Application Server (WebLogic, WebSphere)
– Database Middleware – Database Server)
8
Client/Server
Database Middleware


Provides the connectivity between application
software and the database
Client
application
Standard ODBC API
Driver Manager & Components
SQL Server
Driver
Oracle
Driver
DB2
Driver
Vendors develop their
own drivers for access
to their DBMS’s
9
Lecture Objectives



Client/Server
Data Warehouse
E-Commerce
10
Data Warehouse



Usually only a (very large) read-only
database optimized (un-normalized) for
data analysis and query processing
Generally summarizes operational data into
new data structures that can be accessed
by decision support systems
Data is extracted, transformed and
summarized into new table structures
11
Data Warehouse
Operational data



Is usually stored in a relational database – in
normalized table structures
Optimized to support transaction processing –
daily business transactions that support a
business (I.e. sales, banking)
12
Data Warehouse
Operational data – for example:



Activity is captured in a DBMS transaction log
(I.e. if a sale is made and inventory is updated)
as well as an update being made to inventory to
reduce it to reflect a new quantity on hand as
well as to write a sales record)
If the transaction fails, the transaction log is
used to ‘roll back’ the inventory and sales
records to the state the database was in before
this particular transaction started
13
Data Warehouse
Types of Processing


EIS – Executive Information Systems


DSS – What If Analysis


Review results of queries in pie chart, bar graph or
some graphical format
I.e. run a query and provide the ability to change a
value of the data and see what the result would be
(how would the data change)
Drill Down – Reporting

Provide results at a very high level but provide the
ability to select a ‘category’ and view the results at the
next level
14
Executive Information Systems
Data Warehouse
Figure 11-15: Systems Analysis & Design in a Changing World; Course Technology
15
Decision Support Systems
Data Warehouse
16
Data Warehouse
Figure 11-14: Systems Analysis & Design in a Changing World; Course Technology
Drill Down
17
Lecture Objectives



Client/Server
Data Warehouse
E-Commerce
18
E-Commerce
Two types of applications:



B2B – Business To Business
B2C – Business To Consumer
Two main features of e-commerce
applications:



Online payments
Online selection of products
19
E-Commerce



Uses the internet to perform business (buy,
sell, trade products and services)
Adds value to an organization (visibility for
the company due to wide audience the
internet can serve)
Database design for E-Commerce uses the
same concepts as if designing for a DDBMS
or client/server with a few exceptions…..
20
E-Commerce
Database Design considerations:



As you are dealing with a broad customer base,
additional tables may be required to support the
application (I.e. shopping cart, method of
shipments, method of payments, and tax rates)
Credit card and password data should be
encrypted
21
E-Commerce
Common e-commerce Scenario:

1.
2.
3.
A customer orders products online, entering
order and credit card information on a
merchant’s web page
The information travels from the customer’s
computer over the Internet to the merchant’s
web server
The merchant uses a third-party company to
process payment authorization
22
E-Commerce
Common e-commerce Scenario:

4.
5.
6.
The payment processing company contacts the
customer’s credit card issuing company to
authorize the transaction
The customer’s credit card issuer authorizes the
transaction
The merchant receives authorization, stores the
order and payment data in a database, and
sends order confirmation to customer
23
E-Commerce
Common e-commerce Scenario:

7.
8.
The seller uses a third-party shipping company
to deliver the products
The customer receives order and shipping
information
24
E-Commerce
Common Production tables to support ecommerce application:






CUSTOMER – contains detailed information
about each registered customer
PRODUCT – contains product details
PRODUCT TYPE – used to group product by type
so that searching is easier
ORDER – contains order information
ORDER LINE – contains the products selected
for an order
25
E-Commerce
Common Support tables to support e-commerce
application:








SHOPCART – a temporary table which contains the
products and quantity of products requested
PAYMENT TYPE – a list of payment options offered by the
merchant
SHIPMENT TYPE – a list of shipping options offered by the
merchant
TAX RATE – the tax rate (by province, countries etc)
PROVINCE/COUNTRY – the list of provinces/states
PROMOTION – special promotion information and sales
discounts
PRICING – use to manage pricing levels (I.e. multiple
discounts)
26