Help:COAP-3180

<pageby nominor="false" comments="false"/>

Introduction

Disclaimer: This is a resource page for my Spring 2010 I Web Databases course (COAP 3180) at Webster University, Geneva. This page does neither replace the official syllabus nor the information in the official learning management system (WorldClassroom/Blackboard). It allows students to navigate more efficiently through the materials in this wiki.

The program and materials are provisional and will be adjusted on a weekly basis.

Course level outcomes

At the completion of this course students will be able to:

1. Add a Webpage Database to an HTML page
2. Manipulate data using SQL operations
3. Publish both static and dynamic data on the Web
4. Convert text files to XML databases
5. Be able to query XML data using XQuery
6. Be able to work with relational data as XML

These outcomes defined in the syllabus can be translated to more practical outcomes:

1. Use simple Web 2.0 database services and integrate it into a webpage.
2. Manipulate data using SQL operations and create simple SQL databases in a DBMS.
3. Understand the architecture of a simple web application (HTML client / Web server / Script / Database).
4. Be able use a LAMP/WAMP software bundle and to install and to configure web applications, e.g. a CMS. This can evolve into a term project.
5. Generate a very simple web application that can connect to a database management system (DBMS) though an ODBC API.
6. Understand/recall XML basics, like converting text files and database output to XML, editing an XML file using a schema and displaying XML data.
7. Be able to query and update XML data files and XML Databases using XQuery dand XQuery Update.
8. Understand the general architecture and purpose of LDAP address and authentication servers and light-weight solutions like OpenID.

Week 1 - COAP 3180

Topics Covered

(1) Introduction

• Internet, the WWW and web databases
• Slides: http://tecfa.unige.ch/guides/te/files/webdatabases-intro.pdf (PDF)

(2) Web 2.0 vs. "traditional Internet"

• An ever increasing amount of applications are available through the Internet. Many of these applications can be shared, others are by definition "social software" (e.g. wikis, YouTube, flickr, blogs).
• Reading (for students not familiar with the "web 2.0" concept): O'Reilly Tim (2005), What is Web 2.0, better take the print version.
• Additional resources for those who want to explore this topic further: This wiki, e.g. start from the rich internet application article.

(3) Simple on-line database services

