CS482 - Database Management Systems (Slides and Classnotes)

Week 1, 2, 3, 4, 5, 6, 7, 8, 9,10, 11, 12, 13, 14, 15, 16, 17

Solution of homeworks, Oracle access information/instruction is available at the TA's website.

Week 1: (8/18/03 - 8/24/03)

• Overview - Database. Download in PDF or PPT format.
• Overview - Transaction. Download in PDF or PPT format.
• Homework: Exercise 1.7, 1.10 (Page 13), 2.11 (Page 27).
  Due 11:59 pm, Wednesday, September 3 (Changed!).

  In case you did not get the book, here are the exercises.

  1.7 Give three examples each of applications requiring:
  a. High reliable systems;
  b. High-throughput systems;
  c. Short response time systems.

  1.10 Your school is planning a new automated student registration system. As a possible user, you are being interviewed by the system analyst doing a requirements analysis for the system.
  a. What transactions would you recommend be available for students? Include some query transactions, in which a student can request information from the system.
  b. Pick one of these transactions, and sketch the design of the form the user interacts with when that transaction is run.
  c. What are some other classes of users in addition to students? What transactions do you think they would want?

  2.11 A reservation transaction in an airline reservation system makes a reservation on a flight, reserves a seat on the plane, issued a ticket, and debits the appropriate credit card account. Assume that one of the integrity constraints of the reservation database is that the number of reservations on each flight does not exceed the number of seats on the plane. (Of course, many airlines purposely overbook and so do not use this integrity constraint.) Explain how transactions running on this system might violate
  a. Atomicity
  b. Consistency
  c. Isolation
  d. Durability

Week 2: (8/25/03 - 9/01/03)

• A Brief Introduction to Relational Database. Slides in PDF or PPT format.
• Homework: Read Chapter 3 (The Student Registration System). Here are some slides for this chapter in PDF or PPT format.
• Chapter 4: Relational Data Model. Slides in PDF or PPT format.

Week 3: (9/02/03 - 9/09/03)

• Chapter 4: Relational Data Model (Continue from the last class). Slides in PDF or PPT format.
• Homework for this week are: 4.5, 4.6, and 4.7. Due date: 11:59 pm, September 10, 2003.

Week 4: (9/08/03 - 9/15/03)

• Chapter 5: E-R Model Slides in PDF or PPT format.
• Here is the slides for the second class on this topic in PDF or PPT format.
• Please read the section 5.7.
• Homrwork for this week: 5.7. Please state your assumptions that you make in your solution. Due 11:59pm, Septembet 17, 2003.
• In anticipation of the coming test: Try the exercises of chapters 5, 4, and 2
• Here are the old exams: 2001 and 2002 . Notice that the questions in the old exams may have materials that we have not covered since the organization of the textbook is different.

Week 5: (9/15/03 - 9/22/03) Review and Test 1.

Week 6: (9/22/03 - 9/29/03)

• Chapter 6: Relational Algebra. Slides in PDF or PPT format.
• Homrwork for this week: 6.9 and 6.10. In 6.9, you are asked to write a relational algebra expression that produces the relation with the properties described in (1)-(3). An example of outer joint follows. Due 11:59pm, October 1, 2003.
• Example of outer join: Suppose that we have two relations R(A,B,C) and S(B,C,D). R's instance has the following tuples: (1,2,3), (4,5,6), and (7,8,9). S's instance has the following tuples: (2,3,10), (2,3,11), and (6,7,12). The result of the outer join R outerjoin S with the condition R.B=S.B AND R.C=S.C (this is the natural join condition) is the relation with four attributes (A,B,C,D) and the instance consists of the following tupes: (1,2,3,10), (1,2,3,11), (4,5,6,NULL), (7,8,9,NULL), and (NULL, 6,7,12).

• Working on exercises will help you understand the definitions of all the operators as well as the SQL query language. Please try all other questions if you have time.

Week 7: (9/29/03 - 10/6/03)

• Chapter 8: Database Design II - Relational Normalization Theory Slides in PDF or PPT format.
• Homrwork for this week: 8.4, 8.15, 8.23, 8.24. Due: 11:59 pm, Wednesday, October 8, 2003.
• SQL Homework Use Oracle, (i) create the schema for the student registration system (Page 116 and 117); (ii) insert at least 5 records into each table. This should be done by 11:59 pm, Sunday, October 5, 2003.

Week 8: (10/6/03 - 10/13/03)

• Continue with Chapter 8. The homework 8.4, 8.15, 9.23, and 8.24 is due on Wednesday, October 15, 2003.

Week 9-10: (10/13/03 - 10/27/03)

• Welcome back from the Fall break. We will still continue with Chapter 8. The homework 8.23, and 8.24 is due on Wednesday, October 22, 2003. No new homework for this week.
• Project: The project description is now out.
• Here is a slide on MVD properties in PDF.

  Here is an old note of mine on relational algebra and normalization theory.

  Here are some practice exams: 2001 Test1 2001 Test2 2002 Test2

Week 11: (10/27/03 - 11/02/03)

• We are done with the second test on Oct 28. We will study Chapter 9 on Oct 30. Slides in PDF or PPT format.
Homework for this chapter: Exercise 9.5. Due 11:59pm, Nov 5.

Week 12: (11/02/03 - 11/09/03)

• In case you want to check your answer: here is the second midterm solution.
• Chapter 10 is on the menu. Slides in PDF or PPT format.
No homework for this week but the project is there. If possible, we will continue with chapter 11.
• Here are the slides for chapter 11 in PDF or PPT format.
• Here is an example of the BCNF decomposition.

Week 13: (11/09/03 - 11/16/03)

• We have an extra test. Here is the solution.
• Slides for chapter 13 in in PDF or PPT format.

Week 14: (11/17/03 - 11/24/03)

• We change the schedule and will study Chapter 16 and 17.
• Slides for chapter 16 in in PDF or PPT format.

NOTE:

Week 15: (11/24/03 - 12/01/03)

• Chapter 17.
• Slides for chapter 17 in in PDF or PPT format.

NOTE:

Week 16: (12/01/03 - 12/07/03)

• Continue with Chapter 17.
• Slides for project requirements in PDF or PPT format.

Week 17: (12/07/03 - 12/14/03)

• Final Week
• Summary in PDF or PPT format.

Last modified: Fri Dec 05 13:03:12 Mountain Standard Time 2003