INFS 614 - Section 03 -- Spring 08

INFS 614 -- Section 03: Database Management

Spring 2008 Course Web Page

Thursdays 7:20 - 10pm / Robinson A 247

Professor Ken Smith (kps@mitre.org)

General Information

Course content: A graduate introduction to database management, emphasizing relational database design and queries. The lecture covers principles, and is supplemented by Oracle-based homeworks. Syllabus(tentative).

Textbooks:

The primary text is Database Management Systems, 3rd ed, Raghu Ramakrishnan and Johnnes Gehrke, McGraw-Hill. The course will closely follow the text material, in the order described in the syllabus. Useful supporting material, including answers to many of the odd-numbered exercises are available in POSTSCRIPT and in PDF format. Note that test questions may bear an uncanny resemblance to these exercises.
The secondary text for the class is Oracle Programming: A Primer by Rajshekhar Sunderraman, published by Addison-Wesley. It serves as a reference for the use of Oracle in class assignments. Follow the text's URL to get code samples.

Graduate Teaching Assistant: The GTA for this course Ms. Judy Luo(lockejudy[AT]gmail[DOT]com). She will hold an office hour each week from 6-7pm on Thursday, right before class, in ST2 335.

Computer Resources:

ITU: The ITU support page.
IT&E: The IT&E lab guide.
Linux/Unix:Here is a pointer to an online tutorial.
Oracle: Your Oracle accounts should be automatically assigned to you once you are registered in the class. For further information:

The IT&E guide on how to use Oracle with examples. It mentions the "Oracle Guide", which is available at GMU's Johnson Center copy shop.
More Oracle DBMS information, useful for our particular computing environment, is available on Professor Motro's website.

Honor Code Information: GMU Honor code; specifics related to this course.

Week 1 Notes:

DB Intro Slides: Here are the slides which were used in Lecture 1 (Thursday, 1/24 at 7:20pm) as a .pdf file.

First Lecture Notes: Please remember the concepts of "data model" and "impedance mismatch".

Week 2 Notes:

The slides for the ER lecture are here.

Homework #1:The assignment is due in two parts. Part 1a is due at the beginning of class on Feb 7th. Part 1b is due at the beginning of class on Feb 14th. (Happy Valentine's Day!)
I have worked a similar example , which I will work on the board at the end of class Jan 31st. Here is its implementation as a relational database using SQL DDL statements.

