CS 330
Homework 2
Th Sept. 16, 1999
Due: Tu Sept 21 by 9:00am (before class)

Self-test exercises

 I am not grading ALL of these exercises (only those which you want me to look at, by explicitly asking me). But you should do them, since similar ones will be on your first midterm exam. Also, you cannot do the final project without knowing how to do these queries. Please form study groups and work out these exercises together. If you are in doubt about a non-trivial exercise or have questions, please ask me to look at your solution, or bring up the question in class.

Textbook Ch.2 p. 35, ex. 1-10 (already mentioned on Tuesday).
Textbook Ch. 8, p. 177, ex. 8.2, 8.3, 8.4, 8.5. Do only what pertains to Relational Algebra (do not do Relational (tuple) or Domain calculus).

Required Homework

Part 1: Theory

  1. Consider the following relational database:

    employee(employee-name, street, city)
    works(employee-name, company-name, salary)
    company(company-name, city)
    manages(employee-name, manager-name)

    For each of the following queries, give an expression in the relational algebra.
    1. Find the names of all employees who work for First Bank Corporation.
    2. Find the names and cities of residence of all employees who work for First Bank Corporation.
    3. Find the names, street address and city of residence of all employees who work for First Bank Corporation and earn more than $10000 per year.
    4. Find the names of all employees in this database who live in the same city as the company for which they work.
    5. Find the names of all employees who live in the same city and on the same street as do their managers.
    6. Find the names of all employees in this database who do not work for First Bank Corporation.
    7. Find the names of all employees who earn more than every employee of Small Bank Corporation.
    8. Assume the companies may be located in several cities. Find all companies located in every city in which Small Bank Corporation is located.
  2. With reference to the banking database (you all have a handout with it): write a query in relational algebra to find the name and city of of residence for all customers who have a loan at the bank.
  3. Using the bank database, write relational algebra query to find out those accounts which are held by at least two customers.

Part 2: Access

Implement a small database in Access corresponding to the Donations for Charity database you have designed in Hw1. Careful, use the feedback I gave you on the previous design. Hint: your database should have two tables, Donors and Donations, with a common field, donor_id. Simplify the format of the address (e.g., use only the city name) and of the date (e.g. use only the month): that's all you'll need for the time being.

Create a relationship linking the donor_id fields in the two relations. Make sure that you understand in which table is the donor_id a primary key and where it is a foreign key. Enforce referential integrity.

Create simple forms for entering the data and fill in the tables with a few records (about 5-6 records per table).

Design the following queries:
  1. Find all donors who live in Amherst.
  2. Find the id of all donors who donated in March.
  3. Find the names of all donors who donated in March.
  4. Find the cities where all the donors who donated in March live.


Make sure you have entered enough data so that these queries do not give null answers :-)

Create reports based on these queries.

Using the Help menu, read about Creating an application. Create a main switchboard form with buttons for forms and reports. Think about the various types of users who might be using these forms and reports, and what type of permissions you should give them to the information in the database. You will explain the decisions about security in a short addendum to the homework (handwritten or, if in electronic form, please make sure you give me a printed copy).

To turn in:
  1. Handwritten (but, please, legible) answers to the exercises assigned in Part 1.
  2. An Access database on a diskette for Part 2.
  3. A short handwritten or printed page with whatever explanations you want to accompany your design of the Access part of this homework (tables, queries, relations, forms) and with a few lines about the security that you want to be enforced on your database.
Ileana Streinu