Course Pages on Scripps Scholars

Owned by Avila, Luis
Last updated: Apr 15, 2020
5 min read

Historical Documentation  (via Google Docs)

Last Edited : August 4, 5:18 PM 2014 , April 7, 2020

Luis Avila

Database for Courses

 marinara - scrippsscholars.courses  

Now lives on siodb  courses_backup schema


[lavila@siomail ~]$sudo su - bubba

siomail(bubba): /home/bubba% cd data_scripts/Grad/Scrippsscholars/


dl scripts on siomail - siomail(bubba): data_scripts/Grad/Scrippsscholars% courses.sh  (calls  dl_courses.db2 and inserts downloaded data into marinara. siodb)


not automated - need to update the term(s) in dl_courses.db2


usually hear from someone - Instructor or Grad Dept when they need a new qtr's info

 or a prior qtr updated.


need to define a procedure for initial upload of new qtr  courses and updates to prior quarters.

basically, instructors can change up to the last minute.



marinara

    mysql> describe courses;

+------------------+------------------+------+-----+---------+----------------+

| Field            | Type             | Null | Key | Default | Extra          |

+------------------+------------------+------+-----+---------+----------------+

| courses_id       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |                       

| instructor_pid   | char(9)          | YES  |     | NULL    |                |              isis id for instructor

| instructor_name  | varchar(50)      | YES  |     | NULL    |                |       name

| primary_flag     | char(1)          | YES  |     | NULL    |                |       ‘Y’ if primary, blank if not

| course           | varchar(9)       | YES  |     | NULL    |                |      the course name eg SIO109A

| course_title     | varchar(50)      | YES  |     | NULL    |                |              the title of the course

| course_code      | varchar(5)       | YES  |     | NULL    |                |           the number (eg 109A’)

| subject_code     | varchar(5)       | YES  |     | NULL    |                |           the subject  (eg SIO)

| dept_code        | varchar(5)       | YES  |     | NULL    |                |       the dept offering the course

| instr_type       | char(2)          | YES  |     | NULL    |                |           type - eg le = lecture

| term             | varchar(4)       | YES  |     | NULL    |                |           the term eg FA09

| term_id          | int(10) unsigned | YES  |     | NULL    |                |    the numeric term - for sorting

| section_subtitle | varchar(50)      | YES  |     | NULL    |                |   some sections have a title

+------------------+------------------+------+-----+---------+----------------+

13 rows in set (0.00 sec)


----------------- ---------------- ---------------- ---------------- ---------------- ----------------

(from Terri Departure Meeting Notes )


Added Sept. 18, 2012:

XVI.  RAB Courses ---->  Scripps Scholar Courses.

         marinara  scrippsscholars.courses.  data from DW student_db. dl script on siomail

          20120925 - /home/bubba/data_scripts/Grad/Scrippsscholars/

                needs to be automated - probably qtrly -  

                 see dl_grad_student_status.pl  grad_student_status.sh for example of automated

qtrly dl (already automated)

http://scrippsscholars.ucsd.edu/




20120902 Luis: - talk to Edgar about this scripps scholars app. and there is this course data there.



---------------- ---------------- ---------------- ---------------- ---------------- ----------------


# set the term code

# Set $term_code for GPA downloads by calculating the term based on the month the dl_* script is run.

sub getterm {

my $term_code = "";

my $_year = sprintf"%02d", (localtime)[5] - 100 ; # years

my $_yearminus1 = sprintf"%02d", (localtime)[5] - 101 ; # years

my $_month= sprintf "%02d", (localtime)[4] + 1;  # months

$_year = sprintf "%02d", $_year;

$_yearminus1 = sprintf "%02d", $_yearminus1 ;

#$_month= "07";

print $_month;

print $_year;

# Conditional to determine TERM_CODE.  If the date falls between Jan-March then term_code will be set to the

# Fall quarter of previous year.  If date is in April or May then term_code is set to Winter quarter of present

# year. If date is between July-September then term_code is set to Spring of present year.  Finally, if date

# falls in Oct. or Nov. then term_code is set to Fall of present year.


if  ( $_month eq "01"  || $_month eq "02"  ||  $_month eq "03" ) {  

       $term_code=  "FA$_yearminus1";

}

      elsif ( $_month eq "04"  ||  $_month eq "05" ) {

               $term_code= "WI$_year";

}

       elsif (  $_month eq "06" || $_month eq "07" || $_month eq "08"  ||  $_month eq "09" ) {

                      $term_code= "SP$_year";

}

              elsif ( $_month eq 10  ||  $_month eq 11 ) {

                          $term_code= "FA$_year";

return ($term_code);

}

}

my $term_code = getterm();

---------------- ---------------- ---------------- ---------------- ---------------- ----------------

Added Aug. 4, 2014:


siomail(bubba): data_scripts/Grad/Scrippsscholars% ls -artl

total 28

-rw-rw-r-- 1 bubba bubba    7 Oct  2  2012 .db2zzz

-rw-r--r-- 1 bubba bubba  185 Jan  9  2013 dl_courses.tmp

-rw-rw-r-- 1 bubba bubba 4188 Jul  3  2013 dl_courses.db2

-rwxrwxr-x 1 bubba bubba  972 Jul 23  2013 courses.sh

drwxrwxr-x 2 bubba bubba 4096 Jul 23  2013 .

drwxrwxr-x 4 bubba bubba 4096 Jun 20 13:21 ..

siomail(bubba): data_scripts/Grad/Scrippsscholars% more courses.sh

#!/bin/sh

#

#this script needs to be automated similar to the Grad/gradinfo.sh and .pl

export PATH=$PATH:$HOME/bin:/usr/sbin:/usr/bin:$RDB/bin:$SYBASE/OCS/bin:/usr/local/sbin:

#set up variables

export MYSERVER="marinara.ucsd.edu"

export MYUSER="bubba"

export MYPW="cat $HOME/bin/.pw/.bpass"


#get the data from Darwin

#the term(s) for the download are hardcoded in dl_courses.db2 update as needed

#if "refreshing" data for a term already in the database, delete those

#     records before running this script or there will be duplicates


/home/bubba/bin/db2prod dl_courses.db2 /tmp/courses.tmp

#

#refresh data on Production Server (marinara)

#courses

#existing term data must be deleted manually before running this script

# uncomment below if want to delete all records from courses

#mysql -h $MYSERVER -u $MYUSER -p`$MYPW`  -e "delete from courses" scrippsscholars

mysqlimport -L -h $MYSERVER -u $MYUSER -p`$MYPW` scrippsscholars /tmp/courses.txt


echo "script done. "


siomail(bubba): data_scripts/Grad/Scrippsscholars% more dl_courses.tmp

SQL3104N  The Export utility is beginning to export data to file

"/tmp/courses.txt".


SQL3105N  The Export utility has finished exporting "7125" rows.



Number of rows exported: 7125




siomail(bubba): data_scripts/Grad/Scrippsscholars% more dl_courses.db2

-- this script updates scrippscholars.courses.

-- export sends the output to a tab delim file in /tmp

EXPORT TO /tmp/courses.txt OF DEL MODIFIED BY COLDEL0X09 nochardel

-- there's 2 select statements using UNION

-- get primary instructors

-- need to manually change the term_code before running

select distinct '0' as "ID",

 student_db.s_lec_instructor.lec_instructor_pid as InstructorID,

 student_db.s_lec_instructor.lec_instructor_name as "InstructorName",

 'Y' as "Primary",

  student_db.s_section.crs_course_id as "Course Full" ,

 student_db.s_section.crs_course_title as "Course Title",

   student_db.s_section.crs_course_code as "Course Code",

    student_db.s_section.sub_subject_code as "Subject Code",

 student_db.s_section.dpt_department_code as "Course Department Code",

student_db.s_section.sec_instruction_type as "InstrType",

 student_db.s_term.trm_term_code as "Term",

 student_db.s_term.trm_term_id as "Term Id",

   student_db.s_section.sec_subtitle as "Section Subtitle"

from

 student_db.s_section

 inner join student_db.s_term on student_db.s_section.trm_term_code = student_db.s_term.trm_term_code

 inner join student_db.s_lec_instructor on student_db.s_lec_instructor.sec_section_id = student_db.s_section.sec_section

_id

 left outer join student_db.s_instructor_section on student_db.s_section.sec_section_id = student_db.s_instructor_sectio

n.sec_section_id

 inner join student_db.s_course on student_db.s_course.crs_course_id = student_db.s_section.crs_course_id and student_db

