CS 307 - Using and Designing Databases
Prof Lin Jensen
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.
- Assignment 1, make an E/R model. Due
by Friday September 16, 2022 -
An E-R model
- Assignment 2, due
Friday, 23 September 2022
. Relational schema from an E-R model, "The soccer
league" Libre Office version (not updated)
- Assignment 3, due
Friday, 7 October 2022
SQL for the soccer league
- Assignment 4 -
due Friday 14 October 2022.
- Describe and design the database you want.
- Assignment 5
Not to hand in
Look at the EXPLAIN of a few queries in Relational Algebra.
- Practice Assignment - not
to hand in.
- 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.
- Lab 0, September 20 See instructions
- Lab 1, September 27 See instructions The Ships database
- Lab 2, 4 October See instructions
- Lab 3, 11 October Aggregate functions
- Lab 4, 18 Octobber -
Setting up your
own database (see Assignment 4)
- Lab 5, 25 October - Concurrency and transactions: Gringott's Wizard's Bank. You must be
- 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
- Lab 7, 22 November - client
program to print one order. (select query)
- Lab 8, 20 March -
Look at EXPLAIN of some queries.
- 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
- Fruitstand create table code, class example
- Procedural language
- 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
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.
- more examples on Osiris
Back to Lin Jensen