cedstandards/ceds-ids

The CEDS Integrated Data Store factors the entities and attributes of the CEDS Domain Entity Schema (DES) with standard technical syntax and 3rd normal form database normalization. The IDS Logical Model provides a standard framework for integration of P-20 data systems through a well-normalized “operational data store”. In a P-20 data system, the IDS models the most current view of data available to the enterprise, including some historical data (such as prior assessment data and enrollment records). A “record” is generally added for each Person-Organization Relationship change event in the system. This CEDS logical model does not directly address the production aspects of log and change management.

ceds-ids's Issues

Add OrganizationId to the AssessmentRegistration table

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS
Email Address

Use Case Title
Add OrganizationId to the AssessmentRegistration table

Use Case Overview
This relates to ticket: CEDStandards/CEDS-Elements#479

In the IDS, Assessment is open to any Organization, currently only LEA and School Organizations link to the AssessmentRegistration table. The addition of OrganizationId (NULL) will allow the stakeholder to connect AssessmentRegistration to any Organization.

The Code "LEA" is listed twice in the RefOrganizationType Table - v10.0.0.0

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS
Email Address
[email protected]
Use Case Title
The Code "LEA" is listed twice in the RefOrganizationType Table - v10.0.0.0

Use Case Overview
The Code "LEA" is listed twice in the RefOrganizationType Table - v10.0.0.0. There should only be one code in that table. It appears as thought the first record in the able is the erroneous record. The Domain Entity Schema only has LEA listed once and the definition matches the second record in the list.

Pull Request Number(s) (If applicable)

Use Case Background
Provide information related to why these changes/additions are needed.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Element Gap Analysis
Provide link to CEDS Align Map or Connection.

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Provide a concise description of the proposed changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

Bug in the Populate-CEDS-Ref-Tables.sql Script for 8.0

Describe the bug
There's a bug in the populate reference table script for RefPersonIdentificationSystem and RefOrganizationIdentificationSystem

The 'IF NOT EXISTS' clause for populating values doesn't differentiate by RefPersonIdentifierTypeId which leads to some values not being populated.

For example:
Once
IF NOT EXISTS (SELECT 1 FROM dbo.[RefPersonIdentificationSystem] WHERE [Code] = 'SSN') BEGIN INSERT INTO dbo.[RefPersonIdentificationSystem]([Code], [Description], [Definition], [SortOrder], [RefPersonIdentifierTypeId])
VALUES ('SSN', 'Social Security Administration number', 'The related Child Identifier uses the child''s Social Security Administration number.', 7.00, 1) END

Runs

The following line does not populate because the IF NOT EXISTS clause fails
IF NOT EXISTS (SELECT 1 FROM dbo.[RefPersonIdentificationSystem] WHERE [Code] = 'SSN') BEGIN INSERT INTO dbo.[RefPersonIdentificationSystem]([Code], [Description], [Definition], [SortOrder], [RefPersonIdentifierTypeId])
VALUES ('SSN', 'Social Security Administration number', 'The related Person Identifier uses the person''s Social Security Administration number.', 1.00, 4) END

Add AssessmentResultId to TeacherEducationCredentialExam Table

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS
Email Address
[email protected]
Use Case Title
Add AssessmentResultId to TeacherEducationCredentialExam Table

Use Case Overview
The TeacherEducationCredentialExam table contains information related to specific assessments for credentials for teachers, but it does not contain a score for the exam or a link back to the Assessment table to retrieve a score. Adding the AssessmentResultId will provide a location where the score and any additional information about the results or the assessment itself can be stored.

Additionally to ensure that AssessmentResult can be used alone without having to add additional assessment information, we would make the the AssessmentSubtestId and AssessmentRegistrationId columns nullable.

