Media Management Research Lab

National University of Singapore

  • Increase font size
  • Default font size
  • Decrease font size


CSCI585: Database Systems
Course Summary(Fall 2006 )
This course covers the essential concepts, principles, techniques, and mechanisms for the design, analysis, use, and implementation of computerized database systems. Key information management concepts and techniques are examined: information modeling and representation; information interfaces - access, query, and manipulation, implementation structures, and issues of distribution. The database and information management system technology examined in this course represents the state-of-the-art, including traditional approaches as well as recent research developments. By providing a balanced view of "theory" and "practice," the course should allow the student to understand, use, and build practical database and information management systems. The course is intended to provide a basic understanding of the issues and problems involved in database systems, a knowledge of currently practical techniques for satisfying the needs of such a system, and an indication of the current research approaches that are likely to provide a basis for tomorrow's solutions.
General Information
Lecture times: M 06:30 - 09:20 pm.
Location: SLH 100.
Professor: Roger Zimmermann
  • Office and Office hours:# Office: PHE 414 # Phone: (213) 740-7654 # Email: # Office Hours: M 11:00 am to 12:00 pm, or by appointment
  • Phone:(+65) 6516 7949
Announcements and FAQ

As stated in the university catalog, a passing grade in CSci485 or departmental permission is required to register for this class. Knowledge of relational databases and SQL is required.

The course involves challenging programming assignments and projects for which an understanding of and proficiency in programming with Java is required. Knowledge of JDBC is a plus.

Required Reading Materials
(Still subject to change, 05/08/2006.)

The official textbook for the class is "Fundamentals of Database Systems" by Ramez Elmarsi and Shamkant B. Navathe (4th Edition, Addison Wesley, ISBN 0-321-12226-7). The book is available at the USC Bookstore.

Additional Readings (A.R.). The papers below are required reading for all students in this class. The material covered in lectures should be considered the main definition of the scope of the course. However, the textbook and readings are important to supplement lecture material. Assignments and exams will be based on the topics presented in the lecture, and may also involve issues addressed in the textbook and readings.

