University of California, Berkeley

CS 186: Introduction to Database Systems

Fall 2002

                                    [Project]       [Meeting times]       [Grading]       [Schedule]      



12/16/02 Final Exam grades have been posted on glookup. The average was 65.7. Course grades are listed on glookup in the comments section.
12/16/02 HW6 grades have been posted on glookup. If you think there has been a procedural error, please contact Ryan (cs186-tc@cory) ASAP!
12/12/02 All regrade requests for HW 4 and Project V are due by Saturday 5PM. For HW4 you must submit your request in the HW box on the second floor or email Gaurav for HW4, Ryan for Project V.

Today till 5PM is your last chance to pickup graded assignments before the regrade deadline. See Ryan in 421 Soda.

12/9/02 Grading scripts for Project V are available via UNIX, go HERE for information. Project V grades are due out later tonight, and you can pick up your grading sheets tomorrow at the review session if all goes well. 
12/5/02 The final exam is closed book.  Two cheat sheets are allowed.  For more information about the Final Exam, click HERE.  Also, a practice final (last year's final) is posted in the schedule section below.
11/26/02 A review session (actually Q&A) for the final will be held Tuesday, December 10, 7-9PM in the Woz. Please bring questions, you may email any of the TAs with the question prior to the session as well.
11/26/02 If you have not yet picked old grading stuff, please see Ryan in 421 Soda to get them. This includes:
  • Project I (including regrades)
  • Project II (including regrades)
  • Project III
  • Midterms (including regrades)
11/25/02 The grades for Project-III have been posted. You can pick up your grading sheet tomorrow during class. The grading script files for Project-III are available, ~cs186/adt/gtest.php and ~cs186/adt/, via UNIX. Requests for regrading must be made by Tuesday 12/3 5PM. Make sure to run the grading script on a clean database (using 'make clean' and 'make') using the original files you submitted.
11/22/02 Homework #6 is posted  HERE.  It is due Monday, December 9 at 5PM.  Note, due to the need to hand back solutions, no assignments will be accepted after Tuesday 12/10 5pm. Homework 6 is to be done individually.
11/21/02 Due to Thanksgiving Break, Ryan's office hours next Wednesday (11/27) will be moved to Tuesday (11/26) 1-2PM 651 Soda.
11/21/02 Project V FAQ is posted HERE.
11/15/02 Project V assignment is posted HERE. It is due Tuesday, December 3 at 5PM.
11/14/02 Clarification to question 6 for project IV - For 6(a) you are not allowed to add/delete indexes, or modify the query. You can just play with the existing access methods using the set enable/disable command. If you cannot produce a better plan for some part, you should state that, and state why. Parts b) and c) allow you to make modifications to the indexes and the query respectively, so you can trick the optimizer to get a better plan.
11/12/02 In preparation of Project-V class account quotas have been increased to 80MB. Be forewarned, to complete Project-V you will need all this space! You will not have room for previous projects or other files. You will need to remove all previous PostgreSQL data files. If you wish to keep them, you must back them up appropriately. We'll post more information later this week when the project is ready, until then enjoy the extra bytes.
11/12/02 The full grading scripts for Project-II are now available, ~/cs186/fnfbc/gtest*, via UNIX. Requests for regrading must be made by Friday 5PM, and as always, any request opens the entire project open for re-grading.
11/06/02 Project IV assignment is posted HERE. It is due Thursday, November 14 at 5pm. Note that this assignment is to be done individually and the answers are to be handed in a template provided HERE.
11/03/02 A listing of group ids and logins may be found HERE. If there are concerns, please email your TA.
10/31/02 Btree Demo is posted HERE.  It is a 2MB Java Applet, so it may take a while to start up.
10/29/02 Group changes... if your group is changing in a friendly manner, please email your TA and your old group members. If you need help finding a group because of changes, again email your TA and your group members. Groups of 2 or 3 are acceptable, other sized groups should speak with the Professor. If you have concerns or issues, feel free to see the Professor. All changes should be completed by Friday 5PM.
10/29/02 There is an interesting talk tomorrow by Jennifer Widom from Stanford, 4pm, 306 Soda, on XML Databases and Data Streams.
Ryan's office hours will be shifted to 5:30-6:30PM on Wednesday
Professor Franklin's office hours will be adjusted to 11:30am-noon on Thursday 
10/24/02 Project III assignment is posted HERE.  It is due Wednesday, November 6 at 5pm.
10/24/02 Solution to MIDTERM is posted HERE.  Any regrade requests must be submitted in writing to your TA no later than Friday Nov 1 @ 5:00pm.
10/22/02 Project II presentation meeting with Gaurav and Ariel on Wednesday, Thursdays and Friday will be held in Soda 277.
If you have not signed up yet for a Project II presentation, sign up TODAY!
10/21/02 Project II presentation meeting with Ryan on Tuesday and Friday will be held in Soda 421.
10/21/02 Please be sure to sign up for a Project II presentation. You may sign up for a slot with any TA. The sign up form is outside Soda 421. Presentations begin TOMORROW! Be prepared to show your website and decision support queries. We will post room information later today.
10/16/02 Project II is due today @5PM. You may make multiple submissions, the last one will be graded (and used to calculate slip day usage). Type submit Project-II in the directory with all the files to submit, the program will prompt you to make sure everything is proper.
10/15/02 There is a slight addition to the DDL in sample solution to Project-I. You need to add UNIQUE(trans_num, txn_accnt) to the transactions table. The updated solution can be found HERE. You can also get the DDL in text format from HERE
10/14/02 Information regarding Phase II grading is posted HERE.
10/8/02 Project I was handed back in lecture. Groups that did not pick it up should find Ryan in Soda 421.
Regrading Policy: Math mistakes on the grading form can be fixed by turning the form back in. If you believe we subtracted points improperly, you may turn in both the project and grading form with a note from you describing what you think is wrong. Remember, by requesting regarding you open the entire project open to regrading (i.e. your score may go up or down). All regrading requests should be given to the TA listed on your project by Friday 5PM. Complaints regarding grading policy should be directed to the professor.
10/8/02 There is going to be an additional office hour today (tuesday) from 4:00pm to 5:00 pm in 651 Soda - Gaurav
10/7/02 Instructional support has changed the unix group of all public_html directories for class accounts to nobody and set the permissions to 710 (drwx--x---).
This allows you the owner, to enter/read/write inside the directory.
This allows the webserver (running as nobody) to enter the directory (but not read a directory listing).
This PREVENTS other users from entering/reading anything in the directory.
Coupled with .htaccess files (see HERE) this can prevent other students from peeking at your files either via unix or the web.
10/7/02 Note: The solution to practice midterm #1  has been replaced --- THE PREVIOUSLY POSTED VERSION HAD AN INCORRECT SOLUTION TO THE ER DIAGRAM. Please get the new version (it's marked with today's date at the top.) --- sorry for any confusion - MJF
10/7/02 Midterm Info: Exam is closed book.  You can bring one 8.5x11" page of notes (double-sided).  You are responsible for: 1) material covered in lecture and lecture notes; 2) questions related to Phase I and Phase II of the project; 3) associated chapters of the textbook: Ch 1-5 and ch 15 (except for section 15.8).  You should be comfortable reading and writing SQL, Relational Algebra and at least one of the Relational Calculi.


