This is my first ever post, so not entirely sure where to post it so I thought I'll start here.
I'm relatively new to DB's and relational ones at that, so my question may seem a little stupid.
I'm creating a database for my university project and at present I creating the conceptual model along with a relational representation and a domain of discourse all of which appears to be ticking along, that is until I get to the contraints on relations and how I present them.
The DB is a event booking system, where staff book holidays and managers book sickness, training, emergency holiday etc.
My problem is this...I need to do is prevent no more than 2 TeamLeaders being off on holiday at the same time, but I keep hitting a brick wall or my relational expression appears for too long to make sense.
I can join 2 relations in Staff and Event to show all TeamLeaders who have booked Holiday. However, I want to prevent more that 2 being off at the same time, if they book the same Holiday Event (AD01) which is Annual Leave for a dayshift, if they are in the same Deparment for the same date. This also needs to prevent overlaps where between start dates and finish dates.
This is what I've achieved so for by the following expression.
constraint (TeamLeaderHoliday alias (project (select Staff where Position = ‘TeamLeader’) over StaffNo, Position )join (project (select Event where Reason = ‘Holiday’ )over EventCode, StaffNo, StartDate, FinishDate, Reason, DepartmentCode)) is empty
Which I hope will produce something along these lines.