Some of the documents below are password protected. The password for these files will be given out during the first lectures.

  1. Jim Gray. "Evolution of Data Management." Computer v29 n10 (October 1996):38-46.
    A local copy of the paper is available here (password protected).
  2. Jim Gray. "Database Systems: A Textbook Case of Research Paying Off." White paper, 1997.
  3. "The Lowell Database Research Self-Assessment Meeting Report." Serge Abiteboul et al., May 4-6, 2003, Lowell, Mass.
  4. B. Chandrasekaran, J. Josephson, and V. Benjamins. What are Ontologies, and Why Do We Need Them? IEEE Intelligent Systems, 14(1), 1999.
    A local copy of the paper is available here (password protected).
  5. Andrew Eisenberg, Krishna Kulkarni, Jim Melton, Jan-Eike Michels, and Fred Zemke. "SQL:2003 Has Been Published." SIGMOD Record, 33(1): 119-126, March 2004.
    A local copy of the paper is available here (password protected).
  6. Thomas Connolly, Carolyn Begg, and Anne Strachan. "Ch 17: Object Databases." Database Systems.
  7. Michael Stonebraker. "Object-Relational DBMS-The Next Wave." Informix white paper.
  8. Zhen Hua Liu. "Object-Relational Features in Informix Internet Foundation." Informix technical notes. 9.4 (Q4 1999):77-95.
  9. Extensible Markup Language (XML) 1.0 (Third Edition); W3C Recommendation 04 February 2004 (
  10. Ralf Hartmut Guting. "An Introduction to Spatial Database Systems." VLDB Journal 3(4): 357-399, 1994.
  11. Hanan Samet. "Spatial Data Structures." In Modern Database Systems: The Object Model, Interoperability, and Beyond, W. Kim, ed., Addison Wesley/ACM Press, Reading, MA, 1995, pp. 361-385.
  12. Antomn Guttman. "R-Trees: A Dynamic Index Structure for Spatial Searching." Proceedings of ACM SIGMOD, pp.47-57, 1984.
  13. Timos Sellis, Nick Roussopoulos and Christos Faloutsos. "The R+-Tree: A Dynamic Index for Multi-Dimensional Objects." Proceedings of the 13th VLDB Conference, Brighton 1987.
  14. Dimitris Papadias, Yannis Theodoridis, Timos K. Sellis and Max J. Egenhofer. "Topological Relations in the World of Minimum Bounding Rectangles: A Study with R-trees." Proceedings of SIGMOD, pp.92-103, 1995.
  15. Peter Fankhouser and Philip Wadler. XQuery Tutorial, January 2002.
  16. XQuery 1.0: An XML Query Language ( ).
  17. Alin Deutsch et al. "Querying XML Data." Bulletin of Data Engineering, v22, n3, Sep. 1999.
  18. Ali E. Dashti, Seon Ho Kim, Cyrus Shahabi, and Roger Zimmermann. "Streaming Media Server Design." Book chapters 1 & 2. Published by IMSC Press and Prentice Hall PTR, 1st Edition, March 2003, ISBN: 0-130-67038-3.
  19. Cyrus Shahabi, Roger Zimmermann, Kun Fu, and Shu-Yuen Didi Yao. "Yima: A Second Generation of Continuous Media Servers." IEEE Computer Magazine, Vol.35, No.6, Pages 56-64, June 2002.
  20. The STREAM Group, Stanford University. "STREAM: The Stanford Stream Data Manager" (short overview paper). IEEE Data Engineering Bulletin, Vol. 26 No. 1, March 2003. URL
  21. Sailesh Krishnamurthy, Sirish Chandrasekaran, Owen Cooper, Amol Deshpande, Michael J. Franklin, Joseph M. Hellerstein, Wei Hong, Samuel R. Madden, Fred Reiss, Mehul Shah. "TelegraphCQ: An Architectural Status Report." To appear in: IEEE Data Engineering Bulletin.
  22. The PostgreSQL PDF documentation file is here.
  23. The PostgreSQL documentation web pages are here.
Lecture content subject to change
2006-08-21Introduction and overview, Database fundamentals(A.R. 1 & 2)PDF, PS download
PDF, PS download
2006-08-21ER data model (review)(A.R. 3)PDF, PS download
2006-08-28Relational data model (review)(A.R. 4)PDF, PS download
2006-08-28Extended ER
Introduction to HW#1
PDF, PS download
Homework #1 PDF, PS
2006-09-04No class -- Labor Day
2006-09-11SQL:1999 (review)PDF, PS download
2006-09-11SQL:1999 (advanced)PDF , PS download
2006-09-18SQL:2003(A.R. 5)PDF, PS download
2006-09-18SQL:2003See lecture notes of previous session.
2006-09-25OODB & OO Concepts(A.R. 6)PDF, PS download
2006-09-25OR-DBMS & SQL(A.R. 8)PDF, PS download
streaming video here

Database Connectivity: JDBC & SQL
Introduction to HW#2
Wei-Shinn Ku

PDF, PS download
Homework #1 due
JDBC Example 1, 2, 3, 4, 5
Homework #2 PDF, PS
(A.R. 22, 23)
2006-10-02Web services & DatabasesPDF, PS download

Database Connectivity: JDBC & SQL;Introduction to HW#2(Wei-Shinn Ku)

PDF, PS download
Homework #1 due
JDBC Example 1, 2, 3, 4, 5
Homework #2 PDF, PS
(A.R. 22, 23)
2006-10-09Spatial Databases & GIS(A.R. 10)PDF, PS download
2006-10-09Spatial Index Structures(A.R. 11, 12, 13, 14)PDF, PS download
PDF with Animations
2006-10-16Exam 1Midterm Solutions
2006-10-23Tentativly No Class
2006-10-30XML & Schema(A.R. 9)PDF, PS download
2006-10-30XML & XQuery(A.R. 9, 15)PDF, PS download
2006-10-30XML & XQuery(A.R. 16, 17)PDF, PS download
2006-11-01Homework #2 due(11:59:59 pm)
2006-11-06Multimedia Databases(A.R. 18)PDF, PS download
2006-11-06Multimedia Databases(A.R. 19)PDF, PS download
Homework #3 PDF, PS
2006-11-20Data Stream Processing(A.R. 20, 21)PDF, PS download
2006-11-20Data Stream ProcessingPDF, PS download
2006-11-20Data Stream ProcessingSee lecture notes of previous session.
Homework #3 due
(11:59:59 pm)
Exams and Assignments
Homework Assignment 1 10%
Homework Assignment 2 20%
Homework Assignment 3 10%
Exam 130%
Exam 230%
Assignment Descriptions
Homework #1 (due on TBA)

Grader: TBA

Use the Extended ER concepts to create a conceptual schema for a geotechnical information database example application. Submission of the result is in hardcopy form in class.

Homework #1 Solution

Homework #2 (due on TBA)

Grader: TBA

Create a Java client application with a GUI user interface that communicates with the course Postgres database server via a JDBC connection. The client application should have the following functionality:

  1. Create a SQL schema (and therefore a database) in PostgreSQL of the EER diagram from HW#1 (you may use your own or the sample solution from HW#1).
  2. Load your newly created database with the sample data given in class. The sample data is in XML format and you will need to convert the data to SQL.
  3. Translate the given text queries to SQL and execute them on your database. Display the results of your queries in a text window of the client.
  4. Delete your database.

Submission of HW#2 is in electronic form via We will test it with our own data set.

Homework #3 (due on TBA)

Grader: TBA

Use the Qexo XQuery implementation to design and execute several queries in the XQuery language directly on the borehole data file in XML format.

Submission of HW#3 is in electronic form via We will test it with our own data set.

Seminal Papers in Database Research
  • Kapali P. Eswaran, Jim Gray, Raymond A. Lorie, Irving L. Traiger, The Notions of Consistency and Predicate Locks in a Database System. CACM 19(11): 624-633 (1976).
  • C. Mohan, Donald J. Haderle, Bruce G. Lindsay, Hamid Pirahesh, Peter Schwarz, ARIES: A Transaction Recovery Method Supporting Fine-Granularity Locking and Partial Rollbacks Using Write-Ahead Logging. TODS 17(1): 94-162 (1992).
  • Jim Gray, Paul R. McJones, Mike W. Blasgen, Bruce G. Lindsay, Raymond A. Lorie, Thomas G. Price, Gianfranco R. Putzolu, Irving L. Traiger, The Recovery Manager of the System R Database Manager. Computing Surveys 13(2): 223-243 (1981).
  • Peter Pin-Shan Chen, The Entity-Relationship Model--Toward a Unified View of Data. ACM Transactions on Database Systems, Vol. 1, No. 1, pp. 9-36, March 1976.
  • Selinger, Patricia G., Morton M. Astrahan, Donald D. Chamberlain, Raymond A. Lorie, Thomas G. Price, Access Path Selection in a Relational Database Management System. Proceedings of ACM-SIGMOD, May 1979.
Academic Integrity

All homework and exams must be solved and written independently, or you will be penalized for plagiarism. The USC Student Conduct Code prohibits plagiarism. All USC students are responsible for reading and following the Student Conduct Code, which appears on pp. 76-77 of the 2006-2007 SCampus.

In this course we encourage students to study together. This includes discussing general strategies to be used on individual assignments. However, all work submitted for the class is to be done individually.

Some examples of what is not allowed by the conduct code: copying all or part of someone else's work (by hand or by looking at others' files, either secretly or if shown), and submitting it as your own; giving another student in the class a copy of your assignment solution; consulting with another student during an exam. If you have questions about what is allowed, please discuss it with the instructor.

Students who violate University standards of academic integrity are subject to disciplinary sanctions, including failure in the course and suspension from the University. Since dishonesty in any form harms the individual, other students, and the University, policies on academic integrity will be strictly enforced. We expect you to familiarize yourself with the Academic Integrity guidelines found in the current SCampus.

Violations of the Student Conduct Code will be filed with the Office of Student Conduct, and appropriate sanctions will be given.

For the 2006-2007 academic year there is new Student Judicial Affairs and Community Standards web site. Its resources include two student-oriented publications in both viewable and printable forms:

  1. "Trojan Integrity Guide (A Guide to Avoiding Plagiarism)" addresses issues of paraphrasing, quotations and citations in written assignments, drawing heavily upon materials used in the university's Writing Program;
  2. "How to get an F on an A paper: A Trojan Integrity Overview (A Guide to Understanding and Avoiding Academic Dishonesty)" addresses more general issues of academic integrity, including guidelines for adhering to standards concerning examinations and unauthorized collaboration.
Related Web Sites