The DataBase Course

Exercise No. 3

SQL and Relational Calculus - Practical

Due on January 12th by midnight

The Example Database Schema: Animals participate in research projects, that may span several years and sites in many countries. Animals may participate in many projects, and may also be moved between sites during the same project. The children table keeps parenthood relations between animals, by keeping the animal id of each parent and child.

Animal(aid, species, weight, birthyear)
Children(parent, child)
Sites(sid, name, country, size, climate)
Research(rid, name, subject)
Events(aid, sid, rid, year)

The aid, sid, rid attributes are key attributes. The tables are available to query using Oracle, after you have executed the file ~db/animals.sql (see the "SQL Basics" page in the homepage).

Express the following queries in safe tuple relational calculus and SQL:

  1. Which animals are younger than a12?
  2. Which projects whose subject is medicine are performed entirely in China?
  3. Which animals were born in a year during which no project was using Canada's eastern coral reef?
  4. Get all pairs of animal ids of animals that participate in the same project. Include only one of every (a,b) and (b,a) pair.
  5. For which species of animals there are animals that have joint children with more than one other animal?
  6. Name species in which animals never become parents before they are 20 years old.
  7. Name projects that moved an animal between different climates in consecutive years.
  8. Get project numbers of projects that use the same animal in all the sites.
  9. When (in which year) did the Rainforest Insects project start?
  10. What is the average age of the animals in the Golden Bear project? Only an SQL query is required for this query.

You should submit: