CMPS 180 - Database Systems - Winter Quarter 2002
2:00 to 3:45 PM, Tuesday and Thursday, Kresge Classroom 327
Labs: See TA Office Hours below

INSTRUCTOR:

Arthur M. Keller
Office: Baskin Engineering 153a
Mailbox: Baskin Engineering 237; Do not leave assignments or projects in mailbox.
Office Telephone: (831) 459-1485
Office Hours: 4:30 to 5:30pm Tuesdays and by appointment
Email addresses: ark@soe.ucsc.edu
My UCSC Web Page: http://www.cse.ucsc.edu/~ark

TEACHING ASSISTANT:

Ye Bao, yebao@cse.ucsc.edu, TA
Office hours: Monday, 6:30-8:30pm; Tuesday, 12n-2pm; Friday, 12n-1pm; all in trailer 15.

Jay Garcia, jgarcia@cats.ucsc.edu, Grader/Tutor
Office hours: Wednesday, 7-9pm; Thursday, 8-10pm; all in Baskin Engineering 105.

John Rodrigues, johnrod@cats.ucsc.edu, Grader/Tutor
Office hours: Thursday, 12:15-2pm and 6-8pm; Friday, 11am-1pm; all in Baskin Engineering 105.

Students are welcome to use the labs during other hours. The programming projects will be done using PostgreSQL on a dedicated computer shared with one other class. Students will connect to this dedicated computer using SSH from either a lab workstation or directly from their own computer. Macintosh SSH clients are available from www.macssh.com. SSH for the PC and some other computers is available from www.ssh.com.

NEWSGROUP:

The newsgroup ucsc.class.cmps180 will contain selected course announcements. Be sure to review this webpage often as well.

OBJECTIVES OF THE COURSE:

The course deals with the use of database management systems (DBMSs). Most commercial computer applications involve the use of a DBMS to store information. A bank ATM (automated teller machine) has access to your balance stored in a database. When you use a credit card, information about your card and each transaction is stored in a database. The state Department of Motor Vehicles keeps track of your drivers license and your car in databases. This course will cover the design of database systems, important database theory, SQL, programming and relational databases, and logical, object-oriented, object-relational and XML databases. The course will also involve a multi-part project using PostgreSQL, an SQL database, and the web.

Specifically this course will address:

  1. The background and history of database management systems
  2. The fundamentals of using a database management systems
  3. Industry standards used for database management systems
  4. Theoretical background of the relational model
  5. Queries and Updates
  6. Transactions and Security
  7. Logical, object-oriented, object-relational, semi-structured and XML database systems.
  8. Data warehouses and data mining.

COURSE PREREQUISITES:

CMPS 101 is a prerequisite to this course and sophomore standing is strongly recommended.

TEXTBOOK:

Required: Database Systems: The Complete Book, by Garcia-Molina, Ullman, and Widom (first edition), Prentice Hall, 0-13-031995-3, 2002. You may download the first two chapters if you are uncertain about taking the course. You may download Chapter 1 in Postscript or PDF and Chapter 2 in Postscript or PDF. Unless otherwise specified, all reading assignments will be from this textbook. Errata can be found here.

Lecture notes will be handed out only at the first class. Lecture notes for the entire quarter will be collected and available from the Bay Tree Bookstore by the second class. Alternatively, you may download it from the web. Note that updates will be made to these lecture notes, and those will be noted on this webpage.

Optional: Students may find it helpful to have one of these books as a reference for the SQL language.

A Guide to the SQL Standard: A User's Guide to the Standard Database Language SQL, (fourth edition), by C.J. Date and Hugh Darwen, Addison-Wesley, 2000.

SQL: 1999 - Understanding Relational Language Components, (first edition), by Melton and Simon, Morgan Kaufmann.

In addition, students may find it helpful to have a PostgreSQL reference manual. PostgreSQL: Introduction and Concepts, by Bruce Momjian, Addison-Wesley, 2001. This book is also available from the web, although many people prefer the convenience of a printed and bound copy.

