Business Analysis ITEC-630 Fall 2009

1. Business AnalysisITEC-630 Fall 2009 Additional Database Topics Professor J. Alberto Espinosa

2. Agenda • Client-server computing and database servers • Connecting databases to the web • Other advanced database topics: • Database administration • Transactions • Concurrency • Distributed databases • Data warehouses

3. Client Server Computing and Database Servers

4. Client-Server Computing • A key technological development in the 90’s • A form of “distributed computing” • Most predominant computing architecture today • Software application (i.e., processing) is split into tasks • These tasks are distributed among computers • Depending where it is more efficient to do the processing

5. Clients and Servers Clients • Request specialized services from servers, and • Perform other tasks for users (e.g., screen displays) Servers • Acknowledge service requests from clients, and • Provide requested services (i.e., tasks, processes) • Via responses to clients • Servers and clients connect via networks

6. Client-Server Computing DatabaseClient OtherClient Web Server Request (e.g., SQL query) Service Request(e.g., a web page) Network Response(e.g., query results) Response(e.g., a web page) OtherServer DatabaseServer Browser

7. Examples of Servers • A server can be hardware, software or both • File Server  central file storage, process file requests(ex. Novell’s NetWare, Windows NT) • Database Server back-end DBMS functions (ex. MS SQL Server, Oracle Server, Lotus Notes Server) • Web Server store and fetch web files on request(ex. Apache, Microsoft IIS) • Print Server print job queuing for central printers • Mail Server routes mail to users and other mail servers

8. Examples of Clients • A client can be hardware, software or both • Networked PCs  request files and other services from file servers (Windows 2000, XP) • Database Clients  request records from database server, process data locally, screen formatting, etc. (Lotus Notes client, MS Access) • Web Browsers request web files from web servers, translate HTML code into formatted screen displays(Internet Explorer, Netscape) • Mail Client Send/retrieve mail to/from mail servers, organize and display user mail(Outlook Express; Lotus Notes mail client)

9. Generic Client-Server Architecture Request Application Software (Client Portion) Application Software (Server Portion) Response Client Communication Software Server Communication Software Communication Protocols Client Operating System Server Operating System Hardware Platform Hardware Platform Network Client Server

10. Ex.: “Thin Client” or “Fat Server” model Most of the processing is done by the server Thin Client Fat Server • Example: • Web Server-Browser Applications • Easy to deploy applications • Great for electronic commerce • Easy to support and upgrade applications for distributed use Presentation Software Application Software DBMS Client Communication Software Server Communication Software Client Operating System Server Operating System Hardware Platform Hardware Platform Network

11. Ex.: “Fat Client” or “Thin Server” modelMost of the processing is done by the client Thin Server Fat Client • Example: • File Servers (Novell’s NetWare—your G drive, Windows NT) – file servers do very little – e.g., giving you access to shared drives and folders Presentation Software Application Software Server Communication Software Client Communication Software Server Operating System(incl. file management) Client Operating System Hardware Platform Hardware Platform Network

12. Examples of “Very Thin” Servers: Embeddable Web Servers

13. Example: Client-Server Database Management Systems (DBMS) (fat client) Presentation Software Database Application Request Front-EndDatabase Client SW Back-EndDatabase Server SW Response Client Communication Software Server Communication Software Databases Client Operating System Server Operating System Hardware Platform Hardware Platform Network Client Computer Server Computer

14. Ex.: Web Client-Server Client Server Web pages (HTML and other files) Browser Web Server HTTP HTTP TCP/IP TCP/IP Client Communication Software Server Communication Software Client Operating System Server Operating System Hardware Platform Hardware Platform Network

15. Ex.: Web Client-Server + Database Server (thin client) HTML Response Server HTML Form Client Web pages (HTML and other files) Web Server SQL Queries Browser DBMS Server HTTP HTTP Databases TCP/IP TCP/IP Client Communication Software Server Communication Software Client Operating System Server Operating System Hardware Platform Hardware Platform Network

