Winter 2013 CS 386/CS 586: Introduction to Databases
Draft of topics to be covered – shown in yellow font below.
link
to DB Information
link to Piazza
(for questions/answers)
|
Date |
Topics (approximate) |
Reading |
(in-class) Activities & Demo Sessions |
Assignment Schedule |
|
Jan.
9 |
Relations (sets) Relational Algebra for sets Tables in SQL; DDL (create/alter, primary key, unique) Simple SQL queries (select/from/where, into, distinct, perhaps
intersect, union, except) |
Ch. 1 2.1-2.4.7, 2.4.10, 2.4.11, 2.5 6.1, 6.2, 6.4.1 |
scan (from doc. camera) Activity 1 Activity 1 (answers) |
|
|
Jan.
16 |
Relational Algebra join ( More SQL queries (join, row & table value constructors,
outer join) Embedded SQL (in a programming language) |
2.4.8, 2.4.9 5.2.1, 5.2.7 6.3.6, 6.3.8 |
slides (join & outer join) scan (from doc. camera) Activity 2 Demo Session 1 |
Create Table SQL stmts
(for Assign 2) Data (for Assign 2) |
|
Jan.
23 |
Relational alg. equivalences Relational Algebra for bags + dup-elim
Extended Relational Algebra for bags ( More SQL (group by, having, union all, etc.) |
5.1, 5.1.2, 5.2, 6.4, 16.2 |
slides (this file was updated slightly
on 01/24/2013 at 11:15AM) Activity 3 (incorporated with the slides) Demo Session 2 |
(Assign 1 due) |
|
Jan.
30 |
Subqueries,
scalar values SQL DDL |
6.3 |
Activity 4 (incorporated with the slides) Demo Session 3 |
Data (for Assign 4)
|
|
Feb.
6 |
Constraints Stored Modules (stored procedures) |
6.5 Ch. 7 (skip Section 7.1.3) 9.4 |
Activity 5 (with the slides) sailors-sql.txt (for use in
Activity 5) Demo Session 4 |
(Assign 2 due) (Assign 3 due) Assign 5 |
|
Feb.
13 |
DB Design |
Ch. 4 (but
not 4.7, 4.8, 4.9 or 4.10) |
Activity 6 (with the slides) Demo Session 5 |
Assign 5 |
|
Feb.
20 |
Test 1: 5:30 – 7:30PM Lecture begins at 7:40PM |
Covers materials from the
lectures, reading assignments, and activities for weeks 1-4 plus demo
sessions 1-4, and assignments 1-4. |
||
|
Embedded SQL with php |
9.7 |
Extra credit given for attendance for this lecture |
|
|
|
Feb.
27 |
Normalization |
Sect. 3.1, 3.3 (the book goes into more detail than the lecture/test will; use
the lecture as a guide) |
Activity 7 (with the slides) |
|
|
Mar.
6 |
Transactions Views/Indexes |
Sect. 6.6 (skip 6.6.5 and skip 6.6.6) Sect. 7.1.3 Sect. 8.1 - 8.4 |
Transactions: slides, demo script1, script2 Indices: slides code
examples (transactions & instead-of triggers) Activity 8 (in view and index slides) Demo Session 6 |
(Assign 5 due) |
|
Mar.
13 |
Grad Student Presentations |
|
|
(Assign 6 due) |
|
Mar.
20 |
Test 2: Regular classroom; regular time
(5:30PM) |
|||
Database
Information
PostgreSQL Query Page to issue queries against
the Sailors, LibraryDB, and Spy databases
Database
Support Page to see general information about the Sailors, LibraryDB, and Spy databases, DBMS products, and some data
files.
|
Instructor |
Lois Delcambre Computer Science Department
|
|
TA |
Neena Maldikar with assistance from Suzanna (Suki) Kangas |
|
Assistance |
1.
Use piazza
to ask/answer questions 2.
Discuss
class materials/assignments during class 3.
Meet with
us before or after class – in or near the classroom 4.
Other times
by appointment (either face-to-face or phone appt.) |
|
Meeting Times |
Wednesdays, 17:30 – 9:10 (5:30PM – 9:10PM) |
|
Location |
Engineering Building 103 (EB 103) |
Computer Accounts
All CS586 and CS386 students are entitled to use a computer
account at PSU in the
http://www.cat.pdx.edu/index.php?option=com_content&task=view&id=86&Itemid=148
Questions/Announcements:
No E-mail List for the Class. We’ll use Piazza instead – for questions,
answers, and class announcements.
You must register in Piazza for this class; use your
full name when you register so that I can see who you are. (You can post anonymously to your classmates;
they need not ever see your name.)
Register for a piazza account here:
Visit https://piazza.com/pdx/winter2013/cs386cs586 to signup.
Visit https://piazza.com/pdx/winter2013/cs386cs586/home to log in and post, and answer questions, and to
view questions, answers, and announcements.
If you have questions about the course material or your assignments, post a
question in Piazza; if your question involves a query or some code, be sure to
include your query or code – inline or by attaching a file.
Catalog Description
Introduction to fundamental concepts of database management using
primarily the relational model. Schema design
and refinement. Query languages. Database application
environments. Physical data organization. Overview of query optimization and processing, physical design,
recovery and concurrency control. Query processing for search engines.
The course will cover topics such as security, data warehousing and analytic
databases. Prerequisites: CS 161 or 162, 250. Recommended: CS
251.
Textbooks
REQUIRED:
Database Systems: The Complete Book,
2nd Edition, by Garcia-Molina, Ullman, and Widom,
Publisher: Pearson, Prentice Hall, 2009.
Note: this is the first time that this book will be used for this
course.
SUGGESTED:
Since you will be using SQL in some assignments, you may wish to have
additional information concerning the SQL query language. SQL: 1999 -- Understanding Relational
Language Components, by Melton, Simon, and Gray (2001, ISBN
1-55860-456-1) is a good reference.
Database
Management Systems: For some of the assignments in this class, you will use a
DBMS. We provide access to PostgreSQL; you may also use Microsoft Access, or another
relational DBMS of your choice (with permission of the instructor). You may wish to consult reference
material/documentation for Microsoft Access, PostgreSQL,
or for the DBMS of your choice.
Assignments/Grading
Six assignments based on material from the textbook and
the lectures.
Six face-to-face demonstration sessions to be
conducted during classtime. Students will be
asked to demonstrate specific skills to the instructor or one of the TAs. The skills to be demonstrated will be based
on the lectures and activities from the previous week.
(So, for example, Demo Session 1 will ask you to demonstrate skills that are
covered in Activity 1.)
Graduate Student Assignment: grad students (in teams of 2
students) will prepare a 20 minute lecture – to teach the rest of the class about
an advanced topic.
Possible topics:
Security in SQL including SQL injection attacks (with a
demo)
PEAR DB Library (with a demo)
Datamapper (with a demo)
Other object-relational mapping approaches (with a demo)
Embedded SQL in Python or Ruby (with a demo)
SQLLite
(with a demo)
Recursion in SQL (with a demo)
Introduction to object-relational DBMSs (with a demo)
Overview of several join algorithms – with some sort of
visualization (could be PowerPoint or other slides or some other kind of
visualization).
Introduction to Datalog (also called a linear notation
for relational algebra in our book) (with a demo, if possible; see the
Wikipedia page for Datalog to see possible tools/systems).
students
are welcome to suggest additional topics
Two in-class, open-book tests.
Grade Computation
|
|
Undergraduates |
Graduates |
|
Each Assignment |
5 |
4 |
|
Total (6 Assignments) |
30 |
24 |
|
Grad Assignment |
n/a |
6 |
|
Test 1 |
20 |
20 |
|
Test 2 |
20 |
20 |
|
Each Demo Session |
5 |
5 |
|
Total (6 Demo Sessions) |
30 |
30 |
|
Total |
100% |
100% |
Policies
Demo sessions
will be conducted on the night scheduled. (See below for makeups; you must complete the make up within one week
of the originally scheduled demo unless there is some extraordinary
circumstance.)
Assignments are
due, as scheduled. (See below for makeups; you must complete
the makeup assignment within one week of the original due date for the
assignment unless is some extraordinary circumstance.)
Late demos and assignments. If you complete a demo or turn in an
assignment within one week after the scheduled due date, you will receive a 40%
penalty on your score. No demos or
assignments will be accepted more than one week late. Everyone has one “silver bullet”. This means that you are allowed to have one
late item without incurring the penalty.
Your second and all subsequent late items will incur the penalty. (See below if you have an emergency that
prevents you from doing your demo or turning in your assignment. If you have an emergency, the instructor may
grant you permission to take the demo or turn in the assignment without the
penalty and without using up your “silver bullet”.)
Makeup tests,
demos, and assignments will only be given in cases of medical,
personal, work-related, or other emergencies. If an emergency arises and
you need to reschedule, contact the instructors at your earliest possible
convenience.
Requests for regrading must be submitted to the instructor in writing within one
week of the time the graded assignment was made available for pickup. You
must be specific in saying why you feel your answer deserves additional
credit. A request for regrade may result in a
re-evaluation of the entire assignment and your total grade may increase or
decrease as a result.
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-4150. More information is available at: http://www.pdx.edu/uasc/drc.html
Academic
Integrity
The PSU Student Code of Conduct http://www.pdx.edu/dos/codeofconduct
will be enforced for this class.
Students have the responsibility to be familiar with the code and to
behave in accordance with the code.