CPSC 310: Database Systems /
CPSC 603: Database Systems and Applications
Fall 2005

Announcements

• 12/11: These are topics from the first two exams to concentrate on for the final:
  • Chapter 3: functional dependencies; BCNF
  • Chapter 5: relational algebra
  • Chapter 6: SQL
  • Chapter 11: disk accesses
  • Chapter 12: representing records on disk and in main memory and going back and forth
  • Chapter 13: indexing
  • Chapter 15: algorithms for executing relational algebra operators
  • Chapter 16: optimizing the logical query plan

• 12/4: Review for final exam is now available.

• 12/1: It seems that checks are not really working in the latest version of MySQL. Tables can be created with checks in them, but database modifications that violate the checks are not causing an error message. Go ahead and do the project as stated, including capturing the executions that are supposed to cause a violation.

• 11/27: HW 12, due *Fri*, Dec 2, 9:00 AM, is now available on Gradiance.

• 11/23: In class on Wednesday, Nov 30, I will pass out three surveys, so please come with a number 2 pencil. They are: (1) the normal course evaluations, (2) course survey for ABET (accreditation), and (3) my own survey that will ask about the textbook, Gradiance, and the project. Thanks for giving your input.

• 11/23: HW 12 is postponed to Friday, Dec 2.

• 11/23: As part of the grading process for parts 4 and 5 of the project, Unil needs to meet with one representative from each group on Tuesday or Wednesday of next week. The group representative must be capable of demo'ing the project and answering questions about it. Sign-up sheet will be passed around in class, or check your neo email for times. If no one in your group can make any of these times please contact me directly (welch@cs.tamu.edu).

• 11/21: If you are a computer science (or computer engineering) major, please come to a meeting on Tuesday, November 29, 6:00 - 7:00 PM in room 124 Bright. The purpose of the meeting is to provide input to members of the Department of Computer Science's undergraduate curriculum committee regarding the undergraduate computer science degree program. In addition to verbal input, you'll be asked to fill out a written survey. Pizza will be served!

• 11/21: Participation requirements for project part 6: Each team member must be responsible for (at least one) of the three pieces (view, constraint, trigger). To even out the work load, more than one person can do some of the work on each piece if this is warranted, but one person is mainly responsible for each piece, and every team member must be the responsible party for (at least) one piece.

• 11/21: The latest version of MySQL that we have still does not support the execution of triggers. Thus part 6 of the project has been modified to no longer require the execution of your triggers. Note that you still have to write them.

• 11/17: HW 11, due Wed, Nov 23, 9:00 AM, is now available on Gradiance.

• 11/15: Hints for Exam 2: For the work-out problems, I suggest you focus on these topics:
  • calculating how much disk space is taken by various schemes to lay out relations and indexes on disk, given the tuple size, relation size, block size, etc. This is basically arithmetic.
  • Be able to "simulate" on a small example the workings of the join algorithms, i.e., be able to calculate the results.
  • Know how to push selections and projections down a logical query plan tree.
  • Know the (heuristic) rules for estimating the size of intermediate relations.

• 11/14: In case you are having trouble connecting to your database with your Java program, you must run the program from the Unix command line. Thanks to William London.

• 11/14: Peer teacher Chris Portales' office hours have changed: now they are Monday through Thursday from 4:00pm to 5:00pm in HRBB 219 (same room).

• 11/8: Review for Exam 2 is now available.

• 11/7: Here is the information you need for the JDBC programming in our environment. The template program at http://www.kitebird.com/articles/jdbc.html is helpful. You need to make the following modifications:
  • replace "testuser" with your MySQL user id
  • replace "testpass" with your MySQL password
  • replace "jdbc:mysql://localhost/test" with "jdbc:mysql://database-fall05.cs.tamu.edu/..." where ... is replaced with the name of your database

• 11/7: For part 5 of the project, every team member must do a significant amount of programming. A sample breakdown of work would be: Team member 1 writes the modules for connecting to the database, selecting, and inserting. Team member 2 writes the modules for updating. Team member 3 handles the code integration writes the modules for additional options. Be sure to include a detailed participation statement. Significant points will be taken off individually for a team member that does not contribute sufficiently to the programming.

• 11/7: Homework 10 due date has been changed to Fri, Nov 11, 9:00 AM.

