Given the follow ing table definition:
event(event-id, course-id, sem-id, day, duration, reg)
which represents recurring events in some university (sem_id is semester id, reg is registered students for this event), find the course which which has exactly $2$ recurring events throughout the same semester and day of week and the number of registered students for one of the $2$ events is at least $2$ times larger than the number of the registered students for the other event. The result should contain the course_id and event_id of the event with larger number of registered students and the $event_id$ with smaller number of registered students.
I need to come up with a query based on tuple relational calculus.
I thought of using an equivalent of $3$ cartesian products of event in order to filter the courses that have exactly $2$ events: $$ \bigg\{t(course-id, event-id) | \\\exists r1\in event\bigg(r1[event-id]=t[event-id]\land r1[course-id]=t[course-id]\\\land\lnot\exists r2, r3\in event\bigg(\bigg[r1[event-id]\neq r2[event-id]\land r2[event-id]\neq r3[event-id]\bigg]\\\lor\bigg[r1[event-id]=r2[event-id\bigg]\\\land \bigg[r1[sem-id]=r2[sem-id]\land r2[sem-id]=r3[sem-id]\bigg]\\\land\bigg[r1[day]=r2[day]\land r2[day]=r3[day]\bigg]\\\land\bigg[r1[reg]\ge2\cdot r2[reg]\lor r2[reg]\ge 2\cdot r1[reg]\bigg]\bigg)\bigg)\bigg\} $$
In this query I check that there're no situations when $3$ event ids are different that is the course has more than $2$ recurring events. I also check that there're no situations where $r1[event-id]=r2[event-id]$ which means that the events have the same id and therefore do not contribute to the overall count (which should be $2$). Lastly, I check that $r1,r2,r3$ semesters and days are the same (so that all the comparisons are correct) and that either $r1[reg]$ is at least more than double than $r2[reg]$ or the other way around.
I'm new to tuple calculus and really not sure whether my query is correct.
I'm going to rewrite the whole query using this notation for simplicity : $E(e,c,s,d,dur,reg)$.
$$ \left\{r(c,e_1,e_2)\left\lvert\exists r_1,r_2\in E\left( \array{r.e_1=r_1.e\ne r_2.e=r.e_2 \wedge r.c=r_1.c=r_2.c \\\ \wedge \forall r_3\in E(r_3.c\ne r.c\vee r_3.e=r.e_1\vee r_3.e=r.e_2)\\ \ \wedge r_1.d=r_2.d\wedge r_1.s=r_2.s\wedge r_1.reg\ge 2\ r_2.reg} \right)\right.\right\} $$