Case Study: Relational Algebra Expressions for an Ordering System
Group Submission Requirements
• Deadline: September 29, 2024
• Group Size: 3 members per group
• Submission: Each group member must submit the same PDF copy of the answers. Ensure the names of
all group members are placed at the top of each page.
Scenario
A company maintains an ordering system with the following database schema:
• Customers: (customer_id, name, city)
• Orders: (order_id, customer_id, order_date)
• Products: (product_id, product_name, price)
• OrderDetails: (order_id, product_id, quantity)
The company wants to retrieve important insights from the database to manage customers, orders, and
product inventories efficiently.
Sample Data:
Tasks:
Task 1: Write the Relational Algebra Expression
For each of the following 10 problems, write the relational algebra expression that accurately retrieves the necessary
information from the database.
1. List all customers from New York.
2. Find all orders placed on '2024-01-15'.
3. List the names of products that were ordered.
4. Retrieve the name of the customer who placed order 101.
5. Find the total quantity ordered for the product 'Smartphone'.
6. Find the names of customers who placed an order for 'Laptop' after '2024-01-12'.
7. Find all customers who placed an order for more than $1,000 worth of products in total.
8. Retrieve the list of products that were ordered by customers from New York and Miami.
9. Find customers who have placed orders for both 'Laptop' and 'Smartphone'.
10. Find all customers who have not ordered any products.
Task 2: Provide the Result Relation
For each of the problems in Task 1, simulate the relational algebra query on the provided sample data and provide the
result relation (i.e., the resulting table).
Submission Instructions:
• The deadline for this case study is September 29, 2024.
• Each group will consist of 3 members. All members of the group must submit the same PDF copy of the answers.
• Ensure that the names of all group members are clearly placed at the top of each page.
• Submit the answers in PDF format. Make sure the relational algebra expressions and result relations are neatly
formatted.
Example Layout for the PDF:
Page 1 (Example Layout)
Group Members:
• Member 1: Name (Student ID)
• Member 2: Name (Student ID)
• Member 3: Name (Student ID)
Problem 1: Retrieve the name of the customer who placed order 104.
Relational Algebra Expression:
Result Relation:
name
David Brown
Grading Rubric:
The case study will be evaluated based on the following criteria:
Criteria Points Description
Relational Algebra 40
Correctness of the relational algebra expressions for all 10 problems.
Accuracy points
40
Result Relations Accuracy of the resulting tables (relations) based on the provided sample data.
points
Clarity and 10 Neatness, clarity of presentation, and correct formatting of relational expressions and
Formatting points tables.
10 Each member will rate the collaboration of all group members (including themselves)
Group Collaboration
points based on contribution, teamwork, and communication.
Total: 100 points
Group Collaboration Rating Instructions:
Each group member is required to provide a rating for each member (including themselves) on a scale of 1 to 10, based
on the following criteria:
• Contribution: How much effort each member put into the task.
• Teamwork: How well each member collaborated with the group.
• Communication: How well each member communicated during the project.
The ratings will be averaged for the final score.
Example:
• Member 1's Ratings:
Member 1: 9, Member 2: 8, Member 3: 7
Average Score for Member 1: (9+8+7)/3 = 8
