Database-Driven Websites

1. Database-DrivenWebsites Nate Byrnes

2. Agenda • Definitions • Why Use a Web Database • Designing a Database • Understanding Web Database Technology • Comparing the Tools • Databases with Web Capabilities • HTML Editors with DB Capabilities • Web DB Application Servers • Programming Web Database Solutions • Security • Show and Tell • IA – Do/Don’t(s) • Conclusion

3. Table Collection of RECORDS (rows) & FIELDS (columns) that hold data to define an entity Database (DB): collection of information organized into interrelated tables of data and specifications of data objects Database-driven Web site: Web site that uses a database to search, browse, collect, manipulate and display information Flat File: data files that contain records with no structured relationships Structured Query Language (SQL): is an industry-standard language used for manipulation of data in a relational database SELECT, INSERT, JOIN, UPDATE. SELECT * FROM EmployeesORDER BY LastName Entity A single object about which data can be stored the "subject" of a table Index a database feature used for locating data quickly within a table. Example: Last Name Schema: collection of meta-data that describes the relations in a database. Layout/blueprint outlining the way data is organized into tables Entity-Relationship Diagram Belongs to Employee M 1 Department M Takes M Class Definitions http://databases.about.com/od/administration/a/glossary.htm

4. Why a DB Driven Website? • The Web is a great medium for delivering information. • Databases are the perfect medium for managing information. • Flexibility • Data consistency • Ease of maintenance • Browser independence Ashenfelter, J. P. (1998). Choosing a database for your website. New York:     Wiley. Retrieved October 22, 2007, from NetLibrary database:     http://www.netlibrary.com.ezproxy.lib.utexas.edu/urlapi.asp?action=summary&v=1&bookid=26152 http://www.archetype-it.com/english/view.asp?AutoId=29&

5. OLD MODERN Differences Static vs Dynamic

6. Directories Libraries Surveys Content management Portals Internal databases Online Catalogs Shopping Cart & E-Commerce Systems User Logins Transaction and Online Ordering Systems Email Mailing List Newsletters Form Information Aggregation Examples www.butterflydatabase.com http://www.techsoup.org/learningcenter/databases/page4799.cfm

7. DBA/WebMaster Combo = DataMaster? • web developer • fluent HTML • graphic design • scripting languages • network protocols • database construction and maintenance • do not need to be an expert • understand enough • facilitate communication and management of web database projects.

8. Web Application + DB = Dynamic Page Web ApplicationServer DB

9. WebTemplate DB Dynamic Web Page + = (Row/Record)

10. Ways to Use DBs on the Web • dynamic publishing • Information on the web pages changes automatically • Stock price changes • As buyers add new products… • information transactions • Moving discrete chunks of information between a client and a business • Forms, etc. • data storage and analysis • Static/changed rarely • Resource for analysis or historical purposes • Completed orders • Accounts paid • Closing stock prices • Images in a media archive • Address books Ashenfelter, J. P. (1998). Choosing a database for your website. New York:     Wiley. Retrieved October 22, 2007, from NetLibrary database:     http://www.netlibrary.com.ezproxy.lib.utexas.edu/urlapi.asp?action=summary&v=1&bookid=26152

11. server-based processing of databases web server handles most: data processing application logic delivers results to the web browser client in HTML-formatted web pages work is accomplished by programmatic means whether by using a higher-level scripting language or by programming in traditional computer languages. common features of server-side Web tools Proprietary tag-based format. Integrate with existing HTML web page elements. special prefix delimits the custom tags from standard HTML tags. Tags replaced w results Specific file extensions. The web page files have a specific filename extension that marks them for special processing by the server. (CFM; ASPX…) common features of server-side Web tools (cont’d) Traditional programming structures. designed for developing applications, same logic used in traditional programming is implemented in the server-side language Examples loops; if/then/else and goto structures. Simplified access to server applications and files. Traditional applications interact with files and directories on the server, as well as other applications or function libraries. Server-side web database tools typically make it easier to use files on the server, process email, and call external programs residing on the server. State management for web sessions. Web is that it is a stateless system no foolproof way to track users/data across multiple pages Web applications - sessions. Easier cookie & state tracking development of full-fledged web applications Examples: ASP; Cold Fusion; C#; Java Web DB Application Servers

