Bishop's coat of arms

CS 307 - Using and Designing Databases

Prof Lin Jensen

Course Outline

Winter 2020 class

Winter 2020 class, it went on-line abruptly!

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 17, 2021 - An E-R model
  2. Assignment 2, due Friday, 24 September 2021 . Relational schema from an E-R model, "The soccer league" Libre Office version (not updated)
    possible answers not available until later!
  3. Assignment 3, due Friday, 8 October 2021
    SQL for the soccer league
  4. Assignment 4 - due Friday 22 October 2021. - 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 Later

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

  1. Lab 0, Monday September 20   See instructions 
  2. Lab 1, Monday September 27   See instructions  The Ships database possible answers
  3. Lab 2, Monday 4 October 2021   See instructions  possible answers
  4. Lab 3, Monday 18 October 2021   Aggregate functions
  5. Lab 4, Monday November 1, 2021 - Setting up your own database (see Assignment 4)
  6. Lab 5, 21 February, 2019 - Concurrency and transactions: Gringott's Wizard's Bank. You must be present! (2:30pm)
  7. Lab 6, Momdays 8 & 15 November 2021 - 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, Monday 22 November 2021 - 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 9, 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