CS 417 Course Materials
Database and Transaction Systems
Spring 2023

General

• Syllabus.
• Integrity in My Computer Science Courses.

Assignments

Written Assignments

• E-R/EE-R Diagrams. Due Feb. 14.
• Relational Model. Due Feb. 28. Problem 5 E-R diagram; you might find this clearer than the diagram in the PDF.
• SQL; Due Mar. 9. Solution
• Relation Normalization. Due Apr. 25.
• Transactions. Due May 4.
• Transactions Lab I. Due May 9.
• Transactions Lab II. Due May 11.

Semester Project

• Group names. Due Feb. 16. 0% of project grade.
• Proposal. Due Feb. 28. 15% of project grade.
• E-R design and relational schema. Due Mar. 14. Dia User Manual 15% of project grade.
• Formal specification. Due Mar. 28. 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 17th from 3:00 pm to 5:00 pm EDT, prior to Exam II. 0% of project grade.

Project Pages

• Abou and Romaan
• Anthony and David
• Evan and John
• Gabe and Kai
• Rahel and Kalayha
• Luka and K
• Xavier
• Jack and Alex

Topics

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

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

• Flask Tutorial
  Feb. 9 and 14:
  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. 16 and Feb. 21:
  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, Exercise Solution.

• SQL
  Feb. 23 and 28:
  Feb. 23: read 5.1--5.4.
  Feb. 28: 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. 2
  E-R design and relational schema.

• Practice with a Simple Flask Database-Backed App
  Mar. 7 and 9:
  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. 14
  Exam 1 Structure.

• Database Normalization
  Mar. 16 and 28:
  Mar. 16: read 6.1--6.5
  Mar. 28 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
  This material was skipped.
  Mar. 30 and Apr. 4:
  Read Mar. 30: 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. 6, 11, and 13:
  Read 9.1--9.8 (3rd ed.) or 11.1--11.8 (4th ed.)
  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.
  Transactions Lab I movies.sql
  Transactions Lab II accounts.sql

• Project
  Remainder of semester

• Exam 2
  May 17, 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.