Skip to main content
California Department of Education Logo

AAV of Staff Data - Entity Relationship Diagram

Accessible Alternate Version (AAV) of the Staff Data - Entity Relationship Diagram.

This is an accessible alternate version (AAV) of the Staff Data - Entity Relationship Diagram. The Staff Data - Entity Relationship Diagram (PDF) file should be the version selected by most users.

Tables

Five tables are used in the Staff Data – Entity Relationship Diagram. They are:

  • StaffDemographics
  • StaffSchoolFTE
  • StaffAssignment
  • StaffCredentials
  • CourseEnrollment
  • CoursesTaught
  • ClassEnrollment

Description of the Seven Tables

StaffDemographics Table
  • This table stores one record for every certificated staff member working at an LEA.
  • The fields are: AcademicYear, RecID, DistrictCode, CountyName, DistrictName, GenderCode, Age, EducationLevel, EthnicGroup, YearsTeaching, YearsInDistrict, EmploymentStatusCode, FTE Teaching, FTE Administrative, FTE PupilServices, FileCreated.
  • Primary Key of StaffDemographics is RecID + DistrictCode.
StaffSchoolFTE Table
  • This table stores one record for every staff member, school and job classification.
  • The fields are: AcademicYear, RecID, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, JobClassification, StaffType, FTE, FileCreated.
  • Primary Key of StaffSchoolFTE is RecID + DistrictCode + SchoolCode + JobClassification.
StaffAssignment Table
  • This table stores one record for every staff member, school, ClassID, and course code.
  • The fields are: AcademicYear, RecID, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, StaffType, AssignmentCode, ClassID, CourseCode, EstimatedFTE, FileCreated.
  • Primary Key of StaffAssignment:
    • For Teacher record: RecID + DistrictCode + SchoolCode + ClassID + CourseCode
    • For Administrator or Pupil Services record: RecID + DistrictCode + SchoolCode + AssignmentCode
StaffCredentials Table
  • This table stores one record for every certificated staff member and credential.
  • The fields are: AcademicYear, RecID, CredentialType, AuthorizationType, FileCreated.
  • Primary Key of StaffCredentials is RecID + CredentialType + AuthorizationType.
CourseEnrollment Table
  • This table stores one record for every school, class, class section, grade, and gender.
  • The fields are: AcademicYear, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, ClassID, CourseCode, ClassCourseID,
    GradeLevelCode, GenderCode, EnrollNoEthRpt, EnrollAmInd, EnrollAsian, EnrollPacIsl, EnrollFilipino, EnrollHispanic, EnrollAfrAm, EnrollWhite, EnrollTwoOrMore, EnrollTotal, EnrollEL, FileCreated.
  • Primary Key of CourseEnrollment is DistrictCode + SchoolCode + ClassID + CourseCode + ClassCourseID + GradeLevelCode + GenderCode.
CoursesTaught Table
  • This table stores one record for every school, ClassID, CourseCode, and ClassCourseID.
  • The fields are: AcademicYear, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, ClassID, CourseCode, ClassCourseID,
    UC CSU Approved, NCLB CORE, NCLB HQT, DistanceLearning, IndependentStudy, MultipleTeacherCode, CTE FundingProvider, Enrollment, SEID Indicator, FileCreated.
  • Primary Key of CoursesTaught is DistrictCode + SchoolCode + ClassID + CourseCode + ClassCourseID.
ClassEnrollment Table
  • This table stores one record for every school, class, class section, class grade, and gender (total enrollment for a class).
  • The fields are: AcademicYear, DistrictCode, SchoolCode, CountyName, DistrictName, SchoolName, ClassID, CourseCodeOrMultCourseInd,
    AllClassCourseCodes, GradeLevelCode, GenderCode, EnrollNoEthRpt, EnrollAmInd, EnrollAsian, EnrollPacIsl, EnrollFilipino, EnrollHispanic,
    EnrollAfrAm, EnrollWhite, EnrollTwoOrMore, EnrollTotal, EnrollEL, FileCreated.
  • Primary Key of ClassEnrollment is DistrictCode + SchoolCode + ClassID + GradeLevelCode + GenderCode.

Description of the Relationship between Tables

RELATIONSHIP A 

Each StaffDemographics record will be associated with one or more StaffAssignment record(s). StaffAssignment records are either: (1) teaching assignments, which will include data in the CourseCode and ClassID fields; or (2) administrative or pupil services assignments, which will have data in the AssignmentCode field.

Note: Staff working at more than one district will have a StaffDemographics record for each district – each with the same RecID and a different DistrictCode for each district.

  • Link: RecID + DistrictCode

RELATIONSHIP B 

Each StaffAssignment record will have be associated with or more CoursesTaught record(s). Usually one course is taught in one class (one CoursesTaught record for a class). When more than one course is taught in a class (i.e., a class with two sections; French 1 and French 2), there will be a CoursesTaught record for each course taught in the class.

Note: (1) For team teaching, a CoursesTaught record will have more than one associated StaffAssignment record; (2) Pupil Services and Administrative assignments StaffAssignment records will not have associated CoursesTaught records.

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode
RELATIONSHIP C 

Each StaffAssignment record will be associated with zero, or one or more CourseEnrollment record(s). One CourseEnrollment record will exist for each case where there is enrollment for a grade and gender combination.

Note: For team teaching, CourseEnrollment records will have more than one associated StaffAssignment record.

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode
RELATIONSHIP D 

Each CoursesTaught record will be associated with zero, or one or more CourseEnrollment record(s).

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode + ClassCourseID
RELATIONSHIP E 

Each StaffDemographics record will be associated with zero, or one or more StaffCredentials record(s), containing authorizations that are valid on Census day.

Note: Some staff members may not have a credential that is valid on Census Day.

  • Link: RecID
RELATIONSHIP F 

Each StaffDemographics record will be associated with one or more StaffSchoolFTE record(s). This record will contain the total FTE for each staff member by job classification and by school.

  • Link: RecID + DistrictCode
RELATIONSHIP G 

Each StaffAssignment record will be associated with zero, or one or more ClassEnrollment record(s). One ClassEnrollment record will exist for each case where there is enrollment for a grade and gender combination.

Note: For team teaching, ClassEnrollment records will have more than one associated StaffAssignment record.

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode
RELATIONSHIP H 

Each CoursesTaught record will be associated with zero, or one ClassEnrollment record(s).

Note: In some cases, ClassEnrollment data are not submitted by school districts.

  • Link: DistrictCode + SchoolCode + ClassID + CourseCode

 

Questions: Data Reporting Office | dro@cde.ca.gov | 916-327-0219 
Last Reviewed: Wednesday, July 3, 2019
Recently Posted in Demographics
  • File Structure: Classified Staff 2018 (added 02-Aug-2019)
    File structure for classified staff data by race/ethnicity, gender, and school for 2018–19.
  • File Structure: CBEDS Data About Schools/Districts (added 02-Aug-2019)
    File structure for California Basic Educational Data System (CBEDS) data about schools and districts for 2018–19.
  • File Structure: CGR Data (12-Month) (added 11-Jul-2019)
    File structure for the College-Going Rate (CGR) 12 months after high school completion reported by race/ethnicity, student group, and academic year.
  • File Structure: CGR Data (16-Month) (added 11-Jul-2019)
    File structure for the College-Going Rate (CGR) 16 months after high school completion reported by race/ethnicity, student group, and academic year.
  • Using the CGR Data File (added 11-Jul-2019)
    This page explains how to effectively use the sub-setting variables included in this CGR data file to obtain the desired counts.