The Software Infrastructure for Electronic Commerce

1. The Software Infrastructurefor Electronic Commerce Databases and Data Mining Lecture 1: A Manager’s View of Database Technology Johannes Gehrke johannes@cs.cornell.edu http://www.cs.cornell.edu/johannes

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

3. Goals of the DBMS Lectures (Contd.) • Gain technical understanding to feel confident to ask questions. • Learn to ask the right questions.

4. The Big Picture WWW SiteVisitor Internal User INTRANET,VPN THE WEB InternalWeb Server MainMemory Cache Public Web Server DataWarehouseApplicationServer BusinessTransactionServer DBMS

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

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

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

8. What Happens If You Click On A Link? www.company.com 1 Company web serverwww.company.com Johannes Gehrke 2 Banner web serverwww.banners.com 3 Additional log serverwww.mycookies.com Hidden link 4 SERVER SIDE CLIENT SIDE

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

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

11. Cookies • 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 www.google.com): PREFID=3415aaaf73b7bfe3,TM=956724506|google.com/|0|261887833632111634|2662722800|29339450|*(name=value|domain|secure|expiration date|expiration time|last used date|last used time)

12. Cookies (Contd.) • A cookie is always associated with a specific domain (amazon.com, bn.com, preferences.com, doubleclick.net, cornell.edu) • 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

13. Cookies (Contd.) • Applications of cookies: • Shopping carts • “Log in once” (example: New York Times) • Personalization (amazon.com: Welcome back, Johannes) • General tracking of user behavior • User privacy • Other personalization/tracking techniques: Hidden fields in html pages, unique page names • Online demonstration

14. 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).

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

16. Transactions • 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

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

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

19. Example Transaction: ATM You withdraw money from the ATM machine • Atomicity • Consistency • Isolation • Durability Commit versus Abort? What are reasons for commit or abort?

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

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

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

23. Recovery • 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

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

25. Tips • 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

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

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

28. 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)

29. 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)

30. The Relational Model: Terminology • Relation instance and schema • Field (column) • Record or tuple (row) • Cardinality

31. 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)

32. Product Relation • Relation schema:Products(pid: integer, pname: string, price: float, category: string) • Relation instance:

33. Relation schema:Transactions( tid: integer, tdate: date, cid: integer, pid: integer) Relation instance: Transaction Relation

34. TIPS • 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)

35. The Relational DBMS Market

36. The Relational DBMS Market (Contd.)

37. The Relational DBMS Market (Contd.)

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

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

40. Object-Oriented DBMS (Contd.) Vendors: • Gemstone (www.gemstone.com) • Objectivity (www.objy.com) • Object Design (www.odi.com) • POET (www.poet.com) • Versant (www.versant.com) Organizations: • OMDG: Object Database Management Group(www.omdg.org) • OMG: Object Management Group (www.omg.org)

41. The OO DBMS Market

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

43. Query Languages We need a high-level language to describe and manipulate the data Requirements: • 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

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

45. 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)

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

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

48. 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?

49. 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?

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