Chris Pollett >
Old Classes
> |
HW1 Solutions PageE&N 1.7 Some informal queries: Return all the students who failed some course in Fall 99. Return all students in CC3380 who don't have the prerequisites for this class Return all the class names of classes Knuth is teaching in Spring 99. Some update operation. (I am counting things that would involve adding rows, changing rows, or creating or changing tables) Change the professor of sec 85 to Knuth. Change the grade of Smith in Chang's MATH2410 Fall 99 to an A. Add the prerequisite of MATH2410 for CS2000. E&N 1.9 The StudentNumber's in GRADE_REPORT are related to the StudentNumber's in STUDENT. The CourseNumber's in SECTION are related to the CourseNumber's in COURSE. The CourseNumber's in PREREQUISITE are related to the CourseNumber's in COURSE. The PrerequisiteNumber's in SECTION are related to the CourseNumber's in COURSE. The SectionIdentifier's in GRADE_REPORT are related to the SectionIdentifier's in SECTION. E&N 2.10 Some different users for the database in Figure 1.2 are students, teachers, secretaries, principals, school board statistician. USER USER CATEGORY TYPICAL APP TYPICAL INTERFACE secretary parametric enter student data forms/parametric student casual get grades, enroll GUI/forms teacher casual get rosters, ck student GUI/menu principal casual ck student, ck professor GUI/menu statistician sophisticated calc. student pop. trends menu/SQL // EmailList.java -- create grade/email report from School DB import java.io.*; import java.util.*; /** This class can be used as an application to print out the Name, Email, and Grade of each student in the School DB who has ever taken some course. It is run from the command line as: java EmailList course This class provides two static methods which may be of independent use: printCourse(courseName) - which prints all the Name, Email, Grade's of students who have taken courseName projectSelect - which can be use to apply a PrintStrategy to all the projected columns of all the rows satisfying a selection criteria. @author Chris Pollett @version 1.0 */ public class EmailList { /** Prints the Name, Email, and Grade of each student in the School DB who has ever taken the course courseName. @param courseName - name of course to print student data out for */ public static void printCourse(String courseName) { PrintStrategy strategy = new PrintStrategy() { public void printFunction(String out) { printSection(out); } }; int[] section = {0}; // column with section number projectSelect("SECTION", 2, courseName, section, strategy); } /** Selects rows from table whose selectNum column matches string select. Projects from a selected row the columns in project and applies printFunction in strategy to them. @param table - School DB table on which to do projection seclection operation. @param selectNum - column in table to select against @param select - String to match for in selectNum column @param project - which columns to output if have a match @param strategy - object which says what to do with a matched row */ public static void projectSelect(String table, int selectNum, String select, int[] project, PrintStrategy strategy) { try { BufferedReader in = new BufferedReader( new FileReader(table)); int proLength = (project.length > 0) ? project[project.length-1] :0; int length = (selectNum < proLength) ? proLength : selectNum; String row; while((row = in.readLine()) !=null) { StringTokenizer fields = new StringTokenizer(row,"\""); boolean printRow = false; int projectCnt = 0; String out = ""; for(int i=0; i <= length; i++) { String token = fields.nextToken(); if(projectCnt <= proLength && i == project[projectCnt]) { out += "\""+token+"\" "; projectCnt++; } if(i== selectNum && select.equals(token)) printRow = true; } if(printRow) { strategy.printFunction(out); } } in.close(); } catch(IOException io) { quitWithMessage("Error in reading"+table+"table.", io); } } /* Driver for our application. @param args - args[0] is the name of the course we wish to print out. */ public static void main(String[] args) { if(args.length != 1) { System.err.println("This program should be run from"); System.err.println("the command line with a line like:"); System.err.println("\njava EmailList coursename\n"); System.err.println("where coursename is the name of course to list"); System.exit(1); } printCourse(args[0]); } /* Prints the Name, Email, and Grade of each student in the School DB who was in the section row. @param row - is the section number in quotes. */ private static void printSection(String row) { StringTokenizer fields = new StringTokenizer(row,"\""); String sectionName = fields.nextToken(); PrintStrategy strategy = new PrintStrategy() { public void printFunction(String out) { printStudent(out); } }; int[] student = {0,4}; // 0 -column of ID ; 4 -column of grade projectSelect("GRADE_REPORT", 2, sectionName, student, strategy); } /* Prints Name, Email, Grade of STUDENT supplied in row @param row - contains the ID and grade of selected student in quotes */ private static void printStudent(String row) { StringTokenizer fields = new StringTokenizer(row,"\""); String ID = fields.nextToken(); fields.nextToken(); String grade = fields.nextToken(); PrintStrategy strategy = new PrintStrategy() { public void printFunction(String out) { System.out.print(out); } }; int[] nameEmail = {0,8}; // 0 -column of name; 8 -column of e-mail projectSelect("STUDENT", 2, ID, nameEmail, strategy); System.out.println("\""+grade+"\""); } /* Output error message and quit @param mesg - message to send to err @param io - exception that caused us to want to terminate. */ private static void quitWithMessage(String mesg, IOException io) { System.err.println(mesg); io.printStackTrace(); System.exit(1); } } /** Used by EmailList.projectSelect to perform an operation the projected columns of the given selected row. */ interface PrintStrategy { /** Operation we perform on the selected row @param out - projected columns from selected row. */ public void printFunction(String out); } |