database-driven websites

Database-DrivenWebsites

Nate Byrnes

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

Definitions 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 Employees

ORDER 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

http://databases.about.com/od/administration/a/glossary.htm

ID LastNameFirst Name Rank SSN DOB976234 Johnson Jeremy 2LT 324342344 12-Mar-81976235 Bird Larry GS09 233534343 30-Dec-58976236 Helms Michelle SGT 786373798 19-Feb-84976237 Gold Jonny SPC 233636777 18-Jun-77976238 Young Sara PFC 457839291 22-Sep-86976239 Johansen Hans MAJ 343099894 1-Jul-66976240 White Barry CPT 333225555 27-Oct-75R

ecor

ds

FieldsEmployee

Employee Department

Class

Belongs to

Takes

M

M

M

1

Entity-Relationship Diagram

Why a DB Driven Website?

1. The Web is a great medium for delivering information.

2. Databases are the perfect medium for managing information.

Flexibility Data consistency Ease of maintenance Browser independence

http://www.archetype-it.com/english/view.asp?AutoId=29&

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

OLD

Differences

Static vs DynamicStatic vs DynamicMODER

N

Examples

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

http://www.techsoup.org/learningcenter/databases/page4799.cfm

www.butterflydatabase.com

http://www.butterflydatabase.com/

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.

Web Application + DB = Dynamic Page

DB

Web Application

Server

WebTemplate

DB

Dynamic Web Page

+

=

(Row/Record)

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

Web DB Application Servers

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

Comparison

Databases w ith ‘Web Capabilities’ Web Programs with ‘Database Capabilities’

Complexity Fairly Simple Complex

Cost Inexpensive / FreeMore Costly: Requires additional Softw are (Oracle; SQL Server…)

Learning-CurveLittle to None (use same product)

Increased - Requires different programming capabilities (VB; ASP...)

Compatibility InherentMore Complex: Require technologies to connect dif ferent databases (ODBC, SQL...)

Programs Combo Prog: MS AccessWeb Design Prog: Frontpage; ASP; etc & DB Prog: Oracle; SQL; etc

Scalability Less More

Things to Consider if You Have Pre-Existing DB

Building a database application1. GOAL

Define the goal and purpose of the

2. INCLUSIONS What to include

3. HOW How it will work

4. PRESENT Present to users & explain

5. REFINE Use feedback to refine

6. REPEAT Steps 4–5 until you reach

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

Development Procedures Establishing a Process

1. Who gets what tasks.2. Build applications one step

at a time.3. Test and debug as you go4. Review code periodically,

assuring code is following programming standards

5. Version Control6. Test the application fully

Implementing Standards Quality Assurance Documentation Debugging


Program DB SolutionsWhy program?

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 NOT to program?

TimeCostComplexityShort Web-Technology Life-cycleIf it’s not broke, don’t fix it

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

Show and Tell

My Webspace

My Zoho

IA - Do’s & Don’ts 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

Get it done PROPERLY, the FIRST-TIME!!!Get it done PROPERLY, the FIRST-TIME!!!

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


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&

Questions…

?

Understanding Web Database Technology

The Web SideWeb ClientsWeb Servers

The Database SideDatabase Queries: What Is SQL?Database Servers

Putting It All Together: Web Application Architecture

Comparing the Tools

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?

Security

Sensitive Information Public Search-ability High-Assurance Confidentiality Integrity Availability Authentication Authorization Non-Repudiation

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

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

database-driven websites

Documents

web application db

web databasedesigning

manipulation of data

data files

data storage

way data

web capabilitieshtml

web pages changes