Database Overview - Quick Start

In this step, you'll familiarize yourself with ODS / API databases and understand how the data from your SIS is stored in the ODS. 

Your Starter Kit solution includes three databases:

• EdFi_Admin. A database containing authentication information for API clients.

• EdFi_Security. A database containing authorization information for API clients.

• EdFi_ODS_*.  A database that stores data that your SIS sends to Ed-Fi API, There could be more than one of these databases, one per school year.

You can view these databases in your Starter Kit VM using Microsoft SQL Server Management Studio. 

Step 1. Open Microsoft SQL Server Management Studio as Administrator using Windows search box with "ssms" keyword.

Step 2. With Windows Authentication selected, click Connect.

Step 3. After connection, you will see EdFi_Admin, EdFi_Security and EdFi_Ods database for school year 2022 in the Object explorer.

Ed-Fi Admin Database

The following schema diagram for Ed-Fi Admin database shows that it mainly holds data pertaining to Authentication (i.e., Identifying the API client):

Ed-Fi Security Database

The following schema diagram for Ed-Fi Security database shows that it mainly holds security metadata pertaining to Authorization (i.e., Establishing API client's assess rights to serve the request): 

Ed-Fi ODS Database

The EdFi_Ods database contains tables, structures, and associations for a rich and detailed data model, so that it can handle the real-world complexities of student attendance, grades, discipline events, and so forth. While data model is large, this Starter Kit's use case only deals with data in Alternative and Supplemental Services and Enrollment domains. The following schema diagram for EdFi_Ods database shows tables of interest. 

From your Microsoft SQL Server Management Studio, look at the data in some of these tables to familiarize yourself with the ODS. 

Once you have gone through Introduction to API section, you can verify how the data you POSTed to the API landed in your EdFi_Ods database. 

The following SQL query shows the students' record and their core characteristics:

SELECT s.StudentUSI, s.FirstName, s.LastSurname, s.BirthDate, sa.EntryDate, sa.SchoolId, sa.EntryGradeLevelDescriptorId, sa.ExitWithdrawDate, sa.ExitWithdrawTypeDescriptorId, sa.FullTimeEquivalency,
sexd.Description as Sex, se.HispanicLatinoEthnicity, raced.Description as Race
FROM [edfi].[Student] as s
INNER JOIN [edfi].[StudentSchoolAssociation] as sa on s.StudentUSI = sa.StudentUSI
INNER JOIN [edfi].[StudentEducationOrganizationAssociation] as se on s.StudentUSI = se.StudentUSI
INNER JOIN [edfi].[StudentEducationOrganizationAssociationRace] as ser on s.StudentUSI = ser.StudentUSI AND se.EducationOrganizationId = ser.EducationOrganizationId
INNER JOIN [edfi].[Descriptor] as sexd on se.SexDescriptorId = sexd.DescriptorId
INNER JOIN [edfi].[Descriptor] as raced on ser.RaceDescriptorId = raced.DescriptorId
WHERE (FirstName = 'John' AND LastSurname = 'Smith') OR (FirstName = 'Kelly' AND LastSurname = 'Logan'  )
OR (FirstName = 'Jennifer' AND LastSurname = 'Hart')

You can run another SQL query to verify the students' association with the "Special Education" program: 

SELECT s.StudentUSI, s.FirstName, s.LastSurname, s.BirthDate, sp.BeginDate, sp.ProgramName, d.Description
FROM [edfi].[Student] as s
INNER JOIN [edfi].[StudentSpecialEducationProgramAssociation] as sp on s.StudentUSI = sp.StudentUSI
INNER JOIN [edfi].[Descriptor] as d on sp.ProgramTypeDescriptorId = d.DescriptorId
WHERE (FirstName = 'John' AND LastSurname = 'Smith') OR (FirstName = 'Kelly' AND LastSurname = 'Logan'  )
OR (FirstName = 'Jennifer' AND LastSurname = 'Hart' )