Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2014
    Posts
    3

    Unanswered: Help with query for school project please!

    Hello, could anyone help me with a query for my school project?

    The database represents the campus housing, and I need help with a query that assigns students to newly available rooms, and then updates the waiting list.

    Once a Student graduates(indicated by GraduationDate), he/she will be unassigned the RoomID which then makes that room available in the Room table.

    The next student on the waiting list would then be assigned the available room, and the waitlist number for that student will either be be updated to a null value (easier, and keeps the wait list column static) or can be dynamically updated(not sure how to do)

    I would very much appreciate any help. Thanks!

    http://i57.tinypic.com/314bdw1.png

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why would you need to update a waiting list. isn't this derived data (ie you can alwasy find persons who haven't got a room allocated.

    assiging a waiting list number, which presumably be re calculated every time you get a vacancy seems a waste of resources.

    if you know when (the date and time) a student registers for housing then you can generate a sequence from that

    I suppose if you must have a stored waiting lkist number then you could run two update queries.. the first assigns NULL to the most recently assigned student, and then another which decreases the waiting list index by 1
    ..or just decrement your index, and ignore any rows which are 0 or less.
    I guess you could do that using a trigger

    the problem of managing what ius derived data is that its always a risk if something happens to your data that doesn't 'fit' with your conceptual model. say soemone alters the data throiugh another process but doesn't run the update query. THre are times when you do store derived data (usually for performance reasons or if its other data masquerading as derived) but I don't think this is such a circumstance
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2014
    Posts
    3
    Thanks for the response, I don't need to update it necessarily, i just need a way to assign students a room based on availability.

    When a student GraduationDate=TodaysDate, I need a query to assign their room to the next student on the waiting list.

    I am at a loss at how to do this.

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Why would you need to know that?
    ..if you go down that path it means that it will only work if the process is run ONLY on the graduation date. Far better to process rows where there is a graduation date AND a room still allocated, assuming that you cannot register a graduation date ahead of that date.
    That comes down to your tables
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2014
    Posts
    3
    I was thinking about three queries:

    1) Assign student a 'null' value for RoomID if GraduationDate <= Date()

    2) Then I need a query that assigns the recently unallocated room to the first student on the waiting list

    3) Then the final query would assign null values to ApplicationDate and WaitlistNum because they are now assigned a room.

    http://i61.tinypic.com/do8ntc.png
    http://i61.tinypic.com/1pxzyg.png
    http://i59.tinypic.com/2wnwcwi.png

    This is an extremely introduction course to database management and we learned concepts without much practical use(which is a shame), so im hoping someone can help me with the query language. Thank you very much

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what have you tried

    If this was a normalised database (and Im assuming you are being taught about normalisation) then I'd expect a single table per entity type

    which offhand would be soemthing like, a table for
    students
    room
    students rooms (a table identifying what students are in what room), a so called intersection table)

    how do you determine the priority for geting a room
    ..based on what
    when the student applied, does the time of the application matter, if so you probably need to use the now function, ratter than the now function
    how do you propose to resolve ties in priority if, say only 1 room is available

    because its writing a query, then you need to get your table design sorted out. whether you do an ERD for this is upto you, and presumably the requirements of your coursework. Not every db design needs an ERD but if you are at all sketchy on whatrs re3quired then its worth doing for small designs. Its almost mandatory if you have got multiple designers or the system is complex
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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