Project Phase II CORRECTIONS are posted HERE.
10/04/02 Project-I grades are online, use 'glookup' to view them. Those of you who haven't registered their instructional accounts will not be able to view the grades.
10/03/02 The sample solutions to the Project I have been posted HERE.
10/1/02 The FINAL version of Project II assignment is posted HERE. The support files have been updated, please check to make sure you have the latest versions. It is due Wednesday Oct 16 5PM.
9/25/02 REMINDER: Phase I is due Thursday @ 5PM. Please drop all assignments off in 283 Soda. Our drop box is in cabinet 2 (back of the room) in the top left corner. It is labeled CS186.
9/24/02 A DRAFT version of the Project II assignment is posted HERE.  You are free to make changes to your Phase I based on additional information in the assignment, but you are NOT REQUIRED to do so. Phase I only requires reading the Phase I assignment.
9/20/02 The group registration system is now closed. All groups have been assigned. Please email Ryan with any group related issues.
9/19/02 All students who have not registed their CS186 login should do so ASAP. If you are not already in a group, we can not assign you to a group until you have registered the account.
9/17/02 PostgreSQL documentation is posted HERE.
PHP documentation is posted HERE.
Example PHP/PostgreSQL application documentation is posted HERE.
9/17/02 Project Phase I FAQ is posted HERE.
9/13/02 Project Phase I assignment is posted HERE.  It is due Thursday Sept 26.  
9/12/02 To register your group of 3 students for the projects please go HERE. You will need the first and last names (exactly as listed on BearFacts) along with the logins for each member.
8/16/02 Prof. Franklin will not be available until the first day of class.  Please come to the first class meeting if you have questions.
8/16/02 As usual CS 186 is oversubscribed.  If you want to try to get into the class you need to follow the instructions posted  HERE .  Note that all enrollment decisions are made by the Computer Science Division, not by the professor or TAs.  Please also note that students dropping the course will not enable any new students to enroll, as the admissions have already been done assuming that a percentage of the students will drop.



