Databases and Web Services

Course topics: database services, database design (ER, UML), SQL, relational design theory, transaction management, web applications, XML.

This course introduces into (relational) database systems in theory and practice, with special emphasis on Web-based applications. In the accompanying project work, design and implementation of database and Web service components will be addressed, culminating in a sample database-enabled Web service based on the LAMP open source package. This course is recommended for all students specializing in computer science.

The course requires basic knowledge about algebraic expressions and laws, basic data structures like trees, object-oriented concepts, as well as - for the lab work - HTML and Linux.

Not a CS major? Then contact me upon semester start! Maybe you want to get specific support in the programming aspects.

Course mailing list

If you attend the course, make sure you are subscribed to that list to not miss important information!

Lecture slides

...plus the talk given by Keith Hare, Convenor of the ISO SQL Working group.

Assignments

Assignments will be published weekly (either here or via the course mailing list), they consist of both standalone tasks and workpackages of your projects. Assignments have to be handed in one week later by email as PDF or plain ASCII file to the teaching assistant. Late submissions will be disregarded; the only (and rare) exceptions to this are (i) registrar's exemption and (ii) case-by-case arrangement with me in advance with a Good Reason.

Several tasks require you to submit an individual answer, even though it may be part of the project. In such cases you may want to develop some solution within your team first, and then create your answer sheet by adapting the approach to your individual situation.

For the diagramming tasks, you may want to use dia.

Should you have questions on the outcome of an assignment, please contact the Teaching Assistants - they are happy to assist and explain!

For the rasdaman exercises you find instructions and exercises.

Exams

sample midterm exam, including solutions

Projects

During the course you will have to implement your own Web service, based on Linux, Apache, Python, MySQL ("LAMP").

To this end, you are expected to team up in groups of 2 - 3 early in the semester.

Use the ClamV environment (how to set up my own Web pages directory). Note that this excludes setting up your own environment, eg, on some Windows server! And, no, we tentatively do not use advanced tools like content management systems, because we want to learn about the internals of that technology.

I will suggest some topics, but it is highly appreciated if you come up with your own idea, maybe even for some service that will have its sustained life afterwards. It just needs to fulfil some criteria to make it manageable (not trivial, not too complex), so contact me early with your idea!

The main evaluation criteria for the website you will develop are as follows (in no particular order; authoritative list on course slide deck 00):

bug-free
good engineering (project and code documentation, coding quality, ...)
user-friendliness
complexity (in absolute terms and in comparison to other teams' work)
own understanding (assessed through a final, individual handover)

Literature

Course reference:

Raghu Ramakrishnan, Johannes Gehrke: Database management systems. McGraw-Hill, 2003.
DBWA course database textbook.
James Lee, Brent Ware: Open source Web development with LAMP: using Linux, Apache, MySQL, Perl, and PHP. Addison-Wesley, 2003.
DBWA course LAMP textbook.
Hugh E. Williams, David Lane:
your friend in your PHP life.

Jacobs library has a stock of

and

books.

Further database material:

Jeffrey Ullman, Jennifer Widom: A first course in database systems. Prentice Hall, 2002.
another - very good - database text book; introductory
Hector Garcia-Molina, Jeffrey Ullman, Jennifer Widom: Database system implementation. Prentice Hall, 2000.
another database textbook; advanced material going beyond this course
George Reese, Randy Jay Yarger, Tim King: Managing and using MySQL. O'Reilly, 2002.
specifically for MySQL
Stefan Stanczyk: Theory and practice of relational databases. Taylor & Francis, 2001.
relational foundations.
Codd, E.F. (June 1970). A Relational Model of Data for Large Shared Data Banks. Communications of the ACM 13 (No. 6): pp. 377-387. Association for Computing Machinery. Retrieved on 2007-06-09.
the relational model was born with this paper
Chamberlin, Donald D.; Boyce, Raymond F. (1974). SEQUEL: A Structured English Query Language. Proceedings of the 1974 ACM SIGFIDET Workshop on Data Description, Access and Control: pp. 249-264. Association for Computing Machinery. Retrieved on 2007-06-09.
same for SQL

Database journals and conferences

Databases and the Web:

Bussler, Christoph; Tannen, Val; Fundulaki, Irini (eds.): Semantic Web and Databases. Second International Workshop, SWDB 2004, Toronto, Canada, August 29-30, 2004, LNCS 3372, Springer, 2004
Chaudhri, A.; Jeckle, M.; Rahm, E.; Unland, R. (eds.): Web, Web-Services, and Database Systems. NODe 2002 Web and Database-Related Workshops, Erfurt, Germany, October 7-10, 2002, LNCS 2593, Springer, 2002

Internet/Web:

Extra links

For the Unified Modeling Language (UML) there is many good tutorials available on the Web, for example Borland's tutorial, Sparx Systems' tutorial by with a particular part on Database Modeling in UML. There is also a list of UML tutorials maintained by uml.org itself.
Meantime there is also a host of UML tools available - such as Rational Rose and Enterprise Architect (EA) by Sparx Systems, or Omondo's free UML extension to Eclipse - collected in lists like this one.

Some LAMP links: The LAMP page by O'Reilly. LAMP, as you know, stands for..., well, at first: Linux. Compare this and that site! Sometimes helpful: TLPD - The Linux Documentation Project. Then, about the rest: Apache and mySQL and PHP Perl.
Here some food for those who want to practise their German: Linux für alle and LAMP at eFactory.

For PHP, there is some discussion of Apache configuration aspects, also touching security issues. This input sanitizing script might save your life one day.

Tuning of databases is something only marginally addressed in lecture, albeit a large (and fascinating) area, and of immense practical importance. Just two tools here: Practical query analysis produces HTML reports on slowest queries, most frequent queries, queries by type (select/insert/update/delete), and all that sort of thing for PostgreSQL and MySQL database logs. Using a template postgresql.conf and test data, pgAutotune will run through a process of testing different settings on your PostgreSQL server and determine which settings have the best performance. For additional material, see e.g. Software Engineering for Internat Applications.

For XML there is Altova's widely used XML spy; they also offer an XQuery tool. (note: the download script is broken, you need to manually ftp libxslt-1.1.13.tar.gz and libxml2-2.6.18.tar.gz from ftp://xmlsoft.org/libxslt). Books? Look, e.g., at XML.com!

Given the large variety of Web app implementation languages available it is no surprise that there is hot discussions on which platform is the best one. One line of arguments, which I share to a large extent (but not completely) is presented by two webcasts of a NASA/JPL guy (webcast1, webcast2).