The school library comes from combining two data sets: the most recent CDE staffing data and the schools’ addresses and courier codes. Both of these data sets need to be uploaded in to the database, and then a query combines them into one table.
The CDE staffing data needs to be requested internally from Annette Sevenson in IMS. This is usually available around mid-March for the proceeding year. The mailing addresses come from a the public-facing CDE website here:
https://www.cde.state.co.us/cdereval/downloadablemailinglabels
The courier codes come from CLiC. We need to do an update of them for 2019.
http://courier.clicweb.org/
Here is an overview of some of the relevant tables in our database:
cde_staff_totals
FTE staff data per school and year. Column `is_exception` allows us to flag schools that we insert, e.g. Deer Trail Combined (#2140) in 2015.
schools
Contains data for every school we’ve ever surveyed.
districts
Contains data for every district that has a school we’ve ever surveyed.
survey_sample_2016
A view that shows all schools that match to CDE staffing data for year 2016.
Each year when we get CDE’s FTE data I will group and sum by school then add the data to cde_staff_totals. I will then create a new view, survey_sample_YYYY, that displays all schools who should be surveyed for that year, i.e. schools with FTE > 0.
Enrollment & Level data
We add this data to be able to create benchmarks for levels and enrollments in the school library profiles and the broader dataset.
We used to get this from a public-facing page on the CDE website, but starting in spring 2018 we had to request the data from IMS/Data Services. The query we used in spring 2018 is saved as “State Library Request for School Level and Enrollment” and was completed by Morgan Holmgren around May 9th 2018.
Contact Genevieve Hale (IMS/Data services) for 2018-2019 school data. She should be able to use the same query as Morgan.
Our definitions of school levels are:
- Elementary: starts in
- Middle: starts in 6th, 7th or 8th and ends in 7th, 8th or 9th
- High: starts in 9th and ends in 12th
- Combined: All others
The grade codes are:
- 002 – Infant
- 004 – Preschool
- 006 – Half-day Kindergarten
- 007 – Full-day Kindergarten
- 010 – First grade
- 020 – Second grade
- 030 – Third grade
- 040 – Fourth grade
- 050 – Fifth grade
- 060 – Sixth grade
- 070 – Seventh grade
- 080 – Eighth grade
- 090 – Ninth grade
- 100 – Tenth grade
- 110 – Eleventh grade
- 120 – Twelfth grade