database file for the current class diagram

[Nitin]

Hello Group,

We need to add constraints next..

I have put in some textual data for the employee class.
The rest is all numerical..
Feel free to make changes.

Ehtan, Thanks for coming up with the queries. We need them badly. Please double check these with the
system. Can you post the outputs.

Prashant, we need your excellent documentation skills now.

EJ, sorry for bothering you with my questions. We still need to  come up with a list of constraints.
We will need to refer to you for this.

I have a presentation this evening. And I will be putting in the whole of tomorrow for the project.


Cheers,
Nitin

[Prashant Moorthy]

Nitin,

Did u try opening the text file in MLPQ? It is giving me and error.

 

Rgds,

Prashant

 

begin %ABCstoresInc%
company(cm_id,location_num,branch_num) :- cm_id = 10001, location_num = 1 , branch_num = 50.
company(cm_id,location_num,branch_num) :- cm_id = 10002, location_num = 2 , branch_num = 51.
company(cm_id,location_num,branch_num) :- cm_id = 10003, location_num = 3 , branch_num = 52.
company(cm_id,location_num,branch_num) :- cm_id = 10004, location_num = 4 , branch_num = 53.
company(cm_id,location_num,branch_num) :- cm_id = 10005, location_num = 5 , branch_num = 54.
company(cm_id,location_num,branch_num) :- cm_id = 10006, location_num = 6 , branch_num = 55.
company(cm_id,location_num,branch_num) :- cm_id = 10007, location_num = 7 , branch_num = 56.
company(cm_id,location_num,branch_num) :- cm_id = 10008, location_num = 8 , branch_num = 57.
company(cm_id,location_num,branch_num) :- cm_id = 10009, location_num = 9 , branch_num = 58.
company(cm_id,location_num,branch_num) :- cm_id = 10010, location_num = 10 , branch_num = 59.
company(cm_id,location_num,branch_num) :- cm_id = 10011, location_num = 11, branch_num = 60.
company(cm_id,location_num,branch_num) :- cm_id = 10012, location_num = 12, branch_num = 61.
company(cm_id,location_num,branch_num) :- cm_id = 10013, location_num = 13 , branch_num = 62.
company(cm_id,location_num,branch_num) :- cm_id = 10014, location_num = 14 , branch_num = 63.
company(cm_id,location_num,branch_num) :- cm_id = 10015, location_num = 15 , branch_num = 64.
company(cm_id,location_num,branch_num) :- cm_id = 10016, location_num = 16 , branch_num = 65.
company(cm_id,location_num,branch_num) :- cm_id = 10017, location_num = 17 , branch_num = 66.
company(cm_id,location_num,branch_num) :- cm_id = 10018, location_num = 18 , branch_num = 67.
company(cm_id,location_num,branch_num) :- cm_id = 10019, location_num = 19 , branch_num = 68.
company(cm_id,location_num,branch_num) :- cm_id = 10020, location_num = 20, branch_num = 69.
company(cm_id,location_num,branch_num) :- cm_id = 10021, location_num = 21 , branch_num = 70.
company(cm_id,location_num,branch_num) :- cm_id = 10022, location_num = 22 , branch_num = 71.
company(cm_id,location_num,branch_num) :- cm_id = 10023, location_num = 23 , branch_num = 72.
company(cm_id,location_num,branch_num) :- cm_id = 10024, location_num = 24, branch_num = 73.
company(cm_id,location_num,branch_num) :- cm_id = 10025, location_num = 25 , branch_num = 74.

hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21001,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21002,  post_hr = 250,   map_hr = 180,  pub_hr = 70.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21003,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21004,  post_hr = 300,   map_hr = 180,  pub_hr = 120.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21005,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21006,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21007,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21008,  post_hr = 500,   map_hr = 180,  pub_hr = 320.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21009,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21010,  post_hr = 150,   map_hr = 120,  pub_hr = 30.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21011,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21012,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21013,  post_hr = 160,   map_hr = 140,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21014,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21015,  post_hr = 300,   map_hr = 180,  pub_hr = 120.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21016,  post_hr = 100,   map_hr = 80,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21017,  post_hr = 200,   map_hr = 110,  pub_hr = 90.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21018,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21019,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21020,  post_hr = 100,   map_hr = 80,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21021,  post_hr = 100,   map_hr = 80,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21022,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21023,  post_hr = 120,   map_hr = 100,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21024,  post_hr = 200,   map_hr = 180,  pub_hr = 20.
hourManager(hm_id,post_hr,map_hr,pub_hr) :- hm_id = 21025,  post_hr = 200,   map_hr = 180,  pub_hr = 20.


employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100001, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MANAGER",           salary = 120000,   applID = 40001.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100002, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",    salary = 75000,     applID = 40001.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100003, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",           salary = 120000,     applID = 40002.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100004, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",    salary = 75000,     applID = 40023.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100005, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 120000,     applID = 40012.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100006, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 120000,     applID = 40021.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100007, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "SR. MEM OF CONSULTING STAFF",  salary = 95000,     applID = 40041.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100008, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",     salary = 75000,     applID = 40012.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100009, emplType_num = 022,  emplStatus = "NOTANEMP", positon = "MANAGER",            salary = 120000,     applID = 40008.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100010, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",     salary = 75000,     applID = 40012.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100011, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 120000,     applID = 40021.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100012, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF TECHNICAL STAFF",     salary = 75000,     applID = 40014.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100013, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 120000,     applID = 40014.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100014, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",     salary = 75000,     applID = 40015.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100015, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MANAGER",            salary = 120000,     applID = 40016.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100016, emplType_num = 022,  emplStatus = "NOTANEMP", positon = "SR. MEM OF CONSULTING STAFF",  salary = 95000,     applID = 40017.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100017, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 120000,     applID = 40018.
employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100018, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",     salary = 75000,   applID = 40019.


schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43001, emp_id = 100001, shift_id = 5201, work_grp_num = 02, wk_day_num = 8001.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43002, emp_id = 100012, shift_id = 5202, work_grp_num = 06, wk_day_num = 8002.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43003, emp_id = 100003, shift_id = 5204, work_grp_num = 03, wk_day_num = 8003.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43004, emp_id = 100004, shift_id = 5205, work_grp_num = 05, wk_day_num = 8004.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43005, emp_id = 100016, shift_id = 5206, work_grp_num = 02, wk_day_num = 8005.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43006, emp_id = 100007, shift_id = 5207, work_grp_num = 03, wk_day_num = 8006.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43007, emp_id = 100008, shift_id = 5208, work_grp_num = 07, wk_day_num = 8007.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43008, emp_id = 100003, shift_id = 5209, work_grp_num = 03, wk_day_num = 8008.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43009, emp_id = 100005, shift_id = 5200, work_grp_num = 01, wk_day_num = 8009.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43000, emp_id = 100008, shift_id = 5202, work_grp_num = 02, wk_day_num = 8010.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43001, emp_id = 100012, shift_id = 5203, work_grp_num = 07, wk_day_num = 8011.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43002, emp_id = 100006, shift_id = 5205, work_grp_num = 02, wk_day_num = 8012.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43003, emp_id = 100002, shift_id = 5207, work_grp_num = 06, wk_day_num = 8013.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43004, emp_id = 100005, shift_id = 5203, work_grp_num = 02, wk_day_num = 8014.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43005, emp_id = 100014, shift_id = 5205, work_grp_num = 05, wk_day_num = 8015.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43006, emp_id = 100002, shift_id = 5203, work_grp_num = 02, wk_day_num = 8016.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43007, emp_id = 100018, shift_id = 5204, work_grp_num = 01, wk_day_num = 8017.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43008, emp_id = 100009, shift_id = 5205, work_grp_num = 02, wk_day_num = 8018.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43009, emp_id = 100005, shift_id = 5207, work_grp_num = 07, wk_day_num = 8019.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43000, emp_id = 100013, shift_id = 5209, work_grp_num = 02, wk_day_num = 8020.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43001, emp_id = 100002, shift_id = 5203, work_grp_num = 05, wk_day_num = 8021.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43002, emp_id = 100006, shift_id = 5206, work_grp_num = 02, wk_day_num = 8022.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43003, emp_id = 100012, shift_id = 5203, work_grp_num = 04, wk_day_num = 8023.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43004, emp_id = 100014, shift_id = 5207, work_grp_num = 02, wk_day_num = 8024.
schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43005, emp_id = 100001, shift_id = 5205, work_grp_num = 02, wk_day_num = 8025.

shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5201, sh_start = 0600, sh_end = 0900.
shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5202, sh_start = 0900, sh_end = 1200.
shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5203, sh_start = 1200, sh_end = 1500.
shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5204, sh_start = 1500, sh_end = 1800.
shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5205, sh_start = 1800, sh_end = 2100.
shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5206, sh_start = 2100, sh_end = 0000.
shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5207, sh_start = 0000, sh_end = 0300.
shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5208, sh_start = 0300, sh_end = 0600.

man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410001, post_hr = 200, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410002, post_hr = 250, wk_day_num = 8022.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410003, post_hr = 500, wk_day_num = 8019.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410004, post_hr = 300, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410005, post_hr = 150, wk_day_num = 8012.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410006, post_hr = 160, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410007, post_hr = 100, wk_day_num = 8015.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410008, post_hr = 120, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410009, post_hr = 110, wk_day_num = 8014.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410000, post_hr = 200, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410010, post_hr = 200, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410011, post_hr = 120, wk_day_num = 8011.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410012, post_hr = 200, wk_day_num = 8012.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410013, post_hr = 300, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410014, post_hr = 200, wk_day_num = 8023.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410015, post_hr = 250, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410016, post_hr = 160, wk_day_num = 8025.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410017, post_hr = 200, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410018, post_hr = 500, wk_day_num = 8015.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410019, post_hr = 300, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410020, post_hr = 160, wk_day_num = 8022.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410021, post_hr = 150, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410022, post_hr = 250, wk_day_num = 8024.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410023, post_hr = 500, wk_day_num = 8001.
man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410024, post_hr = 300, wk_day_num = 8026.

