The software infrastructure for electronic commerce
1 / 81

The Software Infrastructure for Electronic Commerce - PowerPoint PPT Presentation

  • Updated On :

The Software Infrastructure for Electronic Commerce. Databases and Data Mining Lecture 1: A Manager’s View of Database Technology Johannes Gehrke Goal Of Lectures One and Two. Understand the basics of modern data management

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'The Software Infrastructure for Electronic Commerce' - clemens

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
The software infrastructure for electronic commerce l.jpg

The Software Infrastructurefor Electronic Commerce

Databases and Data Mining

Lecture 1: A Manager’s View of Database Technology

Johannes Gehrke

Goal of lectures one and two l.jpg
Goal Of Lectures One and Two

  • Understand the basics of modern data management

    • DBMS technology

    • Data models

    • Transaction processing versus decision support

    • An data management architecture of ane-business

Goals of the dbms lectures contd l.jpg
Goals of the DBMS Lectures (Contd.)

  • Gain technical understanding to feel confident to ask questions.

  • Learn to ask the right questions.

The big picture l.jpg
The Big Picture

WWW SiteVisitor

Internal User



InternalWeb Server



Public Web Server




Web pages with database contents l.jpg
Web Pages with Database Contents

  • Web pages contain the results of database queries. How do we generate such pages?

    • Web server creates a new process for a program interacts with the database.

    • Web server communicates with this program via some data exchange protocol

    • Program generates result page with content from the database

Business transaction server l.jpg
Business Transaction Server

  • Application server: Piece of software between the web server and the applications

  • Functionality:

    • Hold a set of pre-forked threads or processes for performance

    • Database connection pooling (reuse a set of existing connections)

    • Integration of heterogeneous data sources

    • Transaction management involving several data sources

    • Session management

Other server side processing l.jpg
Other Server-Side Processing

  • Java Servlets: Java programs that run on the server and interact with the server through a well-defined API

  • JavaBeans: Reusable software components written in Java

  • Java Server Pages and Active Server Pages: Code inside a web page that is interpreted by the web server

What happens if you click on a link l.jpg
What Happens If You Click On A Link?


Company web

Johannes Gehrke


Banner web


Additional log

Hidden link




Web server log data l.jpg
Web Server Log Data

Common Log Format:host ident authuser date request status bytes

  • host: domain name or IP address of the client

  • date: date and time of the request

  • request: the request line from the client

  • status: three digit status code returned to the client

  • bytes: number of bytes in the object returned to the client

Web server log data contd l.jpg
Web Server Log Data (Contd.)

Usually, even more data available:

  • URL of the referring server

  • Name and version of the browser

  • Name of the file requested

  • Time to serve the request

  • IP address of the requesting browser

  • Cookie