The following are some links you may be interested in:
PostgreSQL documention
Mimer SQL documention and an online Mimer SQL validator to check that your SQL conforms to the standard.
Java JDBC documention


Teaching Assistants:

Meeting Times:


T,Th 9:30am -11:00am in 10 Evans







Section 1


 Tu 3-4p 

 343 LeConte

Section 2


 W 9-10a

 343 LeConte

Section 3


 W 11a-12p

 75 Evans

Section 4


 W 2-3p

 4 Evans

Section 5


 W 3-4p

105 Latimer


The newsgroup ucb.class.cs186 will be used to communicate information on the course and assignments. You should read it frequently and regularly.


We will be using Database Management Systems - 2nd Edition by Ramakrishnan and Gehrke. ISBN 0-07-232206-3, McGraw Hill, 2000.  Be sure to get the second edition as it is substantially different from the 1st.

Grading Policy:

 Notes on grading (please read):

         The percentages are subject to change as circumstances dictate.

         Since exams are the main indicators we have of individual grasp of the material, we reserve the right to adjust final letter grades based on exam performance.  In particular, students who do not achieve a passing average on the exams will not receive a passing grade in the class.

         We will be following the EECS departmental policy on Academic Honesty, so be sure you are familiar with it..  


 I On-line Bank - Database Design 9/26/02; 5pm Sample Solution 5%
 II On-line Bank - Implementation 10/16/02; 5pm   15%
III PostgreSQL - New Data Type 11/06/02; 5pm   10%

PostgreSQL - Query Processing 

11/14/02; 5pm Answer Template
Sample Solution
V PostgreSQL Internals - Buffer Manager 12/03/02; 5pm   10%
VI Homework - Transactions etc. 12/9/02; 5pm  Sample Solution 5%

This semester, we will be doing two types of projects.  First, an application- oriented project using PostgreSQL and some web development tools.  Second, we will be modifying the internals of the PostgreSQL system.  The first set of projects will involve Java and some scripting languages (TBD).  The second set of projects will be done in C.  It is assumed that you are comfortable programming in both Java and C.

To register your group of 3 students for the projects please go here. You will need the first and last names (exactly as listed on BearFacts) along with the logins for each member.

Late Policy on Homeworks

Starting with HW 1, each student is given an automatic extension of 4 calendar days. You can use the extension on any assignment(s) remaining during the semester (in increments that are rounded up to the nearest integer). For instance, you can hand in one assignment 4 days late, or each of four assignments 1 day late. For group project assignments, the slip time will be deducted from each team member's remaining slip time.

