database-driven websitesonline.aoi.edu.au/documents/1360543346ppt2.pdf · • why use a web...
TRANSCRIPT
Database-Driven Websites
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 DOB
976234 Johnson Jeremy 2LT 324342344 12-Mar-81
976235 Bird Larry GS09 233534343 30-Dec-58
976236 Helms Michelle SGT 786373798 19-Feb-84
976237 Gold Jonny SPC 233636777 18-Jun-77
976238 Young Sara PFC 457839291 22-Sep-86
976239 Johansen Hans MAJ 343099894 1-Jul-66
976240 White Barry CPT 333225555 27-Oct-75Re
co
rds
Fields
Employee
Employee Department
Class
Belon
gs to
Take
s
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 Dynamic
MODERN
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
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
Web Template 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 with ‘Web
Capabilities’ Web Programs with ‘Database Capabilities’
Complexity Fairly Simple Complex
Cost Inexpensive / Free
More Costly: Requires additional Softw are (Oracle;
SQL Server…)
Learning-Curve
Little to None (use same
product)
Increased - Requires different programming
capabilities (VB; ASP...)
Compatibility Inherent
More Complex: Require technologies to connect
different databases (ODBC, SQL...)
Programs Combo Prog: MS Access
Web 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 application
1. 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 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
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
Program DB Solutions Why 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 Solutions Why NOT to program?
• Time
• Cost
• Complexity
• Short Web-Technology Life-cycle
• If it’s not broke, don’t fix it
Threats and challenges related to 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!!!
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&
Questions…
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
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 routing between 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