Activity: Relational Algebra (Santa)

(No submission)

---

Purpose:

• Be familiar with relational algebra
• Practice writing and interpreting relational algebra database queries
• Get ready to work on homework assignment and course project
• Prepare for exam 2

You may make a copy of a worksheet and complete this activity, or simply type your answers in any text editor.

You may work alone or with at most 3 other students (max size=4) in this course.

---

Consider the Santa_Workshop database. Refer to the Santa's workshop database scenario.

Elf(badge, name, wname)   
    –- FK: wname references Workshop(wname)
is_supervised(badge, sbadge)   
    –- PK: badge, an elf can have at most one supervisor, 
 a supervisor can supervise many elves
    –- FK: badge references Elf(badge)
    –- FK: sbadge references Elf(badge)
Workshop(wname, location)
Toy(toy_id, toy_name)
Built_in(toy_id, wname)
    –- FK: toy_id references Toy(toy_id)
    –- FK: wname references Workshop(wname)
Evaluation(badge, rater, rate_date, rating)
    -- FK: badge references Elf(badge)
    -- FK: rater references Elf(badge)
    -- assume rating is of type INT 

Write RA to solve the following problems

1. Find the badges and names of all elves who work in a workshop named "NorthStar"
2. Find the badges and names of all elves who work at the "NorthStar" or "EastStar" workshops
3. Find the number of elves each supervisor supervises. Display the supervisors' badges along with the number of elves.
4. Consider all elves who work in the "NorthStar" workshop. Find the badges of their supervisors.
5. Consider all elves who work in the "NorthStar" workshop. Find their supervisors' badges and names
6. List the badges of all supervisors who supervise at least 3 elves.
7. Find the information of all toys built in "NorthStar" or "EastStar" workshops
8. Find the information of all toys built in "NorthStar" and "EastStar" workshops
9. Find the information of all toys built in "NorthStar" but not in "EastStar" workshops
10. Find the elves who work in the same workshop as their supervisors. Display the elves' badges and their supervisors' badges.
11. Find the number of evaluations each rater has rated. List the names of the raters along with the number evaluations.
12. [Create your own problem, and write RA to solve it]

[Optional: For more practice, (1) write SQL to solve the problems, inspect and compare your SQL and RA; (2) try to solve as many problems & alternative ways as possible]

---

Copyright © 2025 Upsorn Praphamontripong
Released under the CC-BY-NC-SA 4.0 license.
Last updated 2025-04-07 12:48