Scribd
Upload
12 views2 pages

hw1 Spring25

This document outlines Homework 1 for CS 353 Spring 2025, due on February 13. It includes three main questions focusing on relational algebra queries related to a university system schema, validity of relational equations, and relational algebra expressions involving employee, department, and project relations. Each question specifies tasks to be completed for points, with detailed requirements for each query.

Uploaded by

mehmetanil3361
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
12 views2 pages

hw1 Spring25

This document outlines Homework 1 for CS 353 Spring 2025, due on February 13. It includes three main questions focusing on relational algebra queries related to a university system schema, validity of relational equations, and relational algebra expressions involving employee, department, and project relations. Each question specifies tasks to be completed for points, with detailed requirements for each query.

Uploaded by

mehmetanil3361
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

CS 353 Spring 2025

Homework 1
Due: February 13, Thursday till midnight
You will use the Moodle course page for submission of this assignment

Q.1 [63 pts, 7 pts each] Given the following relational schema for a university system:
Students(s-id, s-name, s-department, s-year)
Courses(c-id, c-name, c-department, credits)
Enrollments(e-id, s-id, c-id, e-semester, e-year)
s-id is a foreign key to Students, c-id is a foreign key to Courses
Instructors(i-id, i-name, i-department)
Teaches(i-id, c-id, t-semester, t-year)
i-id is a foreign key to Instructors, c-id is a foreign key to Courses

For each of the following queries, give an expression in Relational Algebra.


(a) List the id and name of the CS instructors who are teaching 4-credit courses of the MATH
department in Spring 2025.
(b) List the id and name of the 3rd year CS students who have taken CS353 in Fall 2024.
(c) List the id and name of the CS students who are not taking any courses of the EE
department in Spring 2025.
(d) List the id and name of the CS students who are taking all the 4-credit courses of the
MATH department in Spring 2025.
(e) Find the number of courses taught by each instructor from the CS department in Spring
2025.
(f) List the id and name of the CS instructors who are teaching more than 2 courses in Spring
2025.
(g) Find the number of students enrolled in each course of the CS department in Spring 2025.
(h) Find the course (c-id) with the maximum number of students in Fall 2024.
(i) For each department find the course (c-id) with the maximum number of students in Fall
2024.

Q.2 [27 pts, 3 pts each] Given relations R and S that have the same schema. Determine
whether each of the following equations is valid. If an equation does not hold, provide a
counterexample to demonstrate its invalidity.
(a) σƟ(R U S) = σƟ(R) U σƟ(S)
(b) σƟ(R Ո S) = σƟ(R) Ո σƟ(S)
(c) σƟ(R - S) = σƟ(R) - σƟ(S)
(d) σƟ(R U S) = σƟ(R) U S
(e) σƟ(R Ո S) = σƟ(R) Ո S
(f) σƟ(R - S) = σƟ(R) – S
(g) ∏L(R U S) = ∏L(R) U ∏L(S)
(h) ∏L(R Ո S) = ∏L(R) Ո ∏L(S)
(i) ∏L(R - S) = ∏L(R) - ∏L(S)
Q.3 [10 pts, 2.5 pts each] Consider the following relations.

EMPLOYEE DEPARTMENT

PROJECT

Determine the resulting relation instance for each of the following Relational Algebra
expressions.
(a) ∏Pno, Budget, Dname((σSalary < 2000 (EMPLOYEE) DEPARTMENT) PROJECT)
(b) ∏Ename, Pno((EMPLOYEE σDname=’shoe’(DEPARTMENT)) σBudget < 25000(PROJECT))

(c) Dno sum(salary) as Total-salary ((σMgrno ≠ 106(DEPARTMENT) EMPLOYEE))

(d) Dno sum(salary) as Total-salary (σMgrno ≠’Eno(DEPARTMENT EMPLOYEE))

You might also like