CS4750 - Database Systems

POTD 6: Relational Algebra

Due 28-Mar-2024 30-Mar-2024, 12pm/noon EST

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 3-4 other students 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

Find the badges and names of all elves who work in a workshop named "NorthStar"
Find the badges and names of all elves who work at the "NorthStar" or "EastStar" workshops
Find the number of elves each supervisor supervises. Display the supervisors' badges along with the number of elves.
Consider all elves who work in the "NorthStar" workshop. Find the badges of their supervisors.
Consider all elves who work in the "NorthStar" workshop. Find their supervisors' badges and names
List the badges of all supervisors who supervise at least 3 elves.
Find the information of all toys built in "NorthStar" or "EastStar" workshops
Find the information of all toys built in "NorthStar" and "EastStar" workshops
Find the information of all toys built in "NorthStar" but not in "EastStar" workshops
Find the elves who work in the same workshop as their supervisors. Display the elves' badges and their supervisors' badges.
Find the number of evaluations each rater has rated. List the names of the raters along with the number evaluations.
[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]

Grading rubric

[Total: 10 points]: Done (or provide evidence of your attempt, full or reasonable effort)

(5 points) — Providing evidence of your attempt, minimal effort

(-2.5 points) for 24 hours late (submitted after 28-Mar-2024 12pm/noon EST, by 29-Mar-2024 12pm/noon EST)
(-5 points) for 48 hours late (submitted after 29-Mar-2024 12pm/noon EST, by 30-Mar-2024 12pm/noon EST)

[27-Mar] Per our conversation in class, we decided to move the due date.
(-2.5 points) for 24 hours late (submitted after 30-Mar-2024 12pm/noon EST, by 31-Mar-2024 12pm/noon EST)
(-5 points) for 48 hours late (submitted after 31-Mar-2024 12pm/noon EST, by 1-Apr-2024 12pm/noon EST)

Submission

[optional] Take a selfie (or picture) of your team and submit it with your POTD
Include all team member names and computingIDs in the report.
Submit your RA queries
You may do one of the following:
- Write on the paper and take screenshot(s) of your POTD, or
- Type and save your POTD as a .pdf file — No Word document.
Each team submits only one copy.
Upload your report to POTD 6 on Gradescope. Make sure you connect your partner to your group on Gradescope so that everyone receives credit.
Please verify that your POTD is accessible. Making your submission available to instructors and course staff is your responsibility; if we cannot access or open your file(s), we have to assign a zero grade. Be sure to test access to your file(s) before the due date.

CC-BY-NC-SA 4.0 license.
Last updated 2024-03-26 16:41