CS 417 Course Materials
Database and Transaction Systems
Spring 2021

General

• Syllabus.
• Integrity in My Computer Science Courses.

Assignments

Written Assignments

• E-R/EE-R Diagrams. Due Feb. 23.
• Relational Model. Due Mar. 9.
• SQL; Due Mar. 16.
• Relation Normalization. Due Apr. 8.
• Transactions. Due Apr. 20.
• Transactions Lab I. Due Apr 22.
• Transactions Lab II. Due Apr. 27.

Semester Project

• Group formed. Due Feb. 16. 0% of project grade.
• Proposal. Due Feb. 26. 15% of project grade.
• E-R design and relational schema. Due Mar. 12. Dia User Manual 15% of project grade.
• Formal specification. Due Mar. 26. Use Cases. 20% of project grade.
• Sample end-to-end application. Due Apr. 28. Demonstrated during the following class. 20% of project grade.
• Project completion and freeze, and report completion. Due May 11. 30% of project grade.
• Final project and report presentation. Presented during the Final Experience on Thursday, May 15th from 3:00 pm to 5:00 pm EDT, prior to Exam II. 0% of project grade.

Topics

• Introduction and Background
  Feb. 9:
  Read Chapters 1 and 2.
  Notes, Question Set 1, Question Set 1 helpful figures.

• The Entity-Relationship Model
  Feb. 11 and 16:
  Read 3.1--3.8.
  Notes and Questions I, Exercise Solution.
  Read Feb. 5: 3.9--3.13.
  Notes and Questions II, Exercise Solution.
  Cardinality Label Differences Between E-R and EE-R Diagrams.

• Flask Tutorial
  Feb. 18 and 23:
  Before class, read the Flask Quickstart. Pay close attention to these sections:
  • A Minimal Application
  • Routing (and subsections)
  • Rendering Templates
  • Accessing Request Data (and subsections)
  • Sessions
  Also, be aware of the resource links at the end of this page.
  Flask Tutorial
  A Little List app. This app only works if you're on Goucher's network, and not on GoucherGuest. The easiest way to run this app is from a NoMachine connection to phoenix.

• The Relational Model
  Feb. 25 and Mar. 2:
  Read Chapter 4, and On Making Relational Division Comprehensible. See slides 1--18 and watch Division Screencast. Also, Division in Wikipedia. Faculty Who Have Taught All Courses Example.
  Mapping Guidelines.
  Notes and Questions.

• SQL
  Mar. 4 and 9:
  Mar. 4: read 5.1--5.4.
  Mar. 9: read 5.5--5.8.
  Be aware of the SQL/PostgreSQL resources below, particularly the documentation for PostgreSQL.
  SQL Exercise, Solution
  univDDL.sql, univDat.sql
  University E-R diagram

• Project
  Mar. 11
  E-R design and relational schema.

• Practice with a Simple Flask Database-Backed App
  Mar. 16 and 18:
  The following WSGI applications will only work on Goucher's internal wired and wireless networks (i.e., not GoucherGuest).

  dbdemo.py, text of dbdemo.py, text of login.html, text of logout.html, text of query.html, text of result.html.

  Standalone Python program demonstrating database queries: test.py, test.py output
  NOTE: See PostgreSQL and SQL Resources Section below for how to avoid SQL injection attacks.

  Exercise: Modify the following todo app to add this functionality:

  • Works with multiple users. Add registration and login functions. Each user should have their own todo list.
  • At a minimum, the users table will have to have username and password hash attributes. It will ease things if you set the table up so that it automagically generates a unique user id.
  • Only store hashed passwords. Only store hashed passwords. Only store hashed passwords. See the python code under the Welcome banner at Passslib to see how simple this is to do.
  • The todo table has a boolean done attribute. Use it to mark a task done. A user should be able to either mark an active task done or to delete it.
  • Add the capability of displaying either the active tasks or the completed tasks.
  • Add whatever additional functionality that time permits. For example, you could allow users to assign priorities or due dates to tasks. Tasks could be displayed sorted on a particular attribute of the user's choosing.

  todo.py, text of todo.py, text of todo.html, schema for original todo table.

  If necessary, use these SQL commands to drop the original todo table and todo id sequence from your database:

        drop sequence todo_id_seq;
        drop table todo;
        

  Look below for resources.

• Exam 1
  Mar. 23
  Exam 1 Structure. Exam 1 Solution.

• Database Normalization
  Mar. 25 and Apr. 1:
  Mar. 25: read 6.1--6.5
  Apr. 1: read 6.6--6.10
  Normalization Levels and Normalizing a Relation.
  Notes and Questions I. Solution I.
  Notes and Questions II. Solution II.

• PL/pgSQL and Triggers
  Apr. 6 and 8:
  Read Apr. 6: 5.5.2 and 5.8.1 from the textbook. But, be aware that this material isn't specific to PostgreSQL. It would be better for you to read Overview of Trigger Behavior and to be aware of, and use, PL/pgSQL - SQL Procedural Language in the PostgreSQL documentation. Also, see Introduction to PL/pgSQL and Triggers, which will give you a quick overview of PL/pgSQL and triggers in PostgreSQL.
  PostgreSQL Triggers Lab I
  • triggerLab1.sql (downloadable), triggerLab1.sql (viewable)
    
  • triggerLab1 Solution.sql (downloadable), triggerLab1 Solution.sql (viewable)
  PostgreSQL Triggers Lab II
  • triggerLab2.sql (downloadable), triggerLab2.sql (viewable)
    
  • triggerLab2 Solution.sql (downloadable), triggerLab2 Solution.sql (viewable)

• Transactions
  Apr. 13, 15, and 20:
  Read 9.1--9.8
  These readings are PostgreSQL specific, see Transactions and Transaction Isolation. Psycopg2 specific, see Python PostgreSQL Transaction Management using Commit and Rollback.
  Notes and Questions I, refer to SQL Commands, for the reading assignment.
  See pgGroupDbs for the PostgreSQL group accounts to use for the labs. Look in Canvas for the username and password to use to access this file.
  Transactions Lab I movies.sql
  Transactions Lab II accounts.sql

• Project
  Apr. 22 and 27
  Sample end-to-end application. Demo-ed on the 29th.
  
  
• Project
  Apr. 29 and May 4 and 6
  Finish.

• Exam 2
  May 13, 3:00 pm EDT
  Exam 2 Structure.

PostgreSQL and SQL Resources

• PostgreSQL Documentation
• SQL Tutorial
• Psycopg 2 documentation
• Basic Psycopg 2 Module Usage
• Preventing SQL Injection Attacks with Python
• Passlib

Python Resources

• Python 3 Quick Reference Card.
• Think Python.
• Python 3 Documentation.

Flask Resources

• Flask.
• Flask Quickstart, an overview of Flask.
• Be aware --- database queries return an iterable object, not a list. To get a list, wrap the return value in a call to list():

     todos = list(db.query('select * from todo order by id asc;'))
     

  This is useful if you want to iterate through the list more than once; an iterable object can be iterated through only once.

Jinja2 Resources

• Jinja.
• Primer on Jinja Templating.

HTML and CSS Resources

• The Bare Bones Guide to HTML.
• HTML Tutorial.
• CSS Beginner Tutorial.
• CSS Tutorial.