• 11/2: Changes to office hours:
  • Dr. Welch's office hours on Thursday, Nov 3, are cancelled. Please email welch(AT)cs.tamu.edu to reschedule for Friday, Nov 4.
  • Unil: 10:00 AM - 1:00 PM on Monday, Nov 11 (instead of 12:30 - 2:00 PM on Tuesday, Nov 8, and Friday Nov 11)
  • Unil: 12:30 - 2:00 PM on Thursday, Nov 17 (instead of 12:30 - 2:00 PM on Friday, Nov 18)

• 10/31: Exam 2 will be on Wednesday, Nov. 16, and HW 10 will be due on Wednesday, Nov 9.

• 10/26: HW 9, due Wed, Nov 2, is now available on Gradiance. It is due at 9:00 AM Central Standard Time (Gradiance lists times in Pacific Standard Time using 24 hour clock).

• 10/26: Important notice regarding project grading: In order for Unil to accurately grade the projects, he needs to meet with team members at times to find out more about how the project is working. It is in your best interest to cooperate with his requests for meetings -- if, after he has put in a reasonable effort, he is unable to understand what is going on in your project, then you will lose points.

• 10/20: HW 8, due Wed, Oct 26, is now available on Gradiance. It is short (3 questions). It is due at 9:00 AM Central Daylight Time (Gradiance lists times in Pacific Standard Time using 24 hour clock).

• 10/18: Saturday, Oct 22, is the TACS local programming contest. Anyone is welcome to compete but please notify organizers in advance that you plan to attend by emailing icpc(AT)cs.tamu.edu. The contest will be held in room 229 Bright starting at 11:00 AM. Winners will get prizes and will form tams to represent Texas A&M at the regional contest in November. Regional winners proceed to the World Finals. (Did you know that the TAMU team was a regional winner last year?) The main restriction is roughly that students must be no more than 5 years out of high school, so fifth year seniors and first year grad students can be eligible. For those interested, there will be a practice session on Thursday, Oct 20, 7:00 PM, room 229 Bright. For questions, contact Efrain Lopez (efrain(AT)neo.tamu.edu).

• 10/14: I will be out of town and away from email through Sunday evening. Please refer all questions regarding the class to Unil and/or Chris during this time.

• 10/14: Clarification has been given regarding what to turn in for Part 3 of the project. Please read here.

• 10/12: HW 7, due Wed, Oct 19, is now available on Gradiance.

• 10/6: Important change: In order to be able to do part 6 of the project (constraints and triggers), we need to use a later version of MySQL. CSG has just installed it. The machine name is database-fall05.cs.tamu.edu and it is running mysql 5.0.13-rc . You should already have an account on this machine with default password being your UIN. If you changed your password on the old server, it will not be reflected here. Please do all your project work on this machine with this version of MySQL. Thanks to Philip Degarmo for alerting me to the problem with the old version of MySQL and to Phil Mattingly in CSG for installing the new version so quickly.

• 10/6: HW 6, due Wed, Oct 12, is now available on Gradiance.

• 10/4: The Gradiance problem with old homeworks has been fixed.

• 10/3: I've been alerted to the ERROR:999 problem with looking at old homeworks on Gradiance. If the problem isn't fixed by tomorrow morning, I'll find another way to make the old homework solutions available.

• 10/3: The question was asked in class today whether SQL is case sensitive: The answer is no, except inside strings. See p. 244 of the textbook.

• 10/3: Chris Portales (peer teacher) will not be having office hours today.

• 9/29: Review for Exam I is now available.

• 9/27: I've postponed the due date for Culture 2 to be Friday, Oct 7, since the project part 2 is due Oct 3 and Exam I is on Oct 5.

• 9/22: There are no classes on Fri, Sep 23.

• 9/20: HW 5, due Wed, Sep 28, is now available on Gradiance. Important difference! This homework includes two parts, "CPSC 310/603 HW 5" available from the Homework section of the Gradiance site and "CPSC 310/603 HW 5 Lab" available from the Lab Projects section of the Gradiance site. The HW 5 Lab is not a programming lab -- it asks you to write some SQL queries against a given schema. Be sure to do both by next Wednesday morning!

• 9/19: See box on page 283 for clarification regarding NULLs in aggregations and groupings in SQL.

• 9/14: You MUST visit with Unil about your E/R diagram in order to get any points for part 1 of the project. When you visit him, you must bring a COMPUTER-GENERATED draft copy of your diagram for him to comment on. Unil will hold office hours 12:00 - 2:00 PM this Thursday and Friday.

• 9/13: HW 4, due Wed, Sep 21, is now available on Gradiance.

• 9/12: Tips for getting full credit on your culture reports.

