Home » SQL & PL/SQL » SQL & PL/SQL » examination seating plan (10g, XP, Dev6i)
examination seating plan [message #444808] Wed, 24 February 2010 05:33 Go to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Hi All

Can any one share with me a logic.

Currently we are making examination hall seating plan in ms excel. which takes a lot of our time having cut and paste. I am just making my mind How to accomplish this task directly from database.

basic details are as following.
exam hall capacity 400
each row having 10 students.

Quote:
scenario 1:

If there are two papers on the same time having students no <=400 then get students seated in alternate rows for both papers having 10 students in each row.


scenario 2:

If there are more than 2 papers on the same time having students no <= 400 then

a. Take students from any two larger courses
use scenario 1 unless students in one paper are seated.

b. Take students from 3rd large course + the remaining students of other paper(in a) and get them seated.

I am sorry I can not copy any work around because I am thinking (at this stage) just about the logic.( I might use something like nested loops and insert into a temp table the values )

please help me in this regard and any one give me just a LOGIC which I should follow.

Thanks in advance
Riaz

[Updated on: Wed, 24 February 2010 05:45]

Report message to a moderator

Re: examination seating plan [message #444825 is a reply to message #444808] Wed, 24 February 2010 06:18 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
10 studnes in each row ,OK how many row are there in exam ?

I assume 400/10 = 40 !! so 40 rows ?

[Updated on: Wed, 24 February 2010 06:20]

Report message to a moderator

Re: examination seating plan [message #444932 is a reply to message #444825] Wed, 24 February 2010 21:16 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Yes. there are maximum 40 rows.




Riaz

[Updated on: Wed, 24 February 2010 21:53]

Report message to a moderator

Re: examination seating plan [message #444946 is a reply to message #444808] Wed, 24 February 2010 22:21 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
did you try any sql query / done homework ..any basic ideas you might want to come up with !!
Re: examination seating plan [message #444953 is a reply to message #444946] Wed, 24 February 2010 22:50 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
Basically I am consulting with you people before drawing any map. I havent tried any sql.etc. However, i think it can be solved as below (my logic, may not be fine enough)

Quote:

step1:
create a table SEATINGPLAN having 40 cols

step2:
get date and courses from DATESHEET table.
(there may be more than one course on the same date)

step3:
get each student registration no and course code from student registered courses table(SRC) for the first course(paper)for the first date.

step4.
Insert into SEATINGPLAN with appropriate values using PLSQL.


Am I going in the right direction?

However, this is my logic...as I said earlier. I am seeking your with a more sophisticated logic. May some one help me solve this problem by supplying me a logic using only SQL.

Thanks
Riaz

[Updated on: Wed, 24 February 2010 23:42]

Report message to a moderator

Re: examination seating plan [message #444997 is a reply to message #444953] Thu, 25 February 2010 03:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
A more versatile design would be to have a table where each row holds the allocation for a single seat. SOmething like:
CREATE TABLE seat_allocation
(id             number(*,0)
,seat_row       number(*,0)
,seat_column    number(*,0)
,exam_paper_id  number(*,0)
,student_id     number(*,0));


Re: examination seating plan [message #445034 is a reply to message #444997] Thu, 25 February 2010 05:45 Go to previous messageGo to next message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
would I be able to produce report like attachment, from this design?

Riaz


  • Attachment: 001.jpg
    (Size: 628.98KB, Downloaded 221 times)
Re: examination seating plan [message #445035 is a reply to message #445034] Thu, 25 February 2010 05:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many of us can't or don't want to download stuff.
So post it in text with format as explained in OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel
Re: examination seating plan [message #445095 is a reply to message #445035] Thu, 25 February 2010 21:30 Go to previous message
rzkhan
Messages: 345
Registered: March 2005
Senior Member
I currently design it in Excel as below.(excluding other formatting)

HM211, CS211
dd/mm/yyyy


Row1		Row2		Row3		Row4		RowN	
Reg.No	Course	Reg.No	Course	Reg.No	Course	Reg.No	Course	Reg.No	Course
2005212	HM211	2007101	CS211	2005212	HM211	2005212	CS211	2005212	HM211
2005910	HM211	2007102	CS211	2005910	HM211	2005910	CS211	2005910	HM211
2006048	HM211	2007103	CS211	2006048	HM211	2006048	CS211	2006048	HM211
2006070	HM211	2007104	CS211	2006070	HM211	2006070	CS211	2006070	HM211
2006112	HM211	2007105	CS211	2006112	HM211	2006112	CS211	2006112	HM211
2006218	HM211	2007106	CS211	2006218	HM211	2006218	CS211	2006218	HM211
2009239	HM211	2007107	CS211	2009239	HM211		-	2009239	HM211
2006251	HM211	2007108	CS211	2006251	HM211		-	2006251	HM211
2006298	HM211	2007109	CS211	2006298	HM211		-	2006298	HM211
2006309	HM211	2007110	CS211	2006309	HM211		-	2006309	HM211






I have to start with the next row (for the next course) if number of students ends before end of row.


Would simple SQL query be able to accomplish this task? if not then I would go with the logic I explained earlier.

But still waiting for more ideas. Razz


Thanks

Riaz


Previous Topic: PL/SQL Logic
Next Topic: Selecting only part of a string
Goto Forum:
  


Current Time: Sat Dec 03 18:05:10 CST 2016

Total time taken to generate the page: 0.07366 seconds