Changes Compared to Previous Classes

This course offering of CMPS180 is very similar to the offering during Fall 2001 by the same instructor. There differences between the Winter 2002 and the Fall 2001 offerings include:

  1. Additional materials on logic databases, data warehousing and data mining will be covered. Other topics have been slightly rearranged.
  2. The project will be done on a new computer with two processors and more memory and disk space, but the computer will be shared with another class.
  3. The project deadlines are a lot less likely to slip.
  4. Updated and extended documentation will be available on the projects.
  5. The entire quarter's lecture notes will be available in advance instead of handed out at each class period.
The differences between this offering of CMPS180 and prior offerings include:

  1. All assignments, including the project, will be done on an individual basis.
  2. The project will be done in several parts and will use an SQL database. PostgreSQL will be available from the labs.
  3. The grading structure of the course is different.

CLASS SCHEDULE:

Note: This syllabus is subject to change.

  1. Jan. 3 (TH) Intro, Entity-Relationship Model.
    Read Chapter 1 and Sections 2.1-2.2. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  2. Jan. 8 (T) Weak Entity Sets, Entity-Relationship Design.
    Read Sections 2.3-2.4. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  3. Jan. 10 (TH) Relational Model, Functional Dependencies.
    Read Sections 3.1-3.5. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  4. Jan. 15 (T) Normal Forms, Multivalued Dependencies.
    Read Sections 3.6-3.7. Assignment 1 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  5. Jan. 17 (TH) Relational Algebra.
    Read Chapter 5. Project Part 1 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  6. Jan. 22 (T) SQL Queries.
    Read Sections 6.1-6.2. Assignment 2 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  7. Jan. 24 (TH) Subqueries, Grouping and Aggregation.
    Read Sections 6.3-6.4. Project Part 2 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  8. Jan. 29 (T) Modifications, Schemas, Views.
    Read Sections 6.5-6.7. Assignment 3 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  9. Jan. 31 (TH) Constraints.
    Read Sections 7.1-7.3, 7.4.1. Project Part 3 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  10. Feb. 5 (T) Triggers, PL/SQL.
    Read Sections 7.4, 8.2. Assignment 4 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  11. Feb. 7 (TH) PL/SQL, Embedded SQL, CLI, JDBC.
    Read Sections 8.1, 8.3-8.5. Project Part 4 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  12. Feb. 12 (T) Advising Day. No class.

  13. Feb. 14 (TH) Midterm. (14% of course grade)
    Covers material through Feb. 7 (TH) lecture and readings (Chapters 1-3, 5-7, 8.1-8.5).

  14. Feb. 19 (T) Object-Relational Systems.
    Read Sections 4.5, 9.4-9.5. Assignment 5 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  15. Feb. 21 (TH) Transactions, Authorization.
    Read Sections 8.6-8.7. Project Part 5 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  16. Feb. 26 (T) Datalog.
    Read Sections 10.1-10.2. Assignment 6 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  17. Feb. 28 (TH) Datalog and SQL Recursion, ODL.
    Read Sections 10.3-10.4, 4.1-4.4. Project Part 6 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  18. Mar. 5 (T) More ODL, OQL.
    Read Sections 9.1. Assignment 7 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  19. Mar. 7 (TH)More OQL.
    Read Sections 9.2-9.3. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  20. Mar. 12 (T) Semistructured Data, XML, XQuery.
    Read Sections 4.6-4.7, Notes. Assignment 8 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  21. Mar. 14 (TH) Data Warehouses, Data Mining.
    Read Notes. Project Part 7 due. Slides in Powerpoint. Slides in Postscript. Slides in Adobe Acrobat.

  22. Mar. 16 (Sa) Final Exam. (35% of course grade)

GRADE STRUCTURE:

Assignments16% (2% each)
Project35% (5% each)
Midterm Exam14%
Final Exam35%

