Santa Clara School of Law

1
Santa Clara School of Law

• Case Study Class Roster, Email and Seating Chart

2
Introduction

• Andrew Gurthet Director of Technology
• agurthet_at_scu.edu
• Sid Maestre - Law School Webmaster
• smaestre_at_scu.edu

3
History/Background

• About Santa Clara University Law School
• Our original photo roster/seating chart System
• Our original class e-mail system
• Research regarding alternatives
• Need for custom system

4
Existing Content Management

• Built in-house
• Cold Fusion and MS SQL Server
• Secure login for users (staff)
• Faculty Biographies
• Class Information

5
Database Content
CLASS
Cold Fusion
Class Schedule
FACULTY
STUDENT
6
Students Problem

• How to get student information into our SQL
  database?

?
STUDENT
Jon Smith
7
Student Solution

• First, determine where student information is
  stored.
• Second, determine what key information must be
  exported from existing system and in what format.

8
Student Export

• Our student information is stored in a PeopleSoft
  database in our Records Dept.
• Key information we export
• Student First Name, Last Name, Email and Access
  ID number
• Format chosen CSV (comma separated value)

PeopleSoft
CSV File First Name Last Name Email Access ID
9
Student Photos Problem

• Student photos are named by the individual
  student access id number.
• For example Jon Smith has an access id of
  00006598852. Therefore, his image would be
  00006598852.jpg.
• SCU Law has blind grading system and html could
  expose the students identity.

10
Student Photo Solution

• During the import process, we copy and rename the
  image files with a unique name.
• Example
• Jon Smith is imported into our database and a
  unique ID number 3441 is created.
• His image file 00006598852.jpg is copied and
  renamed 3441.jpg in a new directory.

11
Student Import

• Upload Student images to server.
• Upload Student CSV file to server
• Cold Fusion tag CSV2Data is used to convert CSV
  file into an HTML form for import.

Student
Cold Fusion
CSV File First Name Last Name Email Access ID
12
Demonstrate Student Import

• Import Andrew and Sid as students

13
Select CSV file for import
14
Import Students
15
Import Students Done
16
Student List
17
Student Edit Properties
18
Students to Classes Problem

• How do we make the connection between students
  and classes?

CLASS
STUDENT
FACULTY
19
Student to Classes Solution

• Create a joining table to link Students and
  Classes

CLASS ID STUDENT ID
STUDENT
CLASS
STUDENT 2 CLASS
20
Student to Class Export

• Our student schedule information is also stored
  in a PeopleSoft
• Key information we export
• Class Name, Class Number, Student Name, Access ID
  Number
• Format chosen CSV (comma separated value)

PeopleSoft
CSV File Class Name Class Number Student
Name Access ID
21
Student to Class Import

• Upload Class Schedule CSV file to server.
• Cold Fusion tag CSV2Data is used to convert CSV
  information into an HTML form for import.

Student To Class
CSV File Class Name Class Number Student
Name Access ID
Cold Fusion
22
Demonstrate Schedule Import

• Import class schedule with Sid and Andrew both
  enrolled in Advanced Criminal Litigation Seminar

23
Select CSV file for Class Schedule
24
Import Class Schedule Form
25
Import Class Schedule Done
26
2 Students Advanced Criminal Litigation Seminar
27
How is Add/Drop handled

• Complete Student to Class Schedule is imported
  each time.
• Students who add a class will create a new record
  in the Student to Class db table.
• Student who drop a class will delete a record
  from the Student to Class db table.

28
Demonstrate Add/Drop

• Drop Andrew from Class Schedule

29
Importing with Add/Drops
30
Andrew dropped from class
31
1 Student dropped confirm
32
1 Student Advanced Criminal Litigation Seminar
33
Faculty Login

• User accounts were created for each faculty
  member for login
• Given permissions to view seating chart
• Restricted to only classes they teach
• When a new faculty bio is created, a user account
  is automatically created.

34
Work Flow
Login
CMS Home
List of Classes
Individual Class
Photo Roster
Photo Email
Email Log
Seating Chart
35
Photo Roster

• Displays the students photos and names in
  alphabetical order

36
Photo Email

• Allows faculty to email individual students or
  the entire class.
• An email list can be generated for use in
  Groupwise if chosen.

37
Email Log

• A log of emails sent through the system.
• Faculty can search by keyword, students name or
  date.

38
Seating Chart

• Seating charts are available for large
  classrooms.
• Adding, moving and removing students.
• View the completed seating chart
• Create a PDF version for printing.

39
Question Answer

• Sid Maestre smaestre_at_scu.edu
• Andrew Gurthet agurthet_at_scu.edu