time_card(emp_id,start_time,end_time) :- emp_id = 100001, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100002, start_time =  0300, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100003, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100004, start_time =  0900, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100005, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100006, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100007, start_time =  0900, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100008, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100009, start_time =  0900, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100010, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100011, start_time =  1200, end_time = 1800.
time_card(emp_id,start_time,end_time) :- emp_id = 100012, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100013, start_time =  1200, end_time = 1800.
time_card(emp_id,start_time,end_time) :- emp_id = 100014, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100015, start_time =  0900, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100016, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100017, start_time =  1200, end_time = 2100.
time_card(emp_id,start_time,end_time) :- emp_id = 100018, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100009, start_time =  1200, end_time = 1800.
time_card(emp_id,start_time,end_time) :- emp_id = 100000, start_time =  0600, end_time = 1200.
time_card(emp_id,start_time,end_time) :- emp_id = 100001, start_time =  1200, end_time = 2100.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8001, emp_id = 100001, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8002, emp_id = 100002, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8003, emp_id = 100003, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8004, emp_id = 100004, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8005, emp_id = 100005, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8006, emp_id = 100006, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8007, emp_id = 100007, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8008, emp_id = 100008, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8009, emp_id = 100009, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8010, emp_id = 100010, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8011, emp_id = 100011, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8012, emp_id = 100012, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8013, emp_id = 100013, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8014, emp_id = 100014, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8015, emp_id = 100015, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8016, emp_id = 100016, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8017, emp_id = 100017, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8018, emp_id = 100018, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8019, emp_id = 100009, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8020, emp_id = 100002, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8021, emp_id = 100003, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8022, emp_id = 100012, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8023, emp_id = 100013, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8024, emp_id = 100014, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8025, emp_id = 100015, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8026, emp_id = 100016, reg_start_time = 0800, reg_end_time = 0600.
regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8027, emp_id = 100017, reg_start_time = 0800, reg_end_time = 0600.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8001, emp_id = 100001, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8002, emp_id = 100002, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8003, emp_id = 100003, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8004, emp_id = 100004, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8005, emp_id = 100005, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8022, emp_id = 100006, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8021, emp_id = 100007, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8013, emp_id = 100008, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8014, emp_id = 100009, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8015, emp_id = 100010, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8016, emp_id = 100011, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8017, emp_id = 100012, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8018, emp_id = 100013, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8019, emp_id = 100014, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8017, emp_id = 100015, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8012, emp_id = 100016, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8017, emp_id = 100017, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8014, emp_id = 100018, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8006, emp_id = 100019, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8004, emp_id = 100020, premium_st_time = 0600, premium_end_time = 0900.
premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8005, emp_id = 100021, premium_st_time = 0600, premium_end_time = 0900.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 701, t_start = 0900, t_end = 1200, wk_day_num = 8001.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 702, t_start = 1200, t_end = 1500, wk_day_num = 8002.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 703, t_start = 0900, t_end = 1200, wk_day_num = 8003.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 704, t_start = 1500, t_end = 1800, wk_day_num = 8004.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 705, t_start = 1200, t_end = 1500, wk_day_num = 8005.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 706, t_start = 1200, t_end = 1500, wk_day_num = 8006.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 707, t_start = 0900, t_end = 1200, wk_day_num = 8007.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 708, t_start = 0900, t_end = 1200, wk_day_num = 8008.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 709, t_start = 1200, t_end = 1500, wk_day_num = 8009.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 710, t_start = 1500, t_end = 1800, wk_day_num = 8010.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 711, t_start = 0900, t_end = 1200, wk_day_num = 8011.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 712, t_start = 0900, t_end = 1200, wk_day_num = 8012.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 713, t_start = 1500, t_end = 1800, wk_day_num = 8013.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 714, t_start = 1500, t_end = 1800, wk_day_num = 8014.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 715, t_start = 1800, t_end = 2100, wk_day_num = 8015.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 716, t_start = 0000, t_end = 0300, wk_day_num = 8016.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 717, t_start = 0000, t_end = 0300, wk_day_num = 8017.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 718, t_start = 2100, t_end = 0000, wk_day_num = 8018.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 719, t_start = 2100, t_end = 0000, wk_day_num = 8019.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 720, t_start = 2100, t_end = 0000, wk_day_num = 8020.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 721, t_start = 2100, t_end = 0000, wk_day_num = 8021.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 722, t_start = 1800, t_end = 2100, wk_day_num = 8022.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 723, t_start = 2100, t_end = 0000, wk_day_num = 8023.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 724, t_start = 0900, t_end = 1200, wk_day_num = 8024.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 725, t_start = 1200, t_end = 1500, wk_day_num = 8025.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 726, t_start = 1500, t_end = 1800, wk_day_num = 8026.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 727, t_start = 1500, t_end = 1800, wk_day_num = 8027.
empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 728, t_start = 1500, t_end = 1800, wk_day_num = 8001.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1200, emp_id = 100001, pyh_id =  24301.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1201, emp_id = 100002, pyh_id =  24302.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1202, emp_id = 100003, pyh_id =  24302.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1203, emp_id = 100005, pyh_id =  24301.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1204, emp_id = 100004, pyh_id =  24301.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1205, emp_id = 100006, pyh_id =  24301.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1206, emp_id = 100003, pyh_id =  24304.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1207, emp_id = 100006, pyh_id =  24305.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1208, emp_id = 100003, pyh_id =  24306.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1209, emp_id = 100006, pyh_id =  24302.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1210, emp_id = 100007, pyh_id =  24305.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1211, emp_id = 100004, pyh_id =  24306.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1212, emp_id = 100007, pyh_id =  24302.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1213, emp_id = 100002, pyh_id =  24304.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1214, emp_id = 100004, pyh_id =  24302.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1215, emp_id = 100011, pyh_id =  24306.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1216, emp_id = 100015, pyh_id =  24303.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1217, emp_id = 100014, pyh_id =  24307.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1218, emp_id = 100015, pyh_id =  24303.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1219, emp_id = 100013, pyh_id =  24310.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1220, emp_id = 100015, pyh_id =  24311.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1221, emp_id = 100013, pyh_id =  24314.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1222, emp_id = 100011, pyh_id =  24313.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1223, emp_id = 100012, pyh_id =  24311.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1224, emp_id = 100017, pyh_id =  24308.
payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1225, emp_id = 100018, pyh_id =  24309.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2400, emp_id = 100001, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2401, emp_id = 100002, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2402, emp_id = 100003, wk_hour = 8, premium_hr = 3.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2403, emp_id = 100004, wk_hour = 8, premium_hr = 6.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2404, emp_id = 100005, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2405, emp_id = 100006, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2406, emp_id = 100007, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2407, emp_id = 100008, wk_hour = 8, premium_hr = 3.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2408, emp_id = 100009, wk_hour = 8, premium_hr = 6.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2409, emp_id = 100010, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2410, emp_id = 100011, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2411, emp_id = 100012, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2412, emp_id = 100013, wk_hour = 8, premium_hr = 3.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2413, emp_id = 100014, wk_hour = 8, premium_hr = 6.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2414, emp_id = 100015, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2415, emp_id = 100001, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2416, emp_id = 100002, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2417, emp_id = 100003, wk_hour = 8, premium_hr = 3.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2418, emp_id = 100004, wk_hour = 8, premium_hr = 6.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2419, emp_id = 100005, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2420, emp_id = 100011, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2421, emp_id = 100012, wk_hour = 8, premium_hr = 0.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2422, emp_id = 100013, wk_hour = 8, premium_hr = 3.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2423, emp_id = 100014, wk_hour = 8, premium_hr = 6.
actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2424, emp_id = 100015, wk_hour = 8, premium_hr = 0.

premium_description(premium_id, prem_start_time, prem_end_time, description) :- premium_id = 1400, prem_start_time = 1800, prem_end_time = 2100, description = "PR SHIFT 1".
premium_description(premium_id, prem_start_time, prem_end_time, description) :- premium_id = 1401, prem_start_time = 2100, prem_end_time = 0000, description = "PR SHIFT 2".
premium_description(premium_id, prem_start_time, prem_end_time, description) :- premium_id = 1402, prem_start_time = 0000, prem_end_time = 0300, description = "PR SHIFT 3".
premium_description(premium_id, prem_start_time, prem_end_time, description) :- premium_id = 1403, prem_start_time = 0300, prem_end_time = 0600, description = "PR SHIFT 4".
premium_description(premium_id, prem_start_time, prem_end_time, description) :- premium_id = 1404, prem_start_time = 0600, prem_end_time = 0900, description = "PR SHIFT 5".

availability(enr_id, emp_id, pub_id) :- enr_id = 2200, emp_id = 100001, pub_id = 701.
availability(enr_id, emp_id, pub_id) :- enr_id = 2201, emp_id = 100002, pub_id = 702.
availability(enr_id, emp_id, pub_id) :- enr_id = 2202, emp_id = 100003, pub_id = 703.
availability(enr_id, emp_id, pub_id) :- enr_id = 2203, emp_id = 100004, pub_id = 712.
availability(enr_id, emp_id, pub_id) :- enr_id = 2204, emp_id = 100005, pub_id = 715.
availability(enr_id, emp_id, pub_id) :- enr_id = 2205, emp_id = 100006, pub_id = 701.
availability(enr_id, emp_id, pub_id) :- enr_id = 2206, emp_id = 100007, pub_id = 702.
availability(enr_id, emp_id, pub_id) :- enr_id = 2207, emp_id = 100008, pub_id = 703.
availability(enr_id, emp_id, pub_id) :- enr_id = 2208, emp_id = 100009, pub_id = 712.
availability(enr_id, emp_id, pub_id) :- enr_id = 2209, emp_id = 100010, pub_id = 715.
availability(enr_id, emp_id, pub_id) :- enr_id = 2210, emp_id = 100011, pub_id = 701.
availability(enr_id, emp_id, pub_id) :- enr_id = 2211, emp_id = 100012, pub_id = 702.
availability(enr_id, emp_id, pub_id) :- enr_id = 2212, emp_id = 100013, pub_id = 703.
availability(enr_id, emp_id, pub_id) :- enr_id = 2213, emp_id = 100014, pub_id = 712.
availability(enr_id, emp_id, pub_id) :- enr_id = 2214, emp_id = 100015, pub_id = 715.
availability(enr_id, emp_id, pub_id) :- enr_id = 2215, emp_id = 100016, pub_id = 702.
availability(enr_id, emp_id, pub_id) :- enr_id = 2216, emp_id = 100003, pub_id = 703.
availability(enr_id, emp_id, pub_id) :- enr_id = 2217, emp_id = 100004, pub_id = 728.
availability(enr_id, emp_id, pub_id) :- enr_id = 2218, emp_id = 100005, pub_id = 715.
availability(enr_id, emp_id, pub_id) :- enr_id = 2219, emp_id = 100006, pub_id = 722.
availability(enr_id, emp_id, pub_id) :- enr_id = 2220, emp_id = 100007, pub_id = 725.
availability(enr_id, emp_id, pub_id) :- enr_id = 2221, emp_id = 100008, pub_id = 724.
availability(enr_id, emp_id, pub_id) :- enr_id = 2222, emp_id = 100009, pub_id = 724.
availability(enr_id, emp_id, pub_id) :- enr_id = 2223, emp_id = 100010, pub_id = 721.
availability(enr_id, emp_id, pub_id) :- enr_id = 2224, emp_id = 100011, pub_id = 724.
availability(enr_id, emp_id, pub_id) :- enr_id = 2225, emp_id = 100012, pub_id = 725.
availability(enr_id, emp_id, pub_id) :- enr_id = 2226, emp_id = 100013, pub_id = 724.
availability(enr_id, emp_id, pub_id) :- enr_id = 2227, emp_id = 100014, pub_id = 724.
availability(enr_id, emp_id, pub_id) :- enr_id = 2228, emp_id = 100015, pub_id = 721.

end %ABCstoresInc%

[Nitin]

Hey Prashant,

It does not give me an error. Did you download the file, or you are copy pasting the text ?
I have zipped the same so that you can download it.

Thanks,
Nitin

[Prashant Moorthy]

The text file is missing from the ZIP. Please send the txt file again.

 

[Nitin]

Sorry about that !

Please let me know when this runs.

[Prashant Moorthy]

Thanks.. This is opening now...

[Prashant Moorthy]

Guys,

 

I have put some constraints to the database.

To view the constarints open the file in MLPQ

1) Click on Time Slot

2) Click on Regular Time

3) Click on Premium Time

4) Click on Time Card

5) Click on empty Slot

 

If u have any more ideas to put more constarints, pls let me know. I will add it to the file.

 

Rgds,

Prashant

 

[Prashant Moorthy]

Ethan,

 

Do u have the queries ready? Have u run them against the database??

 

If yes, please pass on the queries to me so that I an put it in the report.

[Nitin]

Great Job Prashant.

I am trying to come up with some constraints :

Can we have a constraint for
1. In hourManager table
post_hr = map_hr + pub_hr

2. In Employee table
At any given point there are at least 60% of the employees who
are not on Leave or 7 employees working for example.

3. For shift_pattern table
I have seen that sh_start and sh_end should be within 15mins .
How about adding the constraint of sh_start + 3 hrs = sh_end
To have a 3 hour shift.

4. For man_hour table
For any given wk_day_num . The man post_hr in man_hr should
not be less than say 100 hrs and greater than 550 hrs

5. I havenet specified this for regular time table..
Seems like the data is repeating itself .. for reg_start_time and reg_end_time. Can we have something like in winter months the regular start time is at 9AM but for summer it is 8AM.

6. Same as 5 for premium_time table.

7. For empty_slot table
t_end = t_start + 3

8. I am not really sure about this (EJ Please clarify)
  wk_hour + premium_hr = ( actual_hr )????

[Ethan]

Prashant,

Sorry for replying so late. It's because I had some problem
to run the queries in the MLPQ system. I finished most SQL
queries, and the zip file attached contains the SQL txt file
you can run on the MLPQ. It contains 4 basic queries, 4
aggregation queries, 4 set queries, and 1 nested queries(the
last one on the list). Here is the list of the queries:

1. Basic query
List all the empty slot:

List the ID, position, and salary of the employee who works
more than 30 hours:

List the empty slot on Wednesday:

How many man-hours do we need on Thursday:

2. Aggregation Query
List the average premium hours of the employees who work in
premium time:

List the number of the employee who work in the regular hour
on Friday:

List the average salary of the manager

List the total number of employees in the company

3. Set Query
Retrieve the employee id, position, and salary of the
employee who works on Friday

Select the employeeID and position who works both in regular
hours and premium hours

Find out the employeeID who works for more than 30 hours
totally or 15 premium hours

List the employeesID who provide availability, and whose
actually working hours is more than 40 hours.

4, Nested

List the working hours of the employee who are manager

List the schedule of the employee who works less than 20
regular hours

List the ID of the employee who has the most regular working
hours:

There's a problem that when I run the nested queries, the
MLPQ will shut down automatically. I don't know why and I'm
still trying to solve it. Besides, I also change the
wk_day_num attribute in the database, so that I can do some
queries base on the weekday. The database inserting file is
also include in the zip.

Best regards
Ethan

[Prashant Moorthy]

Guys,

Someone needs to write the description of the classes. Please let me know if you want to do it..

 

Ethan,

Could you send me the results of the SQL's??

 

 

Rgds,

Prashant

[Prashant Moorthy]

Ethan,

What abt Iconic Queries? Does someone know abt iconic queries??

 

Rgds,

Prashant

 

[Prashant Moorthy]

Guys,

 

I have completed the iconic queries.

 

The things still pending are:

1) Description of the classes used in the class diagram

2) 4 Nested SQL's

3) Results of all SQL's.

 

Please use the attached database file to run your queries and generate the output.

 

Please send this to me asap so that I can make the report and submit it to the Prof..

[Ogbeide Ikhile (EJ)]

I have the description of the class diagram ie the complete story and I will type and upload it(just few lines though).

I have being trying to draw some diagram to form a story, this MLPQ tool don't seems to be good enough but l will upload what I got too when I'm done

EJ

Prashant Moorthy <prashant...@gmail.com> wrote:

---

Blab-away for as little as 1¢/min. Make PC-to-Phone Calls using Yahoo! Messenger with Voice.

[Ogbeide Ikhile (EJ)]

Guys I have a brief on the complete history

Prashant Moorthy <prashant...@gmail.com> wrote:

---

Yahoo! Mail goes everywhere you do. Get it on your phone.

[Ethan]

Hi guys,

I modefied the database again, cause I think there maybe
someproblem in the data Prashant gave us.

1.In the payroll and the actual_hour table, it should only
have one tuple for each emp_id, isn't it? Why would any
employee have 2 payroll record? And the actual_hour means
the total working hours of the employee, so why would any
employee have 2 records?

2.I also changed the wk_day_num. 8001 means Monday, 8002
means Tuesday, ...., 8007 means Sunday.

3.In the man_hour table, I think we should have only one
record for each weekday, isn't it? If we have 2 different
man hour required on Monday, it's not right.

I attached the modefied file. Please let me know if there's
any problem. If everyone is ok with it, then I will run the
SQL queries on it and send the result to prashant. By the
way, does anyone try to make a nested query in MLPQ? The
same problem always happens to me which makes me so confused.

Best regards
Ethan

[Prashant Moorthy]

Ethan,

 

I just found that the constraints which we wrote should have a different name. Otherwise, the querires wouldn't run for some reason.

I have made the changes and run your queries and they are running fine.

I have put the output of the SQL's.

Attached is the latest MLPQ file.

 

Send me the nested queries once u are done.

 

Rgds,

Prashant

 

On 5/11/06, Ethan <li...@alumni.nccu.edu.tw> wrote:

company(cm_id,location_num,branch_num) :- cm_id = 10002, location_num = 02 , branch_num = 51.

company(cm_id,location_num,branch_num) :- cm_id = 10003, location_num = 03 , branch_num = 52.

company(cm_id,location_num,branch_num) :- cm_id = 10004, location_num = 04 , branch_num = 53.

company(cm_id,location_num,branch_num) :- cm_id = 10005, location_num = 05 , branch_num = 54.

company(cm_id,location_num,branch_num) :- cm_id = 10006, location_num = 06 , branch_num = 55.

company(cm_id,location_num,branch_num) :- cm_id = 10007, location_num = 07 , branch_num = 56.

company(cm_id,location_num,branch_num) :- cm_id = 10008, location_num = 08 , branch_num = 57.

company(cm_id,location_num,branch_num) :- cm_id = 10009, location_num = 09 , branch_num = 58.

company(cm_id,location_num,branch_num) :- cm_id = 10010, location_num = 10 , branch_num = 59.

company(cm_id,location_num,branch_num) :- cm_id = 10011, location_num = 11,  branch_num = 60.

company(cm_id,location_num,branch_num) :- cm_id = 10012, location_num = 12,  branch_num = 61.

company(cm_id,location_num,branch_num) :- cm_id = 10013, location_num = 13 , branch_num = 62.

company(cm_id,location_num,branch_num) :- cm_id = 10014, location_num = 14 , branch_num = 63.

company(cm_id,location_num,branch_num) :- cm_id = 10015, location_num = 15 , branch_num = 64.

company(cm_id,location_num,branch_num) :- cm_id = 10016, location_num = 16 , branch_num = 65.

company(cm_id,location_num,branch_num) :- cm_id = 10017, location_num = 17 , branch_num = 66.

company(cm_id,location_num,branch_num) :- cm_id = 10018, location_num = 18 , branch_num = 67.

company(cm_id,location_num,branch_num) :- cm_id = 10019, location_num = 19 , branch_num = 68.

company(cm_id,location_num,branch_num) :- cm_id = 10020, location_num = 20,  branch_num = 69.

company(cm_id,location_num,branch_num) :- cm_id = 10021, location_num = 21 , branch_num = 70.

company(cm_id,location_num,branch_num) :- cm_id = 10022, location_num = 22 , branch_num = 71.

company(cm_id,location_num,branch_num) :- cm_id = 10023, location_num = 23 , branch_num = 72.

company(cm_id,location_num,branch_num) :- cm_id = 10024, location_num = 24,  branch_num = 73.

company(cm_id,location_num,branch_num) :- cm_id = 10025, location_num = 25 , branch_num = 74.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10001, location_num >= 57000, location_num<=59000 , branch_num >= 51000, branch_num<= 53000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10002, location_num >= 94000, location_num<=96000 , branch_num >= 28000, branch_num<= 30000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10003, location_num >= 66000, location_num<=68000 , branch_num >= 19000, branch_num<= 21000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10004, location_num >= 71000, location_num<=73000 , branch_num >= 13000, branch_num<= 15000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10005, location_num >= 17000, location_num<=19000 , branch_num >= 10000, branch_num<= 12000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10006, location_num >= 14000, location_num<=16000 , branch_num >= 34000, branch_num<= 36000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10007, location_num >= 16000, location_num<=18000 , branch_num >= 64000, branch_num<= 66000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10008, location_num >= 72000, location_num<=74000 , branch_num >= 75000, branch_num<= 77000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10009, location_num >= 76000, location_num<=78000 , branch_num >= 73000, branch_num<= 75000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10010, location_num >= 63000, location_num<=65000 , branch_num >= 60000, branch_num<= 62000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10011, location_num >= 45000, location_num<=47000 , branch_num >= 17000, branch_num<= 19000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10012, location_num >= 6000, location_num<=8000 , branch_num >= 71000, branch_num<= 73000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10013, location_num >= 17000, location_num<=19000 , branch_num >= 9000, branch_num<= 11000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10014, location_num >= 10000, location_num<=12000 , branch_num >= 7000, branch_num<= 9000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10015, location_num >= 85000, location_num<=87000 , branch_num >= 53000, branch_num<= 55000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10016, location_num >= 73000, location_num<=75000 , branch_num >= 19000, branch_num<= 21000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10017, location_num >= 70000, location_num<=72000 , branch_num >= 81000, branch_num<= 83000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10018, location_num >= 42000, location_num<=44000 , branch_num >= 64000, branch_num<= 66000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10019, location_num >= 97000, location_num<=99000 , branch_num >= 42000, branch_num<= 44000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10020, location_num >= 98000, location_num<=100000 , branch_num >= 10000, branch_num<= 12000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10021, location_num >= 27000, location_num<=29000 , branch_num >= 98000, branch_num<= 100000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10022, location_num >= 85000, location_num<=87000 , branch_num >= 49000, branch_num<= 51000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10023, location_num >= 18000, location_num<=20000 , branch_num >= 37000, branch_num<= 39000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10024, location_num >= 54000, location_num<=56000 , branch_num >= 4000, branch_num<= 6000.

company_Location(cm_id,location_num,branch_num) :- cm_id = 10025, location_num >= 69000, location_num<=71000 , branch_num >= 9000, branch_num<= 11000.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100002, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",    salary = 35000,     applID = 40001.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100003, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",           salary = 110000,     applID = 40002.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100004, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",    salary = 25000,     applID = 40023.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100005, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 140000,     applID = 40012.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100006, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 150000,     applID = 40021.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100007, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "SR. MEM OF CONSULTING STAFF",  salary = 15000,     applID = 40041.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100008, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",     salary = 25000,     applID = 40012.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100009, emplType_num = 022,  emplStatus = "NOTANEMP", positon = "MANAGER",            salary = 90000,     applID = 40008.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100010, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",     salary = 15000,     applID = 40012.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100011, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 60000,     applID = 40021.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100012, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF TECHNICAL STAFF",     salary = 55000,     applID = 40014.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100013, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 50000,     applID = 40014.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100014, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",     salary = 45000,     applID = 40015.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100015, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MANAGER",            salary = 80000,     applID = 40016.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100016, emplType_num = 022,  emplStatus = "NOTANEMP", positon = "SR. MEM OF CONSULTING STAFF",  salary = 55000,     applID = 40017.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100017, emplType_num = 021,  emplStatus = "ON LEAVE", positon = "MANAGER",            salary = 100000,     applID = 40018.

employee(employeeID,emplType_num, emplStatus,positon,salary,applID) :- employeeID = 100018, emplType_num = 020,  emplStatus = "EMPLOYED", positon = "MEM OF CONSULTING STAFF",     salary = 42000,   applID = 40019.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43001, emp_id = 100001, shift_id = 5201, work_grp_num = 02, wk_day_num = 8001.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43002, emp_id = 100012, shift_id = 5202, work_grp_num = 06, wk_day_num = 8002.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43003, emp_id = 100003, shift_id = 5204, work_grp_num = 03, wk_day_num = 8003.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43004, emp_id = 100004, shift_id = 5205, work_grp_num = 05, wk_day_num = 8004.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43005, emp_id = 100016, shift_id = 5206, work_grp_num = 02, wk_day_num = 8005.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43006, emp_id = 100007, shift_id = 5207, work_grp_num = 03, wk_day_num = 8006.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43007, emp_id = 100008, shift_id = 5208, work_grp_num = 07, wk_day_num = 8007.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43008, emp_id = 100003, shift_id = 5209, work_grp_num = 03, wk_day_num = 8001.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43009, emp_id = 100005, shift_id = 5200, work_grp_num = 01, wk_day_num = 8002.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43000, emp_id = 100008, shift_id = 5202, work_grp_num = 02, wk_day_num = 8003.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43001, emp_id = 100012, shift_id = 5203, work_grp_num = 07, wk_day_num = 8004.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43002, emp_id = 100006, shift_id = 5205, work_grp_num = 02, wk_day_num = 8005.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43003, emp_id = 100002, shift_id = 5207, work_grp_num = 06, wk_day_num = 8006.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43004, emp_id = 100005, shift_id = 5203, work_grp_num = 02, wk_day_num = 8007.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43005, emp_id = 100014, shift_id = 5205, work_grp_num = 05, wk_day_num = 8001.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43006, emp_id = 100002, shift_id = 5203, work_grp_num = 02, wk_day_num = 8002.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43007, emp_id = 100018, shift_id = 5204, work_grp_num = 01, wk_day_num = 8003.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43008, emp_id = 100009, shift_id = 5205, work_grp_num = 02, wk_day_num = 8004.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43009, emp_id = 100005, shift_id = 5207, work_grp_num = 07, wk_day_num = 8005.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43000, emp_id = 100013, shift_id = 5209, work_grp_num = 02, wk_day_num = 8006.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43001, emp_id = 100002, shift_id = 5203, work_grp_num = 05, wk_day_num = 8007.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43002, emp_id = 100006, shift_id = 5206, work_grp_num = 02, wk_day_num = 8001.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43003, emp_id = 100012, shift_id = 5203, work_grp_num = 04, wk_day_num = 8002.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43004, emp_id = 100014, shift_id = 5207, work_grp_num = 02, wk_day_num = 8003.

schedule_map(schmap_id, emp_id, shift_id, work_grp_num, wk_day_num) :- schmap_id = 43005, emp_id = 100001, shift_id = 5205, work_grp_num = 02, wk_day_num = 8004.



shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5201, sh_start >= 0600, sh_start <= 0615, sh_end >= 0900, sh_end <= 0915.

shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5202, sh_start >= 0900, sh_start >= 0915, sh_end >= 1200, sh_end >= 1215.

shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5203, sh_start >= 1200, sh_start >= 1215, sh_end >= 1500, sh_end >= 1515.

shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5204, sh_start >= 1500, sh_start >= 1515, sh_end >= 1800, sh_end >= 1815.

shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5205, sh_start >= 1800, sh_start >= 1815, sh_end >= 2100, sh_end >= 2115.

shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5206, sh_start >= 2100, sh_start >= 2115, sh_end >= 0000, sh_end >= 0015.

shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5207, sh_start >= 0000, sh_start >= 0015, sh_end >= 0300, sh_end >= 0315.

shift_pattern(shift_id, sh_start, sh_end ) :- shift_id = 5208, sh_start >= 0300, sh_start >= 0315, sh_end >= 0600, sh_end >= 0615.

man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410001, post_hr = 200, wk_day_num = 8001.

man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410002, post_hr = 250, wk_day_num = 8002.

man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410003, post_hr = 500, wk_day_num = 8003.

man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410004, post_hr = 300, wk_day_num = 8004.

man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410005, post_hr = 150, wk_day_num = 8005.

man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410006, post_hr = 160, wk_day_num = 8006.

man_hour(manhr_id, post_hr, wk_day_num) :- manhr_id = 410007, post_hr = 100, wk_day_num = 8007.



time_slot(cm_id,X, Y) :- cm_id = 100001, X >= 10000,X<=220000,Y >= 0 , Y<= 240000.



time_card(emp_id,start_time,end_time) :- emp_id = 100001, start_time=30 , end_time = 700.

time_card(emp_id,start_time,end_time) :- emp_id = 100002, start_time=1845 , end_time = 1930.

time_card(emp_id,start_time,end_time) :- emp_id = 100003, start_time=830 , end_time = 1130.

time_card(emp_id,start_time,end_time) :- emp_id = 100004, start_time=745 , end_time = 945.

time_card(emp_id,start_time,end_time) :- emp_id = 100005, start_time=1530 , end_time = 2130.

time_card(emp_id,start_time,end_time) :- emp_id = 100006, start_time=1745 , end_time = 1700.

time_card(emp_id,start_time,end_time) :- emp_id = 100007, start_time=2015 , end_time = 145.

time_card(emp_id,start_time,end_time) :- emp_id = 100008, start_time=1715 , end_time = 1915.

time_card(emp_id,start_time,end_time) :- emp_id = 100009, start_time=1015 , end_time = 1215.

time_card(emp_id,start_time,end_time) :- emp_id = 100010, start_time=1230 , end_time = 1445.

time_card(emp_id,start_time,end_time) :- emp_id = 100011, start_time=1630 , end_time = 1730.

time_card(emp_id,start_time,end_time) :- emp_id = 100012, start_time=2215 , end_time = 115.

time_card(emp_id,start_time,end_time) :- emp_id = 100013, start_time=900 , end_time = 1145.

time_card(emp_id,start_time,end_time) :- emp_id = 100014, start_time=630 , end_time = 815.

time_card(emp_id,start_time,end_time) :- emp_id = 100015, start_time=30 , end_time = 615.

time_card(emp_id,start_time,end_time) :- emp_id = 100016, start_time=815 , end_time = 900.

time_card(emp_id,start_time,end_time) :- emp_id = 100017, start_time=615 , end_time = 1345.

time_card(emp_id,start_time,end_time) :- emp_id = 100018, start_time=1815 , end_time = 2045.

time_card(emp_id,start_time,end_time) :- emp_id = 100009, start_time=630 , end_time = 645.

time_card(emp_id,start_time,end_time) :- emp_id = 100000, start_time=2315 , end_time = 2400.

time_card(emp_id,start_time,end_time) :- emp_id = 100001, start_time=115 , end_time = 0500.

time_card(emp_id,X,Y) :- emp_id = 100001, X >= 10000, X<=20000 , Y >= 3000, Y<= 70000.

time_card(emp_id,X,Y) :- emp_id = 100002, X >= 20000, X<=30000 , Y >= 184500, Y<= 193000.

time_card(emp_id,X,Y) :- emp_id = 100003, X >= 30000, X<=40000 , Y >= 83000, Y<= 113000.

time_card(emp_id,X,Y) :- emp_id = 100004, X >= 40000, X<=50000 , Y >= 74500, Y<= 94500.

time_card(emp_id,X,Y) :- emp_id = 100005, X >= 50000, X<=60000 , Y >= 153000, Y<= 213000.

time_card(emp_id,X,Y) :- emp_id = 100006, X >= 60000, X<=70000 , Y >= 174500, Y<= 170000.

time_card(emp_id,X,Y) :- emp_id = 100007, X >= 70000, X<=80000 , Y >= 201500, Y<= 14500.

time_card(emp_id,X,Y) :- emp_id = 100008, X >= 80000, X<=90000 , Y >= 171500, Y<= 191500.

time_card(emp_id,X,Y) :- emp_id = 100009, X >= 90000, X<=100000 , Y >= 101500, Y<= 121500.

time_card(emp_id,X,Y) :- emp_id = 100010, X >= 100000, X<=110000 , Y >= 123000, Y<= 144500.

time_card(emp_id,X,Y) :- emp_id = 100011, X >= 110000, X<=120000 , Y >= 163000, Y<= 173000.

time_card(emp_id,X,Y) :- emp_id = 100012, X >= 120000, X<=130000 , Y >= 221500, Y<= 11500.

time_card(emp_id,X,Y) :- emp_id = 100013, X >= 130000, X<=140000 , Y >= 90000, Y<= 114500.

time_card(emp_id,X,Y) :- emp_id = 100014, X >= 140000, X<=150000 , Y >= 63000, Y<= 81500.

time_card(emp_id,X,Y) :- emp_id = 100015, X >= 150000, X<=160000 , Y >= 3000, Y<= 61500.

time_card(emp_id,X,Y) :- emp_id = 100016, X >= 160000, X<=170000 , Y >= 81500, Y<= 90000.

time_card(emp_id,X,Y) :- emp_id = 100017, X >= 170000, X<=180000 , Y >= 61500, Y<= 134500.

time_card(emp_id,X,Y) :- emp_id = 100018, X >= 180000, X<=190000 , Y >= 181500, Y<= 204500.

time_card(emp_id,X,Y) :- emp_id = 100009, X >= 190000, X<=200000 , Y >= 63000, Y<= 64500.

time_card(emp_id,X,Y) :- emp_id = 100000, X >= 200000, X<=210000 , Y >= 231500, Y<= 240000.

time_card(emp_id,X,Y) :- emp_id = 100001, X >= 210000, X<=220000 , Y >= 11500, Y<= 50000.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8001, emp_id = 100001, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8002, emp_id = 100002, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8003, emp_id = 100003, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8004, emp_id = 100004, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8005, emp_id = 100005, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8003, emp_id = 100006, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8002, emp_id = 100007, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8001, emp_id = 100008, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8004, emp_id = 100009, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8005, emp_id = 100010, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8002, emp_id = 100011, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8004, emp_id = 100012, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8003, emp_id = 100013, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8005, emp_id = 100014, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8002, emp_id = 100015, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8005, emp_id = 100016, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8001, emp_id = 100017, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8001, emp_id = 100018, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8003, emp_id = 100009, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8002, emp_id = 100002, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8004, emp_id = 100003, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8005, emp_id = 100012, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8006, emp_id = 100013, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8003, emp_id = 100014, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8002, emp_id = 100015, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8002, emp_id = 100016, reg_start_time = 0800, reg_end_time = 0600.

regular_time(wk_day_num, emp_id, reg_start_time, reg_end_time) :- wk_day_num = 8004, emp_id = 100017, reg_start_time = 0800, reg_end_time = 0600.



regular_time(cm_id,X, Y) :- cm_id = 100001, X >= 10000,X<=220000,Y >= 090000 , Y<= 170000.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8001, emp_id = 100001, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8002, emp_id = 100002, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8003, emp_id = 100003, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8004, emp_id = 100004, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8005, emp_id = 100005, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8001, emp_id = 100006, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8007, emp_id = 100007, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8006, emp_id = 100008, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8007, emp_id = 100009, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8001, emp_id = 100010, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8002, emp_id = 100011, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8003, emp_id = 100012, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8004, emp_id = 100013, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8005, emp_id = 100014, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8006, emp_id = 100015, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8007, emp_id = 100016, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8003, emp_id = 100017, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8007, emp_id = 100018, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8006, emp_id = 100019, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8004, emp_id = 100020, premium_st_time = 0600, premium_end_time = 0900.

premium_time(wk_day_num, emp_id, premium_st_time, premium_end_time) :- wk_day_num = 8005, emp_id = 100021, premium_st_time = 0600, premium_end_time = 0900.

premium_time(cm_id,X, Y) :- cm_id = 100001, X >= 10000,X<=220000,Y >= 000000 , Y<= 090000.

premium_time(cm_id,X, Y) :- cm_id = 100001, X >= 10000,X<=220000,Y >= 170000 , Y<= 240000.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 701, t_start = 0900, t_end = 1200, wk_day_num = 8001.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 702, t_start = 1200, t_end = 1500, wk_day_num = 8002.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 703, t_start = 0900, t_end = 1200, wk_day_num = 8003.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 704, t_start = 1500, t_end = 1800, wk_day_num = 8004.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 705, t_start = 1200, t_end = 1500, wk_day_num = 8005.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 706, t_start = 1200, t_end = 1500, wk_day_num = 8006.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 707, t_start = 0900, t_end = 1200, wk_day_num = 8007.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 708, t_start = 0900, t_end = 1200, wk_day_num = 8001.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 709, t_start = 1200, t_end = 1500, wk_day_num = 8002.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 710, t_start = 1500, t_end = 1800, wk_day_num = 8003.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 711, t_start = 0900, t_end = 1200, wk_day_num = 8004.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 712, t_start = 0900, t_end = 1200, wk_day_num = 8005.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 713, t_start = 1500, t_end = 1800, wk_day_num = 8006.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 714, t_start = 1500, t_end = 1800, wk_day_num = 8007.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 715, t_start = 1800, t_end = 2100, wk_day_num = 8001.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 716, t_start = 0000, t_end = 0300, wk_day_num = 8002.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 717, t_start = 0000, t_end = 0300, wk_day_num = 8003.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 718, t_start = 2100, t_end = 0000, wk_day_num = 8004.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 719, t_start = 2100, t_end = 0000, wk_day_num = 8005.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 720, t_start = 2100, t_end = 0000, wk_day_num = 8006.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 721, t_start = 2100, t_end = 0000, wk_day_num = 8007.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 722, t_start = 1800, t_end = 2100, wk_day_num = 8001.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 723, t_start = 2100, t_end = 0000, wk_day_num = 8002.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 724, t_start = 0900, t_end = 1200, wk_day_num = 8003.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 725, t_start = 1200, t_end = 1500, wk_day_num = 8004.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 726, t_start = 1500, t_end = 1800, wk_day_num = 8005.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 727, t_start = 1500, t_end = 1800, wk_day_num = 8006.

empty_slot(pub_id, t_start, t_end, wk_day_num) :- pub_id = 728, t_start = 1500, t_end = 1800, wk_day_num = 8001.

empty_slot(cm_id,X, Y) :- cm_id = 100001, X >= 10000,X<=220000,Y >= 000000 , Y<= 003000.

empty_slot(cm_id,X, Y) :- cm_id = 100001, X >= 10000,X<=220000,Y >= 144500 , Y<= 153000.

empty_slot(cm_id,X, Y) :- cm_id = 100001, X >= 10000,X<=220000,Y >= 213000 , Y<= 231500.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1200, emp_id = 100001, pyh_id =  24301.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1201, emp_id = 100002, pyh_id =  24302.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1202, emp_id = 100003, pyh_id =  24302.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1203, emp_id = 100005, pyh_id =  24301.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1204, emp_id = 100004, pyh_id =  24301.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1205, emp_id = 100006, pyh_id =  24301.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1210, emp_id = 100007, pyh_id =  24305.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1212, emp_id = 100008, pyh_id =  24302.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1213, emp_id = 100009, pyh_id =  24304.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1214, emp_id = 100010, pyh_id =  24302.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1215, emp_id = 100011, pyh_id =  24306.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1216, emp_id = 100015, pyh_id =  24303.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1217, emp_id = 100014, pyh_id =  24307.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1219, emp_id = 100013, pyh_id =  24310.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1222, emp_id = 100016, pyh_id =  24313.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1223, emp_id = 100012, pyh_id =  24311.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1224, emp_id = 100017, pyh_id =  24308.

payroll(payroll_id, emp_id, pyh_id) :- payroll_id = 1225, emp_id = 100018, pyh_id =  24309.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2400, emp_id = 100001, wk_hour = 18, premium_hr = 12.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2401, emp_id = 100002, wk_hour = 24, premium_hr = 3.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2402, emp_id = 100003, wk_hour = 33, premium_hr = 3.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2403, emp_id = 100004, wk_hour = 22, premium_hr = 16.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2404, emp_id = 100005, wk_hour = 37, premium_hr = 8.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2405, emp_id = 100006, wk_hour = 40, premium_hr = 0.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2406, emp_id = 100007, wk_hour = 26, premium_hr = 0.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2407, emp_id = 100008, wk_hour = 32, premium_hr = 2.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2408, emp_id = 100009, wk_hour = 4, premium_hr = 26.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2409, emp_id = 100010, wk_hour = 25, premium_hr = 0.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2410, emp_id = 100011, wk_hour = 37, premium_hr = 0.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2411, emp_id = 100012, wk_hour = 28, premium_hr = 0.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2412, emp_id = 100013, wk_hour = 34, premium_hr = 15.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2413, emp_id = 100014, wk_hour = 30, premium_hr = 17.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2414, emp_id = 100015, wk_hour = 32, premium_hr = 0.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2415, emp_id = 100016, wk_hour = 15, premium_hr = 0.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2416, emp_id = 100017, wk_hour = 16, premium_hr = 18.

actual_hour(phy_id, emp_id, wk_hour, premium_hr) :- phy_id = 2417, emp_id = 100018, wk_hour = 27, premium_hr = 10.

IconicMaxPremHrs(MAX53) :-

MAX53=6.



IconicMaxEndTm(MAX986) :-

MAX986=240000.



IconicMaxSalary(MAX909) :-

MAX909=150000.



IconicMaxHours(MAX259) :-

MAX259=10.

end %ABCstoresInc%

[Ogbeide Ikhile (EJ)]

Nitin see the new zip from etan

Ethan <li...@alumni.nccu.edu.tw> wrote:

=== message truncated ===

---

Love cheap thrills? Enjoy PC-to-Phone calls to 30+ countries for just 2¢/min with Yahoo! Messenger with Voice.

[Nitin]

No ,  I am refereing to the one Prashant just posted .

Cheers,
Nitin