Colleague Database Access (MSSQL or Oracle)

Database User Access

The Astra database user must have access to the tables listed below.  Additionally, synonyms must be created for each table as the Astra user to facilitate direct queries against the database. Details related to the queries used, including specific SIS fields involved, will be provided upon request.

All tables are read-only access unless specified. 

Create Synonyms

Use the commands displayed below to create the proper synonyms via SQL Developer, Toad, or other database administration tool. Sign in to the Colleague database as the Astra user that was created for use in the import/export process before running these commands. 

Note that <COLLEAGUE_SCHEMA> should be replaced with the name of your schema. (Example: COLL_PRODUCTION.COURSE_SECTIONS)

  • CREATE SYNONYM COURSE_SECTIONS FOR <COLLEAGUE_SCHEMA>.COURSE_SECTIONS;
  • CREATE SYNONYM COURSE_SEC_MEETING FOR <COLLEAGUE_SCHEMA>.COURSE_SEC_MEETING;
  • CREATE SYNONYM SEC_STATUSES FOR <COLLEAGUE_SCHEMA>.SEC_STATUSES;
  • CREATE SYNONYM STC_STATUSES FOR <COLLEAGUE_SCHEMA>.STC_STATUSES;
  • CREATE SYNONYM STUDENT_COURSE_SEC FOR <COLLEAGUE_SCHEMA>.STUDENT_COURSE_SEC;
  • CREATE SYNONYM COURSE_SEC_FACULTY FOR <COLLEAGUE_SCHEMA>.COURSE_SEC_FACULTY;
  • CREATE SYNONYM PEOPLE_EMAIL FOR <COLLEAGUE_SCHEMA>.PEOPLE_EMAIL;
  • CREATE SYNONYM PERSON FOR <COLLEAGUE_SCHEMA>.PERSON;

Person View

*Rather than requesting access to PERSON, we request that the client run the following command to create an ASTRA_PERSON view that limits the view to only include required fields of the PERSON table, intended to protect sensitive info. Create View: ASTRA_PERSON as select ID, LAST_NAME, FIRST_NAME, MIDDLE_NAME, DECEASED_DATE from PERSON

Student Status

**"Active" student status at end of term is used for section enrollment calculation. If census enrollment is available, access to that data is needed to enhance historical comparison. Please contact your project manager to coordinate integration.

Table Astra Schedule Monitor/ Align Predict  (MSSQL Only) Platinum   (MSSQL Only) HESI       Use
ACAD_CREDENTIALS     X X   Students
ACAD_LEVELS     X X   Lookups, Students
ACAD_PROGRAMS     X X   Lookups, Students
ACAD_PROGRAMS_LS     X X   Students
ADMIT_STATUSES     X X   Lookups
APPROVAL_STATUS   X X X X Courses
BUILDINGS   X X X   CampusBuildingRoom
CATALOGS     X X X Courses
CCDS     X     Concentrations
CLASSES     X X X Lookups
COURSE_CONTACT     X X X Courses
COURSE_COREQS     X X X Courses
COURSE_DEPTS   X X X X Courses
COURSE_EQUATE_CODES_LS   X X X X Courses
COURSE_SEC_FACULTY X X X X X Sections
COURSE_SEC_MEETING read and write access X X X X Section import, Export
COURSE_SECTIONS X X X X X Courses, Section import, Export
COURSES X X X X X Lookups, Courses, Sections, Pgm Rules
COURSES_LS     X X X Courses, Students
CRED_TYPES     X     Concentrations
DEGREES     X X   Lookups, Students
DEPTS   X X X X Courses, Lookups, Pgm Rules
DIVISIONS   X X X X Lookups 
GRADES     X X   Pgm Rules, Students
HIATUS_CODES     X X   Lookups
INSTR_METHODS   X X X X Courses, Lookups
LOCATIONS   X X X X CampusBuildingRoom, Courses
MAJORS   X X X   Lookups, Students
MINORS     X X   Lookups, Students
PEOPLE_EMAIL X   X X X Sections
PERSON* X XID, first_name, last_name, middle_name, deceased_date ID, first_name, last_name, middle_name, deceased_date X X Sections, Section details
PROGRAM_STATUS     X X   Lookups
ROOMS   X X X X CampusBuildingRoom, Section import, Export
SCHED_TYPES   X X     Section details
SCHOOLS   X X     Section details
SEC_STATUSES X X X X X Section import, Export
SPECIALIZATIONS     X     Concentrations
STC_STATUSES** X X X X X Sections
STPR_CCD_LIST     X     Concentrations
STPR_DATES     X     Concentrations
STPR_MAJOR_LIST     X X   Students
STPR_MINOR_LIST     X X   Students
STPR_SPECIALTIES     X     Concentrations
STPR_STATUSES     X X   Students
STU_TYPE_INFO     X X   Students
STUDENT_ACAD_CRED     X X X Courses, Sections
STUDENT_ACAD_LEVELS     X X   Students
STUDENT_COURSE_SEC X X X X X Sections, Students
STUDENT_HIATUS     X X   Students
STUDENT_NON_COURSES     X X   Lookups, Students
STUDENT_PROGRAMS     X X   Students
STUDENT_TYPES     X X   Lookups
SUBJECTS     X X X Lookups, Pgm Rules, Courses, Sections
TERMS   X X X X Sections, Students, Terms, Courses
VALS     X X X CampusBuildingRoom, Lookups, Pgm Rules

Was this article helpful?
1 out of 1 found this helpful

Comments

0 comments

Please sign in to leave a comment.