.s_term.trm_term_code = student_db.s_course.trm_term_code and student_db.s_term.trm_term_code = student_db.s_course.trm_term_co

de  

where

   student_db.s_term.trm_term_id in (select distinct trm_term_id from student_db.s_term where

--trm_term_code ='FA12')

trm_term_code  in ('FA12','WI13','SP13','S313'))

-- right(trm_term_code,2) between '09' and '12')

-- uncomment below if want to limit to certain depts

-- the list is a best guess of science depts

--     and student_db.s_section.dpt_department_code in  (

-- 'BIOL',

-- 'BIOM',

-- 'CHEM',

-- 'COGS',

-- 'CSE',

-- 'ECE',

-- 'ENVR',

-- 'ERTH',

-- 'ESYS',

-- 'MAE',

-- 'MATH',

-- 'MATS',

-- 'NENG',

-- 'NEU',

-- 'PHYS',

-- 'SIO',

-- 'SOE')  

UNION

-- get co instructors

select distinct '0' as "ID" ,

 student_db.s_instructor_section.ins_instructor_pid as "InstructorID",

 student_db.s_instructor_section.ins_instructor_name as "Instructor",

 '' as "Primary",

 student_db.s_section.crs_course_id as "Course Full" ,

 student_db.s_section.crs_course_title as "Course Title",

   student_db.s_section.crs_course_code as "Course Code",

    student_db.s_section.sub_subject_code as "Subject Code",

 student_db.s_section.dpt_department_code as "Course Department Code",

student_db.s_section.sec_instruction_type as "InstrType",

 student_db.s_term.trm_term_code as "Term",  student_db.s_term.trm_term_id as "Term Id",

   student_db.s_section.sec_subtitle as "Section Subtitle"

from

  student_db.s_section inner join student_db.s_term on student_db.s_section.trm_term_code = student_db.s_term.trm_term_code

  left outer join student_db.s_instructor_section on student_db.s_section.sec_section_id = student_db.s_instructor_section.sec_sec

tion_id and student_db.s_instructor_section.ins_primary_flag != 'Y'

  inner join student_db.s_course on student_db.s_course.crs_course_id = student_db.s_section.crs_course_id and student_db.s_term.t

rm_term_code = student_db.s_course.trm_term_code and student_db.s_term.trm_term_code = student_db.s_course.trm_term_code  

where

student_db.s_term.trm_term_id in (select distinct trm_term_id from student_db.s_term where

-- trm_term_code ='FA12')

trm_term_code  in ('FA12','WI13','SP13','S313'))

-- right(trm_term_code,2) between '09' and '12')

 and student_db.s_instructor_section.ins_instructor_pid !=''

-- uncomment below if want to limit to certain depts

-- the list is a best guess of science depts

--    and student_db.s_section.dpt_department_code in (

-- 'BIOL',

-- 'BIOM',

-- 'CHEM',

-- 'COGS',

-- 'CSE',

-- 'ECE',

-- 'ENVR',

-- 'ERTH',

-- 'ESYS',

-- 'MAE',

-- 'MATH',

-- 'MATS',

-- 'NENG',

-- 'NEU',

-- 'PHYS',

-- 'SIO',

-- 'SOE')

fetch first 100000 rows only;

*************************

teaching staff - course info

key words:  scrippsscholars, rab


Wednesday, October 10, 2012 3:48 PM

Hi Cerise,


The class listing is moving from

http://sio.ucsd.edu/Profile/<email_name>

to

http://scrippsscholars.ucsd.edu/<email_name>/classes


For example:


http://sio.ucsd.edu/Profile/grouse


is now


http://scrippsscholars.ucsd.edu/grouse/classes


The data are downloaded from the campus data warehouse student_db tables and is up-to-date as of SP12.


The course detail is another subject - it's not currently a feature on the new site.  It will most likely be added as a

discussion item for the ScrippsScholars team and we'll keep you posted....



Terri

x43424





On Oct 10, 2012, at 2:45 PM, Cerise Maue wrote:


Hi Terri,


Please update the teaching staff database information through Fall 2012.


Also, please update the course database information listed on the web site.


for example,


SIO 284 and SIO 293 course titles and descriptions are outdated and need to be revised.


http://sio.ucsd.edu/Profile/grouse


Thank you!