Cookies l.jpg

  • The communication protocol (HTTP) between your browser and the web server is stateless. (Compare to a vending machine.)

  • Remedy: Store information (called a cookie) at the browser of the user

  • Example cookie (from

    PREFID=3415aaaf73b7bfe3,TM=956724506||0|261887833632111634|2662722800|29339450|*(name=value|domain|secure|expiration date|expiration time|last used date|last used time)

Cookies contd l.jpg
Cookies (Contd.)

  • A cookie is always associated with a specific domain (,,,,

  • Cookies have expiration dates

  • The secrets are in the (name=value) pairs (usually encrypted):PREFID=3415aaaf73b7bfe3,TM=956724506

  • Cookies have their own life on your computer:

    • \Documents and Settings\UserName\Cookies,\Windows\Cookies\Windows\Profiles\UserName\Cookies\ProgramFiles\Netscape\Users\Default\cookies.txt

Cookies contd13 l.jpg
Cookies (Contd.)

  • Applications of cookies:

    • Shopping carts

    • “Log in once” (example: New York Times)

    • Personalization ( Welcome back, Johannes)

    • General tracking of user behavior

  • User privacy

  • Other personalization/tracking techniques: Hidden fields in html pages, unique page names

  • Online demonstration

And then you click purchase l.jpg
And Then You Click Purchase …

(Simplified process)

  • Insert customer data into the database/check customer data

  • Check order availability

  • Insert order data into the database

  • Return order confirmation number to the customer

    All this data is stored in a database system (DBMS).

Why store data in a dbms l.jpg
Why Store Data in a DBMS?

  • Benefits

    • Transactions (concurrent data access, recovery from system crashes)

    • High-level abstractions for data access, manipulation, and administration

    • Data integrity and security

    • Performance and scalability

Transactions l.jpg

  • A transaction is an atomic sequence of database actions (reading, writing or updating a database object).

  • Each transaction must leave the DB in a consistent state (if DB is consistent when the transaction starts).

  • The ACID Properties:

    • Atomicity

    • Consistency

    • Isolation

    • Durability

Example transaction online store l.jpg
Example Transaction: Online Store

Your purchase transaction:

  • Atomicity: Either the complete purchase happens, or nothing

  • Consistency: The inventory and internal accounts are updated correctly

  • Isolation: It does not matter whether other customers are also currently making a purchase

  • Durability: Once you have received the order confirmation number, your order information is permanent, even if the site crashes

Transactions contd l.jpg
Transactions (Contd.)

A transaction willcommitafter completing all its actions, or it could abort(or be aborted by the DBMS) after executing some actions.

Example transaction atm l.jpg
Example Transaction: ATM

You withdraw money from the ATM machine

  • Atomicity

  • Consistency

  • Isolation

  • Durability

    Commit versus Abort?

    What are reasons for commit or abort?

Concurrency control l.jpg
Concurrency Control

(Start: A=$100; B=$100)

Consider two transactions:

  • T1: START, A=A+100, B=B-100, COMMIT

  • T2: START, A=1.06*A, B=1.06*B, COMMIT

    The first transaction is transferring $100 from B’s account to A’s account. The second transaction is crediting both accounts with a 6% interest payment.

Example contd l.jpg
Example (Contd.)

(Start: A=$100; B=$100)

  • Consider a possible interleaving (schedule):

    T1: A=A+$100, B=B-$100 COMMIT

    T2: A=1.06*A, B=1.06*B COMMIT

    End result: A=$106; B=$0

  • Another possible interleaving:

    T1: A=A+100, B=B-100 COMMIT

    T2: A=1.06*A, B=1.06*B COMMIT

    End result: A=$106; B=$6

    The second interleaving is incorrect! Concurrency control of a database system makes sure that the second schedule does not happen.

Ensuring atomicity l.jpg
Ensuring Atomicity

  • DBMS ensures atomicity (all-or-nothing property) even if the system crashes in the middle of a transaction.

  • Idea: Keep a log (history) of all actions carried out by the DBMS while executing :

    • Before a change is made to the database, the corresponding log entry is forced to a safe location.

    • After a crash, the effects of partially executed transactions are undone using the log.

Recovery l.jpg

  • A DBMS logs all elementary events on stable storage. This data is called the log.

  • The log contains everything that changes data: Inserts, updates, and deletes.

  • Reasons for logging:

    • Need to UNDO transactions

    • Recover from a systems crash

Recovery example l.jpg
Recovery: Example

(Simplified process)

  • Insert customer data into the database

  • Check order availability

  • Insert order data into the database

  • Write recovery data (the log) to stable storage

  • Return order confirmation number to the customer

Slide25 l.jpg

  • Transactions, concurrency control, and recovery are important aspects of the functionality of a database system

  • Tips for capacity planning:

    • Load influences level of concurrency Determines hardware requirements

    • Insufficient resources for concurrency and recovery can force serialization of transactions  Bad performance

    • Need ample space for the log, often mirrored onto two disks at the same time

Why store data in a dbms26 l.jpg
Why Store Data in a DBMS?

  • Benefits

    • Transactions (concurrent data access, recovery from system crashes)

    • High-level abstractions for data access, manipulation, and administration

    • Data integrity and security

    • Performance and scalability

Data independence l.jpg
Data Independence

Applications should be insulated from how data is structured and stored. Thus the DBMS needs to provide high-level abstractions to applications!

View 1

View 2

View 3

Conceptual Schema

Physical Schema

Data model l.jpg
Data Model

  • A data model is a collection of concepts for describing data.

  • Examples:

    • ER model (used for conceptual modeling)

    • Relational model, object-oriented model, object-relational model (actually implemented in current DBMS)

The relational data model l.jpg
The Relational Data Model

A relational database is a set of relations. Turing Award (Nobel Price in CS) for Codd in 1980 for his work on the relational model

  • Example relation:Customers(cid: integer, name: string, byear: integer, state: string)

The relational model terminology l.jpg
The Relational Model: Terminology

  • Relation instance and schema

  • Field (column)

  • Record or tuple (row)

  • Cardinality

Customer relation contd l.jpg
Customer Relation (Contd.)

  • In your enterprise, you are more likely to have a schema similar to the following:Customers(cid, identifier, nameType, salutation, firstName, middleNames, lastName, culturalGreetingStyle, gender, customerType, degrees, ethnicity, companyName, departmentName, jobTitle, primaryPhone, primaryFax, email, website, building, floor, mailstop, addressType, streetNumber, streetName, streetDirection, POBox, city, state, zipCode, region, country, assembledAddressBlock, currency, maritalStatus, bYear, profession)

Product relation l.jpg
Product Relation

  • Relation schema:Products(pid: integer, pname: string, price: float, category: string)

  • Relation instance:

Transaction relation l.jpg

Relation schema:Transactions( tid: integer, tdate: date, cid: integer, pid: integer)

Relation instance:

Transaction Relation

Slide34 l.jpg

  • Any enterprise has an abundance of different relations in its DBMS. Good management of this meta-data is crucial:

    • Documentation

    • Evolution

    • Assignment of responsibilities

    • Security

  • (ERP packages usually create several thousand relations)

The object oriented data model l.jpg
The Object-Oriented Data Model

  • Richer data model. Goal: Bridge impedance mismatch between programming languages and the database system.

  • Example components of the data model: Relationships between objects directly as pointers.

  • Result: Can store abstract data types directly in the DBMS

    • Pictures

    • Geographic coordinates

    • Movies

    • CAD objects

Object oriented dbms l.jpg
Object-Oriented DBMS

  • Advantages: Engineering applications (CAD and CAM and CASE computer aided software engineering), multimedia applications.

  • Disadvantages:

    • Technology not as mature as relational DMBS

    • Not suitable for decision support, weak security

    • Vendors are much smaller companies and their financial stability is questionable.

Object oriented dbms contd l.jpg
Object-Oriented DBMS (Contd.)


  • Gemstone (

  • Objectivity (

  • Object Design (

  • POET (

  • Versant (


  • OMDG: Object Database Management Group(

  • OMG: Object Management Group (

Object relational dbms l.jpg
Object-Relational DBMS

  • Mixture between the object-oriented and the object-relational data model

    • Combines ease of querying with ability to store abstract data types

    • Conceptually, the relational model, but every field

  • All major relational vendors are currently extending their relational DBMS to the object-relational model

Query languages l.jpg
Query Languages

We need a high-level language to describe and manipulate the data


  • Precise semantics

  • Easy integration into applications written in C++/Java/Visual Basic/etc.

  • Easy to learn

  • DBMS needs to be able to efficiently evaluate queries written in the language

Relational query languages l.jpg
Relational Query Languages

  • The relational model supports simple, powerful querying of data.

    • Precise semantics for relational queries

    • Efficient execution of queries by the DBMS

    • Independent of physical storage

Sql structured query language l.jpg
SQL: Structured Query Language

  • Developed by IBM (System R) in the 1970s

  • ANSI standard since 1986:

    • SQL-86

    • SQL-89 (minor revision)

    • SQL-92 (major revision, current standard)

    • SQL-99 (major extensions)

Why store data in a dbms46 l.jpg
Why Store Data in a DBMS?

  • Benefits

    • Transactions (concurrent data access, recovery from system crashes)

    • High-level abstractions for data access, manipulation, and administration

    • Data integrity and security

    • Performance and scalability

Integrity constraints l.jpg
Integrity Constraints

  • Integrity Constraints (ICs): Condition that must be true for any instance of the database.

    • ICs are specified when schema is defined.

    • ICs are checked when relations are modified.

    • A legal instance of a relation is one that satisfies all specified ICs.

    • DBMS should only allow legal instances.

  • Example: Domain constraints.

Primary key constraints l.jpg
Primary Key Constraints

  • A set of fields is a superkey for a relation if no two distinct tuples can have same values in all key fields.

  • A set of fields is a key if the set is a superkey, and none of its subsets is a superkey.

  • Example:

    • {cid, name} is a superkey for Customers

    • {cid} is a key for Customers

  • Where do primary key constraints come from?

Primary key constraints contd l.jpg
Primary Key Constraints (Contd.)

  • Can there be more than one key for a relation?

  • What is the maximum number of superkeys for a relation?

  • What is the primary key of the Products relation? How about the Transactions relation?

Foreign keys referential integrity l.jpg
Foreign Keys, Referential Integrity

  • Foreign key : Set of fields in one relation that is refers to a unique tuple in another relation. (The foreign key must be a superkey of the second relation.)

  • Example: The field cid in the Transactions relation is a foreign key referring to Customers.

  • If all foreign key constraints are enforced, we say that referential integrity is achieved.

    • No dangling references.

    • Compare to links in HTML.

Foreign keys example l.jpg
Foreign Keys: Example

  • The pid field of the Transactions relation refers to the cid field of the Customer relation.

Enforcing referential integrity l.jpg
Enforcing Referential Integrity

  • What should be done if a Transaction tuple with a non-existent Customer id is inserted? (Reject it!)

  • What should be done if a Customer tuple is deleted?

    • Also delete all Transaction tuples that refer to it.

    • Disallow deletion of a Customer tuple that has associated Transactions.

    • Set cid in Transactions tuples to a default or special cid.

  • SQL supports all three choices

Where do ics come from l.jpg
Where do ICs Come From?

  • ICs are based upon the semantics of the real-world enterprise that is being described in the database relations.

  • We can check a database instance to see if an IC is violated, but we can NEVER infer that an IC is true by looking at an instance.

    • An IC is a statement about all possible instances!

    • From example, we know state cannot be a key, but the assertion that cid is a key is given to us.

  • Key and foreign key ICs are very common; a DBMS supports more general ICs.

Security l.jpg

  • Secrecy: Users should not be able to see things they are not supposed to.

    • E.g., A student can’t see other students’ grades.

  • Integrity: Users should not be able to modify things they are not supposed to.

    • E.g., Only instructors can assign grades.

  • Availability: Users should be able to see and modify things they are allowed to.

Discretionary access control l.jpg
Discretionary Access Control

  • Based on the concept of access rights or privileges for objects (tables and views), and mechanisms for giving users privileges (and revoking privileges).

  • Creator of a table or a view automatically gets all privileges on it.

    • DMBS keeps track of who subsequently gains and loses privileges, and ensures that only requests from users who have the necessary privileges (at the time the request is issued) are allowed.

  • Users can grant and revoke privileges

Role based authorization l.jpg
Role-Based Authorization

  • In SQL-92, privileges are actually assigned to authorization ids, which can denote a single user or a group of users.

  • In SQL:1999 (and in many current systems), privileges are assigned to roles.

    • Roles can then be granted to users and to other roles.

    • Reflects how real organizations work.

    • Illustrates how standards often catch up with “de facto” standards embodied in popular systems.

Security is important l.jpg
Security Is Important

Financial estimate of database losses, by activity in 1998

(ENT/CSI/FBI Computer Crime Survey, 5/1999)

Activity Loss in million $

Theft of Proprietary Information 28.51

System Penetration by Outsiders 13.39

Financial Fraud 11.24

Unauthorized Insider Access 50.57

Laptop Theft 0.16

Total 103.87

Why store data in a dbms58 l.jpg
Why Store Data in a DBMS?

  • Benefits

    • Transactions (concurrent data access, recovery from system crashes)

    • High-level abstractions for data access, manipulation, and administration

    • Data integrity and security

    • Performance and scalability

Why parallel access to data l.jpg
Why Parallel Access To Data?

At 10 MB/s

1.2 days to scan

1,000 x parallel

1.5 minute to scan.

1 Terabyte


1 Terabyte

10 MB/s


Divide a big problem

into many smaller ones

to be solved in parallel.

Parallel dbms intro l.jpg
Parallel DBMS: Intro

  • Parallelism is natural to DBMS processing

    • Pipeline parallelism: many machines each doing one step in a multi-step process.

    • Partition parallelism: many machines doing the same thing to different pieces of data.

    • Both are natural in DBMS!



















outputs split N ways, inputs merge M ways

Dbms the success story l.jpg
DBMS: The || Success Story

  • DBMSs are the most (only?) successful application of parallelism.

    • Every major DBMS vendor has some || server

    • Workstation manufacturers now depend on || DB server sales.

  • Reasons for success:

    • Bulk-processing (= partition ||-ism).

    • Natural pipelining.

    • Inexpensive hardware can do the trick!

    • Users/app-programmers don’t need to think in ||

Some terminology l.jpg
Some || Terminology


  • Speed-Up

    • More resources means proportionally less time for given amount of data.

  • Scale-Up

    • If resources increased in proportion to increase in data size, time is constant.



degree of ||-ism



(response time)

degree of ||-ism

Architecture issue shared what l.jpg

Shared Memory


Shared Nothing


Shared Disk






Architecture Issue: Shared What?

Hard to program

Cheap to build

Easy to scaleup

Easy to program

Expensive to build

Difficult to scaleup


Compaq, Teradata, SP2


Distributed database systems l.jpg
Distributed Database Systems

  • Data is stored at several sites, each managed by a DBMS that can run independently.

  • Distributed data independence: Users should not have to know where data is located (extends physical and logical data independence principles).

  • Distributed transaction atomicity: Users should be able to write transactions accessing multiple sites just like local transactions.

Types of distributed databases l.jpg
Types of Distributed Databases

  • Homogeneous: Every site runs same type of DBMS.

  • Heterogeneous: Different sites run different DBMSs (different RDBMSs or even non-relational DBMSs).





Distributed dbms architectures l.jpg
Distributed DBMS Architectures


  • Client-Server



Client ships query

to single site. All query

processing at server.

- Thin vs. fat clients.

- Set-oriented


client side caching.





  • Collaborating-Server


Query can span multiple




Dbms and performance l.jpg
DBMS and Performance

  • Efficient implementation of all database operations

  • Indexes: Auxiliary structures that allow fast access to the portion of data that a query is about

  • Smart buffer management

  • Query optimization: Finds the best way to execute a query

  • Automatic query parallelization

Performance tips l.jpg
Performance Tips

  • Bad connection management is the number one scalability problem (can make two order of magnitude difference)

  • Reuse once-executed queries as much as possible

  • Physical performance database tuning is crucial for good performance

    • Performance tuning is an art and a science

    • Recent trend: DBMS include tuning wizards for automatic performance tuning

  • Include space for indexes during space planning

Summary of dbms benefits l.jpg
Summary Of DBMS Benefits

  • Transactions

    • ACID properties, concurrency control, recovery

  • High-level abstractions for data access

    • Data models

  • Data integrity and security

    • Key constraints, foreign key constraints, access control

  • Performance and scalability

    • Parallel DBMS, distributed DBMS, performance tuning

The future of data exchange markup l.jpg
The Future of Data Exchange: Markup

From HTML to XML

Markup Languages: HTML

  • Simple markup language

  • Text is annotated with language commands called tags, usually consisting of a start tag and an end tag

Html example book listing l.jpg
HTML Example: Book Listing



<UL><LI>Author: Milan Kundera</LI?

<LI>Title: Identity</LI>

<LI>Published: 1998</LI>



<UL><LI>Author: Richard Feynman</LI>

<LI>Title: The Character of Physical Law</LI>



Beyond html xml l.jpg
Beyond HTML: XML

  • Extensible Markup Language (XML): “Extensible HTML”

  • Confluence of SGML and HTML: The power of SGML with the simplicity of HTML

  • Allows definition of new markup languages, called document type declarations (DTDs)

Xml language constructs l.jpg
XML: Language Constructs

  • Elements

    • Main structural building blocks of XML

    • Start and end tag

    • Must be properly nested

  • Element can have attributes that provide additional information about the element

  • Entities: like macros, represent common text.

  • Comments

  • Document type declarations (DTDs)

Booklist example in xml l.jpg
Booklist Example in XML

<?XML version=“1.0” standalone=“yes”?>



<BOOK genre=“Fiction”>






<BOOK genre=“Science” format=“Hardcover”>




<TITLE>The Character of Physical Law</TITLE>


Xml dtds l.jpg

  • A DTD is a set of rules that defines the elements, attributes, and entities that are allowed in the document.

  • An XML document is well-formed if it does not have an associated DTD but it is properly nested.

  • An XML document is valid if it has a DTD and the document follows the rules in the DTD.

An example dtd l.jpg
An Example DTD









<!ATTLIST BOOK genre (Science|Fiction) #REQUIRED>

<!ATTLIST BOOK format (Paperback|Hardcover) “Paperback”>


Domain specific dtds l.jpg
Domain-Specific DTDs

  • Development of standardized DTDs for specialized domains enables data exchange between heterogeneous sources

  • Example: Mathematical Markup Language (MathML)

    • Encodes mathematical material on the web

    • In HTML: <IMG SRC=“xysquare.gif” ALT=“(x+y)^2”>

    • In MathML: <apply> <power/> <apply> <plus/> <ci>x</ci> <ci>y</ci> </apply> <cn>2</cn> </apply>

Xml the future l.jpg
XML: The Future

  • Seamless integration of heterogeneous B2B data sources and business processes

  • XML-specified services, discovered by autonomous software agents

  • Industry-specific DTDs that simplify data exchange

  • “Only XML”-industry revenue forecasts:1999: $31 millions, 2001: $93 millions(Interactive Week, 4/1999)

Summary of lecture one l.jpg
Summary Of Lecture One

  • Database systems are powerful, but also complex pieces of software

    • Transactions (concurrent data access, recovery from system crashes)

    • High-level abstractions for data access, manipulation, and administration

    • Data integrity and security

    • Performance and scalability

  • XML is the Future of Data Exchange

In the second lecture l.jpg
In The Second Lecture

  • Database design

  • A short introduction to SQL

  • Transaction processing versus decision support: On to the data warehouse!