CS 448/648 Assignment #1

1. Using (a) relational algebra, (b) tuple relational calculus, and (c) QBE, answer queries 2, 4, 6, 8, and 10 of question 4.5 from Ramakrishnan and Gehrke's text.
2. Translate queries 1, 3, and 5 from question 4.4 in Ramakrishnan and GehrkeÇs text into (a) tuple relational calculus and (b) QBE.
3. Consider the relational database used in the course notes (see Slides 2-5 and 2-7). Translate the following queries on that database into relational algebra:
(a){ t | exist e (e in Emp and t[Ename] = e[Ename] and
exist w (w inWorks and t[Resp] = w[Resp] and e[Eno] = w[Eno] and w[Dur] > 12) ) }
(b){ <p,r> |Ý exists n,b (<n,p,b> in Proj and exists e,d (<e,n,r,d> in Works and (d > 12 or b > 200000 ) ) ) }
(c) { t | exists p (p in Proj and t[Pname] = p[Pname] and
            exists e (e in Emp and t[Ename] = e[Ename] and
            exists w (w in Works and w[Pno] = p[Pno] and w[Eno] = e[Eno] and
            forall v ( (v in Works and v[Resp] = w[Resp] ) implies v[Dur] less-or-equals w[Dur] ) ) ) ) }


(where the keywords exists, forall, in, and, or, implies, and less-or-equals represent corresponding symbols that are not printable in pure HTML).
 

4. (a) Use an Entity-Relationship Diagram to depict the following enterprise. Explain any additional assumptions you make and list any aspects you were not able to depict.

• A TV program, identified by name, has a genre (comedy, news, etc.), an intended audience (children, teens, etc.), and a rating.
• An actor/actress, identified by name, has a date of birth and a sex.
• Similarly, a director, who may or may not be an actor/actress is identified by name and has a date of birth and a sex.
• If a TV channel, identified by number, is independently owned (i.e., it is not part of a network such as CBC or NBC), then it has an owner and a city. Otherwise it has a network affiliation and a city.
• Some actors/actresses have exclusive contracts with one network (such as CBC or NBC).
• A TV program can appear in one or more time slots on one or more channels (including several time slots on one channel or several channels at one time).
• Each TV program has exactly one director.
• An actor/actress can star in one or more TV programs, and arbitrarily many actors and actresses can star in a TV program.