European Experts Exchange (EEE.org) :: Free Experts PHP MySql Oracle Microsoft Web SQL Delphi


Databases :: Oracle :: Best way to populating database tables with given cconstraints
By: Dave	Date: 19/02/2007 02:41:24	Points: 120	Status: Answered Quality : Excellent
I am creating a database that will hold fake timetable information. First I will give a bit of information about the database I wish to create: * There are 40 rooms that I need to create a timetable for. * Each 'period' on the timetable will last 1 hour, there will be 9 periods per day (9AM - 6PM) in each room. It will be a 5 day timetable (Monday - Friday). * 40 Rooms x 9 periods per day x 5 days = 1,800 timetable slots. * Each time-slot will have a 'Class group' and a 'lecturer' occupying the room at that time. What I want to do is figure out the best way to populate these tables given the following constraints: * A class group can only be in one room at a time. * A lecturer can only be in one room at a time. * A room may only be occupied by one lecturer and class group at a time (i.e.. two or more lecturers/class groups may not be in the same room at the same time). Has anybody got any advice on how to do this? I was thinking of using excel to create the insert statements but after plenty of messing around I couldn't get anywhere. Other options might be using PL/SQL or maybe Java. I need a bit of advice on the best way populate the tables and a bit of guidance on how to go about it. Hope somebody can help, Dave
By: VGR	Date: 19/02/2007 09:22:13		Type : Answer
homework for school's classical "lecteurs et rédacteurs" problem :D it seems to me from reading you that you already have an algorithm to "solve" the problem because you are already at the "create inserts statements" part. Am I right ? if yes, then it's not difficult to make a program in you favourite language, or a webserver's script - PHP is recommended - to implement that algorithm and populate the database (or create the insert statements in a text file or on screen) if no, then it's a matter of keeping track at a given moment of the state you are in. You have N lecturers, 40 rooms, 1800 timeslots, M class groups, 45 periods. I would roughly think like this : for each period and until all class groups are placed do repeat reset index arrays get the first class room available, memorize () link to the first class group still unallocated, memorize () link to the first unallocated lecturer out of the N available, memorize () loop () get out on exception : not enough lecturers, not enough rooms, etc = famine = resources starvation this is rough, I don't have the ability to exhumate my old lectures on the subject ;-) PS I'm sure you can find the answer on google.

By: Dave	Date: 04/03/2007 13:47:24		Type : Comment
Thanks for your help VGR, I ended up using Java because it's the language I most familiar with.

By: noriegaa	Date: 04/06/2007 01:51:07		Type : Comment
I strongly disagree with attempting to resolve this problem via solely the front-end, since you cannot handle transactions as you should all the way. What you really need to do is to write one or more store procedures and functions or possibly a small package through which you use those functions and procedures to apply your logic, i.e., the hierarchy imposed by your business rules as described above, with the appropriate exception handlers in each case and scenario. You can populate your entry-point procedure via your CGI-interface and make the all when appropriate which should succeed and fail based on your setting. If this is done in steps, by constraint-based levels, you could also do so, but always rely for constraints on the back-end, since the front interface will usually lead to database inconsistencies. Best regards, Anthony Noriega, OCP, MSCS, MBA, BSSE

Do register to be able to answer

©2010 These pages are served without commercial sponsorship. (No popup ads, etc...). Bandwidth abuse increases hosting cost forcing sponsorship or shutdown. This server aggressively defends against automated copying for any reason including offline viewing, duplication, etc... Please respect this requirement and DO NOT RIP THIS SITE.
Please DO link to this page! Post to Plurk Post to Yahoo Buzz Post to Delicious Post to Digg Post to MySpace Post to Ping.fm Post to Reddit Post to StumbleUpon

Contact

page generated in 297.990080 milliseconds

Why Google AdSense ads ?

New Improved Search!