HW1 Solutions Page

Return to homework page.

E&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);
}