CS 307 - Using and Designing Databases

Prof Lin Jensen

Course Outline

Videos replacing lectures from the Fall 2020 asynchronous class

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 15, 2023 - An E-R model
2. Assignment 2, due Friday, 22 September 2023 . 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, 6 October 2023
   SQL for the soccer league
4. Assignment 4 - due Friday 13 October 2023. - 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 in December - NOW
   

Midterm Exam Friday Oct. 20, 2023

Current labs - labs meet on Fridays, due by the next Wednesday.

0. Lab 0, September 15   See instructions 
1. Lab 1, September 22   See instructions  The Ships database possible answers
2. Lab 2, 6 October   See instructions  possible answers
3. Lab 3, 13 October   Aggregate functions
4. Lab 4, 20 Octobber - Setting up your own database (see Assignment 4)
5. Lab 5, 3 November - Concurrency and transactions: Gringott's Wizard's Bank. You must be present! (11:30am)
   
6. Lab 6, 10 & 17 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.
7. Lab 7, 24 November - client program to print one order. (select query)
8. Lab 8, 20 March - Look at EXPLAIN of some queries.
   
9. Lab 9, Dec. 1 - Due Thursday December 7, 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

• Fruitstand create table code, class example
• Procedural language examples:
  • A trigger function to guard the bank vault, goblin()
    
  • A function to transfer money, xfer(amount, debit, credit) for the bank concurrency lab, explained
• Frutstand "SuperMario's"
  • SQL definition
  • Terminal interfaces to make an order are on linux.ubishops.ca
    All of them prompt for your name, give a list of fruits and prices, and instruct you on how to order. When asked for a fruit, press enter to finish.
    All programs are named fruitstand.
    Using these languages:
    • C or C++ -- fruitstand.c is source, fruitstand-c is executable.
    • php - you can view the code, or execute it: ./fruitstand.php
    • python - you can view the code, or execute it: ./fruitstand.py
  • Web form is on osiris fruitform.php - see the code
  • ... and receive the order: fruitorder.php - see the code
• XSL stylesheet for XML and a DTD  -- can be embedded in an iframe in HTML
  

PostgreSQL Documentation at www.postgresql.org in particular, SQL Commands

PHP Documentation at www.php.net in particular PostgreSQL

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

Some Notes and Examples:

• Using php for forms, including databases (a csc103 note) and more details
• Bridal register example.  For the sources, browse to /home/COURSES/CSC207/bridalregister on linux
  

1. more examples on Osiris

Back to Lin Jensen