Homework Submission Instructions: All your homeworks will include problems whose answers are to be typed/written out and handed in at the beginning of the class when they are due. Please make sure your work is legible, and your name, the assignment number, and the due date are all clearly marked on your paper.
Certain homeworks (like 1b, next week's) have a script file which must also be submitted to the GTA via email.

Week 3 Notes:

Relational Model: Read chapter 3.

Relational Model Slides: Here are the slides used in Lecture 3.

Homework Hint: Remember to assume no constraints (e.g., key, participation) unless explicitly stated in the text. Thus, the default for relationships is M-N with partial participation.

Week 4 Notes:

Relational Algebra Slides: Here are the slides used in Lecture 4. Read Chapter 4.1 and 4.2. Also look at the Wikipedia article on the deep web.

Homework 1b hints: Here are a set of FAQs on the solution to HW 1a. Please use the diagram we derived on the board for part 1b, no matter how you solved 1a. It included 6 entity sets called: Professor, GTA, Team, Section (weak), Class, and Student. There were also 8 relationship sets: on_team1, on_team2, administers, can_teach, prereq_of, takes, waits_for, sec_of (weak), and the ISA relationship between Students and GTAs. Make sure you use the correct attributes, I believe I left two out on the board (e.g., Class has a multi-attribute key).

Homework 1b submission: HW 1b is due at the beginning of class next week (2/14). Please remember to write out your scheme on paper (not a drawing), as described in the textbook and in lecture. For example: Students(SID:integer, sname:varchar(20), major:varchar(20)). In addition, print your script on paper. Turn in both at the beginning of class. (Also, of course, you'll submit your script electronically.)

Note: Electronic Homework Submission Please read the GTA's guidance for electronic submission of homework carefully!

Homework #2 Assignment: Work exercise 4.3 in text (relational algebra only), problems: 1, 3, 4, 7, 8, 10, and 11. Please write your answers neatly! This assignment is due 2/21.

Homework 2 Hint: On problem 7, it asks for the sids of suppliers who supply "every red or green part". Let X be a set containing the pids of every part which is either red or green. This question is asking for the sids of suppliers who supply all pids in X. Problem 8 is asking a different question!

Week 5 Notes:

Relational Calculus: Read Sections 4.3 and 4.4. No online slides for this lecture; I will hand out copies of the slides in class.

Homework Assignment 3: Work exercise 4.3, problems 1, 3, 4, 7, 8, 10, 11 in TRC (tuple relational calculus). Please write your answers neatly! This assignment is due 2/28.

Week 6: Midterm Review:

Slides on "All" and "Only" queries in Relational Calculus: Here are some slides covering the material not in the text, and which I did not get to complete on the board last Thursday night. Hopefully this will make issues like "What if there are no boats at all?" clearer.

Midterm review format: I will spend the entire period going over the previous homework problems, fielding your questions about them, and discussing what will be emphasized on the midterm. Related to this last point, the "key topics to study" below should help guide your study. Please come with your questions!!

Key Topics to Study:
- 1) ER model. All ER terminology definitions presented in class. How to translate a textual model into an ER diagram. The meaning and proper use of ER constructs (e.g., weak entities, aggregations) presented in Chapter 2.
- 2) Relational model. Definitions and features of the relational model, and of a data model in general. How to translate an ER diagram into a relational schema.
- 3) Relational algrebra. The algebraic operators and their effect. How to write queries, and what queries will produce on a given set of instances. Relational completeness. Cardinality and operation reordering.
- 4) Relational calculus. How to form a proper relational calculus query and what it means. How to write specific relational calculus queries, and what a query will produce on a given set of instances.

One set of answers to homeworks 2 and 3 are Click here. (Note that there are several variants for some of these problems!)

Week 9 Notes:

SQL Basics Read Sections 5.1-5.3. This lecture covers SQL from a relational algebra perspective. Here are the slides.

HW 1c. Here is the beginning of your assignment. Bring a draft of your ER diagram to class next week.

Week 10 Notes:

Homework 1c. Here is the assignment for Homework 1c. This assignment is due on April 17th, and is worth 4% of your total grade (HW 1a and 1b are worth 2% each, all other homeworks are worth 3% each). Note that we will have a design session in class this week to finalize our class ER diagram, which everyone will use for the rest of the assignment.

Extra Credit! You have two options:
- 1) You can retake your midterm as a take-home exam. You may use notes and the text book, but you must do your work individually and turn it in within one week. Any newly correct answers will give you extra midterm points: 0.4 midterm points for each newly correct point. For example, someone who got a 50 on the midterm can get a maximum of 20 new points. Someone who got 90 on the midterm can get a maximum of 4 new points. This is due in class one week after you start.
- 2) You can do extra work on Homework 1c (see the bottom of the assignment). This will be worth a maximum of 7 midterm points. This is due (along with the rest of Homework 1c) on April 17th.

Homework 4. Please write out answers to Exercise 5.2, problems 3 through 10 in your text (8 SQL queries). On 10, note that "print" simply means to return certain fields in your SELECT clause. This assignment is due on April 10th.

SQL Lecture #2 This week's lecture covers SQL from a TRC perspective. Here are the slides. Read Section 5.4 this week and 5.5 next week. These are well-written sections in the text, and complement the lecture materials well. Please read and study both.

Week 11 Notes:

Homework 1c ER diagram. Here is an ER diagram for Homework 1c. I omitted lines to attributes for simplicity; attributes belong to the entity set/relationship set they are close to in the diagram. Please use this diagram for the rest of your assignment!
Note that it differs a bit from what we decided on in class. First, I added a "black arrow" relationship between "VisitRecord" and "Doctor" because that will result in Dr-ID being apart of the table for visit record (as required). Otherwise to recover information about the Dr. of a VisitRecord we'd have to do a bunch of joins! (Can you see how?). I also invented "MRI-ID" as a key for MRI. This was not specified in the textual descripiton (Not uncommon in the real world!), but one is still needed.
For those of you doing the extra credit, the above paragraph gives you some ideas about the ambiguities. I would also suggest looking into whether we could use an aggregation (as discussed in class) in which "VisitRecord" is related to the entire "Visit" relationship.