Use Case Background
North Carolina requested a new element (CEDStandards/CEDS-Elements#254) related to the exam score, but through conversations with North Carolina, the addition of the AssessmentResultId would resolve the issue.

Create One to Many Relationship between Course and Competencies

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS Team
Email Address
[email protected]
Use Case Title
Create One to Many Relationship between Course and Competencies

Use Case Overview
Create a directly link between Course and multiple Competencies and/or the competency framework. May want to create a direct link from an OrganizationId to the CompentencyFrameworkId so that any organization (course, program, etc.) can have competencies linked to it. Need to have additional data modeling discussions related to a final proposed model, but there is a need for a FK relationship between course/competency at a mininum.

Create a Version Table in the IDS

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS Team
Email Address
[email protected]
Use Case Title
Create a Version Table in the IDS

Use Case Overview
Create a table in the CEDS IDS that houses the Version of the IDS (e.g. 9.0.0.0)

Pull Request Number(s) (If applicable)

Use Case Background
Provide information related to why these changes/additions are needed.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Data Warehouse (complete DW Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
Provide link to CEDS Align Map or Connection.

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Provide a concise description of the proposed changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

Minor capitalization changes in IDS setup code

Describe the bug
There are a few instances of SQL statements that have different capitalization than Database Projects expect in Visual Studio and they generate warnings when building a project. Having the ability to build fresh installs without warnings makes the installation process easier for new adopters.

To Reproduce
Steps to reproduce the behavior:

1. Create a new database instance following the existing instructions.
2. Create a new Database Project in Visual Studio (tested in 2019 and 2022).
3. Import the schema into the project in VS
4. Build the project
5. You can see multiple warnings related to unexpected capitalization

Expected behavior
A fresh install should allow for a simple import and build without errors or warnings.

Screenshots
Available on request.

Desktop (please complete the following information):

• OS: Windows 10 Enterprise
• Visual Studio 2019 or 2022
• Tested on SQL Server 2016/2019 (on-premises) and Azure SQL Server 2019

Additional context
Proposed changes are included in Pull Request #64

The primary key of RefK12StaffClassification does not follow the standard naming convention

Describe the bug
The primary key of RefK12StaffClassification is currently RefEducationStaffClassificationId rather than RefK12StaffClassificationId which is what would be expected given the naming convention used in other reference tables.

To Reproduce
See description

Expected behavior
We'd expect the primary key of RefK12StaffClassification to be RefK12StaffClassificationId

RefRaceId in the PersonDemographicRace table is missing extended properties

Describe the bug
All columns that apply to one or more CEDS elements should contain extended properties pointing to the Global ID. The RefRaceId column in the PersonDemographicRace table is missing the extended properties.

IDS – Need Mechanism to Track Data by Collection

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Karen Conroy
Jeremy Avery

Authoring Organization(s)
Center for Education Performance and Information (CEPI) in Michigan

Email Address
[email protected]

Target Data Needed
We're coding this at CEPI and will submit the change to the community once the project is complete.

Use Case Title
Need mechanism to track data by collection.

Use Case Overview
Add mechanism to track data by collection so that we can differentiate in the IDS and Data Warehouse what data to use for reporting use cases.

Pull Request Number(s) (If applicable)

Use Case Background
Michigan needs mechanism to track collection data over time. See attached spreadsheet 'Michigan Data Collection Elements' with needed data element information.

Michigan Data Collection Elements.xlsx

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Data Warehouse (complete DW Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
Provide link to CEDS Align Map or Connection.

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Provide a concise description of the proposed changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

RefParticipationType has incorrect Codes

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS
Email Address
[email protected]
Use Case Title
RefParticipationType has incorrect Codes

Use Case Overview
The table RefParticipationType has two codes that contain spaces "MEP Participation" and "Title III LEP Participation". These should be "MEPParticipation" and "TitleIIILEPParticipation"

Add Constraints to tables

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Drew Bennett
Authoring Organization(s)
State of Vermont
Email Address
[email protected]
Use Case Title
Reduce duplication in CEDS by adding unique constraints

Use Case Overview
OrganizationPersonRole
ALTER TABLE CEDS.dbo.OrganizationPersonRole
ADD CONSTRAINT UN_OrganizationPersonRole_OrganizationId_PersonID_RoleID_EntryDate_ExitDate_DataCollectionID
UNIQUE(OrganizationId, PersonID, RoleID, EntryDate, ExitDate, DataCollectionID)

The field Organization.RegionGeoJSON can change over time, so move it to OrganizationDetail

Describe the bug
A clear and concise description of what the bug is.

We want to be able to track an organization's region data over time, which means it needs to be moved to OrganizationDetail.

The primary key of RefHighSchoolGraduationRateIndicator is not consistent with naming conventions

Describe the bug
The primary key of RefHighSchoolGraduationRateIndicator is currently RefHSGraduationRateIndicatorId when by typical naming conventions used in the IDS it should be RefHighSchoolGraduationRateIndicatorId

To Reproduce
See description

Expected behavior
We'd except the primary key to be named RefHighSchoolGraduationRateIndicatorId

Add RefAcademicTermDesignatorId to OrganizationCalendarSession

Describe the bug
A clear and concise description of what the bug is

We need to be able to track the Academic Term Designation for a postsecondary term enrollment. Currently, we can only track Academic Term Designation in the Postsecondary student's academic record.

Additional context
Add any other context about the problem here.

Academic Term Designator is located in both Postsecondary Term Enrollment and Postsecondary Student Record in the CEDS Domain Entity Schema.

Restructure CEDS Incident and Discipline Tables

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
North Carolina SEA

Authoring Organization(s)
North Carolina SEA

Email Address
[email protected]

Use Case Title
Restructure CEDS Incident and Discipline Tables

Use Case Overview
North Carolina is requesting a restructuring of the CEDS Incident and Discipline tables. This is primarily related to the need to track multiple behaviors and multiple discipline actions for an individual incident among other things. CEDS structure is more person based and needs to be more incident based tracking information and details about the incident. A workgroup will be needed to complete the restructuring.

RefTitleISchoolStatus has a misnamed primary key

Describe the bug
The reference table RefTitleISchoolStatus has a primary key named RefTitle1SchoolStatusId (note the numeric rather than roman numeral), which is not consistent with the table name or other similar tables.

To Reproduce
See description

Expected behavior
We'd expect the name of the primary key to be RefTitleISchoolStatusId

Extended Properties for RecordEndDateTime Need Corrected

Describe the bug
In some of the tables (e.g., Activity), the RecordEndDateTime extended properties refers to both the Record Start Date Time and the Record End Date Time elements. It should only refer to the Record End Date Time element.

To Reproduce
Steps to reproduce the behavior:

1. Go to the table "Activity"
2. Expand to look at the columns
3. Right click on the column name "RecordEndDateTime" and view the Properties
4. Click on Extended Properties
5. Observe that the CEDS_Element contains both the Record Start Date Time and Record End Date Time elements, the CEDS_GlobalId has the Global ID for each, the CEDS_URL contains both urls.
6. Repeat for multiple tables with the RecordEndDateTime that have the same issue.

Expected behavior
The RecordEndDateTime column should only have metadata for the Record End Date Time element.

The primary key constraint of ProgramParticipationTeacherPrep is misnamed

Describe the bug
The primary key constraint defined for ProgramParticipationTeacherPrep is currently named FK_ProgramParticipationTeacherPrepId as seen in the statement 'CONSTRAINT [FK_ProgramParticipationTeacherPrepId] PRIMARY KEY CLUSTERED'. Note that this is only the constraint itself (and thus is largely non-functional) and not the column name.

To Reproduce
See description

Expected behavior
We'd expect the constraint to be named PK_ProgramParticipationTeacherPrepId.

"Date" is a reserved word in SQL and should not be used for field names

Describe the bug
A clear and concise description of what the bug is.
The following tables contain a field named "Date."

OrganizationFinancial
LearningResourcePeerRating
RoleAttendanceEvent

Expected behavior
A clear and concise description of what you expected to happen.
We should not use reserved words in our table structure. We should instead use the CEDS technical name.

Additional context
Add any other context about the problem here.

Here are the mappings to from the CEDS IDS "Date" fields mapped to the appropriate CEDS Technical Name, which should be used as the new field name.

OrganizationFinancial.Date = FinancialAccountingDate
LearningResourcePeerRating.Date = PeerRatingDate
RoleAttendanceEvent.Date = AttendanceEventDate

Add State Appropriation Methods to CEDS IDS.

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Sumanth Vuyuru

Authoring Organization(s)
AEM Corporation

Email Address
[email protected]

Target Data Needed
As Soon as Possible for Generate.

Use Case Title
Add State Appropriation Methods to CEDS IDS.

Use Case Overview
Add State Appropriation Method reference table and the related changes.

Pull Request Number(s) (If applicable)

Use Case Background
Provide information related to why these changes/additions are needed.
Changes are needed to develop Edfacts report c207.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

The primary key of RefPDActivityApprovedPurpose does not follow the standard naming convention

Describe the bug
The primary key of RefPDActivityApprovedPurpose is named RefPDActivityApprovedForId rather than RefPDActivityApprovedPurposeId which is what we'd expect given other table name-> surrogate key naming patterns

To Reproduce
See description

Expected behavior
We'd expect the primary key to be named RefPDActivityApprovedPurposeId

Need support for tracking students who enroll in schools out-of-district

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Nathan Clinton

Authoring Organization(s)
Center for the Integration of IDEA Data (CIID)

Email Address
[email protected]

Use Case Title
The IDS needs to be able to link together OrganizationPersonRole records to support students who attend schools out-of-district. We can't rely on OrganizationRelationship to determine a students district.

Use Case Overview
Add a table named OrganizationPersonRoleRelationship that links two OrganizationPersonRole records together to establish the proper LEA-to-school relationship.

Pull Request Number(s) (If applicable)

Use Case Background
This capability is needed for federal reporting via Generate. Many states (if not all) have students who attend school out-of-district, especially in small districts that do not have a high school.

Components Affected

CEDS Integrated Data Store (complete IDS Design Overview Table)

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Add a table named OrganizationPersonRoleRelationship that links together two OrganizationPersonRole records.

As-Is Diagram

Proposed To-Be Diagram
Add as an attachment in this issue.

IDS Elements based on new and changed Elements

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Jim Goodell and Angela Jubinville

Authoring Organization(s)
CEDS Team

Email Address
[email protected]

Use Case Title
Update IDS model based on new and changed elements

Use Case Overview
n/a

Pull Request Number(s) (If applicable)
n/a

Use Case Background
A number of elements were added or added to new contexts that should be reflected in the IDS.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Data Warehouse (complete DW Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
n/a

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
See attached document.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
T.B.D.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
The proposed changes include breaking changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

Primary Key For Every Table

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS
Target Data Needed
NA
Use Case Title
Ensure Every Table In the IDS Contains A Primary Key

Use Case Overview
To allow for IDS to be longitudinal, all tables in the IDS need to have a Primary Key. This will allow for individual records to contain a start and end date. This concept has already been implemented for the tables used by the Generate application. The remainder of the tables need to be reviewed and updated to find any using a Foreign Key as their Primary Key, make those only a Foreign Key and add a Primary Key to the table. The naming convention should be the name of the table + "Id".

Pull Request Number(s) (If applicable)

Use Case Background
To allow for the IDS to contain longitudinal data. To be consistent with the changes already being made by the Generate states.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
Provide link to CEDS Align Map or Connection.

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Provide a concise description of the proposed changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

Create a One to Many Relationship for Assessment Purpose

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS
Email Address
[email protected]
Use Case Title
Create a One to Many Relationship for Assessment Purpose

Use Case Overview
In the table dbo.Assessment, the column RefAssessmentPurposeId references the purpose of the assessment. An assessment may have more than one applicable purpose. The purposes are not hierarchical. There is a need to create another table dbo.AssessmentPurpose with the RefAssessmentPurposeId with a foreign key back to Assessment. This will allow for a one to many relationship between Assessment and AssessmentPurpose.

Pull Request Number(s) (If applicable)

Use Case Background
Provide information related to why these changes/additions are needed.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Data Warehouse (complete DW Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
Provide link to CEDS Align Map or Connection.

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Provide a concise description of the proposed changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

The naming of the primary key of RefIDEAEducationalEnvironmentSchoolAge is not consistent with conventions

Describe the bug
The name of the primary key of RefIDEAEducationalEnvironmentSchoolAge is currently named RefIDESEducationalEnvironmentSchoolAge (IDES as opposed to IDEA) which is not consistent with the pattern of other such keys. This makes it unintuitive to code against and causes confusion for users.

To Reproduce
See description

Expected behavior
We'd expect the primary key to be named RefIDEAEducationalEnvironmentSchoolAge

Fix FK relationship: In table CoreKnowledgeArea column ProfessionalDevelopmentActivityId

Describe the bug
Fix FK relationship:
In table CoreKnowledgeArea column ProfessionalDevelopmentActivityId links to StaffProfessionalDevelopmentActivity not ProfessionalDevelopmentActivity.

DiplomaOrCredentialAwardDate data type inconsistency

Describe the bug
DiplomaOrCredentialAwardDate has a data type mismatch. It's a datetime in K12StudentAcademicRecord but a NVARCHAR(7) in other tables.

To Reproduce
Steps to reproduce the behavior:
Search for DiplomaOrCredentialAwardDate in the CEDS-IDS sql script.

Expected behavior
We'd expect DiplomaOrCredentialAwardDate to have the same data type in all tables.

Column [RefExitGradeLevel] in table [K12StudentEnrollment] was named incorrect in IDS

Description of Proposed Changes
The Foreign Key reference to Exit Grades in in K12StudentEnrollment was named as RefExitGradeLevel

Expected Behavior
We'd expect that to be RefExitGradeLevelId (Just like RefEntryGradeLevelId)

Postsecondary Exit or Withdrawal Type is a product of a student's PS institution enrollment, not a PS program enrollment

Describe the bug
A clear and concise description of what the bug is.

PsExitOrWithdrawalType is in PsProgram, but it should be in PsStudentEnrollment. The definition and option set values work better in that table, and the CEDS Element in the DES is in Postsecondary -> PS Student -> Institution Enrollment.

Ref Table Description Values Longer than character limit

Describe the bug
Currently Description in Ref tables is a nvarchar(100) however several reference tables have values in the populating script that are truncated to fit this length. In most cases these seem to be small, only 1-3 characters and it's straightforward to interpolate what's being cut off, but in others it's hard to tell just what's being cut off. Currently these tables are:
RefCarnegieBasicClassification
RefCourseSectionExitType
RefExitOrWithdrawalType
RefIPEDSFASBFinancialPosition
RefPESCAwardLevelType
RefProgramExitReason
RefProgramGiftedEligibility
RefRlisProgramUse
RefTribalAffiliation

To Reproduce
Steps to reproduce the behavior:

1. Go to 'Populate-CEDS-Ref-Tables.sql'
2. Search for one of the above tables
3. Locate record that is truncated. For example RefPESCAwardLevelType has a Description with the value 'Postsecondary award, certificate, or diploma of less than one academic year (less than 900 contact o' and it's unclear at a quick glance just what's being lost with the truncation.

Expected behavior
I'd expect the Description field to be long enough to hold all values without truncation

Add new element State Appropriation Methods to the IDS (see CEDS-Elements ticket #29)

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Nathan Clinton

Authoring Organization(s)
AEM Corp

Email Address
[email protected]

Target Data Needed
4/21/2020

Use Case Title
Provide a concise description that defines the use case.
Support EDFacts file spec FS207 by adding a new element to track State Appropriation Methods for charter schools

Use Case Overview
Provide a concise, high-level description of the actions of the use case. While being concise, the description should cover all possible actions for the use case.
Refer to EDFacts file spec FS207. This is in support of Generate.

Use Case Background
Provide information related to why these changes/additions are needed.
See CEDS-Elements ticket #29

Components Affected

CEDS Integrated Data Store (complete IDS Design Overview Table)

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.
Add new Ref table called RefStateAppropriationMethod.
Add foreign key reference to RefStateAppropriationMethod to K12School table

The RefRace table for IDS v8.0.0.0 has invalid codes

Describe the bug
The script that populates the RefRace table for the IDS for V8.0.0.0 contains invalid codes. The script needs to be modified to reflect the correct coding for races.

To Reproduce
Steps to reproduce the behavior:
SELECT * FROM RefRace

Expected behavior
The Code column should contain the following:
AmericanIndianorAlaskaNative
Asian
BlackorAfricanAmerican
NativeHawaiianorOtherPacificIslander
White

It presently contains:
Race American Indian or Alaska Native
Race Asian
Race Black or African American
Race Native Hawaiian or Other Pacific Islander
Race White

Modify the script to reflect the correct race codes.

Resolve redundancy between PersonCredential and CredentialAward/CredentialDefinition structure.

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Jim Goodell

Authoring Organization(s)
CEDS Team

Email Address
[email protected]

Use Case Title
Resolve redundancy between PersonCredential and CredentialAward/CredentialDefinition structure.

Use Case Overview
This change would either 1. replace PersonCredential by merging properties into CredentialAward or CredentialDefinition and link to StaffCredential, or 2. keep the redundant table but add an optional link to CredentialDefinition.

The decision may be guided by the potential impact and perceived benefit by the OSC. Option 1 would be a breaking change but would resolve the redundancy. Option 2 would not be a breaking change, it would only add a relationship that could be used to provide richer detail with CredentialDefinition linked to PersonCredential.

Pull Request Number(s) (If applicable)

Use Case Background
Provide information related to why these changes/additions are needed.
The IDS currently supports redundant structures for information about a credential held by a person. 1. PersonCredential is an older construct originally designed to support educational staff qualifications for a job with properties for things like license number. It links to the StaffCredential table with statuses such as RefTeachingCredentialBasis and CPR Certification Expiration Date. 2. CredentialAward which links a Person to any kind of credential defined in CredentialDefinition, is a newer construct designed to support any kind of credential and to be compatible with de-facto standards for credentials.

Components Affected
CEDS Integrated Data Store (complete IDS Design Overview Table)
CEDS Data Warehouse (complete DW Design Overview Table)
CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
No new elements are proposed, only element positions and relationships in the IDS.

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.

Option 1: Merge properties from PersonCredential into CredentialAward or CredentialDefinition as appropriate. Consider appropriate places for information in tables EarlyChildhoodCredential and StaffCredential, e.g. TeachingCredentialBasis as a criteria metadata that may be associated with a teaching credential.

Option 2: Add a relationship CredentialDefinitionId (nullable) to PersonCredential

As-Is Diagram
Add as an attachment in this issue.
TBD based on option chosen by community.

Proposed To-Be Diagram
Add as an attachment in this issue.
TBD based on option chosen by community.

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Provide a concise description of the proposed changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

Managing deprecated option set values in reference ("Ref") tables

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Nathan Clinton

Authoring Organization(s)
CEDS

Target Data Needed
N/A

Use Case Title
Managing deleted or deprecated CEDS option set codes in the IDS to support longitudinal data and also maintaining and identifying the current official CEDS option set in the IDS.

Use Case Overview
In cases where a code is deprecated, stakeholders need to be able to:

1. Store longitudinal data -- If the deprecated code was used, then we need to keep the record in the Ref table in order to maintain both accurate longitudinal data and basic referential integrity.
2. Identify the official CEDS option set values based at any given time -- If a system provides, for example, a drop down list that pulls from the Ref tables, there needs to be a way to select the correct list of options as of a particular date.

Pull Request Number(s) (If applicable)

Use Case Background
The NCES form has recommended removing the code "Exited" from "Exit or Withdrawal Type" (CEDS Elements Issue 5 ). If this code is deprecated, we need to be able to support stakeholders using this code the IDS.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Element Gap Analysis
CEDS Elements Issue 5

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.
Add the following fields to all Ref tables:

1. RecordStartDateTime
2. RecordEndDateTime

New option set values will be assigned a RecordStartDateTimethat corresponds with the date when the option was made available in CEDS and the IDS.

RecordEndDateTime will be null for currently active option set values.

A date will be assigned to RecordEndDateTime that corresponds with the date when the option was officially deprecated.

As-Is Diagram
N/A

Proposed To-Be Diagram
N/A

Column FacilityAcquisitionDate in Facility is of type nvarchar instead of date

Describe the bug
The column FacilityAcquisitionDate in Facility is currently a nvarchar[100]. However the definition of the associated CEDS Element https://ceds.ed.gov/CEDSElementDetails.aspx?TermxTopicId=47092 is a date format value. In other cases within the CEDS-IDS Date format elements are typed as Date, not nvarchar.

To Reproduce
See description

Expected behavior
We'd expect FacilityAcquisitionDate to be a Date type column.

The naming of the primary key of RefGunFreeSchoolsActReportingStatus is not consistent with standards

Describe the bug
The name of the primary key of RefGunFreeSchoolsActReportingStatus is currently RefGunFreeSchoolsActStatusReportingId (note Status and Reporting being reversed) which does not follow the ref table naming convention

To Reproduce
See description

Expected behavior
We'd expect the primary key to be named RefGunFreeSchoolsActReportingStatusId or for the table to be named RefGunFreeSchoolsActStatusReporting

Add New Table K12LeaGradeOffered

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown

Authoring Organization(s)
CIID Generate

Email Address
[email protected]

Use Case Title
Add New Table K12LeaGradeOffered

Use Case Overview
Create a new table in the CEDS Integrated Data Store called K12LeaGradeOffered. This will be used to track the Grades Offered by an LEA. The new table will include the following columns:

K12LeaGradeOfferedId (PK, int, not null)
K12LeaId (FK, in, not null)
RefGradeLevelId (FK, int, not null)
RecordStartDateTime (datetime, null)
RecordEndDateTime (datetime, null)

Pull Request Number(s) (If applicable)

Use Case Background
Provide information related to why these changes/additions are needed.
This change is in reference to the Elements Issue that is presently pending community approval listed here:
CEDStandards/CEDS-Elements#133

The proposal will provide the needed background for the change. Essentially, the grades offered by the schools under the authority of an LEA do not always equal the grades offered by the LEAs. In some states, LEAs offer additional grades beyond what their schools are able to offer and this is done through agreements/contracts with other LEAs. Therefore this information must be tracked separately.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

New table: K12LeaGradeOffered
Columns:
K12LeaGradeOfferedId (PK, int, not null)
K12LeaId (FK, in, not null)
RefGradeLevelId (FK, int, not null)
RecordStartDateTime (datetime, null)
RecordEndDateTime (datetime, null)

K12Course.RefCreditTypeEarnedId in IDS is a required field

The RefCreditTypeEarnedId attribute in IDS is set as a required field while that data is not available in our use case. This is preventing us from storing values in this table.

Expected behavior
Requesting to make changes so that null values for this field can be allowed.

Store person information by organization to handle conflicting data reported by organizations

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Nathan Clinton

Authoring Organization(s)
Center for Education Performance and Information (CEPI) in Michigan

Email Address
[email protected]

Target Data Needed
We're coding this at CEPI and will submit the change to the community once the project is complete.

Use Case Title
Education organizations provide conflicting data about students.

Use Case Overview
Students that attend multiple schools, particularly those at the same time, may have data incorrectly entered into student information systems or provide districts with incorrect or differing data. This includes sex, birthdate, name, race/ethnicity, etc. State Education Agencies must be able to collect and store this conflicting information in the IDS and data warehouse. Conflicting data is often handled at the reporting level, or it may be reviewed and corrected during data quality validations. Regardless, in the education world there are multiple systems of record (district, postsecondary institution, etc.) for student data and each needs to be trusted.

Pull Request Number(s) (If applicable)

Use Case Background
Students attending multiple schools were found to have different birthdates in Michigan. This is traditionally handled at the report level, so the CEDS data models need to be able to handle this scenario.

Components Affected
CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Data Warehouse (complete DW Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Add OrganizationId to dbo.Person. Note that this will create an additional step to determine uniqueness for a person. Currently, uniqueness is down to the person level, but with this change, it is at the person and organization level. To find a unique person record in the new format, you will need to filter by PersonIdentifier.Identifier and Person.OrganizationId. We will leave Person.OrganizationId nullable for those that do not need to account for this scenario, meaning they can still refer to PersonIdentifier.Identifier to establish uniqueness.

As-Is Diagram

Proposed To-Be Diagram

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Birthdate, Sex, CohortYear, and HispanicLatinoEthnicity will be moved out of DimK12Student, DimPsStudent, and any other person-related slowly changing dimensions and added directly to Fact tables. This will require a new dimension table, DimSexes. Still considering where to store HispanicLatinoEthnicity. We may change DimRaces to DimRacesEthnicities, converting it to a junk table.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
All scripts that handle person data will need to accommodate the addition of OrganizationId to dbo.Person, taking care to note that this field is nullable.

Two or More Races Missing from RefRace

Describe the bug
The Two or More Races Code is missing from the RefRace table in the IDS V10.

Add Homeless to the RefPersonStatusType table in the IDS

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS
Email Address
[email protected]
Use Case Title
Add Homeless to the RefPersonStatusType table in the IDS

Use Case Overview
Homeless is a status a person can have in CEDS thus it should exist within the RefPersonStatusType table in the CEDS IDS.

Pull Request Number(s) (If applicable)

Use Case Background
Provide information related to why these changes/additions are needed.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Data Warehouse (complete DW Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
Provide link to CEDS Align Map or Connection.

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Provide a concise description of the proposed changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

Please change default branch name from master to main

Many organizations are renaming software and processes in an effort to use a more inclusive language. GitHub has moved to make main the default branch name for new projects (https://github.com/github/renaming).

Please consider doing that for this project.

Thanks!

Include options in the RefCipCode table in the next release of the IDS

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Duane Brown
Authoring Organization(s)
CEDS
Email Address
[email protected]
Use Case Title
Include options in the RefCipCode table in the next release of the IDS

Use Case Overview
The IDS contains a reference table for the Classification of Instruction Programs Code (CIP) - RefCipCode but the table is not populated with the options/descriptions. In a future release of the IDS, include the codes in that table.

Pull Request Number(s) (If applicable)

Use Case Background
Provide information related to why these changes/additions are needed.

Components Affected
Delete everything from the list below except the components affected:

CEDS Integrated Data Store (complete IDS Design Overview Table)

CEDS Data Warehouse (complete DW Design Overview Table)

CEDS Data Migrations (complete Data Migration Impact Analysis Table)

CEDS Element Gap Analysis
Provide link to CEDS Align Map or Connection.

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA WAREHOUSE DESIGN OVERVIEW (Includes Dim, Fact, and Report tables)

Description of Proposed Changes
Provide a concise description of the proposed changes.

As-Is Diagram
Add as an attachment in this issue.

Proposed To-Be Diagram
Add as an attachment in this issue.

---

DATA MIGRATION IMPACT ANALYSIS

Description of Proposed Changes
Provide a concise description of the proposed changes.

New Business Rule
Provide a concise and comprehensive description of the new business rule.

Existing Business Rule
Provide a concise and comprehensive description of the existing business rule.

Changed Business Rule
Provide a concise and comprehensive description of the changes to be made to the business rule.

Rubric/RubricCriterion join to CompetencySet

Is your feature request related to a problem? Please describe.
A clear and concise description of what the problem is.

Ex. I'm always frustrated when [...]
The current model doesn't support the use case of a Rubric or Rubric Criterion used to evaluate a Competency Set (e.g. a complex performance task that requires 2 or more competencies).

Describe the solution you'd like
A clear and concise description of what you want to happen.

Add join table between Rubric/RubricCriterion and CompetencySet?

Describe alternatives you've considered
A clear and concise description of any alternative solutions or features you've considered.

Additional context
Add any other context or screenshots about the feature request here.

RefOrganizationIdentificationSystem Missing Rows

Describe the bug
The CEDS IDS Version 8.0.0.0 RefOrganizationIdentificationSystem population script appears to be missing rows. CEDS version 7.1 contained 47 rows. CEDS version 8.0.0.0 contains only 18 rows when the reference table is populated. Reference values/options were not removed from CEDS between the versions so the reference script is likely missing them.

To Reproduce
Steps to reproduce the behavior:

SELECT *
FROM RefOrganizationIdentificationSystem

Expected behavior
Compare the options between 7.1 and 8.0 and the expected options for version 8 and add the missing values into the script.

RefStateANSICode does not follow the design pattern of other reference tables

Describe the bug
RefStateANSICode has only two columns: Code and StateName, which is very different from other reference tables and their structure.

To Reproduce
See description

Expected behavior
We'd expect the design of the RefStateANSICode to match that of other reference tables

RefTitleIIILanguageInstructionProgramType Identical SortOrder Values

Describe the bug
The IDS Reference Table "RefTitleIIILanguageInstructionProgramType" contains two identical SortOrder values.

To Reproduce
Steps to reproduce the behavior:

SELECT *
FROM dbo.RefTitleIIILanguageInstructionProgramType

The Code of "NewcomerPrograms" and the Code of "Other" both have a SortOrder of "11.00"

Expected behavior
Modify the row with the code of "Other" to have a SortOrder of "12.00"

Expand Personal Information Verification to be a 1:Many relationship in CEDS-IDS

This is for capturing needs not currently supported by the CEDS model. Please do not send or share actual data as examples in this issue or in attachments.

Author(s)
Eric Marshall
Authoring Organization(s)
North Carolina Department of Public Instruction
Email Address
[email protected]
Use Case Title
Expand Personal Information Verification to be a 1:Many relationship.

Use Case Overview
Expand Expand Personal Information Verification (000618) currently stored in PersonDetail to be a 1:Many relationship to allow multiple sources of verification to be stored.

Pull Request Number(s) (If applicable)
N/A

Use Case Background
Currently in the CEDS-IDS only one source of personal information verification can be stored for a person. However, one person may provide multiple sources of verification due to various factors, such as a single document not verifying all personal information. North Carolina would like to be able to store all of these verification sources.

Components Affected

CEDS Integrated Data Store (complete IDS Design Overview Table)

---

INTEGRATED DATA STORE DESIGN OVERVIEW

Description of Proposed Changes
We'd like to add a new table, proposed name PersonPersonalInformationVerification, to create a 1:Many relationship between Person and RefPersonalInformationVerification, which is currently a 1:1 relationship found in PersonDetail as field RefPersonalInformationVerificationId. This would be similar to other currently existing tables such as PersonLanguage.

At present it would only have RefPersonalInformationVerificationId as a data field, making the SQL look something like:

CREATE TABLE [dbo].[PersonPersonalInformationVerification](
[PersonPersonalInformationVerificationId] [int] IDENTITY(1,1) NOT NULL,
[PersonId] [int] NOT NULL,
[RefPersonalInformationVerificationId] [int] NOT NULL,
[RecordStartDateTime] [datetime] NULL,
[RecordEndDateTime] [datetime] NULL,
CONSTRAINT [PK_PersonPersonalInformationVerification] PRIMARY KEY CLUSTERED
(
[PersonPersonalInformationVerificationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

This would also entail removing/deprecating RefPersonalInformationVerificationId from PersonDetail

---