• Learn how to create and use simple tabular databases with a web 2.0 service (http://creator.zoho.com)
• Practical details are explored in class and through homework one.

Classroom activities and homework

Synopsis:

(1) Take an account with Zoho (see also below)

• http://www.zoho.com/ or directly http://creator.zoho.com
• Create a database form and view with Zoho Creator

(2) Take an account with Netvibes (see also below)

• http://netvibes.com
• Create a new page (tab)
• Import the result view from Zoho
• Export this page to the public

(3) Turn in this homework in the WorldClassroom:

• Provide the NetVibes URL
• Define the objective of this application in 2-3 sentences (including the target audience)

Homework 1

1. Think about a little database (i.e. a simple list of items that have features/properties) and that you would like to put on the web. Then make a list of properties that describe such a "item".
   • Examples: A list of CDs, a list things you want to sell, your favorite list of flowers
2. Implement a Zoho creator database form that allows to describe each item with a few properties (fields). Make sure to select the right data type for a given kind of item.
3. Add some test data to this database. Revise the database definition if needed.
4. Enter some more data (at least 5 items)
5. Make the Zoho "view" that displays the results public (gathered data becomes public on the web). You also can change the way data are displayed.
6. If you wish, you also can make the data gathering public
7. Create a public NetVibes page and import your Zoho creator widget (advanced course participants may use another environment that allows insertion of web widgets, talk to the instructor ....)
8. Add an explanation using some appropriate NetVibes "text" widget, i.e. describe to the reader what your pagecast (in particular the Zoho form) is good for.
9. In order to achieve better user experience, you also should add some useful extra information/widgets to the Netvibes page (or equivalent), i.e. the page cast should include the Zoho database plus the "what's this" (previous point) plus some useful extra information.
10. Submit your work as assignment in the Worldclassroom. Important: Urgently consult the librarian if you can't log into this Webster on-line environment. In the submission field:
    • Provide the URL
    • Define the objective of this application in 2-3 sentences (including the target audience)

Remarks:

• If you prefer to remain anonymous, do not leave Name, Firstname, professional email etc. or anything that can identify you on the public page.
• Tips that help you getting this homework done are below ...

Submission Dates

• This homework is due at start of Wednesday week 2 lesson
• Students must provide the URL (where the instructor can see the widget) in the world classroom
• Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

• Quality of Zoho database: Items should be described in a useful way with respect to your objectives and the target audience. You should use appropriate data types (but don't make it too big !)
• Quality of the Netvibes pagecast (is it usable and understandable ?)
• Worldclassroom upload (short definition of the objectives and correct URL)

Tip:

• Quality is essentially related to the idea that your applications are useful to a given range of users.
• Make sure that all required elements are present

Tips for the homework

Below is a rough outline of the procdure you should follow.

(1) Leave all the needed applications open in your browser

Make sure that you are logged to both applications.

• Netvibes with your login
• Zoho: Switch to Zoho Creator

Tip: It is best to work with 3 different web browser tabs: One for Netvibes, one for Zoho, and one for this page. If necessary, ask the instructor to explain tabbed browsing...

(2) Read the Help, in particular the Quick Start Guide for Zoho

Important starting points:

• http://help.creator.zoho.com/ - The most important thing to understand about Creator is that you will have two interfaces: One for defining the database table structure (form authoring) and another for gathering data and looking at the results (the view).
• http://tour.netvibes.com/overview.php - The most important thing to understand about NetVibes is that you must find a Netvibes widget called HTML/UWA, insert it in a pagecast and then enter the Zoho widget code through the Edit button.

(3) Make a Zoho widget public

To export a widget from Zoho:

• Click on Edit this application / Share
• Tick This application is: X public

(4) Exporting Zoho widget code To get the Zoho widget code for embedding in some other place

• Click on Access this application
• Select a view from the menu to the left
• Then use the pull-down menu More Actions->Embed in your Website
• Copy/Paste the code to another web application. E.g. the HTML Netvibes widget as explained below.

(5) Configuring Netvibes and widget import

Firstly (if this isn't already the case) create a new page in Netvibes

• Then make this Netvibes page public:
  • Click on the Edit button (in the Tab of the page)
  • Then click on "Share this Tab" / Publish on your page.
  • Make sure to write down the URL (You will have to submit it as homework)

To import a Zoho Creator application (database) into Netvibes:

• Create a HTML/UWA Widget (you can find it under "HTML" in the list of essential widgets)
• Edit the widget
  • Copy/Paste the widget code from Zoho

See also the Creating websites with online services article in this wiki. It provides an overview of some easy and free online services.

Links and teaching materials

Online software

http://creator.zoho.com/ (Zoho creator online database)

http://help.creator.zoho.com/ (Help entry point)

http://netvibes.com/ (NetVibes webtop)

http://faq.netvibes.com/ (Help entry point)

Teaching materials

Creating websites with online services

Using web widgets

Slides for the overview talk (PDF)

Week 2 - COAP 3180

Topics Covered

We will explore (concurrently) the following topics:

Web application architecture overview

• Overview of web hosting alternatives
• Application architectures / frameworks overview
• WAMP/LAMP software bundles and applications, various options
• Use of a portable WAMP architecture (Mowes)
• The three tier model

Short overview of databases

• What is a Database and how is it used in Internet applications ?
• Relational data base management systems (RDBMS) and ownership of databases
• MySQL (databases, tables and users): First contact with this RDBMS

Portalware (Content management systems / light-weight web application frameworks)

• Short overview of typical LAMP-based systems systems (and similar)
• An overview of typical installation and configuration work.

Classroom activities

Monday

(1) Mix, download and install a Mowes combo'

(a) Download the Modular Webserver System (Mowes) server

• http://www.chsoftware.net/en/mowes/mowesportable/mowes.htm (Mix and download a server).

You will need at least the following modules

• Server software

Apache 2_SE

MySQL 5_SE

PHP5

• Mission critical application software (!!)

PHPMyAdmin

• Application software

Drupal

Joomla

ModX

Wordpress

(b) Dezip the downloaded archive to drive c:\temp2

• Ask a lab assistant or the instructor if you need help with 7zip

(c) Click on Mowes.exe

• This will install the Mowes server in the directory plus install all the downloaded application software. Be patient. This may take some time.

(d) You can add other portals by downloading a new module from MOWES. Drop the file to the top-level directory and restart the mowes application.

Getting your own copy

• You may copy the whole directory to a memory stick (may take a full hour to copy). Applications will continue to work. Mowes doesn't write anything to a system directory or the registry and will adapt to a new directory structure.
• Alternatively, you can re-enact the process at home on your personal computer...

Donwlaod again from http://www.chsoftware.net/en/mowes/mowesportable/mowes.htm

(2) A tour of the Mowes WAMP system

• The instructor will show some features of the system and relate it to an introductory talk about web applications

(3) Playing with portalware

• Use http://php.opensourcecms.com/ according to instructions
• For homework, you also should consult the portalware article

(4) Configuration of portalware (Mowes)

• Mowes packages all include the same admin login and password.

Login = admin and password = password

• You may need to figure the admin URL for each application.

Wednesday

(1) Configuration of Wordpress

• Install Wordpress (if it is not alreday present in your MOWES install !). If you have to install:
  • Get the package from http://www.chsoftware.net/en/mowes/mowesportable/mowes.htm (or from the instructor)
  • Save the file into the mowes top-level directory (should look like below)

       18348 09-24 15:25 GPL.txt
         660 09-24 15:25 Licence.txt
           0 01-12 22:08 apache2
           0 01-12 22:07 mowes
      409600 09-24 15:25 mowes.exe
           0 01-12 22:08 mysql
           0 01-12 22:08 php5
                         wordpress.pck  <- newly added Mowes package
           0 01-12 22:13 www

• • (re)start the server. Wordpress will be automatically installed

• Update
  • Log in as admin (password = password)
  • Click on the update banner to automatically update Wordpress to a latest version. (also available in Tools->Upgrade)

(2) Configure wordpress

• See wordpress installation and configuration

• Change the theme

• Change the contents of the first default article, then post an article

• Remove / add links

• Add the formidable plugin (http://wordpress.org/extend/plugins/formidable/)
  • This will allow you to create forms (a bit like Zoho creator)
  • Click on the "Formidable" icon at the bottom of the left-hand "Dashboard" column in the admin interface and define a form
  • Create a page (click on pages), then add the "formidable" form inside. Use the following code to insert a form:

[ formidable id=x ]

• Add a user or two and test drive with these (using a different web browser)

(3) Installing applications that are not packaged by MOWES

• Create a database and a database user with PhpMyAdmin
• Download, dezip to mowes/www directory
• Install/configure the system according to instructions found

Students not familiar with this procedure should install a new version of wordpress.

• Make sure to dezip the system into another directory than .www/wordpress, e.g. call it .www/blog.
• Read wordpress installation and configuration

(3) Discussion about web hosting

Homework 2

Topic: High-level exploration of a portalware (CMS/CMF) / preparation of the term project

Task

Task Summary:

1. Shortly sketch out a website project for a given targent audience
2. Collect links about a portalware (not wordpress) that "may do the job" and create a resource about this select
3. Finally, try to install it on a local webserver (or a provider) and make a simple test to see if it runs

Deliverable: Produce a resource (e.g. an HTML page) that includes the five following elements:

(1) Objectives and target population of a web site that you plan to create. This website must include some dynamic elements (e.g. the possibility to add contents for specified users). Here is nice little template from the wordpress codex:

This website will be dedicated to X, Y, and Z,

and cover the topics of A, B, and C. The audience will

be __________ ________________ _______. I will be adding

posts every _____________ about ________ _______ ______________.

I am doing this because _____________ _____________ __________________.

(2) Provide some links to useful information. Create a shortlist of three CMS/CMF systems that run under PHP/MySQL. Select the most promising one and add the following links:

• Download page of the software
• A link to two respectable websites that provide comments and evaluations of this system
• Link to the installation manual
• Link to the configuration manual (if different)
• 2-3 links to other useful help sites

(3) Make a simple test. Install the system with MOWES. If the installation fails, try to explain why and skip step 4.

(4) Create at least one form of content and explain what you had to do to get it down and tell us if it was difficult.

(5) Write a short general comment about the portware you looked at. Do you think that it might be suitable for your needs ?

Important: You are not allowed to choose Wordpress (WP) since I want to look at another system, but you may choose WP for the term project ...)

You can create this resource in three ways:

• Create a webpage on your PC and upload the web page to the worldclassroom (you don't need to hand code HTML, just use a tool like Kompozer)
• Create a netvibes pagecast and submit the URL (see hw 1)
• Use your own personal web site, e.g. create a blog post and submit the URL.

This homework is a preparation for the term project. Doing it will help you get started.

Submission Dates

• This homework is due on wednesday week 3 (before lesson start)
• Students must provide an URL or upload an HTML file in the world classroom (no Word!).
• Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

see the grading form in the worldclassroom

Tips for the homework

• The easiest solution is to select a CMS/CMF system that is available as Mowes package, e.g. ModX, Typolight, Drupal, Joomla, Zikula (all CMS/CMF)
• Otherwise start from the links in the Portalware article. Do not google for finding a good CMS (too many websites you will have to sort out). Best bet is to start exploring OpensourceCMS.com. Ambitious students should try this strategy. It you help you become a CMS expert :)

Links and teaching materials

Software

• http://www.chsoftware.net/en/mowes/mowesportable/mowes.htm (Mix and download a server).
• See the portalware article for pointers regarding CMS/CMF systems

Teaching materials

• Slides: http://tecfa.unige.ch/guides/te/files/webdatabases-intro.pdf (PDF)
• See the next item for various links you may consult depending on your needs

Reading

I suggest that you have a look at the following articles. Reading is not mandatory, but may help you understand some concepts and issues.

• About Web hosting
  • web hosting service
  • Web Hosting Service (Wikipedia)
• Shared web hosting service

• About the LAMP/WAMP bundles
  • LAMP (software bundle) (Wikipedia)
  • WAMP/LAMP/MySQL (this wiki)
  • Wikipedia has extensive documentation on databases, e.g. about SQL or about the MySQL RDMS.

• About Web applications, content management frameworks and systems
  • (short) Edutechwiki articles: web application, Portalware, content management system, Web application framework
  • Wordpress installation and configuration

Week 3 - COAP 3180

Topics Covered

Portalware configuration (continued):

• discussion of homework 2 if needed

Relational databases: Introduction to SQL (part 1):

• Retrieving data
• Definition of SQL tables and use of the PhpMySQL tool
• Relational tables (principles)

Main teaching materials

• Slides used: http://tecfa.unige.ch/guides/te/files/mysql-intro.pdf
• Lecture notes (very similar to slides): SQL and MySQL tutorial

Classroom activities

Monday

• Define a table with SQL code
• Learn how to inject SQL code in MySQL Admin (a web application to administer web databases) in order to create databases and tables
• Create a simple 1-table MySQL database with the database administration tool

Play time

Warning. Be careful with quotes and double quotes.

• Quotes must be straight (same for double quotes).

 '  = Ok
 ’  = BAD !!!

... So use a real text editor and not a word processor.

(1) Demo 1 example

CREATE TABLE demo1 (
  id int(10) NOT NULL auto_increment,
  login char(10) DEFAULT '' NOT NULL,
  password char(100),
  fullname char(40) DEFAULT '' NOT NULL,
  url char(60) DEFAULT '' NOT NULL,
  food int(11) DEFAULT '0' NOT NULL,
  work int(11) DEFAULT '0' NOT NULL,
  love int(11) DEFAULT '0' NOT NULL,
  leisure int(11) DEFAULT '0' NOT NULL,
  sports int(11) DEFAULT '0' NOT NULL,
  PRIMARY KEY (id),
  KEY login (login)
);

(2) Student database with 2 tables

DROP TABLE IF EXISTS student;
DROP TABLE IF EXISTS exercise;

CREATE TABLE student (
  id int(10) NOT NULL auto_increment,
  name char(40) DEFAULT '' NOT NULL,
  first_name char(40) DEFAULT '' NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO student VALUES (NULL,'Testeur','Bill');
INSERT INTO student VALUES (NULL,'Testeur','Joe');
INSERT INTO student VALUES (NULL,'Testeuse','Sophie');

CREATE TABLE exercise (
  id int(10) NOT NULL auto_increment,
  title char(40) DEFAULT '' NOT NULL,
  student_id int(10) NOT NULL,
  comments varchar(128),
  url char(60) DEFAULT '' NOT NULL,
  PRIMARY KEY (id),
  KEY student_id (student_id)
);

INSERT INTO exercise VALUES (NULL,"Exercise 1",'1',"small comment",'http://tecfa.unige.ch/');
INSERT INTO exercise VALUES (NULL,"Exercise 2",'1',"no comment",'http://tecfa.unige.ch/');

Classroom activities Wednesday

• Continue creating a table (if needed)
• Relational databases with 2 or 3 tables (simple 1-N and N-N cases)
• Web hosting (discussion)
• The anatomy of Wordpress

Homework 3

Topic: Database definition (2-3 tables), Details to be announced on Wednesday.

Define an SQL file that will create 2-4 related tables. You can freely choose the application area, but it should somewhat relate to prior homework or the term project.

Minimal requirements:

• Minimum 2 tables and max. 4 tables. Make sure that the tables are related.
• At least 10 fields (for all tables together, e.g. 6 for table one and 4 for table 2)
• At least 1 integer and 1 varchar (for all tables)
• Each table must define a primary key
• You may look at real example and take inspiration from these under the condition that you make some modifications and that you quote the source in the comment !!

The SQL file must include some documentation on top that (1) explains the purpose of this database and of each table and that (2) provides some high-level design elements. See the SQL and MySQL tutorial for comment syntax.

Remarks and tips

This homework is not related to the term project, but it helps you preparing exams.

Before you submit your homework, make sure to test it, i.e. load it into the database, e.g. with PhPMyAdmin. If you this more than once, you must kill the old tables, use the following SQL command:

DROP TABLE IF EXISTS name_of_your_table;

E.g.

DROP TABLE IF EXISTS exercise;

To get the "2 related tables" structure right, think for example about a relationship between a person and what he can own or produce. E.g. One Author writes N books, One CD includes N titles.

An example discussed in Yarger et al. (1999:18ff) is a four tables structure: Artists - songs - CDs - Record labels. One artist can produce several songs. A CD contains several songs. A Record label publishes several CDs. A corresponding table structure would look like this:

Artist
     Artistid  primary key
     name      ....

CD 
     CDid      primary key
     title     ....
     LabelID   foreign key

Song
     Songid    primary key
     name      ....
     CDId      foreign key
     ArtistId  foreign key 

Label
     Labelid   primary key
     name      ....

Submission Dates

• This homework is due at start of wed week 4
• Students must upload the SQL file to the worldclassroom (don't forget the documentation inside this file).
• Each homework counts 10% in your global evaluation. The four best homeworks will be taken into account.

Evaluation

• See the worldclassroom

• A well documented and well designe two table design may also get an A (so you don't must go for a three our four table solution ...)

Teaching materials and further reading

Online software

• We shall use MoWes again

Teaching materials (slides and reading)

• Slides used: http://tecfa.unige.ch/guides/te/files/mysql-intro.pdf
• Lecture notes (very similar to slides): SQL and MySQL tutorial

You probably will have to complete my lecture notes with some extra reading. Pick one of the following. (Unfortunately, some of these articles are presented as multiple pages ....)

• Introduction to Structured Query Language: Version 4.11 by James Hoffman. Good bet if you like concise texts.
• Getting Started With SQL by Ted Brockwood, Web developper's journal. Good as first introduction.
• SQLCourse and SQLCourse2
• Introduction to Databases for the Web: Pt. 1 by Selena Sol, database journal (good, but too many distracting ads)
• Yarger et al. (1999). MySQL & MSQL, O'Reilly, Chapter 2 on database design (the rest is outdated).

List of all Coap 3180 pages

Weekly programs

• Help:COAP-3180/week1
• Help:COAP-3180/week2
• Help:COAP-3180/week3

• Help:COAP-3180/template (instructor only)

Most important wiki pages

• Creating websites with online services
• Using web widgets
• web application
• Portalware
• content management system
• Web application framework
• web hosting service
• Wordpress installation and configuration

Other teaching resources

• webdatabases-intro.pdf

Most important software

• Mowes