• 9/12: In case you are having trouble getting electronic access to the IEEE digital library, try this: First, you have to be behind the TAMU firewall. Then go to http://library.tamu.edu. Under "Resources to Find Articles and E-Books", click on "Index/Database" (NOT E-Journal), enter "IEEE" into Search Term, and hit submit. This should bring up a page with "IEEE Xplore Coverage: 1988-Through Present", etc. Click on "IEEE Xplore".

  Alternatively, if you are behind the firewall, the following links should work: ACM digital library and IEEE digital library.

• 9/12: You might be interested in this simple guide to five normal forms by William Kent.

• 9/7: Teams are formed. They are listed at http://faculty.cs.tamu.edu/welch/teaching/310.f05/teams.txt. Replace (AT) with @ to get email addresses. Asterisk (*) means communicator.

• 9/6: HW 3, due Wed, Sep 14, is now available on Gradiance.

• 9/6: For the culture reports, please bring them to class on the due dates.

• 9/5: If you are having trouble finding a team, please email me (welch @ cs.tamu.edu) and I will help you.

• 9/4: The programming project is now available. Start forming teams of 3! Teams are due this Wednesday.

• 9/2: Unil's office hours are Tuesdays and Fridays, 12:30 - 2:00 PM.

• 9/2: Project details are on their way. The project will be done in teams of 3. You can start thinking now about forming your teams -- the deadline for forming the teams is Wednesday, Sep 7.

• 9/2: The way Gradiance works, if you don't submit anything for a homework, you cannot see the questions or solutions after the due date. For those of you who didn't do HW 1 and are curious, this is the question that was on it: "Which of the following is a property of a transaction? Durability, schema, relation, attribute." The correct answer is "durability".

• 8/30: Chris Portales' office hours will be MW 4-5:30, TR 8-9:30 and 3:45-5:30 in HRBB 219 starting 8/31.

• 8/30: HW 2, due Wed, Sep 7, is now available on Gradiance.

• 8/29: Powerpoint slides for today's lecture are available in the Calendar section.

Back to beginning

Syllabus

Teaching Assistant: Unil Yun
Office: 408G H.R. Bright Bldg
Office Hours: Tuesdays and Fridays 12:30 - 2:00 PM
Email: yunei @ neo.tamu.edu
Office Phone: 845-4924

Peer Teacher: Christopher Portales
Office: 219 H.R. Bright Bldg (Open Access Lab)
Office Hours: Mondays and Wednesdays 4:00 - 5:30, Tuesdays and Thursdays 8:00 - 9:30 and 3:45 - 5:30
Email: cportales @ tamu.edu
Office Phone: N/A

Course URL: http://faculty.cs.tamu.edu/welch/teaching/310.f05

Prerequisites: For CPSC 310, prerequisite is CPSC 211 (or 210); For CPSC 603, prerequisite is CPSC 601. In particular, you should be familiar with hash tables and search trees.

Lecture: Monday, Wednesday, Friday, 9:10-10:00 AM, CHEN 106.

Textbook: Database Systems, The Complete Book, Hector Garcia-Molina, Jeffrey D. Ullman, and Jennifer Widom, Prentice Hall, 2002.

Your grade will be based on four components:

• exams 45% - There will be two midterm exams and one final exam, worth 15% each.

• homework 15% - Homework will be done using the Gradiance on-line system. There will be 12 weekly assignments, typically due every Wednesday. More details are available here.

• project 35% - There will be a significant programming project. More details are available here.

• culture reports 5% - This component is to round out your classwork. Find, read, and write short reports on five technical papers in computer science journals. More details are available here.

No late assignments will be accepted. There will be no make-up exams except for university-excused absences. Please discuss unusual circumstances in advance with the instructor.

Course grades will be assigned according to this scale:
A for 90% or above of the total points,
B for 80 to 89%,
C for 70 to 79%,
D for 60 to 69%,
and F for less than 60%.

Academic Integrity: The Aggie Honor Code states "An Aggie does not lie, cheat or steal or tolerate those who do". More information on academic integrity, plagiarism, etc. is available at the Aggie Honor System Office web site http://www.tamu.edu/aggiehonor, including:

• Definitions of academic misconduct, which includes plagiarism
• List of sanctions that can be applied if academic misconduct is found.

Americans with Disabilities Act (ADA) Policy Statement: The Americans with Disabilities Act (ADA) is a federal antidiscrimination statute that provides comprehensive civil rights protection for persons with disabilities. Among other things, this legislation requires that all students with disabilities be guaranteed a learning environment that provides for reasonable accommodation of their disabilities. If you believe you have a disability requiring an accommodation, please contact the Department of Student Life, Services for Students with Disabilities in Cain Hall, Rm. B118, or call 845-1637.