12. Comparison

13. Building a database application • GOAL • Define the goal and purpose of the • INCLUSIONS • What to include • HOW • How it will work • PRESENT • Present to users & explain • REFINE • Use feedback to refine • REPEAT • Steps 4–5 until you reach agreement. • FINALIZE • Design-documents • time lines/milestones • sign off Westman, S. R. (2006, January 1). Creating Database-Backed Library Web Pages :Using Open Source Tools. ALA Editions. Retrieved October 22, 2007, from     Univ of Texas Libraries: Library Catalog database:     http://catalog.lib.utexas.edu.ezproxy.lib.utexas.edu/search/     X?SEARCH=web+database&searchscope=25&m=z&m=g&m=k&m=p&l=eng&Da=&Db=&p=&SORT=D

14. Establishing a Process 1. Who gets what tasks. 2. Build applications one step at a time. 3. Test and debug as you go 4. Review code periodically, assuring code is following programming standards 5. Version Control 6. Test the application fully Implementing Standards Quality Assurance Documentation Debugging Development Procedures Westman, S. R. (2006, January 1). Creating Database-Backed Library Web Pages :Using Open Source Tools. ALA Editions. Retrieved October 22, 2007, from     Univ of Texas Libraries: Library Catalog database:     http://catalog.lib.utexas.edu.ezproxy.lib.utexas.edu/search/     X?SEARCH=web+database&searchscope=25&m=z&m=g&m=k&m=p&l=eng&Da=&Db=&p=&SORT=D

15. Limitations of Proprietary Web Database Applications Servers flexibility for development purposes, but have limitations. proprietary algorithms and techniques cannot be tweaked to improve performance, stability, security, or scalability. Limited to certain computing environments Web database applications developed from scratch Can be modified to improve performance, stability, security, or scalability Customized for existing computing-environment CGI programming and Java can conceivably run on any web server on any platform Control No compromising (like with prepackaged solutions) i.e. High-End Business Systems – Fidelity.com Program DB SolutionsWhy program?

16. Program DB SolutionsWhy NOT to program? • Time • Cost • Complexity • Short Web-Technology Life-cycle • If it’s not broke, don’t fix it

17. Threats and challenges relatedto security in Web Services • Maintaining security while routing between multiple Web Services • Confidentiality, Integrity, Authentication, Non-repudiation • Unauthorized access • Authentication, Authorization • Parameter manipulation/Malicious input • Availability, Integrity • Network eavesdropping and message replay • Confidentiality, Integrity, Authentication, Non-repudiation • Denial of Service • Availability • Bypassing of firewalls • Confidentiality, Integrity, Authentication

18. Show and Tell • My Webspace • My Zoho

19. Do Liberally-Estimate The Work Involved. Making A Website Is Easy Linking To A Database = More Complex DB Skills - Prerequisite Learning Curve Is Steep Be Good At HTML Be Willing To Put In A Lot Of Time If Not, Hire A Professional Do Use Appropriate Technologies. Access vs. SQL Do Understand The Implications Of A Database-driven Site. Increased Load On Your Webserver Server-side include Do Look Out For Packaged Solutions That Do What You Want. Cheaper Ie:Shopping Carts Before You Buy Meets Needs Scalable Ensure You Have Skill-set Necessary Do Invest In Proper Data Analysis Prior Poorly Created Sites: Difficult To Work With And Maintain Poor Performance Data Inconsistencies Inflexibility Do Check The Qualifications Designer Graphic/Web Skills ≠ Database Skills Ensure Solid Previous Experience Technologies Involved Development Don't Be The Guinea Pig Common Government Problem Don't Forget Murphy's Law! If Something Can Go Wrong, It Will Backups Test, Test, Test Ensure Error-handling IA - Do’s & Don’ts Get it done PROPERLY, the FIRST-TIME!!!