16. Connecting Databases with Web Pages

17. Dynamic Web Pages:Connecting Web Pages to Databases Request (ex. get a price quote, place an order) Response (ex. query results with HTML-formatted product price or order confirmation notice)

18. Service Request Response:DynamicallyFormattedHTML Pagew/Results Client / Server Computing:BrowserClient / Web + Database Servers= Dynamic Web Pages WebServer Database Query String Client Query Results Results Network Database Server(usually runs in the same computer as the web server) ClickSubmit Browser Server

19. Setting Up Your Own Site With Dynamic Web Capabilities Steps: • Register your own domain name (e.g., my domain is www.Jibe4Fun.com) – there are hundreds of domain registration services ($20 to $40 per year to keep your domain name active) (through a service like http://DomainName.com) • Contract web hosting services with a company to hold your web pages – there are hundreds of web hosting services ranging from ($100 per year for a few MB of storage to highly priced commercial-strength e-commerce services (through a service like http://www.Alentus.com) • Map your domain name to your web hosting service (5 minutes) • Design, normalize and populate your database(s) • Design and develop your HTML files and related scripts • Upload your HTML files, scripts and databases to your assigned web space with your web hosting service

20. Dynamic Web Pages:Scripts

21. HTTP and Static HTML HTTP = a document fetching protocol: • User clicks on URL with HTTP protocol • Client requests connection to server, server connects • Browser requests HTML page to web site • Server finds/sends HTML page to client “AS IS” • Client’s browser interprets and displays HTML doc • Server disconnects from client HTML is static: text (info) and tags (formatting), ex.: <FONT SIZE=2><BOLD>Hello!! </BOLD><U>there</U></FONT> Displays as:Hello!!there

22. Open connection and find HTML docfile.html Send HTML doc and close connection Static HTML via HTTP Web Server Microsoft Internet Information Server (IIS) Apache Requestconnectionto server and file.html Client Browser Internet Explorer Netscape Navigator

23. Static HTML:HTTP Shortcomings • Corporate information is dynamic • As corporate information changes, • Database contents change too • Web pages need to change too • By hand? Or, do we link to databases? • How to customize displays for different users?

24. How to make web pages Dynamic? 2 generic solutions (workarounds) to static HTML: • Client-side scripting • Scripts that are processed by the browser in the local machine 2.Server-side scripting • Scripts that are processed by the web server

25. Client-Side Scripting • Script commands embedded in HTML file • Browsers need capability to process scripts • Processing is done by browserAFTERpage is fetched from server • Useful for interactive and visual effects • Browsermust support scripting language • Most popular: JavaScript, VB Script

26. Client-Side ScriptingEmbedding Client-Side Scripts in HTML HTML lines <SCRIPT LANGUAGE = “JavaScript”> script lines </SCRIPT> More HTML lines <SCRIPT LANGUAGE = “Perl”> script lines </SCRIPT> More HTML lines …………

27. Example 1

28. Example 2 • See: http://faculty.vassar.edu/lowry/kappa.html

29. Example 3 Other examples: http://auapps.american.edu/~alberto/images/BouncingDots.html http://auapps.american.edu/~alberto/images/BouncingHearts.html

30. Server-Side Scripting • Script commands embedded in HTML file • The server must have capability to process scripts • Processing is done by web server BEFORE page is sent to browser • Useful to customize pages based on data stored on the server (databases, images, etc.) • And for centralized processing (at the server) • Web sever must support the scripting language • For example: • Microsoft’s Active Server Pages (ASP) • Which is a web scripting environment • It runs on Microsoft IIS (Internet Info Server) Web Servers • Supports VB Script or JScript (MS version of JavaScript) • Other scripting languages • PHP: Like ASP, Open Source for Apache servers • Perl: used with CGI scripts (Unix servers)

31. Server-Side Scripting with Microsoft’s ASP • Embedded scripts in HTML page HTML code (i.e., tags and text) <%‘ Everything after <% is an ASP script ‘ Note: use quote for comments ASP script code (using VB Script as default or other as declared) ………..………. ……………….... %>‘ ASP script ends with %> More HTML code <% more ASP %> Etc.

32. How ASP Works: • Web file needs to be named .asp (instead of .html) • User clicks on URL with .asp file • Browser sends request for .asp file to server • Web server notices file extension .asp and • Loads a program (DLL) called ASP.DLL • Which processes this and other .asp files • Server generates a “new” web file • Contains all original HTML stuff • Plus processing results from ASP scripts • These are dynamically formatted w/HTML tags • Server sends the “new” web file to the browser • Not the“original”ASP file!!

33. Process Scripts SQL Query (if any) file.asp asp.dll HTML Databases Query Results(recordset) Request file.html Response HTML doc Generated On-the-Fly HTML doc Fetched (+ Client-Side scripts, if any) Request file.asp  file.asp file.asp = file.html file.html How ASP Works Microsoft’s Web Server(ASP + MS Access or SQL Server) Client Browser Internet Explorer Netscape Navigator

34. HTML <H3>Welcome to my page</H3> <H2>Here is my product list</H2> <P>Thank you very much for inquiring about our products Dynamically generated HTML lines by ASP <P> <B>Product Price</B> <HR> <P>Hammer ……... $8.50 <P>Pliers ……….… $7.79 <P>Screwdriver ..… $4.50 <P>Power Drill ….. $49.99 <P>Chainsaw …… $95.95 <P>Wrench ……….. $6.50 HTML Dynamic HTML with ASP ASP file on web server (file.asp) HTML file sent to browser (file.asp) <H3>Welcome to my page</H3> <H2>Here is my product list</H2> <% ‘Start ASP script Open a database connection SQL queries to database Copy results to a record set Display records one at a time Close database connection %> ‘End ASP script <P>Thank you very much for inquiring about our products

35. Common Uses of ASP with Databases • Register a client (add record in database) • List products & services (query database) • Place orders (add records in database)[Illustrations: Database DesignShopping CartOrder Entry] • Track order status (query database) • Tech support (query a knowledge database) • Fill out a survey (add records in database)

36. See: http://www.jibe4fun.com/scripts/orders/

37. ASP HTML Both Example: ASP (Query) Script

38. Example: Query Results Sent to Browser(HTML dynamically generated by previous ASP script) <IMG SRC="music22.gif"><B>Alberto's Music Instruments, Inc.<p> <TABLE BORDER="0"><B>Customer List</B> <TR><TH>ClientID</TH> <TH>Client Name</TH> <TH>Shipping Address</TH> <TH>Telephone</TH> </TR> <TR><TD>josee</TD> <TD>Alberto Espinosa</TD> <TD>Schenley Park, GSIA Building, #20</TD> <TD>412-268-3681<BR></TD> </TR> <TR><TD>sandy</TD> <TD>Sandra Slaughter</TD> <TD>5000 Forbes Avenue, Pittsburgh PA 15213</TD> <TD>412-268-3681<BR></TD> </TR> etc. </TABLE></BODY></HTML>

39. See: http://www.jibe4fun.com/scripts/orders/Customer_List.asp

40. Using Forms with ASP, HTML and Databases • Capture data from user using HTML forms • Feed form data to an ASP script • Which is what the “Submit” button does • HTML forms contain data items with field names • Which are passed to ASP scripts for processing • Often used to embed an SQL command • To query a database (product list, etc.) • Or to insert records in a database (orders, etc.)

41. On submit,Pass on to Form Object Example: HTML Form (Data Input)Doesn’t have to be ASP, can be plain HTML <B>Customer Registration</B><P> <FORM ACTION= “http://softrade-11.gsia.cmu.edu/data/customerSubmit.asp” METHOD=“POST”> <TABLE> <TR><TD>Please enter a customer ID (4 to 16 characters)</TD> <TD><INPUT TYPE=“text” SIZE=“35” NAME="CustomerID"> </TD></TR> <TR><TD>Please enter your name</TD> <TD><INPUT TYPE=“text” SIZE=“35” NAME="CustName"> </TD></TR> etc. </TABLE> <INPUT TYPE="submit" VALUE=“Submit”></TD></TR> </TABLE> </FORM>

42. See: http://www.jibe4fun.com/scripts/orders/Customer_Input.htmlhttp://www.jibe4fun.com/scripts/orders/

43. Request From Form Object Add record in database Example: ASP Processing Data from Forms <!-- customerSubmit.asp -->

44. ASP Resources • A periodic publication on ASP. It contains articles with ASP issues as well as some tips and tricks: http://www.asptoday.com • Nice introductory book with examples and a web site where you can download running code: Beginning Active Server Pages 3.0 http://www.wrox.com/books/0764543636.shtml • A more advanced book. Probably one of the most useful reference books to have for people who are doing serious ASP coding: Professional ASP.NET 1.0. http://www.wrox.com/books/0764543962.shtml • A useful book if you need more help with Visual Basic Script: VB Script Programmer's Reference. http://www.wrox.com/books/0764543679.shtml • A good and concise reference of ASP objects for those who already know ASP fairly well: ASP in a Nutshell, Weissinger and Petrusha, O'Reilly series. http://www.amazon.com/exec/obidos/ASIN/1565924908/

45. Other Related Technologies Server-Side Processing: • JSP (Java Server Pages): Sun's version of ASP (*.jsp files) • ColdFusion (*.cfm files), Dreamweaver (Macromedia) http://www.macromedia.com/ • (Like ASP but) Open Source – PHP (*.php files) • Lotus Notes & Domino IBM, http://lotus.com/home.nsf/welcome/domino

46. Other Related Technologies (cont'd.) Extensible Markup Language (XML) • Standard for inter and intra-organizational data exchange • Very important for B2B e-commerce applications • Like HTML, but used to fetch data, not documents • Each tag is defined data, not formats, ex.: <LastName>Espinosa</LastName> <FirstName>Alberto</FirstName> <U>josee</U> (not underline, just a variable called U) • Data defined in "Document Type Definition" files (DTD) • Data itself in XML file [Examples: booksnutrition] • Need and XML processor to process XML data(a browser is an HTML processor)

47. AnotherXMLExampleCan you draw a table that contains the following data? <RECORD>1</RECORD> <FIRSTNAME>Alberto</FIRSTNAME> <LASTNAME>Espinosa</LASTNAME> <EMAIL>alberto@american.edu</EMAIL> <PROFESSION>Professor</PROFESSION> <SCHOOL>American University</SCHOOL> <DEPARTMENT>Information Technology</DEPARTMENT> <REMARKS>Looks tired, needs vacation</REMARKS> <RECORD>2</RECORD> <FIRSTNAME>Gwanhoo</FIRSTNAME> <LASTNAME>Lee</LASTNAME> <EMAIL>glee@american.edu</EMAIL> <PROFESSION>Professor</PROFESSION> <SCHOOL>American University</SCHOOL> <DEPARTMENT>Information Technology</DEPARTMENT> <REMARKS>He teaches the other 2 MIS sections</REMARKS> <RECORD>3</RECORD> <FIRSTNAME>Jill</FIRSTNAME> <LASTNAME>Klein</LASTNAME> <EMAIL>klein@american.edu</EMAIL> <PROFESSION>Professor</PROFESSION> <SCHOOL>American University</SCHOOL> <DEPARTMENT>Information Technology</DEPARTMENT> <REMARKS>She teaches the MBA MIS course</REMARKS>

48. Same data in database table format

49. Business to Business E-Commerce Example using XML DBMS(e.g., MS SQL Server) INSERT query Supplier XML Document (e.g., Purchase Order) XML Processor Internet XML Processor XML Document (e.g., Purchase Order) Query results Buyer DBMS(e.g., Oracle) SELECT query

50. AdvancedDatabaseTopics