Fall 2011 CS 386/586 Introduction to Databases

This Document is stored at www.cs.pdx.edu/~maier/386

Announcements (Last update 5 December 4:50P):

·        HW 7 solution key posted

·        Exam info posted (fixed)

·        Sample of Midterm 2 posted

·        Database Support Page Has been mostly updated.

 

Instructor

David Maier maier at cs dot pdx dot edu, 115-14 FAB. Note: When sending email to me directly, please put ‘cs386’ at the beginning of the subject line.

Class Meeting

Tuesday, Thursday  10:00 AM - 11:00 AM ,  NH 350

Office Hours

Mondays 2:00-3:00 PM (I will stay as long as students are there, except the first Monday of the month.)

TA

Yue Zhang; yuez at cs dot pdx dot edu

TA Offices Hours

Wednesdays 1:00-3:00 PM; Fridays 11:00AM-noon
In CS lab 88-09 (map)

Weekly Schedule

[This schedule is preliminary and subject to change]

Quizzes & Project submissions on Tuesdays, Assignments due Thursdays,

Wk

Date

Topic

Reading Assgt 

Slides; Quizzes (Tuesdays)

Due (Thursdays)

1

Sept 27,29

Intro. to relational
databases; SQL

Ch. 1;

Ch. 3: Intro, 3.1-3.4;

Ch. 5: Intro, 5.1, 5.2

Lecture 1
Quiz 0 key
Practice 1

 

2

Oct 4,6

Relational Algebra  and Complex SQL

Ch. 5: 5.3-5.6;

Ch. 4: Intro, 4.1, 4.2

Lecture 2

Quiz 1 key

Practice 2

Homework 1

HW 1 solutions

3

Oct 11,13

Relational Algebra and Complex SQL (cont.)

Constraints & Triggers

Ch. 5: 5.7-5.9

Lecture 3
Quiz 2 key

Practice 3

Homework 2 

HW 2 solutions

4

Oct 18,20

Views

Use Cases

Ch 3: 3.6-3.7

Lecture 4

Quiz 3 key

Homework 3 (corrected)

HW 3 solutions 

5

Oct 25,27

Database Design

Embedded SQL

Ch 2: Intro, 2.1-2.9 (not 2.4.4, 2.4.5, 2.5.4)

Ch. 3: 3.5 (not 3.5.6, 3.5.7)

Lecture 5

Jones Lecture

Jones Extra

Quiz 4 key

Homework 4

HW 4 solutions

6

Nov 1

Tuesday Nov 1: First EXAM on all preceding material

 

Sample Midterm

Grad Project (rev 1)

 

Nov 3

PHP

Ch. 7

Lecture 6

 

7

Nov 8,10

Storage and Indexing, Disks and Files, Query Evaluation

Sec. 9.1;

Ch. 8: Secs 8.1-8.3, 8.5, 8.6;

Ch 12

Ch 13: Secs 13.1-13.3

Lecture 7

Quiz 5

 

 

Homework 5
HW 5 solutions

8

Nov 15,17

Query Optimization

Transactions

Sec. 14.4, Sec. 15.3

Ch 16

Sample 1 Sample 2

Lecture 8A

Lecture 8B
Quiz 6

Homework  6
HW 6 solutions

9

Nov 22

Schema Refinement

Normalization

Ch. 19 (not 19.8)

Lecture 9
Quiz 7

 

10

Nov 29, Dec 2

Physical Database Design and Tuning; Security

Ch 20: 20.1-20.3, 20.6-20.9;

Ch 21: 21.1-21.3

Lecture 10
 Quiz 8

 Homework 7

HW 7 solutions

 

Tues Dec 6

Second EXAM at final exam time (10:15-12:05), on material since First EXAM

Sample Exam

Exam Info

Class E-mail (OK to join list now)

I hope the e-mail list for this class will be cs386-list@cs.pdx.edu.  It will be used for announcements from the instructor.  You can also send questions and answers to this mail list.  You can subscribe to the list at https://mailhost.cecs.pdx.edu/mailman/listinfo/cs386-list. Please enter at least you last name and initial, in addition to your email address.

Catalog Description

Introduction to fundamental concepts of database management with the relational model. Schema design and refinement, query languages, transaction management, security, database application environments, physical data organization, overview of query processing, physical design tuning. Prerequisites: CS 161 or 162, 250. Recommended prerequisites: CS 251.

 

Course Goals

To learn how to design effective database schemas that accurately model real-world information semantics. To learn how to pose complex database queries in SQL as well as theoretical query languages. To understand what is meant by a transaction. To gain an introductory background in concurrency control and recovery. To learn about indexing and query optimization in database systems.

 

