Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2013

    Relational Theory, Help!!

    Hi Everyone,

    Having posted this to the new members side of the site I thought I'd try here as I've had no response or direction as to where to go.

    This is my first ever post, so not entirely sure where to post it.

    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.

    StaffNo Position EventCode StartDate FinishDate Reason DepCode
    12348 TeamLeader AD01 12/03/2013 12/03/2013 Holiday GGIn
    12347 TeamLeader AD01 12/01/2013 12/01/2013 Holiday GGIn
    12349 TeamLeader AD01 10/01/2013 12/01/2013 Holiday GGIn
    12347 TeamLeader AD01 12/03/2013 12/03/2013 Holiday HCler
    12345 TeamLeader AD01 12/03/2013 14/03/2013 Holiday HCler
    12342 TeamLeader AD01 14/03/2013 14/03/2013 Holiday HCler
    12341 TeamLeader AD01 23/07/2013 30/07/2013 Holiday HInd
    12341 TeamLeader AD01 23/07/2013 30/07/2013 Holiday HInd
    12346 TeamLeader AD01 23/07/2013 30/07/2013 Holiday HInd

    I hope someone can point me in the right direction.

  2. #2
    Join Date
    Jun 2003
    In MSSQL, you could create this constraint using an Indexed View.
    Your view would calculate the total number of managers off on any given day, and you'd put on a constraint to prevent this ever exceeding 1.
    If it's not practically useful, then it's practically useless.

    blindman "sqlblindman"

  3. #3
    Join Date
    Jul 2013
    Hi blindman,

    Thanks for your reply, but I need to write a relational expression before implementing it in a view, although I'll bear your comments in mind when I get to implementing it.


  4. #4
    Join Date
    Feb 2004
    In front of the computer
    The problem definition allows two TeamLeads to be off on the same day? Ouch!

    This is a very high level description just to give you the concept, I won't give you the exact details (you need to do some of the work yourself). The way that I'd approach this is:
    1. Add a calendar relation, defined to contain a tuple for every working day.
    2. Add an attribute to the HolidayRequest relation to indicate the sequence of the requests.
    3. Use the new sequence attribute to create a "FirstDibs" projection showing the first request for a team lead, by day (using the calendar), with department.
    4. Create a "SecondDibs" projection based on "FirstDibs" to show a second team lead.
    5. Constrain the insert based on the "SecondDibs" being empty for the department.

    There are more concise ways to do this, but those require some pretty advanced features and aren't appropriate for someone just learning how to do this kind of modeling.

    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Jul 2013
    Hi Pat,

    Thanks for your reply. It's made me think of things differently to how I was thinking.

    I've already got another relation which works the same as a calendar and has a relationship with the Event relation through the foreign key Date. Based on this I think I should be able to join another relation and project it slightly different to how I'm doing it now, but ensure Position TeamLeader < 3.

    In addition I've already got a relation called Quota to stipulates how many events can occur on a given date, so I think I'll be able to crack it.

    Thanks so much for your help.
    I really appreciate it.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts