#include #include #include EXEC SQL INCLUDE SQLCA; /* SQL communication area structure */ EXEC SQL BEGIN DECLARE SECTION; /* declare host variables */ char db_name[8]; /* database name */ long c_cno; char c_subj[7]; short c_nper; char c_day; short c_per; char c_tname[15]; short c_tload; short c_grade; char c_hr; short c_hra, c_hrb; long c_numwin; short c_numDays; EXEC SQL END DECLARE SECTION; EXEC SQL WHENEVER SQLWARNING CONTINUE; /* sqlca.sqlcode > 0 */ EXEC SQL WHENEVER SQLERROR CONTINUE; /* sqlca.sqlcode < 0 */ EXEC SQL WHENEVER NOT FOUND CONTINUE; /* sqlca.sqlcode = 100 */ /* sqlca.sqlcode = 0 (no error) */ /************************************************************************ * Report all teacher conflicts which occur when a teacher has been * * scheduled to teach two classes at the same time. * ************************************************************************/ void tConflict() { char tmp_tname[15]; char tmp_day = '@'; short tmp_per = -1; int count = 0; strcpy(tmp_tname, "@%$"); /* Cursor delcaration. */ EXEC SQL DECLARE c1 CURSOR FOR SELECT DISTINCT TEMP.tname, TEMP.day, TEMP.per FROM (SELECT a.cno, tname, day, per FROM Taught_by tb, Assigned a WHERE tb.cno=a.cno) AS TEMP, (SELECT a.cno, tname, day, per FROM Taught_by tb, Assigned a WHERE tb.cno=a.cno) AS TEMP2 WHERE TEMP.tname=TEMP2.tname AND TEMP.cno<>TEMP2.cno AND TEMP.day=TEMP2.day AND TEMP.per=TEMP2.per; /* Open the cursor in order to get tuples back */ EXEC SQL OPEN c1; printf("Teacher conflict:\n"); printf("Teacher name\tDay\tPer\n"); printf("------------------------------\n"); while (SQLCODE == 0) { /* fetch tuples from the cursor. */ EXEC SQL FETCH c1 into :c_tname, :c_day, :c_per; if (count > 0 ) printf("%s\t%c\t%d\n",tmp_tname, tmp_day, tmp_per); count++; strcpy(tmp_tname, c_tname); tmp_per = c_per; tmp_day = c_day; } if (count > 0 && strcmp(tmp_tname, c_tname) != 0 && tmp_per != c_per && tmp_day != c_day) printf("%s\t%c\t%d\n",c_tname, c_day, c_per); if (SQLCODE != 100) { /* SQLCODE refers to sqlca.sqlcode */ printf("Error! Reason: %ld\n", sqlca.sqlcode); } if (count == 0) printf("No homeroom conflict!\n"); else; EXEC SQL CLOSE c1; } /************************************************************************ * Report all homeroom conflicts which occur when a student has been * * scheduled to take two classes at the same time. * ************************************************************************/ void hrConflict() { /* Temporary variables. */ char tmp_day = '@'; char tmp_hr = '@'; short tmp_grade = -1; short tmp_per = -1; int count = 0; /* Cursor declaration. */ EXEC SQL DECLARE c2 CURSOR FOR SELECT DISTINCT TEMP2.grade, TEMP2.hr, TEMP2.day, TEMP2.per FROM (SELECT a.cno, grade, hr, day, per FROM Taught_to tt, Assigned a WHERE tt.cno=a.cno AND hr<>'Z' UNION SELECT cno, g.grade, hr, day, per FROM Grades g, (SELECT a.cno, grade, day, per FROM Taught_to tt, Assigned a WHERE tt.cno=a.cno AND tt.hr='Z') AS TEMP1 WHERE g.grade=TEMP1.grade) AS TEMP2, (SELECT a.cno, grade, hr, day, per FROM Taught_to tt, Assigned a WHERE tt.cno=a.cno AND hr<>'Z' UNION SELECT cno, g.grade, hr, day, per FROM Grades g, (SELECT a.cno, grade, day, per FROM Taught_to tt, Assigned a WHERE tt.cno=a.cno AND tt.hr='Z') AS TEMP1 WHERE g.grade=TEMP1.grade) AS TEMP3 WHERE TEMP2.grade=TEMP3.grade AND TEMP2.hr=TEMP3.hr AND TEMP2.day=TEMP3.day AND TEMP2.per=TEMP3.per AND TEMP2.cno<>TEMP3.cno; /* Open the cursor. */ EXEC SQL OPEN c2; printf("Homeroom conflict:\n"); printf("Grade\tHr\tDay\tPer\n"); printf("------------------------------\n"); while (SQLCODE == 0) { /* Fetch tuples from the cursor. */ EXEC SQL FETCH c2 into :c_grade, :c_hr, :c_day, :c_per; if (count > 0) printf("%d\t%c\t%c\t%d\n", tmp_grade, tmp_hr, tmp_day, tmp_per); count++; tmp_per = c_per; tmp_day = c_day; tmp_hr = c_hr; tmp_grade = c_grade; } if (SQLCODE != 100) printf("Error! Reason: %ld\n", sqlca.sqlcode); if (count == 0) printf("No homeroom conflict!\n"); EXEC SQL CLOSE c2; } void dupOffer() { int flag=0; EXEC SQL BEGIN DECLARE SECTION; long c_tmpcno3; char c_tmpday3; short c_tmpper3; EXEC SQL END DECLARE SECTION; /* cursor declaration.*/ EXEC SQL DECLARE c31 CURSOR FOR SELECT DISTINCT A.cno, A.day, A.per FROM (SELECT DISTINCT a1.cno, a1.day FROM Assigned a1, Assigned a2 WHERE a1.cno=a2.cno AND a1.day=a2.day AND a1.per<>a2.per ) AS TEMP, Assigned A WHERE TEMP.cno=A.cno AND TEMP.day=A.day ORDER BY A.cno, A.day; EXEC SQL OPEN c31; printf("Courses that are taught twice in the same day are: \n"); printf(" CNO DAY PER \n"); printf(" --------------------\n"); do { EXEC SQL FETCH c31 into :c_tmpcno3, :c_tmpday3, :c_tmpper3; if (SQLCODE != 0) { if (flag==0) { printf("There's no course taught twice in the same day!\n"); break; } else break; } flag++; printf(" %ld At %c %d \n", c_tmpcno3, c_tmpday3, c_tmpper3); } while (1); EXEC SQL CLOSE c31; } /************************************************************************ * report the total number of windows(over all teachers) in the teacher * * teacher schedules * ************************************************************************/ long countWindows() { EXEC SQL BEGIN DECLARE SECTION; long windows; EXEC SQL END DECLARE SECTION; EXEC SQL SELECT SUM(winnum) INTO:windows FROM(SELECT TEMP.tname, TEMP.day, TEMP.winnum FROM(SELECT tname, day, (MAX(per) - MIN(per) + 1 - COUNT(DISTINCT per)) AS winnum FROM Taught_by tb, Assigned a WHERE tb.cno = a.cno GROUP BY tname, day) AS TEMP ) AS TP; if (SQLCODE != 0){ printf("error happens, reason: %ld\n", SQLCODE); exit(1); } else return windows; } /************************************************************************ * Return the index of parameter 'day'. * ************************************************************************/ int getCount(char day, const char * days) { int count = 0; for (count = 0; count < strlen(days); count++) { if (days[count] == day) break; } return count; } /************************************************************************ * Print a teacher schedule on a day of a period * ************************************************************************/ int printTSchedule(int count, char * space, char * subj, short grade, char hr) { if (count == 1) { printf("%s%s%d%c ", space, subj, grade, hr); count = 0; } /* Print only grade for multiple homerooms. */ else if(count > 1) { printf("%s%s%d ", space, subj, grade); count = 0; } return count; } /************************************************************************ * Generate all teacher schedules. * ************************************************************************/ void tSchedule() { /* Temporary variables. */ char tmp_tname[15]; char tmp_subj[7]; char space[40]; char * days; char tmp_day = '@'; char tmp_hr = '@'; short tmp_grade = -1; short tmp_per = -1; int hrCount = 0; int dayCount = 0; int curCount = 0; int hroomCount = 0; int conflict = 0; int i, j; strcpy(tmp_tname, "@%$"); strcpy(tmp_subj, "@%$"); strcpy(space, ""); /* Get the total number of days in Table Period. */ EXEC SQL SELECT COUNT(TMP.day) INTO: c_numDays FROM (SELECT DISTINCT day FROM Periods) AS TMP; /* Malloc memory for days. */ days = (char*)malloc(sizeof(char)*c_numDays); /* Read each day, using cursor c51, into 'days' array. */ EXEC SQL DECLARE c51 CURSOR FOR SELECT DISTINCT day FROM Periods; EXEC SQL OPEN c51; while (SQLCODE == 0 && curCount < c_numDays) { EXEC SQL FETCH c51 into :c_day; days[curCount] = c_day; curCount++; } EXEC SQL CLOSE c51; curCount = 0; /* Cursor c52 is declared to get the information for teachers' schedules. */ EXEC SQL DECLARE c52 CURSOR FOR SELECT * FROM (SELECT tname, per, day, subj, grade, hr FROM Courses C, Taught_by TB, Taught_to TT, Assigned A WHERE C.cno=TB.cno AND TB.cno=TT.cno AND TT.cno=A.cno AND TT.hr<>'Z' UNION SELECT tname, per, day, subj, g.grade, hr FROM Grades G, (SELECT tname, day, per, subj, grade FROM Courses C, Taught_by TB, Taught_to TT, Assigned A WHERE C.cno=TB.cno AND TB.cno=TT.cno AND TT.cno=A.cno AND TT.hr='Z') AS TEMP WHERE TEMP.grade=G.grade) AS TP ORDER BY TP.tname, TP.per, TP.day; EXEC SQL OPEN c52; while (SQLCODE == 0) { EXEC SQL FETCH c52 into :c_tname, :c_per, :c_day, :c_subj, :c_grade, :c_hr; c_subj[4] = 0; /* Get the space for printing next course. */ curCount = getCount(tmp_day, days); j = (curCount-dayCount)*9; for (i = 0; i < j; i++) space[i] = ' '; if(j >= 0) space[j] = 0; else space[0] = 0; /* Check conflict. */ if (strcmp(tmp_tname, c_tname) == 0 && tmp_day == c_day && tmp_per == c_per && (strcmp(c_subj, tmp_subj) != 0 || tmp_grade != c_grade) && hroomCount == 0) { printf("%sConflict ", space); dayCount = getCount(tmp_day, days) + 1; if(tmp_hr != c_hr) hroomCount++; else hroomCount = 0; conflict++; hrCount = -1; } /* Changing teacher. Print the course and homeroom for previous teacher, and header for the current teacher. */ else if (strcmp(c_tname, tmp_tname) != 0) { if(!conflict) { int tmp = hrCount; if (hrCount > 0) { hrCount = printTSchedule (hrCount, space, tmp_subj, tmp_grade, tmp_hr); printf("\n----"); for (i = 0; i < c_numDays; i++) printf("---------"); printf("\n"); } if (hrCount == 0 && tmp > 0) dayCount = 0; printf("\n\nTeacher: %s\n====", c_tname); for (i = 0; i < c_numDays; i++) printf("========="); printf("\nPer "); for (i = 0; i < c_numDays; i++) printf(" %c ", days[i]); printf("\n----"); for (i = 0; i < c_numDays; i++) printf("---------"); printf("\n%d ", c_per); } else conflict = 0; } /* Changing period. Print course and homeroom, start next period. */ else if (tmp_per != c_per) { if(!conflict) { int tmp = hrCount; if (hrCount > 0) hrCount = printTSchedule(hrCount, space, tmp_subj, tmp_grade, tmp_hr); if (hrCount == 0 && tmp > 0) dayCount = 0; printf("\n%d ", c_per); } else conflict = 0; } /* Changing day. Print the course and homeroom. */ else if (tmp_day != c_day) { if(!conflict) { int tmp = hrCount; if (hrCount > 0) hrCount = printTSchedule(hrCount, space, tmp_subj, tmp_grade, tmp_hr); if (hrCount == 0 && tmp > 0) dayCount = getCount(tmp_day, days) + 1; } else conflict = 0; } hrCount++; strcpy(tmp_tname, c_tname); tmp_per = c_per; strcpy(tmp_subj, c_subj); tmp_day = c_day; tmp_hr = c_hr; tmp_grade = c_grade; } /* Print the last course and homeroom. */ curCount = getCount(tmp_day, days); j = (curCount-dayCount)*9; for (i = 0; i < j; i++) space[i] = ' '; if(j >= 0) space[j] = 0; else space[0] = 0; if (hrCount > 0) hrCount = printTSchedule(hrCount, space, tmp_subj, tmp_grade, tmp_hr); printf("\n----"); for (i = 0; i < c_numDays; i++) printf("---------"); printf("\n"); if (SQLCODE != 100) printf("Error! Reason: %ld\n", sqlca.sqlcode); EXEC SQL CLOSE c52; /* Free days. */ free(days); } /************************************************************************ * Print course, subject and teacher for a homeroom. * ************************************************************************/ int printHrSchedule(int count, char * space, char * subj, const char * tname) { char name[15]; if (count == 1) { strcpy(name, tname); name[10] = 0; printf("%s%s%s ", space, subj, name); count = 0; } /* Print only * for multiple teachers. */ else if(count > 1) { printf("%s%s* ", space, subj); count = 0; } return count; } /************************************************************************ * Generate all homeroom schedules. * ************************************************************************/ void hrSchedule() { /* Temporaty variables. */ char tmp_tname[15]; char tmp_subj[7]; char space[80]; char * days; char tmp_day = '@'; char tmp_hr = '@'; short tmp_grade = -1; short tmp_per = -1; int dayCount = 0; int tnameCount = 0; int curCount = 0; int tCount = 0; int conflict = 0; int i, j; strcpy(tmp_tname, "@%$"); strcpy(tmp_subj, "@%$"); strcpy(space, ""); /* Get the total number of days in Table Period. */ EXEC SQL SELECT COUNT(TMP.day) INTO: c_numDays FROM (SELECT DISTINCT day FROM Periods) AS TMP; /* Malloc memory for days. */ days = (char*)malloc(sizeof(char)*c_numDays); /* Read each day, using cursor c61, into 'days' array. */ EXEC SQL DECLARE c61 CURSOR FOR SELECT DISTINCT day FROM Periods; EXEC SQL OPEN c61; while (SQLCODE == 0 && curCount < c_numDays) { EXEC SQL FETCH c61 into :c_day; days[curCount] = c_day; curCount++; } EXEC SQL CLOSE c61; curCount = 0; /* Cursor c62 is used to get all attributes for homeroom schedules. */ EXEC SQL DECLARE c62 CURSOR FOR SELECT * FROM (SELECT grade, hr, per, day, subj, tname FROM Courses c, Assigned a, Taught_to tt, Taught_by tb WHERE c.cno=a.cno AND a.cno=tt.cno AND tt.cno=tb.cno AND tt.hr<>'Z' UNION SELECT g.grade, hr, per, day, subj, tname FROM Grades g, (SELECT grade, day, per, subj, tname FROM Courses c, Assigned a, Taught_to tt, Taught_by tb WHERE c.cno=a.cno AND a.cno=tt.cno AND tt.cno=tb.cno AND tt.hr='Z') AS TEMP WHERE TEMP.grade=g.grade) AS TP ORDER BY TP.grade, TP.hr, TP.per, TP.day, TP.tname; EXEC SQL OPEN c62; while (SQLCODE == 0) { EXEC SQL FETCH c62 into :c_grade, :c_hr, :c_per, :c_day, :c_subj, :c_tname; c_subj[4] = 0; /* Get the space for next printing. */ curCount = getCount(tmp_day, days); j = (curCount-dayCount)*16; for (i = 0; i < j; i++) space[i] = ' '; if(j >= 0) space[j] = 0; else space[0] = 0; /* Check homeroom conflict first. */ if (strcmp(c_subj, tmp_subj) != 0 && tmp_day == c_day && tmp_per == c_per && tmp_hr == c_hr && tmp_grade == c_grade && tCount == 0) { printf("%sConflict! ", space); dayCount = getCount(tmp_day, days) + 1; if(strcmp(tmp_tname, c_tname)!= 0) tCount++; else tCount = 0; conflict++; } /* Changing homeroom. Print the course and teacher for previous homeroom, and header for the current one.*/ else if (tmp_grade != c_grade || tmp_hr != c_hr) { if(conflict == 0) { int tmp = tnameCount; if (tnameCount > 0) { tnameCount = printHrSchedule(tnameCount, space, tmp_subj, tmp_tname); printf("\n----"); for (i = 0; i < c_numDays; i++) printf("----------------"); printf("\n"); } if (tnameCount == 0 && tmp > 0) dayCount = 0; printf("\n\nHomeroom: %d%c\n====", c_grade, c_hr); for (i = 0; i < c_numDays; i++) printf("================"); printf("\nPer "); for (i = 0; i < c_numDays; i++) printf(" %c ", days[i]); printf("\n----"); for (i = 0; i < c_numDays; i++) printf("----------------"); printf("\n%d ", c_per); tCount = 0; } else conflict = 0; } /* Changing period. Print for the last period, and start next one. */ else if (tmp_per != c_per) { if(conflict == 0) { int tmp = tnameCount; if (tnameCount > 0) tnameCount = printHrSchedule(tnameCount, space, tmp_subj, tmp_tname); if (tnameCount == 0 && tmp > 0) dayCount = 0; printf("\n%d ", c_per); tCount = 0; } else conflict = 0; } /* Print the previous course and teacher. */ else if (tmp_day != c_day) { if(conflict == 0) { int tmp = tnameCount; if (tnameCount > 0) tnameCount = printHrSchedule(tnameCount, space, tmp_subj, tmp_tname); if (tnameCount == 0 && tmp > 0) dayCount=getCount(tmp_day, days)+1; tCount = 0; } else conflict = 0; } tnameCount++; strcpy(tmp_tname, c_tname); tmp_per = c_per; strcpy(tmp_subj, c_subj); tmp_day = c_day; tmp_hr = c_hr; tmp_grade = c_grade; } /* We still need to print for the last slot. */ curCount = getCount(tmp_day, days); j = (curCount-dayCount)*15; for (i = 0; i < j; i++) space[i] = ' '; if(j >= 0) space[j] = 0; else space[0] = 0; if (tnameCount > 0) printHrSchedule(tnameCount, space, tmp_subj, tmp_tname); printf("\n----"); for (i = 0; i < c_numDays; i++) printf("----------------"); printf("\n"); /* Check end of the cursor. */ if (SQLCODE != 100) printf("Error! Reason: %ld\n", sqlca.sqlcode); /* Close the cursor. */ EXEC SQL CLOSE c62; } /*************************************************************/ void changePeriod() { int flag=0; int add=0; EXEC SQL BEGIN DECLARE SECTION; long c_tmpcno7; char c_tmpday7[1]; long c_tmpper7; EXEC SQL END DECLARE SECTION; while (1) { int choice71=0; printf("\n\t\t************************************************"); printf("\n\t\t* *"); printf("\n\t\t* PLEASE SELECT ONE OF FOLLOWING OPTIONS *"); printf("\n\t\t* 1. Specify the course by course number *"); printf("\n\t\t* 2. Specify the course by the subject and *"); printf("\n\t\t* a homeroom to which it is taught *"); printf("\n\t\t* 3. Quit to the main menu *"); printf("\n\t\t************************************************"); printf("\n\t\t-----> "); scanf("%d", &choice71); while (choice71 < 1 || choice71 > 3) { printf("\nPlease enter 1-3 for your selection:"); scanf("%d", &choice71); } switch (choice71) { case 1: printf("\n"); printf("Please input the course number: "); scanf("%ld", &c_tmpcno7); break; case 2: { EXEC SQL BEGIN DECLARE SECTION; long c_tmpgrade7; char c_tmpsubj7[3]; char c_tmphr7[1]; EXEC SQL END DECLARE SECTION; printf("\n"); printf("Please input the grade(an integer): "); scanf("%ld", &c_tmpgrade7); printf("homeroom(single character): "); scanf("%s", c_tmphr7); printf("and the subject(3 characters, like Mat): "); scanf("%s", c_tmpsubj7); //printf("subj: %s, grade: %ld hr: %s\n", c_tmpsubj7, c_tmpgrade7, c_tmphr7); EXEC SQL SELECT C.cno INTO :c_tmpcno7 FROM Courses C, Taught_to T WHERE C.cno=T.cno AND C.subj=:c_tmpsubj7 AND T.grade=:c_tmpgrade7 AND (T.hr=:c_tmphr7 OR T.hr='Z'); } break; case 3: break; default: break; } if(choice71==3) break; else { EXEC SQL DECLARE c71 CURSOR FOR SELECT A.day, A.per FROM Assigned A WHERE A.cno= :c_tmpcno7 ORDER BY A.day, A.per; EXEC SQL OPEN c71; printf("\n"); printf("Periods that have been assigned to course %ld are:\n", c_tmpcno7); printf(" DAY PER \n"); printf(" ------------\n"); do { EXEC SQL FETCH c71 into :c_tmpday7, :c_tmpper7; if (SQLCODE !=0) { if (flag==0) { printf("\n"); printf("No peroids have been assigned to this course yet!\n"); printf("Do you want to add a period? "); printf("( 1 for yes, 0 for no.) \n"); scanf("%d", &add); break; } else break; } flag++; printf(" %s %ld \n", c_tmpday7, c_tmpper7); } while(1); EXEC SQL CLOSE c71; if (add==1) { int tmp1=0; printf("\n"); printf("Please specify the day(a single character) and period"); printf("(an integer) you want this course to be assigned to, "); printf("use a blank between them.\n"); scanf("%s", c_tmpday7); scanf("%ld", &c_tmpper7); EXEC SQL INSERT INTO Assigned VALUES (:c_tmpcno7, :c_tmpday7, :c_tmpper7); if(SQLCODE!= 0){ printf("can't add on Assigned, reason: %ld\n", SQLCODE); exit(1); } printf("\n"); printf("Peroid %s %ld has been added to course %ld successfully!\n", c_tmpday7, c_tmpper7, c_tmpcno7); add=0; printf("\n"); printf("Want to make another change? ( 1 for yes, 0 for no.)\n"); scanf("%d", &tmp1); if (tmp1==0) break; else tmp1=0; } else { int choice7=0; //if (flag==1) { flag=0; printf("\n\t\t************************************************"); printf("\n\t\t* Change Period Submenu *"); printf("\n\t\t* *"); printf("\n\t\t* PLEASE SELECT ONE OF FOLLOWING OPTIONS *"); printf("\n\t\t* 1. Add a new period *"); printf("\n\t\t* 2. Modify an existing period *"); printf("\n\t\t* 3. Delete an existing period *"); printf("\n\t\t* 4. Quit to the previous menu *"); printf("\n\t\t************************************************"); printf("\n\t\t-----> "); scanf("%d", &choice7); while (choice7 < 1 || choice7 > 4) { printf("\nPlease enter 1-4 for your selection:"); scanf("%d", &choice7); } switch (choice7) { case 1: { int tconflict=0; int hrconflict=0; int ch=3; EXEC SQL BEGIN DECLARE SECTION; char c_tmptname7[15]; long c_tmpcno72; long c_tmpcno73; long c_tmpgrade73; char c_tmphr73[1]; EXEC SQL END DECLARE SECTION; strcpy(c_tmptname7, "abc"); printf("\n"); printf("Please specify the day and period"); printf("you want this course to be assigned to.\n"); printf("Day(single upper case letter): "); scanf("%s", c_tmpday7); printf("Peroid(an integer): "); scanf("%ld", &c_tmpper7); printf("\n"); /*********************************/ /* Check for teacher conflict. */ /*********************************/ EXEC SQL DECLARE c72 CURSOR FOR SELECT TB1.tname, TB1.cno FROM Assigned A, Taught_by TB1, Taught_by TB2 WHERE A.day=:c_tmpday7 AND A.per=:c_tmpper7 AND TB1.cno=A.cno AND TB2.cno=:c_tmpcno7 AND TB1.tname=TB2.tname; EXEC SQL OPEN c72; do { EXEC SQL FETCH c72 into :c_tmptname7, :c_tmpcno72; if (SQLCODE !=0) { break; } tconflict=1; printf("# CONFLICT: Teacher %s scheduled to course %ld at period %s %ld #\n", c_tmptname7, c_tmpcno72, c_tmpday7, c_tmpper7); } while(1); EXEC SQL CLOSE c72; /*********************************/ /* Check for homeroom conflict. */ /*********************************/ EXEC SQL DECLARE c73 CURSOR FOR SELECT TT1.grade, TT1.hr, TT1.cno FROM Assigned A, Taught_to TT1, Taught_to TT2 WHERE A.day=:c_tmpday7 AND A.per=:c_tmpper7 AND TT1.cno=A.cno AND TT2.cno=:c_tmpcno7 AND TT1.grade=TT2.grade AND (TT1.hr=TT2.hr OR TT1.hr='Z' OR TT2.hr='Z'); EXEC SQL OPEN c73; printf("\n"); do { EXEC SQL FETCH c73 into :c_tmpgrade73, :c_tmphr73, :c_tmpcno73; if (SQLCODE !=0) { break; } hrconflict=1; if (strcmp(c_tmphr73, "Z")==0) printf("# CONFLICT: All homerooms of grade %ld scheduled to course %ld at period %s %ld #\n", c_tmpgrade73, c_tmpcno73, c_tmpday7, c_tmpper7); else printf(" # CONFLICT: Homeroom %ld %s scheduled to course %ld at period %s %ld#\n", c_tmpgrade73, c_tmphr73, c_tmpcno73, c_tmpday7, c_tmpper7); } while(1); EXEC SQL CLOSE c73; if (tconflict==1 || hrconflict==1) { printf("\nDo you still want to add this period? (1 for yes, 0 for no.)\n"); scanf("%d", &ch); if (ch==0) { printf("\nNo change has been made to course %ld !\n", c_tmpcno7); break; } } EXEC SQL INSERT INTO Assigned VALUES (:c_tmpcno7, :c_tmpday7, :c_tmpper7); if(SQLCODE!= 0){ printf("can't add on Assigned, reason: %ld\n", SQLCODE); exit(1); } printf("\n"); printf("Peroid %s %ld has been added to course %ld successfully!\n", c_tmpday7, c_tmpper7, c_tmpcno7); } break; case 2: { int tconflict=0; int hrconflict=0; int ch=3; EXEC SQL BEGIN DECLARE SECTION; char tmp2[1]; long tmp3; char c_tmptname74[15]; long c_tmpcno74; long c_tmpcno75; long c_tmpgrade75; char c_tmphr75[1]; EXEC SQL END DECLARE SECTION; strcpy(c_tmptname74, "abc"); printf("\n"); printf("Please specify the day and period you want to modify.\n"); printf("Day(single upper case letter): "); scanf("%s", tmp2); printf("Period(an integer): "); scanf("%ld", &tmp3); printf("\n"); printf("Please specify the day and period"); printf("you want this course to be assigned to.\n"); printf("Day(single upper case letter): "); scanf("%s", c_tmpday7); printf("Period(an integer): "); scanf("%ld", &c_tmpper7); printf("\n"); /*********************************/ /* Check for teacher conflict. */ /*********************************/ EXEC SQL DECLARE c74 CURSOR FOR SELECT TB1.tname, TB1.cno FROM Assigned A, Taught_by TB1, Taught_by TB2 WHERE A.day=:c_tmpday7 AND A.per=:c_tmpper7 AND TB1.cno=A.cno AND TB2.cno=:c_tmpcno7 AND TB1.tname=TB2.tname; EXEC SQL OPEN c74; do { EXEC SQL FETCH c74 into :c_tmptname74, :c_tmpcno74; if (SQLCODE !=0) { break; } tconflict=1; printf("# CONFLICT: Teacher %s scheduled to course %ld at period %s %ld #\n", c_tmptname74, c_tmpcno74, c_tmpday7, c_tmpper7); } while(1); EXEC SQL CLOSE c74; /*********************************/ /* Check for homeroom conflict. */ /*********************************/ EXEC SQL DECLARE c75 CURSOR FOR SELECT TT1.grade, TT1.hr, TT1.cno FROM Assigned A, Taught_to TT1, Taught_to TT2 WHERE A.day=:c_tmpday7 AND A.per=:c_tmpper7 AND TT1.cno=A.cno AND TT2.cno=:c_tmpcno7 AND TT1.grade=TT2.grade AND (TT1.hr=TT2.hr OR TT1.hr='Z' OR TT2.hr='Z'); EXEC SQL OPEN c75; printf("\n"); do { EXEC SQL FETCH c75 into :c_tmpgrade75, :c_tmphr75, :c_tmpcno75; if (SQLCODE !=0) { break; } hrconflict=1; if (strcmp(c_tmphr75, "Z")==0) printf("# CONFLICT: All homerooms of grade %ld scheduled to course %ld at period %s %ld #\n", c_tmpgrade75, c_tmpcno75, c_tmpday7, c_tmpper7); else printf("# CONFLICT: Homeroom %ld %s scheduled to course %ld at period %s %ld #\n", c_tmpgrade75, c_tmphr75, c_tmpcno75, c_tmpday7, c_tmpper7); } while(1); EXEC SQL CLOSE c75; if (tconflict==1 || hrconflict==1) { printf("\nDo you still want to modify this period? (1 for yes, 0 for no.)\n"); scanf("%d", &ch); if (ch==0) { printf("\nNo change has been made to course %ld !\n", c_tmpcno7); break; } } EXEC SQL UPDATE Assigned SET cno=:c_tmpcno7, day=:c_tmpday7, per=:c_tmpper7 WHERE cno=:c_tmpcno7 AND day=:tmp2 AND per=:tmp3; if(SQLCODE!= 0){ printf("can't add on Assigned, reason: %ld\n", SQLCODE); exit(1); } printf("\n"); printf("Peroid %s %ld has been assigned to course %ld successfully!\n", c_tmpday7, c_tmpper7, c_tmpcno7); } break; case 3: printf("\n"); printf("Please specify the day and period you want to delete. \n"); printf("Day(single upper case letter): "); scanf("%s", c_tmpday7); printf("Period(an integer): "); scanf("%ld", &c_tmpper7); EXEC SQL DELETE FROM Assigned WHERE cno=:c_tmpcno7 AND day=:c_tmpday7 AND per=:c_tmpper7; if(SQLCODE!= 0){ printf("can't delete on Assigned, reason: %ld\n", SQLCODE); exit(1); } printf("\n"); printf("Peroid %s %ld has been deleted from course %ld successfully!\n", c_tmpday7, c_tmpper7, c_tmpcno7); break; case 4: break; default: break; } //} } } } } /************************************************************************ * gives the course cno which has yet to be assigned all of its periods * * has the fewest number of periods available to it * ************************************************************************/ void fewestPer() { EXEC SQL BEGIN DECLARE SECTION; long c_tmpcno; long c_assignedPer, c_fewestPer, availablePer; short dummy; EXEC SQL END DECLARE SECTION; /* initialization */ c_tmpcno = -1; c_fewestPer = 9999; /* get all cno and corresponding nper */ EXEC SQL DECLARE c81 CURSOR FOR SELECT cno, nper FROM Courses; if(SQLCODE!= 0){ printf("can't query on Courses, reason: %ld\n", SQLCODE); exit(1); } /* retrieve all periods */ EXEC SQL DECLARE c82 CURSOR FOR SELECT * FROM Periods P; if(SQLCODE!= 0){ printf("query failed on P and A, reason: %ld\n", SQLCODE); exit(1); } EXEC SQL OPEN c81; EXEC SQL FETCH c81 INTO :c_cno, :c_nper; while(SQLCODE==0) { availablePer = 0; /* initialization */ EXEC SQL SELECT COUNT(*) INTO :c_assignedPer FROM Assigned A WHERE A.cno=:c_cno; if(c_assignedPer < c_nper) /* assigned less than nper */ { EXEC SQL OPEN c82; EXEC SQL FETCH c82 INTO :c_day, :c_per; while(SQLCODE == 0) { EXEC SQL SELECT COUNT(*) INTO :dummy FROM Assigned A WHERE A.day = :c_day AND A.cno = :c_cno; /* dummy = 0 means there is no this couse assigned on c_day*/ if(dummy == 0) { EXEC SQL SELECT COUNT(*) INTO :dummy FROM Taught_by T1, Taught_by T2, Assigned A WHERE T1.cno = :c_cno AND T1.tname = T2.tname AND T2.cno = A.cno AND A.day = :c_day AND A.per = :c_per; /* dummy = 0 means no teacher conflict */ if(dummy == 0) { EXEC SQL SELECT COUNT(*) INTO :dummy FROM Taught_to T1, Taught_to T2, Assigned A WHERE T1.cno = :c_cno AND T1.grade = T2.grade AND (T1.hr = 'z' OR T2.hr = 'z' OR T1.hr = T2.hr) AND T2.cno = A.cno AND A.day = :c_day AND A.per = :c_per; /* dummy=0 means no homeroom conflict */ if(dummy == 0) availablePer++; } } EXEC SQL FETCH c82 INTO :c_day, :c_per; } EXEC SQL CLOSE c82; if(availablePer < c_fewestPer) { c_tmpcno = c_cno; c_fewestPer = availablePer; } } EXEC SQL FETCH c81 INTO :c_cno, :c_nper; } EXEC SQL CLOSE c82; EXEC SQL COMMIT; if(c_tmpcno == -1) printf("No courses need to be assgined period.\n"); else printf ("Course with cno %ld has fewest number of period available.\n", c_tmpcno); } /***********************************************************************/ /* option 9 reduce windows */ /***********************************************************************/ /* * count the number of windows in teachers schedule on c_day. */ int countDailyWin() { EXEC SQL BEGIN DECLARE SECTION; long numwin; /* total number of windows */ EXEC SQL END DECLARE SECTION; /* do the query */ EXEC SQL SELECT SUM(winnum) into :numwin FROM (SELECT tname, (MAX(per)-MIN(per)+1-COUNT(DISTINCT per)) AS winnum FROM Taught_by tb, Assigned a WHERE tb.cno=a.cno AND a.day=:c_day GROUP BY tname) AS TEMP; return numwin; } /* * update table Assigned by switching all the courses assigned to period * with all the courses assigned to period b on day c_day. */ void switchPer(short a, short b) { EXEC SQL BEGIN DECLARE SECTION; short tmpPer, pera, perb; EXEC SQL END DECLARE SECTION; tmpPer = '@'; /* initialize tmpPer*/ pera = a; /* assign value of a to host variabel pera */ perb = b; /* assign value of b to host variable perb */ /* use three SQL update command to implement switch periods */ EXEC SQL UPDATE Assigned SET per = :tmpPer WHERE day = :c_day AND per = :pera; EXEC SQL UPDATE Assigned SET per = :pera WHERE day = :c_day AND per = :perb; EXEC SQL UPDATE Assigned SET per = :perb WHERE day = :c_day AND per = :tmpPer; } /* * try all possible pair of switch on the same day to make the number * of windows in teachers schedule to as few as possible */ void reduceWin() { EXEC SQL BEGIN DECLARE SECTION; long numWin, tmpnumwin, numper; long oldTotalWin, newTotalWin; short *per; char dummyDay; short dummyPer, peri, perj; EXEC SQL END DECLARE SECTION; /* initialization, assume no day is named '@', and no per has value -1*/ dummyDay = '@'; dummyPer = -1; /* get the overall number of windows before switch */ oldTotalWin = countWindows(); /* get different days */ EXEC SQL DECLARE c91 CURSOR FOR SELECT DISTINCT P.day FROM Periods P; if(SQLCODE!= 0){ printf("can't query on Periods, reason: %ld\n", SQLCODE); exit(1); } EXEC SQL OPEN c91; EXEC SQL FETCH c91 INTO :c_day; /* perform switch on each day */ while(SQLCODE == 0) { int i, j; /* number of windows on c_day before switch */ numWin = countDailyWin(); /* get total number of periods on c_day */ EXEC SQL SELECT COUNT(*) INTO :numper FROM Periods P WHERE P.day = :c_day; /* declare cursor to stor names of different period */ EXEC SQL DECLARE c92 CURSOR FOR SELECT DISTINCT P.per FROM Periods P WHERE P.day = :c_day; /* stor these period name into an array */ per = (short *)malloc(numper * sizeof(short)); EXEC SQL OPEN c92; for (i=0; i 0) { printf("\tafter switch periods -------- %ld\n", newTotalWin); printf("\tnumber of windows is reduced by %ld.\n", oldTotalWin-newTotalWin); } else { printf("This number is already minimun.\n"); } } /************************************************************************ * Create individual teacher constraints through trigger. * ************************************************************************/ void triggerTeacher() { int choice; EXEC SQL BEGIN DECLARE SECTION; /* declare host variables */ char trig_insert[30]; char trig_update[30]; EXEC SQL END DECLARE SECTION; /* Loop until user exit. */ while (choice != 4) { printf("\n\t\t* PLEASE SELECT ONE OF FOLLOWING OPTIONS *"); printf("\n\t\t* 1. Teacher_time constraints *"); printf("\n\t\t* 2. Teacher_course constraints *"); printf("\n\t\t* 3. Teacher_load constraints *"); printf("\n\t\t* 4. Back to the main manu *"); printf("\n\t-----> "); scanf("%d", &choice); while (choice < 1 || choice > 4) { printf("\nPlease enter 1-3 for your selection:"); scanf("%d", &choice); } switch (choice) { case 1: /* The teacher can't teach the specified period. */ printf("\nPlease type teacher_time constraint in following \ format:"); printf("\n\tteacher_name(less than 15 chars) day(char) \ per(int)"); printf("\n\t-----> "); scanf("%s %c %hd", c_tname, &c_day, &c_per); /* Get the names for Insert and Update triggers. */ strcpy(trig_insert, c_tname); strcat(trig_insert, "_time_insert"); strcpy(trig_update, c_tname); strcat(trig_update, "_time_update"); printf("\ntrig_insert=%s\ttrig_update=%s\n", trig_insert, trig_update); printf("\nname=%s\tday=%c\tper=%d\n", c_tname, c_day, c_per); /* Create a View for the triggers. */ EXEC SQL GRANT ALL ON Table Taught_by TO c434h05; EXEC SQL GRANT ALL ON Table Assigned TO c434h05; EXEC SQL CREATE VIEW Teach_Per(tname, day, per) AS SELECT T.tname, A.day, A.per FROM Taught_by T, Assigned A WHERE T.cno=A.cno; /* Create the triggers. */ EXEC SQL CREATE TRIGGER trig_insert NO CASCADE BEFORE INSERT ON Teach_Per FOR EACH ROW MODE DB2SQL WHEN (day=c_day AND per=c_per) SIGNAL SQLSTATE'75000' ('Can not teach in the period specified!'); EXEC SQL CREATE TRIGGER trig_update NO CASCADE BEFORE UPDATE OF day, per ON Teach_Per FOR EACH ROW MODE DB2SQL WHEN (day=c_day AND per=c_per) SIGNAL SQLSTATE'75000' ('Can not teach in the period specified!'); break; case 2: /* The teacher can't teach the specified course. */ printf("\nPlease type teacher_course constraint in \ following format:"); printf("\n\tteacher_name(less than 15 chars) \ course_number(int)"); printf("\n\t-----> "); scanf("%s %ld", c_tname, &c_cno); strcpy(trig_insert, c_tname); strcat(trig_insert, "_course_insert"); strcpy(trig_update, c_tname); strcat(trig_update, "_course_update"); EXEC SQL CREATE TRIGGER trig_update NO CASCADE BEFORE UPDATE OF cno ON Taught_by FOR EACH ROW MODE DB2SQL WHEN (tname=c_tname AND cno=c_cno AND tname=c_tname) SIGNAL SQLSTATE'75000'('Can not teach the course specified!'); EXEC SQL CREATE TRIGGER trig_insert NO CASCADE BEFORE INSERT ON Taught_by FOR EACH ROW MODE DB2SQL WHEN (tname=c_tname AND cno=c_cno) SIGNAL SQLSTATE'75000'('Can not teach the course!'); break; case 3: /* The teacher can't teach more than the specified load. */ printf("\nPlease type teacher_load constraint in following \ format:"); printf("\n\tteacher_name(less than 15 chars) load(int) "); printf("\n\t-----> "); scanf("%s %hd", c_tname, &c_tload); strcpy(trig_insert, c_tname); strcat(trig_insert, "_load_insert"); strcpy(trig_update, c_tname); strcat(trig_update, "_load_update"); EXEC SQL CREATE TRIGGER trig_update NO CASCADE BEFORE UPDATE OF tload ON Teachers FOR EACH ROW MODE DB2SQL WHEN (tname=c_tname AND tload>c_tload) SIGNAL SQLSTATE'75000'('Load is over the limit for that \ teacher!'); EXEC SQL CREATE TRIGGER trig_insert NO CASCADE BEFORE INSERT ON Teachers FOR EACH ROW MODE DB2SQL WHEN (tname=c_tname AND tload>c_tload) SIGNAL SQLSTATE'75000'('Load is over the limit for that \ teacher!'); break; case 4: break; default: break; } } } int main() { int choice = 0; strcpy(db_name, "c434h05"); EXEC SQL CONNECT TO:db_name; if (sqlca.sqlcode != 0) { printf("Connect failed!: reason %ld\n", sqlca.sqlcode); exit(1); } while (choice != 11) { /***********change here if add more options*/ printf("\n\t\t************************************************"); printf("\n\t\t* WELCOME!!! *"); printf("\n\t\t* *"); printf("\n\t\t* PLEASE SELECT ONE OF FOLLOWING OPTIONS *"); printf("\n\t\t* 1. Report teacher conflicts *"); printf("\n\t\t* 2. Report homeroom conflicts *"); printf("\n\t\t* 3. Report all course that are taught *"); printf("\n\t\t* twice a day *"); printf("\n\t\t* 4. Report total number of windows in *"); printf("\n\t\t* tearcher schedules. *"); printf("\n\t\t* 5. Report all teacher schedules. *"); printf("\n\t\t* 6. Report all homeroom schedules. *"); printf("\n\t\t* 7. Change period assignments *"); printf("\n\t\t* 8. Report the course that has fewest number *"); printf("\n\t\t* of periods available to finish assignment*"); printf("\n\t\t* 9. switch period to get less windows *"); printf("\n\t\t* 10.add teacher constraint. *"); printf("\n\t\t* 11. Quit *"); printf("\n\t\t************************************************"); printf("\n\t\t-----> "); scanf("%d", &choice); while (choice < 1 || choice > 11) { /* change here if add * more options */ printf("\nPlease enter 1-10 for your selection:"); scanf("%d", &choice); } /* * initialize all string, otherwise sql initialize it to 8 * char */ strcpy(c_subj, " "); strcpy(c_tname, " "); switch (choice) { case 1: tConflict(); break; case 2: hrConflict(); break; case 3: dupOffer(); break; case 4: c_numwin = countWindows(); printf ("The total number of window in the ");\ printf("teacher schedules is %ld.\n", c_numwin); break; case 5: tSchedule(); break; case 6: hrSchedule(); break; case 7: changePeriod(); break; case 8: fewestPer(); break; case 9: reduceWin(); break; case 10: triggerTeacher(); case 11: break; default: break; } } printf("\n\t********************************************************"); printf("\n\t* *"); printf("\n\t* Thank you for using c434h05 timetable database! *"); printf("\n\t* *"); printf("\n\t* GOOD BYE! *"); printf("\n\t********************************************************\n"); EXEC SQL CONNECT RESET; return 1; }