Back to beginning

Calendar

• readings
• homework
• project
• exams
• culture reports

• Introduction: 8/29
• Entity-Relationship Model: 8/31
• Relational Model and Converting from E/R: 9/2, 9/5
• Functional Dependencies: 9/7
• Normalization: 9/9
• Multivalued Dependencies: 9/12
• Relational Algebra: 9/16, 9/19
• Introduction to SQL: 9/19, 9/21, 9/26
• More on SQL: 9/28, 9/30
• Constraints, Triggers and Views: 9/30, 10/3
• Combining SQL and Conventional Programming Languages: 10/10
• Transactions: 10/10
• Data Storage: 10/12
• Representing Data Elements: 10/14
• Indexes on Sequential Files: 10/17
• Secondary Indexes and B-Trees: 10/19,10/21 (powerpoint file updated 10/20)
• Hashing: 10/24, 10/26
• Query Execution: One-Pass Algorithms: 10/28
• Query Execution: Two-Pass Algorithms: 10/31, 11/2
• Query Compilation: Logical Query Plans: 11/4, 11/7
• Query Compilation: Physical Query Plans: 11/7, 11/9
• Recovery: Undo and Redo Logging: 11/14, 11/18
• Recovery: Undo/Redo Logging: 11/18, 11/21
• Concurrency Control: 11/21, 11/23, 11/28
• More on Transaction Management: 11/30, 12/2

• details on the assignments
• exam reviews

Back to beginning

Homework

User guides for students are available at http://www.gradiance.com/info.html

Before you can start doing your homework on Gradiance, you have to create an account:

• Go to http://www.gradiance.com/freeDB.
• Create an account for yourself using the Sign Up Token announced in class.
• You may now start doing homeworks that are already assigned for this class.

Project

Back to beginning

Culture Reports

You are to find, read, and write short reports on five technical papers in computer science journals. Articles in the following two journals are usually at about the right level:

• Communications of the ACM
• IEEE Computer

Each report is to be one to two pages long, typed, and must include

• correct bibliographic information for the paper (title of article; names of all authors; name of journal with volume, number, date and page numbers)
• a summary of the paper contents
• at least one comment about the paper or question that it raised in your mind, indicating that you have thought critically about the material.
• Filled-in cover sheet.
• a photocopy of the original article attached.

DO NOT PLAGIARIZE! You must write up your summary in your own words. See academic integrity policy in the syllabus.

Report due dates are indicated in the calendar and are summarized here:

• Culture Report 1: due 9:10 AM, Mon, Sep 12
• Culture Report 2: due 9:10 AM, Fri, Oct 7 (*previously was Oct 3*)
• Culture Report 3: due 9:10 AM, Mon, Oct 24
• Culture Report 4: due 9:10 AM, Mon, Nov 14
• Culture Report 5: due 9:10 AM, Fri, Dec 2

Here are some tips for getting full credit on your reports.

On occasion, a culture assignment may be substituted by attendance at and a report on a Distinguished Lecture. These opportunities will be announced as they arise.

Each report is to be one to two pages long, typed, and must include

• title of the lecture
• name and professional affiliation of the speaker
• a summary of the lecture
• at least one comment about the lecture or question that it raised in your mind, indicating that you have thought critically about the material.

Back to beginning

Useful Links

• Web site for the textbook

• Gradiance web site

• William Kent's guide to normal forms

• Computer Science Peer Teacher web site

• Turn-in program information

• MySQL web page and tutorial

• Using MySQL at TAMU

• SQL Tutorial from W3 Schools, submitted by Jonathan Stewart

Computing-Related at TAMU

• Texas A&M Computing Society (TACS) -- student organization for those interested in computing. Affiliated with the two main professional organizations, ACM and IEEE Computer Society.

• Computer Science Department, TAMU

• Student Computing and Information Page: central source of information about student computing at TAMU.

• Schedule of short courses offered by the CIS Computer Help and Training Group.

Careers and Mentoring

• Office of Honors Programs and Academic Scholarships

• CS Dept. Student Research Information

• TAMU Co-operative Education

• Computing Research Association Distributed Mentor Project for Undergraduate Females in Computer Science and Engineering: If you think you might be interested in graduate school in CS or CE, this could be a great opportunity for you if/when you are a sophomore or junior. Be thinking ahead!

Back to beginning