Bishop's coat of arms

CS 307 - Using and Designing Databases

Prof Lin Jensen

Course Outline

Fall 2022 class

Fall 2022 class

Videos replacing lectures from the Fall 2020 asynchronous class

View in this approximate order
The first assignment will cover E/R modelling (Chapter 4).We will consider the relational model, upon which SQL is based, (Ch. 2) only after we understand Entities and Relationships.

This top-down approach will avoid the problems mentioned in Ch. 3. We will then proceed with SQL, the database language.

Written assignments

  1. Assignment 1, make an E/R model. Due by Friday September 16, 2022 - An E-R model
  2. Assignment 2, due Friday, 23 September 2022 . Relational schema from an E-R model, "The soccer league" Libre Office version (not updated)
    possible answers
  3. Assignment 3, due Friday, 7 October 2022
    SQL for the soccer league
  4. Assignment 4 - due Friday 14 October 2022. - Describe and design the database you want.
  5. Assignment 5 Not to hand in Look at the EXPLAIN of a few queries in Relational Algebra.
  6. Practice Assignment - not to hand in.
  7. Solution to Practice Assignment - Available NOW

Midterm Exam Friday Oct. 21, 2022

Open book, but no electronics.

Current labs - labs meet on Tuesdays, due on Friday.

  1. Lab 0, September 20   See instructions 
  2. Lab 1, September 27   See instructions  The Ships database possible answers
  3. Lab 2, 4 October   See instructions  possible answers
  4. Lab 3, 11 October   Aggregate functions
  5. Lab 4, 18 Octobber - Setting up your own database (see Assignment 4)
  6. Lab 5, 25 October - Concurrency and transactions: Gringott's Wizard's Bank. You must be present! (1:30pm)
  7. Lab 6, 8 & 15 November - Open a store, collect orders and maintain inventory (how many items are in stock). You will need a rule or trigger, and a function, say, to process an arriving shipment.
  8. Lab 7, 22 November - client program to print one order. (select query)
  9. Lab 8, 20 March - Look at EXPLAIN of some queries.
  10. Lab 9, Nov. 29 & Dec. 6 - Due Thursday December 8, that being the "last day of classes.".
      Second client program: To modify your database - May be in C, C++, Python, or PHP
    Write another program to allow a client to enter an order. (insert and/or update statements). Client should identify her or himself, and client (or clerk) see a list of things that can be ordered. Orders to be entered into the database. (or the equivalent). This can be a GUI or web application if you wish.

Notes and Examples

PostgreSQL Documentation at in particular, SQL Commands

PHP Documentation at in particular PostgreSQL

Likewise, documentation for other programming languages can be found on the net, including Perl, Python, and Java

Some Notes and Examples:

Example web application, a Bridal Register, is on osiris.
  1. more examples on Osiris

Back to Lin Jensen