Homework 2
This week's exercise is to write a variety of relational algebra expressions. You must produce a pdf using LaTeX for your responses. I have provided a LaTeX template (below) for expedience, although if you prefer to write your own LaTeX (or, for that matter, TeX) from scratch, you may.
In the course of working through these exercises, you will discover for yourselves that ⋈ is very useful indeed (and, as a bonus, resembles a tie fighter).
In the definitions that follow, certain concessions have been made to simplify the expressions you must write. Specifically, relation schemas are all represented by single capital letters, field names are unique throughout the schemas in which they appear, and (as a corollary to the last point) it is obvious how foreign keys connect to other relations by their names alone. You cannot, unfortunately, expect to enjoy these conveniences in practice (with real databases).
In your responses, you may use s to name a relation instance of schema S, p to refer to P, and so on. Capital letters are also acceptable. Since the notation will be unambiguous in either case, use what you prefer. There is a LaTeX macro rel in the template to handle the italics of relation names, and also macros for operations like natural join.
Schema A: National Parks Here are relation schemas for states (as in United States), parks, and park rangers, in that order. Primary keys are underlined like this, foreign keys are underlined like this.
- S (sID, sname, area_sqMi, pop)
- P (pID, sID, pname, visits_per_year)
- R (rID, lname, ssn, pID)
Write relational algebra expressions to compute the following:
- A1. The names of all parks with fewer than 100 visits per year.
- A2. The social security numbers of the rangers who work at "Yellowstone".
- A3. The last names of all rangers who work at parks in states with populations less than 2 million.
Schema B: Musical Recordings The following relations represent songs, people, tracks (where a track is a particular instance of recording a song), and instruments, in that order; then, in two join tables, they represent who (co-)wrote which song and who played what instrument on which track. The underlining conventions for keys are the same as above.
- S (sID, sname)
- P (pID, lname, fname)
- T (tID, sID, date)
- I (iID, iname)
- W (sID, pID)
- R (tID, pID, iID)
Write relational algebra expressions to compute the following:
- B1. The last names of the songwriters of "Yesterday".
- B2. The names of all songs where one of the songwriters was "Richard" "Rodgers".
- B3. The names of all songs on which at least one musician played "flute".
- B4. The last names of all musicians in the database who played "guitar" on any track.
- B5. The first and last names of all musicians who played "tuba" on a track where they themselves wrote or co-wrote the song.
This is the LaTeX template: relational-algebra.tex
This work is due by Thursday April 23 at 11:59pm. Please submit all TeX/LaTeX sources (which may be one file), a Makefile, and a pdf named exactly hw2.pdf in a directory hw2 in your repository.