The intent is to let you schedule due dates around the due dates for other courses. When you hand in a late assignment, you must identify at the top of the assignment, (i) how late this assignment is, and (ii) how much of the total slip time you have left.

No assignment will be accepted more then 4 days late. Note, due to the need to hand out solutions, no late assignments for Homework 6 will be accepted after Tuesday 12/10 at 5pm.

After you have used up your slip time, any assignment handed in late will be marked off 25% per day. There will be no extensions granted.

Class Schedule and Lecture Notes:

1) This schedule is subject to change so check it frequently.  In particular, check for changes to due dates and guest lectures.  
2) All lecture notes are in PDF (2up = 2 slides per page; 6up = 6 per page). The date the PDF was created is also listed. Watch for updates.




Book Chapter


Lecture  Notes

Section Notes


T 8/27




2up, 6up (8/29)  

Th 8/29

The Entity Relationship Model



2up, 6up (8/29)  


T 9/3

ER Continued



2up, 6up (8/29) Ex; Sol; Notes

Th 9/5

The Relational Data Model



2up, 6up (8/29)  


T 9/10

Logical DB Design 15   2up, 6up (9/10) Ex; Sol

Th 9/12

Logical DB Design Continued 15   2up, 6up (9/10)  


T 9/17

Relational Algebra 4   2up, 6up (9/13) Ex; Sol; Notes

Th 9/19

Relational Calculus 4   2up, 6up (9/13)  


T 9/24

Intro to SQL 5   2up, 6up (9/23) Ex; Sol

Th 9/26

SQL - part 2 5 Project 1 Due 2up, 6up (9/23)  


T 10/1

SQL - part 3/Phase II Overview 5   A:2up, 6up (10/2) 
B:2up, 6up (10/1)
Ex; Sol; Notes

Th 10/3

Ph II (cont.)/ XML     2up, 6up (10/2)  


T 10/8

Object-Relational Databases - Guest Lecture: Dr. Wei Hong 25   2up, 6up (10/7)  

Th 10/10

Midterm Review   Midterm 7-9pm    1 Leconte   PracticeExam 1  ( fixed 10/7)

PracticeExam 2 


T 10/15

Storing Data - Disks and Files 7 Project 2 Due Wednesday 2up, 6up (10/14)  

Th 10/17

File Organization and Indexing 8   2up, 6up (10/14)  


T 10/22

Tree Structured Indexes 9   2up, 6up (10/21) Ex; Sol

Th 10/24

Tree-based Indexes (cont.) 9       (see previous) BTree Demo


T 10/29

Hash-based Indexes 10   2up, 6up (10/21) Ex; Sol

Th 10/31

Relational Operator Evaluation 12   2up, 6up (11/6)  


T 11/5

Operator Evaluation (continued) 12 Project 3 Due
(see previous) Ex; Sol

Th 11/7

Query Optimization 13   2up, 6up (11/6)  


T 11/12

Query Optimization (continued) 14   (see previous) Ex; Sol

Th 11/14

Q. Opt (finish)/Sensor QP Demo/ Physical DB Design & Tuning 16 Project 4 Due 2up, 6up (11/12)  


T 11/19

Transaction Management 18   2up, 6up (11/18) Ex; Sol

Th 11/21

Concurrency Control 19   2up, 6up (11/21)    


T 11/26

Concurrency Control II 19   (see previous) Ex; Sol

Th 11/28




T 12/3

Recovery 20 Project 5 Due 2up, 6up (11/21) Ex; Sol

Th 12/5

Transaction and Course Wrap-up   HW (a.k.a. Proj.) 6 Due Monday 12/9    

T 12/10

Review Session 7-9PM, Woz        


F 12/1

FINAL EXAM (8:00am!!!)

Rms 9 and 100 Lewis


    Practice Exam, Soln (fixed 12/12)