This table holds character based benchmark level scores such as PLB, PLG, PL5B and PL5G scores related to a specific test event and score reporting category for the test.

use StudentCentered
go

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

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

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

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

/*==============================================================*/
/* Table: BnchLvl */
/*==============================================================*/
create table dbo.BnchLvl (
ID int not null,
SMFConfig_ID int not null,
UnqTstEvent_ID int not null,
Ctgry_ID int not null,
BnchLvlTyp_ID int not null,
BnchLvlMng_ID int not null,
BnchLvlVal char(2) null,
EffDt datetime not null default getdate(),
EndDt datetime null,
UpdtID varchar(18) null,
UpdtDt datetime null,
CrtrID varchar(18) null,
CrtDt datetime null,
LoadSeq_ID int null,
LoadDt datetime null,
constraint BnchLvl_PK primary key nonclustered (ID)
on LargeIDX
)
on LargeTBL
go

/*==============================================================*/
/* Index: UnqTstEvent_FK */
/*==============================================================*/
create index UnqTstEvent_FK on dbo.BnchLvl (
UnqTstEvent_ID,
SMFConfig_ID
)
on LargeIDX
go

/*==============================================================*/
/* Index: Ctgry_FK */
/*==============================================================*/
create index Ctgry_FK on dbo.BnchLvl (
Ctgry_ID
)
on LargeIDX
go

/*==============================================================*/
/* Index: ScoreTyp_FK */
/*==============================================================*/
create index ScoreTyp_FK on dbo.BnchLvl (
BnchLvlTyp_ID
)
on LargeIDX
go

Unique ID assigned and maintained by the database for the BnchLvl table.

Foreign key from the Standard Message Format Configuration (SMFConfig) table. This key represents a subject and year combination.

Foreign key from the Unique Test Event (UnqTstEvent) table. This identifier is used to locate all versions of a given test in tables throughout the StudentCentered database.

Foreign key to the Category (Ctgry) table where the names of the score reporting category are held.

Foreign key to Master Codes (MstrCd) table for the Score Type. Examples of the type of scores loaded here are: 5LvlBnch (PL5B), 5LvlGrd, (PL5G), PerfLvlBnch (PLB), PerfLvlGrd (PLG) as well as total scores.

Foreign key to the Master Codes (MstrCd) table that may be used to tie the Bench Level Value to a meaning.

The score given for a type of bench level within a category of score.

The date that this row of data becomes effective in StudentCentered.

The date that this row of data ceases to be effective in StudentCentered.

The user identification of the person or process that last updated the record in StudentCenteredStaging.

The date of the last update to the source row of data from StudentCenteredStaging.

The user identification of the person or process that created the record in StudentCenteredStaging.

The date the source row of data was created in StudentCenteredStaging.

Foreign key from the Load Sequence (LoadSeq) table. Each load receives a unique identifier.

The date the data was loaded into StudentCentered.