Due Date Clarification! Please check the updated syllabus for all class and homework due dates for the rest of the semester. HW 4 is due on Thursday April 10th (as in the syllabus)! I mistyped it here earlier and apologize for the confusion. (If it were due on April 17th (with 1c) you would not have enough time to get your graded homeworks back before the final.)

SQL Lecture #3 This week's lecture covers the rest of SQL, including aggregation, nulls, ordering, and other topics. Here are the slides. Please read Sections 5.5 and 5.6.

Week 12 Notes:

This coming week: Functional Dependencies, Homework, and Internships This week's lecture covers chapter 19.1-19.3 in your text. Here are the slides. Your HW4 is due, and HW5 (the last one!) will be assigned. I will also spend about 10-15 minutes of class time discussing internships, as several of you are going to be involved in these.

HW 1c Queries The rest of the HW 1c assignment is to enter data and write a set of queries. The details described in this document. Note, as discussed in class, you may modify your database to combine "date" and "time" into a single Oracle timestamp field.

Class on April 24th On April 24th (week 14) we will have three guest speakers, presenting topics very relevant to our class. First off, Dr. Michael Morse will spend an hour on indexing, with a special emphasis on spatial indexing and spatial queries (e.g., Find all persons living within 10 miles of GMU). Then, Dr. Douglas Burdick will spend an hour discussing OLAP and the data cube operator, a powerful way to explore aggregates (e.g., sums, averages). Our third lecture will be given by class member Paul Lockaby who tunes large databases for performance as his "day job". He will give examples and demonstrations of some relevant tools. All three speakers work actively in the areas they are presenting on, and all three topics have tremendous practical relevance. I am looking forward to each of these presentations!

Week 13 Notes:

Homework 1c. Due in class on 4/17. Please turn in your ER design, relational tables, and script. Please also email your script to the GTA.

Homework 5 (the last one!). Please do Exercises: 19.1 problems 1, 2, 5, and 6; 19.2 problems 1 and 3; 19.5 part b only; 19.6 (all); and 19.7 parts a and c only. This is due in class on 4/24.

Internships/Book. Last night, after the lecture, we discussed internships and careers. I mentioned a book What Color Is Your Parachute? by Richard Nelson Bolles, which I have found very useful. Its been published annually since the 70's! Here is an Amazon link for it.

Normalization Lecture. Please read Chapter 19.4-7 for this coming week. Here are the slides.

Extra Credit on Week 14. On 4/24 we will be having 3 guest lectures (about an hour each). For extra credit, please write 1-2 pages answering the following two questions: 1) Which lecture did you like best and why? 2) How does the material in that lecture relate to, or expand on, what you have learned so far in class? I will not award actual points, but will use your writeup if you are on a borderline between two grades.

Week 14 Notes:

Updated normalization notes. Here is an updated version of the slides on normalization. These clarify the issues raise during lecture. Please study these; we will go over the issues during the final review session. Also read carefully sections 19.5.2 and 19.6.1 in your text.

SQL homework solutions (to help you study for the final). Here are the solutions for HW4 (SQL).

Week 15, Final Review:

Normalization Homework Solutions. Here are the solutions for HW5 (Normalization).

Final Material. We will go over your specific questions in class this week. In general, the final is comprehensive, covering material presented both in lecture and in the text. However, it will emphasize the major new material since the midterm: nested and aggregate SQL, and normalization into BCNF. As on the midterm, be prepared to turn a written description into an ER diagram, and to manually execute a query on a given relation instance, and produce the result. The final exam will primarily be fill-in-the-blank and multiple choice (so I can grade it quickly!).

Guest lectures. Here are the slides for the lectures by: Michael Morse (Indexing), Douglas Burdick (OLAP), and by Paul Lockaby (Oracle tuning).

Your Grades

All course grades posted. All grades have now been posted to Patriot Web. Best wishes in the future! Professor Smith.