Institution
There are three key fields in CUNYfirst which act in similar fashion but are defined differently. These three fields are INSTITUTION, BUSINESS_UNIT and SETID.
In CUNYfirst, certain information belongs to students without considering the institution which they attend. This information includes names, addresses, phones, medical information, etc. In fact, any information in the Campus Community menu (excluding service indicators) belongs to students regardless of the institution or even if an institution exists for the students. The keys to these tables include the EMPLID.
Certain academic information comes to students via the attending institution. This information includes enrollment, STDNT_ENRL record, and term activation, STDNT_CAR_TERM record. This information is created for students by a particular institution, so the keys to these tables include the EMPLID and the INSTITUTION field. There are some tables where the key structure does not include the INSTITUTION field, but INSTITUTION appears in the table anyway. An example of this is the academic program, ACAD_PROG. This academic information exists only because of the students’ relationship with a particular institution.
Business Units or Setid
On the other hand financial side of the students’ involvement with an institution, the field, INSTITUTION, is not used. Instead, the field BUSINESS_UNIT is used. Charges for tuition and fees are stored in the item table, ITEM_SF. Here the keys include EMPLID and BUSINESS_UNIT.
Other information belongs to the institution without considering the students. Such information includes item types, ITEM_TYPE_TBL, and facilities, FACILITY_TBL. The keys to these tables include SETID.
When joining two tables in query, the system will create a join on similar keys. The EMPLID from one table will be joined to the EMPLID of another table; INSTITUTION will be joined with INSTITUTION, etc. Because INSTITUTION, BUSINESS_UNIT and SETID are defined differently, query will not create a join between them. The query writer must do it. So if the query needs to join two records, one of which has INSTITUTION as a key, another with BUSINESS_UNIT, the code first_table.INSTITUTION = second_table.BUSINESS_UNIT must be added manually. In CUNYfirst, the INSTITUTION always equals the BUSINESS_UNIT and SETID. The example has illustrated below:
INSTITUTION = ‘BAR01’ (Baruch), then the BUSINESS_UNIT = ‘BAR01’ and the SETID = ‘BAR01’
Example of Joining different records with similar keys with different Names below:
| Academic | key(s) | Financial | Key(s) |
|---|---|---|---|
ACAD_PROG | EMPLID, INSTITUTION | FACILITY_TBL | SETID |
| STDNT_ENRL | EMPLID, INSTITUTION | ITEM_SF | BUSINESS_UNIT |
| STDNT_CAR_TERM | EMPLID, INSTITUTION |