HW1 Solutions Page

Return to homework page.

1.11 Cite some examples of integrity constraints that you think hold on the database shown in Figure 1.2.

I was looking for maybe a half dozen examples and these examples should illustrate at least a couple of different kinds of constraints. Some example constraints that should hold are:

Every CourseNumber in SECTION snd in PREREQUISITE should match a CourseNumber of a tuple in COURSE.

Every SectionIdentifier in GRADE_REPORT should match a SectionIdentifier of a tuple in SECTION.

Every StudentNumber in GRADE_REPORT should match a StudentNumber of a tuple in STUDENT.

Each StudentNumber in STUDENT, SectionIdentifier in SECTION, and CourseNumber in COURSE should be unique

Each SectionIdentifier, each StudentNumber, each Year, each CreditHours, and each Class should be an int

Each Grade in GRADE_REPORT should be a single char

Each Name, CourseName, CourseNumber, Department, Semester, Instructor, should be a varchar(40)

2.11 Think of different users for the database of Figure 1.2 What types of applications would each user need? To which user category would each belong, and what type of interface would each need?

The database in question is a database for a school. Typical users might be students, instructors, registrar clerk, departmental secretaries, DBA, chairs and deans. A student might want to add or drop a class or check grades. A student would probably use a menu-based web interface. Instructors would want to get class roster as well as enter grades. The former could be done with a menu-based web interface, and the latter could be done with a form-based interface. Students and instructors would probably count as naive user. A clerk at the registrar's office might initial set up a students data and might use an interface for a parametric user. A departmental secratary might need to set up information for a student in a specialized manner or do ad-hoc queries like a casual end user. This could be done with a forms-based interface. Chair and Deans might need to do specialized queries to see how many students are taking which course in which departments for budget allocations. They could be viewed as sophisticated end users and they might use a GUI interface. Finally, DBA might tweak with the actual database, and would use an interfaces geared towards DBAs.

Then imagine you had the task of storing data about bank accounts and transactions on such accounts. What different attributes would you store? What ways would you group these attributes into tables

I will both group under a name and list in parentheses different attributes below:

BANK_BRANCH(BranchNo, Branch_Name, Manager, Street Address)

CUSTOMER(CID, Name, Address, BranchNo)

ACCOUNT(CID, AccountType, AccountNo, Balance)

ATM_SERVICE_PROVIDER(ServiceID, Address)

TRANSACTION(AccountNo, Type, Amount, ServiceID, Fees, Time, Date)

SectionProf.java

//SectionProf.java
import java.util.*;
import java.io.*;

/**
   Solution to first homework for CS157a Fall 2005. This program is run from the
   command line with a line like:
   java SectionProf file1 file2
   
   file1 is supposed to be a file of course info, file2 is supposed to
   be a file of sections info. The output is supposed to be lines
   printed to the screen of professor followed by coursenames as per the
   HW1 description
   
   @author C. Pollett
   @version 7.9.2005
*/
public class SectionProf 
{

   /**
      This method cycles through the sections and looks for courses which match
      if so they are output.
   
      @throws FileNotFoundException if cannot find sections file
      @throws IOException on any other read error
   */
   public static void processSections() throws FileNotFoundException, IOException
   {

      BufferedReader buf= new BufferedReader(
         new FileReader(sectionsName), BUFFER_SIZE);
      
      String line;
      String columns[];
      
      line = buf.readLine();
      
      while(line != null)
      {
         columns = line.split("\t\042"); /* note: you can fool this regular expression
            but it is not worth the added complication to make it perfect */
         processCourses("\""+columns[1], columns[4]);

         line = buf.readLine();
      }
             
      buf.close();
  
   }

   /**
      This method takes the supplied courseNumber and professor and scan the courses file once for a match
      if so it is output to the screen
   
      @throws FileNotFoundException if cannot find courses file
      @throws IOException on any other read error   
   */
   public static void processCourses(String courseNumber, String professor) throws FileNotFoundException, IOException
   {

      BufferedReader buf= new BufferedReader(
         new FileReader(coursesName), BUFFER_SIZE);
      
      String line;
      String columns[];
      
      line = buf.readLine();
      
      while(line != null)
      {
         columns = line.split("\t\042");/* note: you can fool this regular expression
            but it is not worth the added complication (you would probably need to write another
            method) to make it perfect */

         if(columns[0].equals(courseNumber))
         {
            System.out.println("\""+professor+"\t\""+columns[1]);
         }

         line = buf.readLine();
      }
             
      buf.close();
  
   }
   
   /**
      Driver method for our application
      
      @param args - command line arguments used to set up the coursesName and sectionsName
   */         
   public static void main (String args[])
   {
      try
      {
         coursesName = args[0];
         sectionsName = args[1]; // if too few arguments an exception will be thrown and caught below
          
         processSections();
      }
      catch(FileNotFoundException fe)
      {
         System.err.println("File not found");
         System.exit(1);
      }
      catch(IOException ie)
      {
         System.err.println("An I/O Exception occurred:"+ie.toString());
         System.exit(1);
      }
      catch(Exception ee)
      {
         ee.printStackTrace();
         System.exit(1);
      }
   }
   
   public static String coursesName; // name of the file with courses
   public static String sectionsName; // name of the file with sections
   public static int BUFFER_SIZE = 4096; // how many bytes for our file buffer.
}

Return to homework page.