Note: All written assignments must be prepared using a word processor. Drawings may be hand-drawn if legible.

Assignments (16% of course grade, 2% each)

Watch this space for assignments and solutions.

  1. Assignment 1 due Jan. 15 (T)
  2. Assignment 2 due Jan. 22 (T)
  3. Assignment 3 due Jan. 29 (T)
  4. Assignment 4 due Feb. 5 (T)
  5. Assignment 5 due Feb. 19 (T)
  6. Assignment 6 due Feb. 26 (T)
  7. Assignment 7 due Mar. 5 (T)
  8. Assignment 8 due Mar. 12 (T)

Project (35% of course grade, 5% each)

  1. Project Part 1 due Jan. 17 (TH) Choose a project and design an E/R diagram.
  2. Project Part 2 due Jan. 24 (TH) Relational design.
  3. Project Part 3 due Jan. 31 (TH) Create database in PostgreSQL.
  4. Project Part 4 due Feb. 7 (TH) Queries, updates, and indexes.
  5. Project Part 5 due Feb. 21 (TH) Embedded SQL.
  6. Project Part 6 due Feb. 28 (TH) Views, constraints, and triggers.
  7. Project Part 7 due Mar. 14 (TH) Entire database application accessed via the web.

Examinations: (49% of course grade)

The midterm will be based on all material covered through Thursday, Feb. 7. The final exam will be comprehensive and cover all of the material addressing during the entire quarter.

Exams should be taken when they are scheduled. Make-up exams will be available only if the student has received permission from the instructor to take a make-up exam before the exam is given to the rest of the class and a really, really good reason is required. Early exams will not be given; please don't ask.

DUE DATES:

All written assignments are due at the beginning of class on the dates indicated. The first two project parts are due in class on the dates indicated. The remaining 5 project parts are programming projects and are turned in online. They are due at midnight at the end of the date indicated. You may hand in at most two written assignments at the beginning of the class period following the due date (the late deadline) without penalty. Additional late assignments will have a 50% penalty. No written assignments will be accepted more than one class period late.

You may hand in at most two project parts up to 48 hours late (the late deadline) without penalty. Additional late project parts will have a 50% penalty. No project parts will be accepted more than 48 hours late.

Assignments and project parts submitted after by the late deadline will not be accepted and will receive a grade of zero. Absence from class does not excuse late papers or penalties. Assignments will typically be available on Tuesdays and due one week later. Solutions will be released after the "late deadline." Projects will typically be due on Thursdays.

ACADEMIC DISHONESTY:

The basic presumption is that the work you do is your own. Occasionally, especially when working problem sets or writing programs (but never on exams!), it may be necessary to ask someone for help. You are permitted to do so, provided you meet the following two conditions.

  1. You acknowledge the help on the work you hand in.

  2. You understand the work that you hand in, so that you could explain the reasoning behind the parts of the work done for you by another.

Any other assistance by another person constitutes academic dishonesty and will be treated as such.

We shall not deduct credit for small amounts of acknowledged assistance. Even working as a team on one of several problems in a problem set may not hurt your grade, as long as all members of the group acknowledge their collaboration. Such shared interest can be beneficial to all concerned. We do reserve the right to give less than full credit in circumstances where it appears that there has been large-scale division of labor, and you are not getting as much learning out of the assignment as you should. However, as long as you acknowledge your sources, you cannot get into academic dishonesty trouble.

If you have any questions about what this policy means, please discuss the matter with the instructor now.

Any confirmed academic dishonesty including but not limited to copying papers or cheating on exams, will constitute a failure of the ethics standards of this class and will result in a no-pass or failing grade. You are encouraged to read campus policies regarding academic integrity.

We shall ask everyone to acknowledge that they have read the above material on the class signup sheet.


Arthur M. Keller, UC Santa Cruz, Computer Science Dept., ark@soe.ucsc.edu