ECE385H1 Chapter Notes -Food Bank, Metar, Query Language

44 views3 pages
Published on 12 Apr 2013
School
UTSG
Department
Electrical & Computer Engineering
Course
ECE385H1
Professor
Page:
of 3
Assignment 1
CSC343 Introduction to databases
Fall 2008
Submission instructions
This assignment is due Monday October 6th at 1 pm in the CSC343 drop-box in BA2210 (south-west corner).
Assignments must be typed, pages numbered and stapled together (no paper-clips or envelopes). If you are
working in a team of two, submit just one assignment (otherwise, the assignment with the lower mark will
be considered.
We won't accept late work, but if you have a valid reason for not being able to turn your work in on
time, contact your instructor as soon as possible with documented evidence of your reason, and we'll work
to make sure you aren't penalised for events beyond your control
Introduction
These exercises are based on a schema created for a course project by a DCS undergraduate, for an existing
foodbank in the GTA. The foodbank allows impoverished clients who cannot obtain sucient food from
their income to withdraw some additional food from the bank. A client may withdraw food or milk up to the
cash value specied in vouchers issued either to them, or to another client who is their provider (often
a family breadwinner). relationships between dependents and providers must be recorded, as well as
the voucher history of any transactions changing the balance of milk or cash on their voucher. The
foodbank must keep track of staff who issue vouchers, and client's addresses are recorded independently
of clients.
You should consult material from lecture slides, on-line Postgres SQL documentation, and Chapter 3 of
the textbook.
Data definition
Examine the schema in foodbank_schema.txt. You may experiment with these DDL statements in your
own database following the instructions to connect to your own postgres database at:
http://www.cdf.toronto.edu/~csc343h/fall/postgres.shtml
Once you have connected to your own database, you may read in the statements in foodbank_schema.txt:
csc343h-<your userid>=> \i foodbank_schema.txt
1
. . . provided foodbank_schema.txt is in your current directory.
Describe the action(s) caused by the actions below. When actions follow from integrity constraints, say
why you think the DBA specied those constraints.
1. Change a sta member's password.
2. Delete a record for a sta member.
3. Delete a record for a client who is a provider.
4. Change an existing client's primary phone number.
5. Change a client's address.
6. Change an address record from \Street" to \Crescent."
7. Add a new dependent for an existing provider.
Comment on the following.
1. Why do you think the DBA decided to make addresses unique? Why are all the elds except unit_ext
required to be NOT NULL? How does the UNIQUE constraint help?
2. Does allowing NULL values for \deceased," or phone numbers in the foodbank.client table make
sense? Explain.
3. Does the foodbank schema allow a client to have two or more vouchers at the same time? Explain.
4. Why do you think the DBA specifed \ON DELETE NO ACTION" in the FOREIGN KEY clause of
voucher that references staff?
5. Are there any constraints for which there seems to be no plausible explanation?
Relational Algebra
For the following questions, please refer to foodbank_schema.txt. These will give you practice formulating
queries in terms of the set operations of Relational Algebra, without having to worry (yet!) about how these
should be expressed in a query language such as SQL. Be sure you understand the structure of the database
before answering the questions.
1. Find the name and address of clients with a voucher for at least $900, issued by vcarro.
2. Find the name of sta who issued vouchers for clients who share an address with at least one other
client.
3. Find the name and address of clients who have had no transactions in the voucher history.
4. Find the name and login of sta who have issued fewer than three vouchers.
5. Find the name and phone number of clients who have a voucher with a cash balance of more than
$5000 and don't share an address with any other client, or have a voucher with a milk balance of more
than $2000.
6. Find the name and login of any sta (one or more) who have issued a voucher with the highest cash
balance.
2
Basic SQL
For the following questions, please refer to the foodbank schema dened in foodbank_schema.txt. You'll
need to know how to formulate basic SQL queries (Chapter 5) of the form:
SELECT attribute1 [as a1], attribute2 [as a2], ...
FROM table1 [as t1], table2 [as t2], table3 [as t3], ...
WHERE predicate1 (AND|OR) predicate2 (AND|OR) predicate3 ...
Write SQL queries for the following:
1. The rst two questions from the last section.
2. Find the name and phone number of clients with a cash balance of at least $3000 and a credit for milk
of at least $500.
3

Document Summary

This assignment is due monday october 6th at 1 pm in the csc343 drop-box in ba2210 (south-west corner). Assignments must be typed, pages numbered and stapled together (no paper-clips or envelopes). If you are working in a team of two, submit just one assignment (otherwise, the assignment with the lower mark will be considered. These exercises are based on a schema created for a course project by a dcs undergraduate, for an existing foodbank in the gta. The foodbank allows impoverished clients who cannot obtain sucient food from their income to withdraw some additional food from the bank. The foodbank must keep track of staff who issue vouchers, and client"s addresses are recorded independently of clients. You should consult material from lecture slides, on-line postgres sql documentation, and chapter 3 of the textbook. You may experiment with these ddl statements in your own database following the instructions to connect to your own postgres database at: http://www. cdf. toronto. edu/~csc343h/fall/postgres. shtml.