Results 1 to 6 of 6
  1. #1
    Join Date
    Apr 2010
    Posts
    3

    Unanswered: A query for reserving seats

    Assume that i want to register online for an exam and after registration is complete, applicants will receive an entrance ID card.
    To do so i have considered the following tables:
    Days(day_id,day_name)
    Schools(school_id, school _name)
    Times(time_id,time_name)
    Exame(exam_id,day_id, school _id,time_id,seat,student_id) --exame_id is identity
    seats(day_id, school _id,time_id,seat,taken)
    The exam will be held in a couple of days, and capacity of each school for each time is 10 students. Seats will be filled by DAY,SCHOOL and TMIE respectively. For example:
    Day1, school1, time1; 10 students.
    Day1, school1, time2; 10 students.

    Day1, school1, last time; 10 students.
    Day1, school2, time1; 10 students.
    Day1, school2, time2; 10 students.
    Day1, school2, last time; 10 students.
    And so on.
    The SEATS table indicates weather seats are taken or not. To enter input i have used the following algorithm:



    insert into seats(day_id, school_id, time_id, seat)

    SELECT day_id, school_id, time_id,



    ROW_NUMBER() OVER(ORDER BY day_id, school_id, time_id, i) AS id

    FROM days

    CROSS JOIN schools

    CROSS JOIN times

    CROSS JOIN(SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 5 UNION ALL

    SELECT 6 UNION ALL

    SELECT 7 UNION ALL

    SELECT 8 UNION ALL

    SELECT 9 UNION ALL

    SELECT 10) AS d(i)



    Now to get the next empty seat i need to run this query:



    select top(1) * from seats where taken=0 order by

    day_id,school_id,time_id,seat



    so far there is no problem, now i want to add the capacity field to the school table and i do not know what query should be used to fill the seats table.
    Ill appreciate your help.

    example:

    Assume that the data in tables are this:
    Days
    day_id day_name
    1 sunday 2010/03/23
    2 Tuesday 2010/03/25
    ------------------------------------
    Schools
    school_id school_name
    1 school1
    2 school2
    ----------------------------------
    Times
    time_id time_name
    1 9 - 10
    2 10 - 11
    -------------------------------------
    If all seats reserved ,the exam table must be like this :
    Exam

    Day_id School_id Time_id Personal_id
    1 1 1 1
    1 1 1 2
    1 1 2 3
    1 1 2 4
    1 2 1 5
    1 2 1 6
    1 2 2 7
    1 2 2 8
    2 1 1 9
    2 1 1 10
    2 1 2 11
    2 1 2 12
    2 2 1 13
    2 2 1 14
    2 2 2 15
    2 2 2 16

    ---------------------------------
    Consider data in tables days,schools,times , now seats table must be like this.
    Day_id School_id Time_id seat taken
    1 1 1 1 0
    1 1 1 2 0
    1 1 2 3 0
    1 1 2 4 0
    1 2 1 5 0
    1 2 1 6 0
    1 2 2 7 0
    1 2 2 8 0
    2 1 1 9 0
    2 1 1 10 0
    2 1 2 11 0
    2 1 2 12 0
    2 2 1 13 0
    2 2 1 14 0
    2 2 2 15 0
    2 2 2 16 0
    -----------------------------------------
    When a clients wants to register ,we must figure out where we can register him?!!so we must find out the first free seat by this query:

    select top(1) * from Places where taken=0 order by dayid,schoolid,timeid,seat

    and then we use this information for insert into exam table and we update the seats table for new taken seat.(set taken=true)
    now,the problem is I dont now how to fill seats table(a query for initialize table [if we add the capacity field to the schools table])

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mahdi87_gh View Post
    Days(day_id,day_name)
    Times(time_id,time_name)
    this always kills me

    a table for day names, and somehow there is a perceived need to obfuscate them by assigning a surrogate key

    let me guess, the id for sunday is 1, the id for monday is 2...

    also, the time... what's wrong with actually using the time instead of giving each time a surrogate key?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Apr 2010
    Posts
    3
    no, please look at the sample i used above.
    the table days content is not like 1,sunday 2,monday,....

    assume that we want to perform exam in two days,one 2010/03/23 and other 2010/03/29

  4. #4
    Join Date
    Feb 2007
    Posts
    15
    Hello mahdi87_gh,

    Personally this is a very interesting challenge, I will take a look and try to help you.

    By the way r937, for the very next time just read all the message until the end before criticize someone's question.

    All the best.

    OS

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    yeah, thanks for reminding me that sometimes i do slip up slightly

    i should have said that using a surrogate key for a date (not a day name) is silly

    just use the date!!!

    the surrogate key is still useless
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Apr 2010
    Posts
    3
    Personally this is a very interesting challenge, I will take a look and try to help you.
    i'll appreciate your help
    and i look forward hearing from you soon.

Posting Permissions

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