Upon the successful completion of this course students will be able to:

  1. Write complex SQL and relational algebra queries
  2. Transform SQL queries to relational algebra and relational algebra queries to SQL
  3. Transform SQL queries into equivalent forms, e.g., to make them more efficient
  4. Explain how queries are processed and optimized
  5. Evaluate the utility of an index for a relational database table
  6. Evaluate existing database designs and design new databases effectively
  7. Use SQL queries embedded in a software application
  8. Describe and use transaction and recovery services

 

Textbooks

REQUIRED:
Database Management Systems, 3rd Edition. By Raghu Ramakrishnan and Johannes Gehrke, McGraw Hill, 2000, ISBN 0-07-246563-8. 

SUGGESTED:
Since you will be using SQL in a number of assignments, you may wish to have additional information concerning the SQL query language. SQL: 1999 -- Understanding Relational Language Components, by Melton and Simon (2001, ISBN 1-55860-456-1) is a good reference. There are also a number of on-line references and tutorials (though some seem to be content farms).

Reading

It is okay to do the reading after the lecture where the material is covered, but you should certainly complete it before the next week’s quiz.

 

Grading
Homework: There are 7 homeworks. Each is worth 4%, except HW 7 is worth 5%, for 29% of your grade.  Homeworks can be done individually or in teams of two students.  If you work in a team, then turn in one paper with the names of all team members on it.  Assignments are given out on THURSDAY and are due the following THURSDAY (except HW 7).  Make sure your homework is legible. You may seek help from the instructor and the TA (and your partner, if you have one), but otherwise work independently. You may post questions (and answer them, if you want) on the course mailing list.


Quizzes: There are 8 quizzes. For CS 386 students, each is worth 3%, but I drop your lowest quiz grade, so the quizzes are worth 21% of your grade.  For CS 586 sutdents, each is worth 2%, with the lowest one dropped, for 14% of your grade. A quiz is given on most TUESDAYs.  Each quiz covers material from the preceding week, including both lecture and reading material.  There are NO MAKEUPS FOR QUIZZES.  Quizzes will be closed book.

 

Project: CS 586 students will also have a project involving the design and implementation of a database, worth 7% of your grade.

 

Exams: There is a first exam (25%) that covers the 1st half of the class material, and a second exam (25%) during finals week that covers the 2nd half of class material.   Exams will be open book and notes.

Information

Database Support Page (general information about DBMS products and data files), answers to odd numbered questions, symbols for Word and symbols for Power Point.

Policies

Students are responsible for anything that transpires during a class - therefore if you're not in a class, you should get notes from someone else (not the instructor).  

Homeworks are due at the beginning of the class period. 

Late homework will not be accepted without prior approval from me. 

Requests for re-grading must be submitted to me in writing within one week of the time the graded assignment was made available for pickup.  Please attach a note saying which problem or problems should be re-graded, and why. 

Makeup exams will not be given except in cases of severe medical or family emergencies.  If an emergency arises and you are going to miss an exam, contact me BEFORE the exam to arrange for a special circumstance. 

Students with disabilities who are in need of academic accommodations should contact me as soon as possible to arrange needed supports.  Students are also encouraged to contact the Disability Resource Center (DRC) for additional information on  support services and available accommodations at 503/725-4240 or 503 725-4150.

Academic Integrity

[Excerpt from the 2010-2011 PSU Catalog, page 34]
The policies of the University governing the rights, freedoms, responsibilities and con­duct of students are set forth in the Portland State University Code of Student Conduct and Responsibility which has been issued by the president under authority of the Administrative Rules of the Oregon State Board of Higher Education. Students may consult these documents by visiting our website at: http://www.pdx.edu/dos/codeofconduct. Observance of these rules, policies, and procedures helps the University to oper­ate in a climate of free inquiry and expres­sion and assists it in protecting its academic environment and educational purpose.

Academic honesty: The policy governing academic honesty is part of the Code of Student Conduct and Responsibility. Academic honesty is a cor­nerstone of any meaningful education and a reflection of each student's maturity and integrity. The Code of Student Conduct and Responsibility, which applies to all students, prohibits all forms of academic cheating, fraud, and dishonesty. These acts include, but are not limited to: plagiarism, buying and selling of course assignments and research papers, performing academic assignments (including tests and examina­tions) for other persons, unauthorized dis­closure and receipt of academic information, and other practices commonly understood to be academically dishonest.

 Supplementary Readings

What follows is a list of online resources that you may find helpful over the course of the class. None of these materials are necessary to complete the class; however, you may find that they can offer you perspectives beyond those offered by the textbook or the instructor/TA, especially if you plan to continue your study in the database field.