This table relates districts to each student and provides the maximum date (EndDt) that the district is allowed access to the students test records. This table provides the means for the Regional Warehouse Providers (RWP) to limit the access for the districts they serve to the proper period of time to be in accordance with the Family Educational Rights and Privacy Act (FERPA). The RWP should replace the contents of this table on each extract.

use StudentCentered
go

if exists (select 1
from sysindexes
where id = object_id('FIRS')
and name = 'EndDt_IDX'
and indid > 0
and indid < 255)
drop index FIRS.EndDt_IDX
go

if exists (select 1
from sysindexes
where id = object_id('FIRS')
and name = 'ChkDigitStdntID_IDX'
and indid > 0
and indid < 255)
drop index FIRS.ChkDigitStdntID_IDX
go

if exists (select 1
from sysindexes
where id = object_id('FIRS')
and name = 'DisInstID_IDX'
and indid > 0
and indid < 255)
drop index FIRS.DisInstID_IDX
go

if exists (select 1
from sysindexes
where id = object_id('FIRS')
and name = 'UnqFIRS_IDX'
and indid > 0
and indid < 255)
drop index FIRS.UnqFIRS_IDX
go

if exists (select 1
from sysobjects
where id = object_id('FIRS')
and type = 'U')
drop table FIRS
go

/*==============================================================*/
/* Table: FIRS */
/*==============================================================*/
create table FIRS (
DistInstID int not null,
ChkDigitStdntID int not null,
EndDt datetime null
)
go

/*==============================================================*/
/* Index: UnqFIRS_IDX */
/*==============================================================*/
create unique index UnqFIRS_IDX on FIRS (
DistInstID,
ChkDigitStdntID,
EndDt
)
go

/*==============================================================*/
/* Index: DisInstID_IDX */
/*==============================================================*/
create index DisInstID_IDX on FIRS (
DistInstID
)
go

/*==============================================================*/
/* Index: ChkDigitStdntID_IDX */
/*==============================================================*/
create index ChkDigitStdntID_IDX on FIRS (
ChkDigitStdntID
)
go

/*==============================================================*/
/* Index: EndDt_IDX */
/*==============================================================*/
create index EndDt_IDX on FIRS (
EndDt
)
go

ID assigned and maintained by the Institution database.

An integer version of the Check Digit Student Identifier (ChkDigitStdntID).

The maximum date that the related district is allowed access to the test data for the related student. Only tests for the related student with a TstDt less than the EndDt should be shown to the related district.