...
Improve the usability and accessibility of student data for researchers.
Establish clear correlations between CO-MIS and CCCApply data, enabling more comprehensive analysis.
...
To analyze educational attainment, researchers can use the apply__application_vw and apply__education_vwtables, as this attribute is linked to each student’s college application. The following examples demonstrate how to retrieve this information.
Figure 1: Application Table
college | tstmp_ | tstmp_ | tstmp_ | app_id | source | app_lang | status | ccc_id |
181 | 51:26.5 | 05:05.4 | 246163 | stnd_application | en | AAP | ||
181 | 58:30.4 | 05:05.4 | 246170 | stnd_application | en | AAP | ||
181 | 40:35.5 | 05:05.4 | 14308 | stnd_application | en | AAA | ||
181 | 04:30.1 | 05:06.7 | 766185 | stnd_application | en | AAA | ||
181 | 11:06.6 | 05:05.4 | 243283 | stnd_application | en | AAP | ||
181 | 43:57.4 | 05:05.4 | 244160 | stnd_application | en | AAP | ||
863 | 22:35.0 | 05:43.2 | 05:43.2 | 799145 | stnd_application | en | S | AAA |
863 | 16:01.8 | 09:35.5 | 799957 | stnd_application | en | AAB | ||
863 | 55:24.6 | 55:24.9 | 10:41.5 | 839913 | stnd_application | en | S | ABS |
863 | 32:53.0 | 32:53.1 | 33:19.3 | 785127 | stnd_application | en | S | ABK |
The application table includes fields common to all application data sources. The source field specifies the origin of each record, as shown in the table below.
Figure 2: Application Source
source | number_of_records |
bogfw_application | 75648 |
stnd_application | 801022 |
intl_application | 63459 |
The application_education table, linked to the source field, provides additional details on educational background and goals, as shown below.
Figure 3: Application Education Table
app_id | source | ccc_id | tstmp_create | field | value |
823812 | stnd_application | ABQ7872 | 29:06.4 | description | Apprenticeship: Electrical (Cert) |
823812 | stnd_application | ABQ7872 | 29:06.4 | highest_edu_level | 32013 |
827804 | stnd_application | ABR7662 | 40:17.0 | intended_major | 1491 |
827873 | stnd_application | ABR6671 | 01:56.5 | major_category | Behavioral Sciences |
823812 | stnd_application | ABQ7872 | 29:06.4 | major_category | Apprenticeship Education |
840738 | stnd_application | ABQ7872 | 36:43.0 | highest_edu_level | 0 |
840738 | stnd_application | ABQ7872 | 36:43.0 | description | ASL/English Interpreter Education |
840738 | stnd_application | ABQ7872 | 36:43.0 | edu_goal | A |
781669 | stnd_application | AAA3495 | 45:28.3 | intended_major | 84102 |
827873 | stnd_application | ABR6671 | 01:56.5 | edu_goal | F |
...
To determine how many submitted applications include an edu_goal, researchers can perform a left join between the application and application_education tables using the query below.
Figure 4: Example Query
| Code Block |
|---|
SELECT count(*) as num_applications
FROM apply__application_vw ca
LEFT JOIN apply__education_vw cae
ON (ca.app_id = cae.app_id)
WHERE ca.source = 'stnd_application'
AND cae.field = 'hs_edu_level' ; |
Figure 5: Query Result
num_applications |
801022 |
To generate a report on educational attainment by student, use the following query:
Figure 6: Example Query
| Code Block |
|---|
(
SELECT source, app_id, hs_name, hs_city, hs_state
FROM apply__education_vw
PIVOT
(
max(value) FOR field IN ('hs_name','hs_city', 'hs_state')
)) group by source, app_id; |
...
To generate a report on student sociodemographic information, use the following query. This requires data from the apply__application_vw and apply__personal_information_vw tables.
Figure 7: apply_personal_infromation_vw View
app_id | source | ccc_id | tstmp_create | field | value |
119389 | bogfw_application | ABS2569 | 28:30.7 | married_or_rdp | y |
119389 | bogfw_application | ABS2569 | 28:30.7 | homeless_youth_school | y |
119389 | bogfw_application | ABS2569 | 28:30.7 | cert_national_guard | y |
119389 | bogfw_application | ABS2569 | 28:30.7 | marital_status | M |
119389 | bogfw_application | ABS2569 | 28:30.7 | homeless_youth_other | y |
119389 | bogfw_application | ABS2569 | 28:30.7 | elig_police_fire | y |
119389 | bogfw_application | ABS2569 | 28:30.7 | parents_deceased | y |
119389 | bogfw_application | ABS2569 | 28:30.7 | reg_dom_partner | y |
119389 | bogfw_application | ABS2569 | 28:30.7 | us_veteran | y |
119389 | bogfw_application | ABS2569 | 28:30.7 | parents_assistance | y |
Figure 8: apply_personal_sociodemographic View
By querying these tables, researchers can access attributes related to a student's sociodemographic status. For example, to determine how many students indicated they were married when completing their application, use the following query.
Figure 9: Marriage Information Query
| Code Block |
|---|
SELECT pi.app_id,sd.field,sd.value,pi.value FROM apply__personal_sociodemographic_vw sd inner join apply__personal_information_vw pi on pi.app_id=sd.app_id where sd.field='married_or_rdp' and pi.field='lastname'; |
This query returns the following results:
Figure 10: Query Results
ABU2416 | married_or_rdp | |
ABU6245 | married_or_rdp | n |
ABU6245 | married_or_rdp | n |
ABU6245 | married_or_rdp | n |
ABU6245 | married_or_rdp | n |
ABU6903 | married_or_rdp | n |
ABU6903 | married_or_rdp | n |
ABU3934 | married_or_rdp | n |
ABU3934 | married_or_rdp | n |
...