Student Database

Infrastructure

The student database is built inside a PostgreSQL database engine running on a Solaris server. A daily backup is made of the data along with a full transaction log that includes the person and IP address of the user making the query. A Microsoft Access front end is used by some staff so they can have more direct access to the data.

Miscellaneous

When have ODBC write conflict issues on table "ProgramCandidacy" check the LastUpdate field. Apparently, ODBC can't handle the precision used by Postgres. Can fix the problem for an individual record with the following

cehddb=# update "ProgramCandidacy" set "LastUpdate"=date_trunc('second',"LastUpdate") where "ProgramCandidacyID"=138664;

cehddb=# update "ProgramCandidacy" set "LastUpdate"=date_trunc('second',"LastUpdate") where "LastUpdate" != date_trunc('second',"LastUpdate");

cehddb=# update "SemesterRecords" set "LastUpdated"=date_trunc('second',"LastUpdated") where "LastUpdated" != date_trunc('second',"LastUpdated");

For these two tables, the problem is with the LastUpdated fields populated by 'now.' To prevent this from happening in the first place, use date_trunc('second',timestamp 'now' ) instead of 'now.'

Student/Program Data Updates

  1. Run report Retrieve data from Argos Report: SOC_GET_BASIC_STUDENT_LIST under data block SOC_SIMPLE_LIST for given semester
  2. Save CSV file to PUBLICWEB:/disks/users/strader/Argos/students_201021.csv
  3. Copy CSV to DBWEB: scp publicweb:/disks/users/strader/Argos/students_201021.csv 201021.csv
  4. Clean file by opening/saving the file in nano as a "DOS" file.
  5. within addstudents.pl
    • Adjust $CURRSEM
    • Adjust MAKEPROGRAMCHANGE, ADDSTUDENTS, INTERACTIVE appropriately
    • ADDSTUDENTS = 1 will add any missing students to the DB, set to 0 to not add. Allows user to see students to be added.
    • MAKEPROGRAMCHANGE = 1 will make the program changes, set to 0 to see counts of proposed changes
    • INTERACTIVE = 1 asks the user before making each program change, set to 0 to make all proposed changes.
    • Run: ./addstudents.pl 201021.txt to run the checks and updates.

Course/Grade Data Updates

  1. Run report Retrieve data from Argos Report: SOC_CEHD_COURSE_RECORDS under data block SOC_COURSE_RECORDS_All for given semester
  2. Save CSV file to PUBLICWEB:/disks/users/strader/Argos/courses_201021.csv
  3. Copy CSV to DBWEB: scp publicweb:/disks/users/strader/Argos/courses_201021.csv 201021.csv
  4. Clean file by opening/saving the file in nano as a "DOS" file.
  5. within Enrollment/addcourserecords.pl
    • Adjust $CURRSEM

Graduation Data Updates

  1. Run report Retrieve data from Argos Report: SOC_CEHD_GRADUATES under data block SOC_GRADUATES for given semester
  2. Save CSV file to PUBLICWEB:/disks/users/strader/Argos/grads_201021.csv
  3. Copy CSV to DBWEB: scp publicweb:/disks/users/strader/Argos/grads_201021.csv .
  4. within Enrollment/updategraduates.pl
    • Adjust $GRADSEM and DOUPDATES and INTERACTIVE appropriately
    • Run script passing the downloaded file as a parameter

Degree Progress Data Updates

  1. Run report from Argos: SOC_ED_GRADUATE_STUDENT_TRACKING
    • Each record contains one piece of tracking information (Degree plan, prelim, defense, etc.)
    • The goal is to update the DegreeProgress table for a given user with the tracking info.
  2. Copy file to DBWEB, compass/DegreeProgress
  3. Run update.pl FILENAME
  4. Will have to manually deal with program mismatches, no open programs, missing students, etc.

Graduate Student Committees

  • Download PDF for SRC_AL_GRAD_COMM_ED from e-print
  • Select all from the PDF, copy/paste data
  • Split data by space and commas
  • Clean up the resulting file to get rid of headers and line up columns (people without middle names or with 2+ middle names will result in data in wrong columns.
    • The membership type (CH, CO, M) should be column E. Sorting by columns d, e, f can help speed up the process of adjusting data columns
    • Sort by col column D, find the ones with CH or CO in column D and move that content to the right
    • Sort by col D, then E. Find 'M' in Col D where col E is not M, CH, or CO and move columns to the right
    • Sort by Col E (where the membership types should be) and look for non M, CH, or CO. People with 4 names could be extended into this column. Delete the extra name or combine with middle name to reduce faculty data to three names.
    • Sort by Col H (should be student middle name, but some UINs will be there for students without a middle name listing). Move the ones with UINS to the right.
    • Sort by Col I (which is where UINs should be) and find rows that have a name in it. These are students with 4+ names. Delete/Combine to reduce to 3 names for each student.
    • Students without concentrations will have one less column than the rest. Need to move the dept from column 14 to 15 for all without a concentration. Sort by column O, any row with something in column 15 are the ones with concentrations. All other columns need to have the last column moved 1 column to right.
    • When complete should have 15 columns: faculty dept, faculty last, faculty first, faculty middle, member type, student last, student first, student middle, uin, t-number, class, degree, major, concentration, student department
  • Rearrange the columns to match script:
    • UIN, Student last, student first, student middle, faculty last, faculty first, faculty middle, member type, classification, degree, student dept, concentration, major
Taxonomy: