AAV of 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