20. References • Ashenfelter, J. P. (1998). Choosing a database for your website. New York:     Wiley. Retrieved October 22, 2007, from NetLibrary database:     http://www.netlibrary.com.ezproxy.lib.utexas.edu/urlapi.asp?action=summary&v=1&bookid=26152 • Chapple, M. (n.d.). Database Glossary. In About: Databases. Retrieved October     21, 2007, from http://databases.about.com/od/administration/a/glossary.htm • Colley, A. (2006, January 31). Sunbeam polishes its e-image. The Austrailian: IT     Broadsheet Edition, p. 2. Retrieved October 21, 2007, from LexisNexis     database: http://www.lexisnexis.com.ezproxy.lib.utexas.edu/us/lnacademic/     search/homesubmitForm.do • Gianni, A. (2002, April 8). Database-Driven Web Sites. In Techsoup Learning     Center: Databases . Retrieved October 21, 2007, from     http://www.techsoup.org/learningcenter/databases/page4799.cfm • Westman, S. R. (2006, January 1). Creating Database-Backed Library Web Pages :     Using Open Source Tools. ALA Editions. Retrieved October 22, 2007, from     Univ of Texas Libraries: Library Catalog database:     http://catalog.lib.utexas.edu.ezproxy.lib.utexas.edu/search/     X?SEARCH=web+database&searchscope=25&m=z&m=g&m=k&m=p&l=eng&Da=&Db=&p=&SORT=D • Yuill, V. (2002). Databases: not just for big boys. In Archetype-IT: Articles.     Retrieved October 20, 2007, from Archetype IT Ltd Web site:     http://www.archetype-it.com/english/view.asp?AutoId=29& • Yuill, V. (2002). Decoding database lingo. In Archetype-IT: Articles. Retrieved     October 20, 2007, from Archetype IT Ltd Web site:     http://www.archetype-it.com/english/view.asp?AutoId=31&        Decoding database lingo        by Veronica Yuill  • Yuill, V. (2002). The Dos and Don'ts of database-driven websites. In     Architype-IT: Articles. Retrieved October 20, 2007, from Archetype IT     Ltd Web site: http://www.archetype-it.com/english/view.asp?AutoId=30& Yuill, V. (2002). 5 essential tools you'll need for your database-driven site.     In Architype-IT: Articles. Retrieved October 20, 2007, from Archetype IT     Ltd Web site: http://www.archetype-it.com/english/view.asp?AutoId=32&

21. Questions… • ?

22. Understanding Web Database Technology • The Web Side • Web Clients • Web Servers • The Database Side • Database Queries: What Is SQL? • Database Servers • Putting It All Together: Web Application Architecture

23. Purpose: What Is It Designed to Do? Extensions to Existing Database Tools HTML Editors with Database Capabilities Web Database Application Servers Programmatic Web Database Tools Technology: How Are the Features Implemented? Ease of Learning Ease of Use Robustness Scalability Compatibility Security Extensibility Performance Reusability/Modularity Support: What Do I Need to Implement Those Features? Portability Cost ISP Support Evaluation: How does it work in the real-world? Comparing the Tools

24. Security • Sensitive Information • Public Search-ability • High-Assurance • Confidentiality • Integrity • Availability • Authentication • Authorization • Non-Repudiation

25. IA Focus? • Website architecture is an approach to the design and planning of websites which, like architecture itself involves technical, aesthetic and functional criteria. • the user and on user requirements • particular attention • web content • business plan • Usability • interaction design • information architecture • web design

26. Maintaining security while routingbetween multiple Web Services • Traditional security techniques, such as SSL, are designed to protect communication between two points, i.e. security context 1 • Traditional security techniques can not handle end-to-end security, i.e. security context 2 • Traditional security techniques work at the session layer while SOAP works at the application layer • A SOAP message has to be decrypted at the intermediary, thereby threatening confidentiality, integrity and authentication which all are related to authorization and non-repudiation Holgersson, J., & Söderström, E. (September 2005). Web Service Security–Vulnerabilities and Threats in the Context of WS-Security [Data file].     Retrieved October 23, 2007, from University of Skoevde, Sweden Web site:     http://siit2005.dreamhosters.com/presentations/S3-Stds-Impl/     0509-SIIT-S3-J.Holgersson.pdf