Application Tables

OSMIS – Open Source School Management System

Database Definition

April 2003

Introduction

This document presents the database definition for the Open Source School Management System (OSSMS). Each database table is described in detail. Where the table contains static data, this data is also specified.

Intended Audience

This document is targeted primarily at the project team responsible for the development OSSMS. However, being an Open Source development project, it is made freely available for anyone to read, review and/or comment on. This is a technical document, and previous knowledge of entity-relational databases is assumed.

Outline of the Document

The remainder of the document proceeds as follows. Section 2 presents the database definition for the static data in the database. Next, Section 3 presents the database definition for the auxiliary tables, while Section 4 presents the application tables. There is no real difference between auxiliary and application tables – this merely provides a way of separating the general helper tables from the application-specific tables.

A Note on Internationalisation

Some care was taken to make the task of extending and internationalising the system possible with reduced modifications to the database. However, due to the nature of the requirements analysis task, and the constraints of system performance requirements and development time, the database design was not generalised to the fullest extent.

General Information

Document name	OSSMS-DBDefinition
Author	Adi Attar
Status	In progress
Creation date	25/03/2003
Last update date	04/21/03 10:31 PM
Version	1.2
Printing date
File	OSSMS-DBDefinition.doc

Revision History

Version	Date	Amended by	Comments
1.0	21/04/2003	Adi Attar	Creation
1.1	02/05/2003	Adi Attar	Added static table ExtraCurricularActivity (2.29) Added SportsHouse.StaffMemberID (3.6) Added LearnerPA.FeesExempt (4.12) Renamed table LearnerLeaveType to LeaveType (2.18) Added table FeesPayment (4.26) Added table LearnerRepCouncil (4.27) Added table StaffLeavePA (4.28) Added table StaffLeave (4.29)
1.2	14/05/2003	Adi Attar	Added table TimetablePattern (4.30) Added field GradePA.TimetablePatternID Added table TimetableDay (4.31) Added table TimetablePeriod (4.32) Added table SGBDetails (4.33) Added field School.SGBDetailsID Added table SGBMemberPA (4.34) Added table SGBMeeting (4.35) Added table SchoolPlatoon (4.36) Added field School.SchoolPlatoonID Aded table SchoolStructure (4.37) Added field School.SchoolStructureID Added table ExtraCurriculum (4.38) Added table Fundraising (4.39) Added table Newsletter (4.40)
1.3	16/06/2003	Adi Attar	Abstracted person contact details into Person table as follows: Added to Person fields PhysicalAddressID, PostalAddressID, HomeTelephoneID, WorkTelephoneID, FaxNumberID, MobileNumberID, EmailAddressID Removed from Learner fields PhysicalAddressID, PostalAddressID, HomeTelephoneID Removed from StaffMember fields PhysicalAddressID, PostalAddressID, HomeTelephoneID, MobileNumberID Removed from Guardian PhysicalAddressID, PostalAddressID, HomeTelephoneID, WorkTelephoneID, FaxNumberID, MobileNumberID, EmailAddressID Dropped table EmergencyContact Changed Leaner.EmergencyContactID to reference Person.PersonID instead of EmergencyContact.EmergencyContactID Removed ExtraCurriculum.ContactNumberID Added StaffMember.NextOfKinID Removed from AdminStaff NextOfKinName, NextOfKinPhoneID Removed from SupportStaff NextOfKinName, NextOfKinPhoneID
1.4	17/06/2003	Adi Attar	Changed School.TelefaxNumber to TelefaxNumberID (reference to Telephone.TelephoneID) Renamed MedicalStatus table to LearnerMedicalStatus Renamed Learner.MedicalStatusID to LearnerMedicalStatusID Added table StaffMedicalStatus Added to StaffMember fields StaffMedicalStatusID, MedicalAidID Added table LearnerExtraCurriculum Added table LearnerECAchievement Added static table IncidentType Added table LearnerIncident Added table LearnerFeesPayer Added Learner.LearnerFeesPayerID Added LearnerPA.FeesPayable Added table PaymentCycle
1.5	01/09/2003		Changed document title Corrected and finalised static data for Province table Renamed Race table to EthnicGroup. Changed all references accordingly Changed LearningArea.LearningAreaCode constraint to not null Renamed table ExtraCurricularActivity to ECActivity (naming conflicts in automatic sequence creation in postgres scripts), and ExtraCurricularActivityID to ECActivityID. Changed all references accordingly Changed Department.EmailAddressID constraint to nullable Changed the following LearnerMedicalStatus fields from boolean to varchar(64): Glasses, Asthma, BloodPressure, HearingAid, Prosthesis, Diabetic, ChronicIllness Added table GlobalConfiguration (5.45) Changed all occurrences of tinyint to smallint for SQL compliance Added table StaffDisability (2.32) Added field StaffMember.StaffDisabilityID Added fields LearnerEnrollment.Reapplication and ReapplicationStatusID Added table SubjectLearningOutcome (5.46) Added table SubjectPromotion (5.47) Added table SchoolPromotion (5.48) Added table PerformanceHistory (5.49)

Table Of Contents
1 Introduction *

1.1 Intended Audience *

1.2 Outline of the Document *

1.3 A Note on Internationalisation *

1.4 General Information *

1.5 Revision History *

2 Static Tables *

2.1 GradeType *

2.1.1 Field Definitions *

2.1.2 Constraints *

2.1.3 Relationships *

2.1.4 Values *

2.2 Language *

2.2.1 Field Definitions *

2.2.2 Constraints *

2.2.3 Relationships *

2.2.4 Values *

2.3 TelephoneType *

2.3.1 Field Definitions *

2.3.2 Constraints *

2.3.3 Relationships *

2.3.4 Values *

2.4 AddressType *

2.4.1 Field Definitions *

2.4.2 Constraints *

2.4.3 Relationships *

2.4.4 Values *

2.5 Province *

2.5.1 Field Definitions *

2.5.2 Constraints *

2.5.3 Relationships *

2.5.4 Values *

2.6 Country *

2.6.1 Field Definitions *

2.6.2 Constraints *

2.6.3 Relationships *

2.6.4 Values *

2.7 Race *

2.7.1 Field Definitions *

2.7.2 Constraints *

2.7.3 Relationships *

2.7.4 Values *

2.8 TeachingLevel *

2.8.1 Field Definitions *

2.8.2 Constraints *

2.8.3 Relationships *

2.8.4 Values *

2.9 PersonnelCategory *

2.9.1 Field Definitions *

2.9.2 Constraints *

2.9.3 Relationships *

2.9.4 Values *

2.10 QualificationType *

2.10.1 Field Definitions *

2.10.2 Constraints *

2.10.3 Relationships *

2.10.4 Values *

2.11 MortalityReason *

2.11.1 Field Definitions *

2.11.2 Constraints *

2.11.3 Relationships *

2.11.4 Values *

2.12 EmploymentType *

2.12.1 Field Definitions *

2.12.2 Constraints *

2.12.3 Relationships *

2.12.4 Values *

2.13 RemunerationScheme *

2.13.1 Field Definitions *

2.13.2 Constraints *

2.13.3 Relationships *

2.13.4 Values *

2.14 LearnerStatus *

2.14.1 Field Definitions *

2.14.2 Constraints *

2.14.3 Relationships *

2.14.4 Values *

2.15 GuardianType *

2.15.1 Field Definitions *

2.15.2 Constraints *

2.15.3 Relationships *

2.15.4 Values *

2.16 ApplicationStatus *

2.16.1 Field Definitions *

2.16.2 Constraints *

2.16.3 Relationships *

2.16.4 Values *

2.17 EducationalDisability *

2.17.1 Field Definitions *

2.17.2 Constraints *

2.17.3 Relationships *

2.17.4 Values *

2.18 LeaveType *

2.18.1 Field Definitions *

2.18.2 Constraints *

2.18.3 Relationships *

2.18.4 Values *

2.19 LeaveValidationType *

2.19.1 Field Definitions *

2.19.2 Constraints *

2.19.3 Relationships *

2.19.4 Values *

2.20 LearningArea *

2.20.1 Field Definitions *

2.20.2 Constraints *

2.20.3 Relationships *

2.20.4 Values *

2.21 Subject *

2.21.1 Field Definitions *

2.21.2 Constraints *

2.21.3 Relationships *

2.21.4 Values *

2.22 LearningOutcome *

2.22.1 Field Definitions *

2.22.2 Constraints *

2.22.3 Relationships *

2.22.4 Values *

2.23 PromotionDecision *

2.23.1 Field Definitions *

2.23.2 Constraints *

2.23.3 Relationships *

2.23.4 Values *

2.24 SubjectStatus *

2.24.1 Field Definitions *

2.24.2 Constraints *

2.24.3 Relationships *

2.24.4 Values *

2.25 SchoolOwnerShipType *

2.25.1 Field Definitions *

2.25.2 Constraints *

2.25.3 Relationships *

2.25.4 Values *

2.26 LandOwnershipType *

2.26.1 Field Definitions *

2.26.2 Constraints *

2.26.3 Relationships *

2.26.4 Values *

2.27 ExamAuthType *

2.27.1 Field Definitions *

2.27.2 Constraints *

2.27.3 Relationships *

2.27.4 Values *

2.28 SpecialisationType *

2.28.1 Field Definitions *

2.28.2 Constraints *

2.28.3 Relationships *

2.28.4 Values *

2.29 ExtraCurricularActivity *

2.29.1 Field Definitions *

2.29.2 Constraints *

2.29.3 Relationships *

2.29.4 Values *

2.30 IncidentType *

2.30.1 Field Definitions *

2.30.2 Constraints *

2.30.3 Relationships *

2.30.4 Values *

2.31 PaymentCycle *

2.31.1 Field Definitions *

2.31.2 Constraints *

2.31.3 Relationships *

2.31.4 Values *

3 Auxiliary Tables *

3.1 Address *

3.1.1 Field Definitions *

3.1.2 Constraints *

3.1.3 Relationships *

3.2 EmailAddress *

3.2.1 Field Definitions *

3.2.2 Constraints *

3.2.3 Relationships *

3.3 Telephone *

3.3.1 Field Definitions *

3.3.2 Constraints *

3.3.3 Relationships *

3.4 GradePA *

3.4.1 Field Definitions *

3.4.2 Constraints *

3.4.3 Relationships *

3.5 Classroom *

3.5.1 Field Definitions *

3.5.2 Constraints *

3.5.3 Relationships *

3.6 SportsHouse *

3.6.1 Field Definitions *

3.6.2 Constraints *

3.6.3 Relationships *

3.7 BankDetails *

3.7.1 Field Definitions *

3.7.2 Constraints *

3.7.3 Relationships *

3.8 MedicalAid *

3.8.1 Field Definitions *

3.8.2 Constraints *

3.8.3 Relationships *

4 Application Tables *

4.1 Person *

4.1.1 Field Definitions *

4.1.2 Constraints *

4.1.3 Relationships *

4.2 School *

4.2.1 Field Definitions *

4.2.2 Constraints *

4.2.3 Relationships *

4.3 SchoolEMIS *

4.3.1 Field Definitions *

4.3.2 Constraints *

4.3.3 Relationships *

4.4 Department *

4.4.1 Field Definitions *

4.4.2 Constraints *

4.4.3 Relationships *

4.5 FeesStructurePA *

4.5.1 Field Definitions *

4.5.2 Constraints *

4.5.3 Relationships *

4.6 StaffMember *

4.6.1 Field Definitions *

4.6.2 Constraints *

4.6.3 Relationships *

4.7 Educator *

4.7.1 Field Definitions *

4.7.2 Constraints *

4.7.3 Relationships *

4.8 EducatorPA *

4.8.1 Field Definitions *

4.8.2 Constraints *

4.8.3 Relationships *

4.9 AdminStaff *

4.9.1 Field Definitions *

4.9.2 Constraints *

4.9.3 Relationships *

4.10 SupportStaff *

4.10.1 Field Definitions *

4.10.2 Constraints *

4.10.3 Relationships *

4.11 Learner *

4.11.1 Field Definitions *

4.11.2 Constraints *

4.11.3 Relationships *

4.12 LearnerPA *

4.12.1 Field Definitions *

4.12.2 Constraints *

4.12.3 Relationships *

4.13 Guardian *

4.13.1 Field Definitions *

4.13.2 Constraints *

4.13.3 Relationships *

4.14 LearnerEnrollment *

4.14.1 Field Definitions *

4.14.2 Constraints *

4.14.3 Relationships *

4.15 LearnerMedicalStatus *

4.15.1 Field Definitions *

4.15.2 Constraints *

4.15.3 Relationships *

4.16 LearnerMerit *

4.16.1 Field Definitions *

4.16.2 Constraints *

4.16.3 Relationships *

4.17 LearnerLeave *

4.17.1 Field Definitions *

4.17.2 Constraints *

4.17.3 Relationships *

4.18 SchoolSubject *

4.18.1 Field Definitions *

4.18.2 Constraints *

4.18.3 Relationships *

4.19 SubjectAllocation *

4.19.1 Field Definitions *

4.19.2 Constraints *

4.19.3 Relationships *

4.20 LearnerSubject *

4.20.1 Field Definitions *

4.20.2 Constraints *

4.20.3 Relationships *

4.21 TermPerformance *

4.21.1 Field Definitions *

4.21.2 Constraints *

4.21.3 Relationships *

4.22 SubjectPerformance *

4.22.1 Field Definitions *

4.22.2 Constraints *

4.22.3 Relationships *

4.23 SubjectOutcomePerformance *

4.23.1 Field Definitions *

4.23.2 Constraints *

4.23.3 Relationships *

4.24 SubjectTaskPerformance *

4.24.1 Field Definitions *

4.24.2 Constraints *

4.24.3 Relationships *

4.25 FeesPayment *

4.25.1 Field Definitions *

4.25.2 Constraints *

4.25.3 Relationships *

4.26 LearnerRepCouncil *

4.26.1 Field Definitions *

4.26.2 Constraints *

4.26.3 Relationships *

4.27 StaffLeavePA *

4.27.1 Field Definitions *

4.27.2 Constraints *

4.27.3 Relationships *

4.28 StaffLeave *

4.28.1 Field Definitions *

4.28.2 Constraints *

4.28.3 Relationships *

4.29 TimetablePattern *

4.29.1 Field Definitions *

4.29.2 Constraints *

4.29.3 Relationships *

4.30 TimetableDay *

4.30.1 Field Definitions *

4.30.2 Constraints *

4.30.3 Relationships *

4.31 TimetablePeriod *

4.31.1 Field Definitions *

4.31.2 Constraints *

4.31.3 Relationships *

4.32 SGBDetails *

4.32.1 Field Definitions *

4.32.2 Constraints *

4.32.3 Relationships *

4.33 SGBMemberPA *

4.33.1 Field Definitions *

4.33.2 Constraints *

4.33.3 Relationships *

4.34 SGBMeeting *

4.34.1 Field Definitions *

4.34.2 Constraints *

4.34.3 Relationships *

4.35 SchoolPlatoon *

4.35.1 Field Definitions *

4.35.2 Constraints *

4.35.3 Relationships *

4.36 SchoolStructure *

4.36.1 Field Definitions *

4.36.2 Constraints *

4.36.3 Relationships *

4.37 ExtraCurriculum *

4.37.1 Field Definitions *

4.37.2 Constraints *

4.37.3 Relationships *

4.38 Fundraising *

4.38.1 Field Definitions *

4.38.2 Constraints *

4.38.3 Relationships *

4.39 Newsletter *

4.39.1 Field Definitions *

4.39.2 Constraints *

4.39.3 Relationships *

4.40 StaffMedicalStatus *

4.40.1 Field Definitions *

4.40.2 Constraints *

4.40.3 Relationships *

4.41 LearnerExtraCurriculum *

4.41.1 Field Definitions *

4.41.2 Constraints *

4.41.3 Relationships *

4.42 LearnerECAchievement *

4.42.1 Field Definitions *

4.42.2 Constraints *

4.42.3 Relationships *

4.43 LearnerIncident *

4.43.1 Field Definitions *

4.43.2 Constraints *

4.43.3 Relationships *

4.44 LearnerFeesPayer *

4.44.1 Field Definitions *

4.44.2 Constraints *

4.44.3 Relationships *

Static Tables

GradeType

Database	OSSMS
Table Description	Stores the available grades as per the country’s education system. The ordering field gives the order in which the grades occur, to keep the name of the grade free from meaning.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
GradeTypeID	Identity	10	N		Unique identifier
GradeName	varchar	30	N		Name of the grade
Ordering	smallint		N		Absolute ordering of the grade, starting from 1

Constraints

Field Name	Constraint Type	Description
GradeTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
GradeType	GradeTypeID	Grade	GradeTypeID
GradeType	GradeTypeID	School	LowestGradeID
GradeType	GradeTypeID	School	HighestGradeID
GradeType	GradeTypeID	LearnerEnrollment	HighestGradePassedID
GradeType	GradeTypeID	Subject	GradeTypeID
GradeType	GradeTypeID	PerformanceHistory	GradeTypeID

Values

GradeTypeID	GradeName	Ordering
1	1	1
2	2	2
3	3	3
4	4	4
5	5	5
6	6	6
7	7	7
8	8	8
9	9	9
10	10	10
11	11	11
12	12	12

Language

Database	OSSMS
Table Description	Stores the available languages, relevant to the country in which the system is installed
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LanguageID	Identity	10	N		Unique identifier
LanguageName	varchar	30	N		Name of the language

Constraints

Field Name	Constraint Type	Description
LanguageID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Language	LanguageID	Person	HomeLanguageID

Values

LanguageID	LanguageName
1	Afrikaans
2	English
3	Isindebele
4	Siswati
5	Isixhosa
6	Isizulu
7	Sesotho
8	Sepedi
9	Setswana
10	Tshivenda
11	Xitsonga
12	Other

TelephoneType

Database	OSSMS
Table Description	Stores the types of telephone numbers available.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
TelephoneTypeID	Identity	10	N		Unique identifier
TelephoneType	varchar	30	N		Description of the telephone type.

Constraints

Field Name	Constraint Type	Description
TelephoneTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
TelephoneType	TelephoneTypeID	Telephone	TelephoneTypeID

Values

TelephoneTypeID	TelephoneType
1	Mobile (Cellular) Phone
2	Home Landline
3	Work Landline
4	Home Fax
5	Work Fax

AddressType

Database	OSSMS
Table Description	Stores the types of addresses available.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
AddressTypeID	Identity	10	N		Unique identifier
AddressType	varchar	30	N		Description of the address type.

Constraints

Field Name	Constraint Type	Description
AddressTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
AddressType	AddressTypeID	Address	AddressTypeID

Values

AddressTypeID	AddressType
1	Business Physical Address
2	Business Postal Address
3	Home Physical Address
4	Home Postal Address

Province

Database	OSSMS
Table Description	Stores the available provinces.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
ProvinceID	Identity	10	N		Unique identifier
ProvinceCode	char	2	N		Two-character province code
ProvinceName	varchar	30	N		Name of the province
CountryID	numeric	10	N		Country associated with this province

Constraints

Field Name	Constraint Type	Description
ProvinceID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Province	ProvinceID	Address	ProvinceID
Country	CountryID	Province	CountryID
Province	ProvinceID	SchoolEMIS	ProvinceID
Province	ProvinceID	Department	ProvinceID

Values

AddressTypeID	AddressType	ProvinceCode	CountryID
1	Eastern Cape	EC	1
2	Free State	FS	1
3	Gauteng	GP	1
4	Kwazulu Natal	NL	1
5	Mpumalanga	MP	1
6	Northern Cape	NC	1
7	Northern Province	NP	1
8	Northwest Province	NW	1
9	Western Cape	WC	1

Country

Database	OSSMS
Table Description	Stores the available countries.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
CountryID	Identity	10	N		Unique identifier
CountryCode	char	2	N		Two-character country code
CountryName	varchar	30	N		Name of the country

Constraints

Field Name	Constraint Type	Description
CountryID	Primary Key
CountryCode	Unique

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Country	CountryID	Province	CountryID

Values

CountryID	CountryCode	CountryName
1	ZA	South Africa

EthnicGroup

Database	OSSMS
Table Description	Stores the available ethnic groups.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
EthnicGroupID	Identity	10	N		Unique identifier
EthnicGroup	varchar	30	N		Name of the ethnic group

Constraints

Field Name	Constraint Type	Description
EthnicGroupID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
EthnicGroup	EthnicGroupID	Person	EthnicGroupID

Values

EthnicGroupID	EthnicGroup
1	African
2	Coloured
3	Indian (Asian)
4	White
5	Other

TeachingLevel

Database	OSSMS
Table Description	Stores the available teaching levels.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
TeachingLevelID	Identity	10	N		Unique identifier
TeachingLevel	varchar	30	N		Teaching level description

Constraints

Field Name	Constraint Type	Description
TeachingLevelID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
TeachingLevel	TeachingLevelID	Educator	TeachingLevelID

Values

TeachingLevelID	TeachingLevel
1	Pre-Primary
2	Primary
3	Secondary
4	Post-Matric

PersonnelCategory

Database	OSSMS
Table Description	Stores the available personnel categories.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
PersonnelCategoryID	Identity	10	N		Unique identifier
PersonnelCategory	varchar	30	N		Personnel category description

Constraints

Field Name	Constraint Type	Description
PersonnelCategoryID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
PersonnelCategory	PersonnelCategoryID	Educator	PersonnelCategoryID

Values

PersonnelCategoryID	PersonnelCategory
1	Educator
2	Principal
3	HOD
4	Deputy Principal

QualificationType

Database	OSSMS
Table Description	Stores the available educator qualification types.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
QualificationTypeID	Identity	10	N		Unique identifier
QualificationType	varchar	64	N		Qualification type description

Constraints

Field Name	Constraint Type	Description
QualificationTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
QualificationType	QualificationTypeID	Educator	QualificationTypeID

Values

QualificationTypeID	QualificationType
1	Professional Diploma or Certificate
2	Academic Degree
3	Professional Degree
4	Technical Certificate or Diploma
5	Other

MortalityReason

Database	OSSMS
Table Description	Stores the available reasons specified for mortality.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
MortalityReasonID	Identity	10	N		Unique identifier
MortalityReason	varchar	30	N		Mortality reason description

Constraints

Field Name	Constraint Type	Description
MortalityReasonID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
MortalityReason	MortalityReasonID	Educator	MortalityReasonID
MortalityReason	MortalityReasonID	LearnerEnrollment	MortalityReasonID

Values

MortalityReasonID	MortalityReason
1	Illness
2	Accident
3	Suicide
4	Violence or Homicide
5	Other

EmploymentType

Database	OSSMS
Table Description	Stores the available employment types.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
EmploymentTypeID	Identity	10	N		Unique identifier
EmploymentType	varchar	30	N		Employment type description

Constraints

Field Name	Constraint Type	Description
EmploymentTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
EmploymentType	EmploymentTypeID	StaffMember	EmploymentTypeID

Values

EmploymentTypeID	EmploymentType
1	Permanent
2	Temporary fulltime
3	Other

RemunerationScheme

Database	OSSMS
Table Description	Stores the available renumeration schemes.
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
RemunerationSchemeID	Identity	10	N		Unique identifier
RemunerationScheme	varchar	30	N		Remuneration scheme description

Constraints

Field Name	Constraint Type	Description
RemunerationSchemeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
RemunerationScheme	RemunerationSchemeID	StaffMember	RemunerationSchemeID

Values

RemunerationSchemeID	RemunerationScheme
1	Paid by State
2	Paid by Governing Body
3	Other

LearnerStatus

Database	OSSMS
Table Description	Stores the available learner status options
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerStatusID	Identity	10	N		Unique identifier
LearnerStatus	varchar	30	N		Learner status description

Constraints

Field Name	Constraint Type	Description
LearnerStatusID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearnerStatus	LearnerStatusID	Learner	LearnerStatusID

Values

LearnerStatusID	LearnerStatus
1	Current
2	Left
3	Expelled
4	Suspended

GuardianType

Database	OSSMS
Table Description	Stores the available learner guardian types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
GuardianTypeID	Identity	10	N		Unique identifier
GuardianType	varchar	30	N		Guardian type description

Constraints

Field Name	Constraint Type	Description
GuardianTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
GuardianType	GuardianTypeID	Guardian	GurdianTypeID

Values

GuardianTypeID	GuardianType
1	Mother
2	Father
3	Family Member
4	Other

ApplicationStatus

Database	OSSMS
Table Description	Stores the available status types for a new learner application to the school
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
ApplicationStatusID	Identity	10	N		Unique identifier
ApplicationStatus	varchar	30	N		Application status description

Constraints

Field Name	Constraint Type	Description
ApplicationStatusID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
ApplicationStatus	ApplicationStatusID	LearnerEnrollment	ApplicationStatusID
ApplicationStatus	ApplicationStatusID	LearnerEnrollment	ReapplicationStatusID

Values

ApplicationStatusID	ApplicationStatus
1	Received
2	Pending
3	Approved
4	Rejected
5	Completed
6	Outstanding

EducationalDisability

Database	OSSMS
Table Description	Stores the available status types for a learner’s possible educational disabilities
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
EducationalDisabilityID	Identity	10	N		Unique identifier
EducationalDisability	varchar	64	N		Educational disability description

Constraints

Field Name	Constraint Type	Description
EducationalDisabilityID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
EducationalDisability	EducationalDisabilityID	LearnerMedicalStatus	EducationalDisabilityID

Values

EducationalDisabilityID	EducationalDisability
1	Deaf
2	Hard of hearing
3	Blind
4	Partially sighted
5	Deaf-blind disabled
6	Cerebral palsied
7	Specific learning disabled
8	Behavioural disorder
9	Mild or moderate intellectually disabled
10	Severe intellectually disabled
11	Physically disabled
12	Autistic spectrum disorder
13	Epilepsy
14	Attention deficit disorder
15	Multiple disabilities

LeaveType

Database	OSSMS
Table Description	Stores the available reasons for learners and staff members being absent from school (some options only make sense for staff members)
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LeaveTypeID	Identity	10	N		Unique identifier
LeaveType	varchar	30	N		Leave type description

Constraints

Field Name	Constraint Type	Description
LeaveTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LeaveType	LeaveTypeID	LearnerLeave	LeaveTypeID
LeaveType	LeaveTypeID	StaffLeave	LeaveTypeID

Values

LeaveTypeID	LeaveType
1	Illness
2	Special
3	Truancy
4	Maternity
5	Paternity
6	Study
7	Other

LeaveValidationType

Database	OSSMS
Table Description	Stores the available methods in which learner leave can be validated
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LeaveValidationTypeID	Identity	10	N		Unique identifier
LeaveValidationType	varchar	30	N		Leave validation type description

Constraints

Field Name	Constraint Type	Description
LeaveTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LeaveValidationType	LeaveValidationTypeID	LearnerLeave	LeaveValidationTypeID
LeaveValidationType	LeaveValidationTypeID	StaffLeave	LeaveValidationTypeID

Values

LeaveValidationTypeID	LeaveValidationType
1	Phone call
2	Letter
3	Email
4	Fax
5	Medical Certificate
6	Other

LearningArea

Database	OSSMS
Table Description	Stores a global list of learning areas available in the country
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearningAreaID	Identity	10	N		Unique identifier
LearningAreaName	varchar	30	N		Name of learning area
LearningAreaCode	varchar	8	N		Learning area code

Constraints

Field Name	Constraint Type	Description
LearningAreaID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearningArea	LearningAreaID	Subject	LearningAreaID

Values

LearningAreaID	LearningAreaName	LearningAreaCode
1	?? to be completed	LLC
2		MLMMS
3		NS
4		HSS
5		EMS
6		AC
7		LO

Subject

Database	OSSMS
Table Description	Stores a global list of subjects available corresponding to the learning areas
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SubjectID	Identity	10	N		Unique identifier
LearningAreaID	numeric	10	N		The learning area to which this subject corresponds
SubjectName	varchar	30	N		Name of subject
SubjectCode	varchar	8	Y		Subject code
GradeTypeID	numeric	10	N		Reference to the grade of the subject

Constraints

Field Name	Constraint Type	Description
SubjectID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearningArea	LearningAreaID	Subject	LearningAreaID
Subject	SubjectID	LearningOutcome	SubjectID
Subject	SubjectID	SchoolSubject	SubjectID
GradeType	GradeTypeID	Subject	GradeTypeID

Values

SubjectID	SubjectName	SubjectCode	GradeTypeID
1	?? to be completed
2
3
4
5
6

LearningOutcome

Database	OSSMS
Table Description	Stores a global list of learning outcomes available corresponding to the subjects
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearningOutcomeID	Identity	10	N		Unique identifier
SubjectID	numeric	10	N		The subject to which this learning outcome corresponds
LearningOutcomeName	varchar	30	N		Description of the learning outcome
LearningOutcomeCode	varchar	8	Y		Code of the learning outcome

Constraints

Field Name	Constraint Type	Description
LearningOutcomeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Subject	SubjectID	LearningOutcome	SubjectID
LearningOutcome	LearningOutcomeID	SubjectOutcomePerformance	LearningOutcomeID
LearningOutcome	LearningOutcomeID	SubjectTaskPerformance	LearningOutcomeID
LearningOutcome	LearningOutcomeID	SubjectLearningOutcome	LearningOutcomeID

Values

LearningOutcomeID	LearningOutcomeName	LearningOutcomeCode
1	?? to be completed
2
3
4
5
6

PromotionDecision

Database	OSSMS
Table Description	Stores a list of available promotion decisions
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
PromotionDecisionID	Identity	10	N		Unique identifier
PromotionDecision	varchar	30	N		Description of the promotion decision

Constraints

Field Name	Constraint Type	Description
PromotionDecisionID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
PromotionDecision	PromotionDecisionID	TermPerformance	PromotionDecisionID
PromotionDecision	PromotionDecisionID	LearnerPA	PromotionDecisionID
PromotionDecision	PromotionDecisionID	PerformanceHistory	PromotionDecisionID

Values

PromotionDecisionID	PromotionDecision
1	Promoted
2	Not promoted
3	Condoned

SubjectStatus

Database	OSSMS
Table Description	Stores the available subject status types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SubjectStatusID	Identity	10	N		Unique identifier
SubjectStatus	varchar	8	N		String describing the subject status

Constraints

Field Name	Constraint Type	Description
SubjectStatusID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SubjectStatus	SubjectStatusID	LearnerSubject	SubjectStatusID

Values

SubjectStatusID	SubjectStatus
1	OG
2	HG
3	SG
4	LG

SchoolOwnerShipType

Database	OSSMS
Table Description	Stores the available school ownership types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SchoolOwnerShipTypeID	Identity	10	N		Unique identifier
SchoolOwnershipType	varchar	30	N		Description of the school ownership type

Constraints

Field Name	Constraint Type	Description
SchoolOwnershipTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SchoolOwnershipType	SchoolOwnershipTypeID	SchoolEMIS	SchoolOwnershipTypeID

Values

SchoolOwnershipTypeID	SchoolOwnershipType
1	Public
2	Independent

LandOwnershipType

Database	OSSMS
Table Description	Stores the available land ownership types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LandOwnershipTypeID	Identity	10	N		Unique identifier
LandOwnershipType	varchar	30	N		Description of the land ownership type

Constraints

Field Name	Constraint Type	Description
LandOwnershipTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LandOwnershipType	LandOwnershipTypeID	SchoolEMIS	LandOwnershipTypeID

Values

LandOwnershipTypeID	LandOwnershipType
1	Government
2	Church
3	Factory
4	Farm
5	Hospital
6	Mine
7	Trust
8	Private
9	Private Company
10	Other

ExamAuthType

Database	OSSMS
Table Description	Stores the available examination authority types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
ExamAuthTypeID	Identity	10	N		Unique identifier
ExamAuthType	varchar	30	N		Description of the examination authority type

Constraints

Field Name	Constraint Type	Description
ExamAuthTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
ExamAuthType	ExamAuthTypeID	SchoolEMIS	ExamAuthTypeID

Values

ExamAuthTypeID	ExamAuthType
1	Provincial
2	IEB
3	Other

SpecialisationType

Database	OSSMS
Table Description	Stores the available school specialisation types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SpecialisationTypeID	Identity	10	N		Unique identifier
SpecialisationType	varchar	64	N		Description of the school specialisation type

Constraints

Field Name	Constraint Type	Description
SpecialisationTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SpecialisationType	SpecialisationTypeID	SchoolEMIS	SpecialisationTypeID

Values

SpecialisationTypeID	SpecialisationType
1	Agriculture
2	Arts, Drama, Music or Ballet
3	Commercial
4	Technical
5	Comprehensive

ECActivity

Database	OSSMS
Table Description	Stores the available extra curricular activities
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
ECActivityID	Identity	10	N		Unique identifier
ExtraCurricularActivity	varchar	64	N		Description of the extra curricular activity

Constraints

Field Name	Constraint Type	Description
ECActivityID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
ECActivity	ECActivityID	ExtraCurriculum	ECActivityID

Values

ECActivityID	ExtraCurricularActivity
1	Athletics
2	Chess
3	Choir
4	Cricket
5	Debating
6	Drama
7	Hockey
8	Music
9	Netball
10	Rubgy
11	Soccer
12	Softball
13	Tennis
14	Volleyball
15	Dancing
16	Visual Art
17	Swimming
18	Boxing
19	Karate
20	Drum Majorettes
21	Cadette Band
22	Squash
23	Cross Country
24	Gymnastics

IncidentType

Database	OSSMS
Table Description	Stores the available incident types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
IncidentTypeID	Identity	10	N		Unique identifier
IncidentType	varchar	64	N		Description of incident type

Constraints

Field Name	Constraint Type	Description
IncidentTypeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
IncidentType	IncidentTypeID	LearnerIncident	IncidentTypeID

Values

IncidentTypeID	IncidentType
1	Sexual Harassment
2	Racial Conflict
3	Other Conflict
4	Pregnancy
5	Other

PaymentCycle

Database	OSSMS
Table Description	Stores the available payment cycle types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
PaymentCycleID	Identity	10	N		Unique identifier
PaymentCycle	varchar	30	N		Description of the payment cycle

Constraints

Field Name	Constraint Type	Description
PaymentCycleID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
PaymentCycle	PaymentCycleID	LearnerFeesPayer	PaymentCycleID

Values

PaymentCycleID	PaymentCycle
1	Weekly
2	Monthly
3	Quarterly
4	Biannually
5	Annually
6	Other

StaffDisability

Database	OSSMS
Table Description	Stores the available staff disability types
Type of Data	Static

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
StaffDisabilityID	Identity	10	N		Unique identifier
StaffDisability	varchar	30	N		Name of the disability

Constraints

Field Name	Constraint Type	Description
StaffDisabilityID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
StaffDisability	StaffDisabilityID	StaffMember	StaffDisabiltiyID

Values

StaffDisabilityID	StaffDisability
1	Sight
2	Hearing
3	Physical
4	Multiple
5	Epilepsy
6	Other

Auxiliary Tables

Address

Database	OSSMS
Table Description	Stores addresses for multiple entities.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
AddressID	Identity	10	N		Unique identifier
AddressLine1	varchar	128	N		First line of the address
AddressLine2	varchar	64	Y		Second line of the address (optional)
Suburb	varchar	64	Y		Suburb associated with the address
City	varchar	64	N		City associated with this address
PostalCode	varchar	16	Y		Postal code associated with this address
ProvinceID	numeric	10	Y		Reference to province associated with this address
AddressTypeID	numeric	10	Y		The type associated with this address – not required, but a useful field to have

Constraints

Field Name	Constraint Type	Description
AddressID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
AddressType	AddressTypeID	Address	AddressTypeID
Province	ProvinceID	Address	ProvinceID
Address	AddressID	School	PhysicalAddressID
Address	AddressID	School	PostalAddressID
Address	AddressID	Department	PhysicalAddressID
Address	AddressID	Department	PostalAddressID
Address	AddressID	LearnerEnrollment	PreviousSchoolAddressID
Address	AddressID	Person	PhysicalAddressID
Address	AddressID	Person	PostalAddressID

EmailAddress

Database	OSSMS
Table Description	Stores email addresses for multiple entities.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
EmailAddressID	Identity	10	N		Unique identifier
EmailAddress	varchar	64	N		An email address associated with this contact.

Constraints

Field Name	Constraint Type	Description
EmailAddressID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
EmailAddress	EmailAddressID	School	EmailAddressID
EmailAddress	EmailAddressID	Department	EmailAddressID
EmailAddress	EmailAddressID	Person	EmailAddressID

Telephone

Database	OSSMS
Table Description	Stores telephone numbers for multiple entities.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
TelephoneID	Identity	10	N		Unique identifier
AreaCode	varchar	6	Y		The area code for this telephone number
TelephoneNumber	varchar	16	N		The main part of the telephone number
TelephoneTypeID	numeric	10	Y		The type associated with this telephone number – not required, but a useful field to have

Constraints

Field Name	Constraint Type	Description
TelephoneID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
TelephoneType	TelephoneTypeID	Telephone	TelephoneTypeID
Telephone	TelephoneID	School	TelephoneNumberID
Telephone	TelephoneID	School	FaxNumberID
Telephone	TelephoneID	School	TelefaxNumberID
Telephone	TelephoneID	Department	TelephoneNumberID
Telephone	TelephoneID	Department	FaxNumberID
Telephone	TelephoneID	Guardian	EmployerTelephoneID
Telephone	TelephoneID	LearnerEnrollment	PreviousSchoolTelephoneID
Telephone	TelephoneID	MedicalAid	MedicalAidTelephoneID
Telephone	TelephoneID	Person	HomeTelephoneID
Telephone	TelephoneID	Person	WorkTelephoneID
Telephone	TelephoneID	Person	FaxNumberID
Telephone	TelephoneID	Person	MobileNumberID
Telephone	TelephoneID	LearnerFeesPayer	EmployerTelephoneID

GradePA

Database	OSSMS
Table Description	Stores annual information about the grades offered at the school
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
GradePAID	Identity	10	N		Unique identifier
GradeTypeID	numeric	10	N		Grade associated with this entry
Year	char	4	N		Year in which entry applies
TimetablePatternID	numeric	10	Y		Reference to the timetable pattern that applies to this grade in this year

Constraints

Field Name	Constraint Type	Description
GradePAID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
GradeType	GradeTypeID	GradePA	GradeTypeID
GradePA	GradePAID	LearnerPA	GradePAID
TimetablePattern	TimetablePatternID	GradePA	TimetablePatternID
GradePA	GradePAID	TimetableDay	GradePAID
GradePA	GradePAID	ExtraCurriculum	GradePAID

Classroom

Database	OSSMS
Table Description	Stores information about the classrooms at the school
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
ClassroomID	Identity	10	N		Unique identifier
ClassroomNumber	varchar	8	N		String describing the room number
SchoolID	numeric	10	N		The school associated with the classroom

Constraints

Field Name	Constraint Type	Description
ClassroomID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Classroom	ClassroomID	EducatorPA	ClassroomID
Classroom	ClassroomID	SubjectAllocation	ClassroomID
School	SchoolID	Classroom	SchoolID
Classroom	ClassroomID	TimetablePeriod	ClassroomID

SportsHouse

Database	OSSMS
Table Description	Stores information about the school sports houses.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SportsHouseID	Identity	10	N		Unique identifier
HouseName	varchar	30	N		The name of the school house
Colour	varchar	30	Y		The colour associated with the school house
SchoolID	numeric	10	N		The school associated with this school house
StaffMemberID	numeric	10	Y		The staff member responsible for this sports house, if applicable

Constraints

Field Name	Constraint Type	Description
SportsHouseID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
School	SchoolID	SportsHouse	SchoolID
SportsHouse	SportsHouseID	Learner	SportsHouseID

BankDetails

Database	OSSMS
Table Description	Stores banking details for multiple entities.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
BankDetailsID	Identity	10	N		Unique identifier
AccountName	varchar	30	N		The account name or name of account holder
BankName	varchar	30	N		Name of bank with which account is held
BranchName	varchar	30	Y		The branch name at which account is held
BranchCode	varchar	8	Y		The branch code of the branch
AccountNumber	varchar	15	N		Account number

Constraints

Field Name	Constraint Type	Description
BankDetailsID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
BankDetails	BankDetailsID	School	BankDetailsID

MedicalAid

Database	OSSMS
Table Description	Stores medical aid details for multiple entities
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
MedicalAidID	Identity	10	N		Unique identifier
MedicalAidName	varchar	30	N		Name of medical aid
MedicalAidNumber	varchar	15	N		Medical aid account number
PrincipalMember	varchar	30	N		Name of principal member
MedicalAidTelephoneID	numeric	10	Y		Reference to telephone number of medical aid

Constraints

Field Name	Constraint Type	Description
MedicalAidID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
MedicalAid	MedicalAidID	Learner	MedicalAidID
Telephone	TelephoneID	MedicalAid	MedicalAidTelephoneID
MedicalAid	MedicalAidID	StaffMember	MedicalAidID

Application Tables

Person

Database	OSSMS
Table Description	The central table for managing information related to individuals. Actual entities such as learners, parents and staff members have an entry in this table.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
PersonID	Identity	10	N		Unique identifier
Surname	varchar	30	N		The surname of the individual
Firstname	varchar	30	N		The first name of the individual
Middlenames	varchar	50	Y		Other names of the individual that are not the first name
DateOfBirth	date		Y		The date of birth of the individual
Gender	char	1	Y		M (male) or F (female) – not cross-referenced for efficiency purposes
EthnicGroupID	numeric	10	Y		Reference to available ethnic group types (for SA: African, Coloured, Asian, White, Other)
Religion	varchar	30	Y		The religion of the individual
Nationality	varchar	30	Y		The nationality of the individual
HomeLanguageID	numeric	10	Y		Reference to available home languages – see relevant table for details
IDNumber	varchar	30	Y		Unique ID number of the individual
PhysicalAddressID	numeric	10	Y		Reference to physical address
PostalAddressID	numeric	10	Y		Reference to postal address
HomeTelephoneID	numeric	10	Y		Reference to home telephone number
WorkTelephoneID	numeric	10	Y		Reference to work telephone number
FaxNumberID	numeric	10	Y		Reference to fax number
MobileNumberID	numeric	10	Y		Reference to mobile number
EmailAddressID	numeric	10	Y		Reference to email address

Constraints

Field Name	Constraint Type	Description
PersonID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
EthnicGroup	EthnicGroupID	Person	EthnicGroupID
Language	LanguageID	Person	HomeLanguageID
Person	PersonID	StaffMember	PersonID
Person	PersonID	Learner	PersonID
Person	PersonID	Guardian	PersonID
Person	PersonID	SGBMemberPA	PersonID
Person	PersonID	ExtraCurriculum	PersonID
Address	AddressID	Person	PhysicalAddressID
Address	AddressID	Person	PostalAddressID
Telephone	TelephoneID	Person	HomeTelephoneID
Telephone	TelephoneID	Person	WorkTelephoneID
Telephone	TelephoneID	Person	FaxNumberID
Telephone	TelephoneID	Person	MobileNumberID
EmailAddress	EmailAddressID	Person	EmailAddressID
Person	PersonID	Learner	EmergencyContactID
Person	PersonID	StaffMember	NextOfKinID
Person	PersonID	StaffMedicalStatus	DoctorID
Person	PersonID	LearnerFeesPayer	PersonID

School

Database	OSSMS
Table Description	Stores general information for the school. This table will generally have a single row for the school deploying the system, but may in future be used to hold information for other schools as system functionality increases.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SchoolID	Identity	10	N		Unique identifier
SchoolName	varchar	45	N		Official school name
PhysicalAddressID	numeric	10	Y		Reference to physical address of the school
PostalAddressID	numeric	10	Y		Reference to postal address of the school
EmailAddressID	numeric	10	Y		Reference to the email address of the school
TelephoneNumberID	numeric	10	Y		Reference to the telephone number of the school
FaxNumberID	numeric	10	Y		Reference to the fax number of the school
WebsiteURL	varchar	128	Y		URL of the school website
TelefaxNumberID	numeric	10	Y		Reference to school telefax number
LowestGradeID	numeric	10	N		Reference to lowest grade offered
HighestGradeID	numeric	10	N		Reference to highest grade offered
DepartmentID	numeric	10	Y		Reference to the education department information of this school
BankDetailsID	numeric	10	Y		Reference to the banking details of the school
SGBDetailsID	numeric	10	Y		Reference to the school governing body commitee, if applicable
SchoolPlatoonID	numeric	10	Y		Reference to school platooning details, if applicable
SchoolStructureID	numeric	10	Y		Reference to details of physical school structure

Constraints

Field Name	Constraint Type	Description
SchoolID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Address	AddressID	School	PhysicalAddressID
Address	AddressID	School	PostalAddressID
EmailAddress	EmailAddressID	School	EmailAddressID
Telephone	TelephoneID	School	TelephoneNumberID
Telephone	TelephoneID	School	FaxNumberID
Telephone	TelephoneID	School	TelefaxNumberID
GradeType	GradeTypeID	School	LowestGradeID
GradeType	GradeTypeID	School	HighestGradeID
Department	DepartmentID	School	DepartmentID
BankDetails	BankDetailsID	School	BankDetailsID
School	SchoolID	SchoolEMIS	SchoolID
School	SchoolID	SportsHouse	SchoolID
School	SchoolID	SchoolSubject	SchoolID
School	SchoolID	FeesStructurePA	SchoolID
School	SchoolID	StaffMember	SchoolID
School	SchoolID	Classroom	SchoolID
SGBDetails	SGBDetailsID	School	SGBDetailsID
SchoolPlatoon	SchoolPlatoonID	School	SchoolPlatoonID
SchoolStructure	SchoolStructureID	School	SchoolStructureID
School	SchoolID	Fundraising	SchoolID
School	SchoolID	Newsletter	SchoolID
School	SchoolID	SchoolPromotion	SchoolID

SchoolEMIS

Database	OSSMS
Table Description	Stores extended (EMIS) information for the school. This table will generally have a single row for the school deploying the system, but may in future be used to hold information for other schools as system functionality increases.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SchoolEMISID	Identity	10	N		Unique identifier
SchoolID	numeric	10	N		The school associated with this entry
ProvinceID	numeric	10	Y		Province associated with this school
EMISNumber	char	10	Y		School EMIS number
GPSLatitude1	float		Y		First latitude reading from GPS
GPSLatitude2	float		Y		Second latitude reading from GPS
GPSLongitude1	float		Y		First longitude reading from GPS
GPSLongitude2	float		Y		Second longitude reading from GPS
GPSInfo	varchar	25	Y		Details of GPS data source
PersalPayPoint	varchar	6	Y		Persal pay point number
PersalComponent	varchar	6	Y		Persal component number
EducationRegion	varchar	25	Y		Education region (Schooltool)
EducationDistrict	varchar	25	Y		Education district (Schooltool)
Circuit	varchar	25	Y		Circuit (Schooltool)
ExamCentreName	varchar	30	Y		Name of examination centre associated with the school
ExamCentreReg	varchar	9	Y		Examination centre registration number
ExamAuthTypeID	numeric	10	Y		Reference to the examination authority type associated with the school and examination centre (IEB, provincial)
SchoolOwnershipTypeID	numeric	10	Y		Reference to the ownership type of school
LandOwnershipTypeID	numeric	10	Y		Reference to the ownership type of the land
SpecialisationTypeID	numeric	10	Y		Reference to the type of specialisation of the school, if applicable
NumMultigradeClasses	smallint		Y		Number of multigrade classes
NumRemedialClasses	smallint		Y		Number of remedial or aid classes

Constraints

Field Name	Constraint Type	Description
SchoolEMISID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
School	SchoolID	SchoolEMIS	SchoolID
Province	ProvinceID	SchoolEMIS	ProvinceID
ExamAuthType	ExamAuthTypeID	SchoolEMIS	ExamAuthTypeID
SchoolOwnershipType	SchoolOwnershipTypeID	SchoolEMIS	SchoolOwnershipTypeID
LandOwnershipType	LandOwnershipTypeID	SchoolEMIS	LandOwnershipTypeID
SpecialisationType	SpecialisationTypeID	SchoolEMIS	SpecialisationTypeID

Department

Database	OSSMS
Table Description	Stores information about the education department associated with the school
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
DepartmentID	Identity	10	N		Unique identifier
DepartmentName	varchar	64	N		The name of the education department
ProvinceID	numeric	10	N		The province associated with this department
PhysicalAddressID	numeric	10	N		Reference to the physical address of the education department
PostalAddressID	numeric	10	N		Reference to the postal address of the education department
EmailAddressID	numeric	10	Y		Reference to the email address of the education department
WebsiteURL	varchar	128	Y		URL of the department website
TelephoneNumberID	numeric	10	Y		Reference to the telephone number of the department
FaxNumberID	numeric	10	Y		Reference to the fax number of the department

Constraints

Field Name	Constraint Type	Description
DepartmentID	Primary Key
DepartmentName	Unique

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Department	DepartmentID	School	DepartmentID
Province	ProvinceID	Department	ProvinceID
Address	AddressID	Department	PhysicalAddressID
Address	AddressID	Department	PostalAddressID
EmailAddress	EmailAddressID	Department	EmailAddressID
Telephone	TelephoneID	Department	TelephoneNumberID
Telephone	TelephoneID	Department	FaxNumberID

FeesStructurePA

Database	OSSMS
Table Description	Stores information about the fees stucture for the school. A new entry must be created for every year. This value can be overridden on a per-learner basis in the LearnerPA table.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
FeesStructureID	Identity	10	N		Unique identifier
Year	char	4	N		The year for which this data applies
SchoolID	numeric	10	N		The school associated with this data
Fee	money		N		The school fee for the year

Constraints

Field Name	Constraint Type	Description
FeesStructureID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
School	SchoolID	FeesStructurePA	SchoolID

StaffMember

Database	OSSMS
Table Description	Stores general information common to all staff members, including admin, support and teaching staff.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
StaffMemberID	Identity	10	N		Unique identifier
PersonID	numeric	10	N		The person data associated with this entry
SchoolID	numeric	10	N		The school associated with this staff member
CommencementDate	date		Y		Date of commencement of contract
DisabilityStatus	varchar	30	Y		Sight / Hearing / Physical / Multiple / Epilepsy / Other – not cross-referenced for efficiency purposes
CriminalRecord	varchar	30	Y		Criminal record
EmploymentTypeID	numeric	10	Y		Reference to type of employment – see relevant table for details
RemunerationSchemeID	numeric	10	Y		Remuneration scheme: paid by state or governing body
NextOfKinID	numeric	10	Y		Reference to next of kin details
StaffMedicalStatusID	numeric	10	Y		Reference to medical status information
MedicalAidID	numeric	10	Y		Reference to medical aid information
StaffDisabilityID	numeric	10	Y		Reference to disability

Constraints

Field Name	Constraint Type	Description
StaffMemberID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Person	PersonID	StaffMember	PersonID
School	SchoolID	StaffMember	SchoolID
EmploymentType	EmploymentTypeID	StaffMember	EmploymentTypeID
RemunerationScheme	RemunerationSchemeID	StaffMember	RenumerationSchemeID
StaffMember	StaffMemberID	Educator	StaffMemberID
StaffMember	StaffMemberID	AdminStaff	StaffMemberID
StaffMember	StaffMemberID	SupportStaff	StaffMemberID
StaffMember	StaffMemberID	StaffLeavePA	StaffMemberID
StaffMember	StaffMemberID	ExtraCurriculum	StaffMemberID
Person	PersonID	StaffMember	NextOfKinID
StaffMedicalStatus	StaffMedicalStatusID	StaffMember	StaffMedicalStatusID
MedicalAid	MedicalAidID	StaffMember	MedicalAidID
StaffDisability	StaffDisabilityID	StaffMember	StaffDisabilityID

Educator

Database	OSSMS
Table Description	Stores information specific to educators. Note: This table contains large amounts of EMIS-specific data. However since the majority of the fields are EMIS-specific, it was decided not to create a separate EducatorEMIS table. Future implementations of this table should completely redefine it according to the new requirements.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
EducatorID	Identity	10	N		Unique identifier
StaffMemberID	numeric	10	N		The staff member entity associated with this educator
SchoolRefNumber	varchar	30	Y		Unique school reference number
PersalNumber	varchar	30	Y		Number used to identify teachers paid by government
PostLevel	char	1	Y		See ASS2002 pg36
TeachingLevelID	numeric	10	Y		Reference to teaching level – see relevant table for details
PersonnelCategoryID	numeric	10	Y		Reference to personnel category – see relevant table for details
YearsTeaching	smallint		Y		Years of teaching experience
QualificationCategory	smallint		Y		Valid options are 10, 11, 12, 13, 14, 15, 16, 17
QualificationTypeID	numeric	10	Y		Reference to qualification type – see relevant table for details
LanguageProficiency	varchar	256	Y		String of language proficiency information
MortalityReasonID	numeric	10	Y		Reference to reason for mortality – NULL if not applicable
SexualHarrassment	varchar	256	Y		Details of sexual harrassment history, if applicable

Constraints

Field Name	Constraint Type	Description
EducatorID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
StaffMember	StaffMemberID	Educator	StaffMemberID
TeachingLevel	TeachingLevelID	Educator	TeachingLevelID
PersonnelCategory	PersonnelCategoryID	Educator	PersonnelCategoryID
QualificationType	QualificationTypeID	Educator	QualificationTypeID
MortalityReason	MortalityReasonID	Educator	MortalityReasonID
Educator	EducatorID	EducatorPA	EducatorPAID

EducatorPA

Database	OSSMS
Table Description	Stores general information pertaining to an educator in a specific registration year
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
EducatorPAID	Identity	10	N		Unique identifier
EducatorID	numeric	10	N		The educator corresponding to this entry
Year	char	4	N		The year to which this entry applies
ClassroomID	numeric	10	Y		Reference to educator’s classroom, if applicable

Constraints

Field Name	Constraint Type	Description
EducatorPAID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Educator	EducatorID	EducatorPA	EducatorPAID
EducatorPA	EducatorPAID	SubjectAllocation	EducatorPAID
Classroom	ClassroomID	EducatorPA	ClassroomID

AdminStaff

Database	OSSMS
Table Description	Stores information specific to administrative staff members
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
AdminStaffID	Identity	10	N		Unique identifier
StaffMemberID	numeric	10	N		The staff member entity associated with this support staff member
Responsibilities	varchar	256	Y		Job responsibilities
JobDescription	varchar	128	Y		Job description

Constraints

Field Name	Constraint Type	Description
AdminStaffID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
StaffMember	StaffMemberID	AdminStaff	StaffMemberID

SupportStaff

Database	OSSMS
Table Description	Stores information specific to support staff members
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SupportStaffID	Identity	10	N		Unique identifier
StaffMemberID	numeric	10	N		The staff member entity associated with this admin staff member
Responsibilities	varchar	256	Y		Job responsibilities
JobDescription	varchar	128	Y		Job description

Constraints

Field Name	Constraint Type	Description
SupportStaffID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
StaffMember	StaffMemberID	SupportStaff	StaffMemberID

Learner

Database	OSSMS
Table Description	Stores general information common to all learners.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LeanerID	Identity	10	N		Unique identifier
PersonID	numeric	10	N		The person entity associated with this learner
ReferenceNumber	varchar	16	Y		Unique reference string generated by the system when a new learner’s information is captured for the first time
PreferredName	varchar	30	Y		Learner’s preferred name
LearnerStatusID	numeric	10	Y		Current learner status – see relevant table for more details
SportsHouseID	numeric	10	Y		Reference to the sports house the learner belongs to
BirthCountry	varchar	64	Y		Country of birth
PositionInFamily	smallint		Y		Child’s position in family: first (1), second (2), etc.
Distance	smallint		Y		Distance from school, rounded up to the nearest integer
TransportMode	varchar	20	Y		Mode of transport to school
LearnerEnrollmentID	numeric	10	Y		Reference to learner enrollment information
LearnerMedicalStatusID	numeric	10	Y		Reference to medical information
MedicalAidID	numeric	10	Y		Reference to medical aid information
EmergencyContactID	numeric	10	Y		Reference to emergency contact information
MortalityReasonID	numeric	10	Y		Reference to reason for mortality – NULL if not applicable
MortalityYear	char	4	Y		Year deceased, if applicable
LearnerFeesPayerID	numeric	10	Y		Reference to details of person responsible for fees payment

Constraints

Field Name	Constraint Type	Description
LearnerID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Person	PersonID	Learner	PersonID
LearnerStatus	LearnerStatusID	Learner	LearnerStatusID
SportsHouse	SportsHouseID	Learner	SportsHouseID
Learner	LearnerID	LearnerPA	LearnerID
Learner	LearnerID	Guardian	LearnerID
LearnerEnrollment	LearnerEnrollmentID	Learner	LearnerEnrollmentID
LearnerMedicalStatus	LearnerMedicalStatusID	Learner	LearnerMedicalStatusID
MedicalAid	MedicalAidID	Learner	MedicalAidID
Learner	LearnerID	LearnerMerit	LearnerID
Learner	LearnerID	LearnerLeave	LearnerID
Person	PersonID	Learner	EmergencyContactID
MortalityReason	MortalityReasonID	LearnerEnrollment	MortalityReasonID
LearnerFeesPayer	LearnerFeesPayerID	Learner	LearnerFeesPayerID
Learner	LearnerID	PerformanceHistory	LearnerID

LearnerPA

Database	OSSMS
Table Description	Stores general information pertaining to a learner in a specific registration year
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LeanerPAID	Identity	10	N		Unique identifier
LearnerID	numeric	10	N		The learner corresponding to this entry
Year	char	4	N		The year to which this entry applies
GradePAID	numeric	10	N		Reference to the annual grade information for which the learner is registered for in this year
SchoolAftercare	boolean		Y		True if the learner attends school aftercare, false otherwise
YearPercentage	float		Y		Year percentage mark for learner as a value between 0 and 100 (optional)
YearSymbol	char	1	Y		The symbol associated with the year percentage mark
PerformanceIndicator	smallint		Y		OBE performance indicator. Valid values are 1,2,3,4
PromotionDecisionID	numeric	10	Y		Promotion decision based on the year’s performance
ReportComment	varchar	256	Y		Optional field that will appear on the report card, if completed
FeesExempt	boolean		Y		True if this learner is exempt from paying school fees for this year
FeesPayable	money		Y		If specified, overrides the default amount payable for the year in FeesStructurePA.Fee

Constraints

Field Name	Constraint Type	Description
LearnerPAID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Learner	LearnerID	LearnerPA	LearnerID
GradePA	GradePAID	LearnerPA	GradePAID
LearnerPA	LearnerPAID	LearnerSubject	LearnerPAID
PromotionDecision	PromotionDecisionID	LearnerPA	PromotionDecisionID
LearnerPA	LearnerPAID	TermPerformance	LearnerPAID
LearnerPA	LearnerPAID	FeesPayment	LearnerPAID
LearnerPA	LearnerPAID	LearnerRepCouncil	LearnerPAID
LearnerPA	LearnerPAID	LearnerExtraCurriculum	LearnerPAID
LearnerPA	LearnerPAID	LearnerIncident	LearnerPAID

Guardian

Database	OSSMS
Table Description	Stores general information about guardians of leaners
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
GuardianID	Identity	10	N		Unique identifier
PersonID	numeric	10	N		The person entity associated with this guardian
LearnerID	numeric	10	N		Learner corresponding to this guardian
GuardianTypeID	numeric	10	N		Reference describing the type of guardian
PrimaryContact	boolean		N		True if this guardian is a primary caretaker of the learner. (Note that both parents may be primary caretakers as long as the child lives with them both.)
Occupation	varchar	30	Y		Occupation of the guardian
Employer	varchar	30	Y		Employer name of the guardian
EmployerTelephoneID	numeric	10	Y		Reference to telephone number of the employer

Constraints

aField Name	Constraint Type	Description
GuardianID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Person	PersonID	Guardian	PersonID
Learner	LearnerID	Guardian	LearnerID
GuardianType	GuardianTypeID	Guardian	GuardianTypeID
Telephone	TelephoneID	Guardian	EmployerTelephoneID

LearnerEnrollment

Database	OSSMS
Table Description	Stores enrollment-specific information pertaining to a learner
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LeanerEnrollmentID	Identity	10	N		Unique identifier
EnrollmentDate	date		N		Date the learner first enrolled at the school
ApplicationStatusID	numeric	10	N		Reference to status of new application
PreviousSchoolInProvince	boolean		Y		True if the learner’s previous school is in the same province as this one; false if not and NULL if the learner was not in school in the previous year
PreviousSchoolName	varchar	64	Y		Name of school learner was transferred from, if applicable
PreviousSchoolAddressID	numeric	10	Y		Reference to address of learner’s previous school, if applicable
PreviousSchoolTelephoneID	numeric	10	Y		Reference to telephone number of learner’s previous school, if applicable
HighestGradePassedID	numeric	10	Y		Reference to highest grade passed at previous school, if applicable
DatePassed	date		Y		Date the highest grade was passed, if applicable
ReasonForLeaving	varchar	30	Y		Reason for leaving previous school, if applicable
DateLeft	date		Y		Date the previous school was left, if applicable
Reapplication	boolean		Y		True if the learner is applying to the school for the following year; false if the learner is not reapplying, and NULL if not applicable to the school
ReapplicationStatusID	numeric	10	Y		Reference to status of reapplication

Constraints

Field Name	Constraint Type	Description
LearnerEnrollmentID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearnerEnrollment	LearnerEnrollmentID	Learner	LearnerEnrollmentID
ApplicationStatus	ApplicationStatusID	LeanerEnrollment	ApplicationStatusID
Address	AddressID	LearnerEnrollment	PreviousSchoolAddressID
Telephone	TelephoneID	LearnerEnrollment	PreviousSchoolTelephoneID
GradeType	GradeTypeID	LearnerEnrollment	HighestGradePassedID
ApplicationStatus	ApplicationStatusID	LearnerEnrollment	ReapplicationStatusID

LearnerMedicalStatus

Database	OSSMS
Table Description	Stores medical status details pertaining to a learner
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerMedicalStatusID	Identity	10	N		Unique identifier
Allergies	varchar	64	Y		String listing allergies
Glasses	varchar	64	Y		Details of medical condition
Asthma	varchar	64	Y		Details of medical condition
BloodPressure	varchar	64	Y		Details of medical condition
HearingAid	varchar	64	Y		Details of medical condition
Prosthesis	varchar	64	Y		Details of medical condition
Diabetic	varchar	64	Y		Details of medical condition
ChronicIllness	varchar	64	Y		Details of medical condition
Measles	boolean		Y		True if the person has had measles
GermanMeasles	boolean		Y		True if the person has had German measles
Chickenpox	boolean		Y		True if the person has had chicken pox
Mumps	boolean		Y		True if the person has had mumps
ScarletFever	boolean		Y		True if the person has had scarlet fever
WhoopingCough	boolean		Y		True if the person has had whooping cough
Bilharzia	boolean		Y		True if the person has had bilharzia
Cholera	boolean		Y		True if the person has had cholera
TBImmuneDate	date		Y		Date of TB immunisation
PolioImmuneDate	date		Y		Date of Polio immunisation
DTImmuneDate	date		Y		Date of DT immunisation
DTPImmuneDate	date		Y		Date of DTP immunisation
MeaslesImmuneDate	date		Y		Date of measles immunisation
HIBImmuneDate	date		Y		Date of HIB immunisation
MumpsImmuneDate	date		Y		Date of mumps immunisation
RubellaImmuneDate	date		Y		Date of Rubella immunisation
Medication	varchar	30	Y		Medication the person is taking, if applicable
MedicationReason	varchar	30	Y		Reason for taking the medication, if applicable
EducationalDisabilityID	numeric	10	Y		Reference to educational disability that may result in special education needs – see relevant table for details

Constraints

Field Name	Constraint Type	Description
LearnerMedicalStatusID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearnerMedicalStatus	LearnerMedicalStatusID	Learner	LearnerMedicalStatusID
EducationalDisability	EducationalDisabilityID	LearnerMedicalStatus	EducationalDisabilityID

LearnerMerit

Database	OSSMS
Table Description	Stores learner merit and demerit information
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerMeritID	Identity	10	N		Unique identifier
LearnerID	numeric	10	N		The learner corresponding to this entry
Merit	boolean		N		True if this entry corresponds to a merit, and false if it corresponds to a demerit
EventDescription	varchar	30	Y		Description of the misconduct in the case of a demit, and of the cause in the case of a merit
ActionTaken	varchar	30	Y		A description of the action taken
Number	smallint		N		The number of merits or demerits awarded
EventDate	date		N		Date of the merit / demerit event

Constraints

Field Name	Constraint Type	Description
LearnerMeritID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Learner	LearnerID	LearnerMerit	LearnerID

LearnerLeave

Database	OSSMS
Table Description	Stores information related to learner’s days absent
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerLeaveID	Identity	10	N		Unique identifier
LearnerID	numeric	10	N		The learner corresponding to this entry
LeaveStart	date		N		First date learner was absent (inclusive)
LeaveEnd	date		N		Last date learner was absent (inclusive)
LeaveTypeID	numeric	10	N		Reference to the reason for the learner’s days absent
LeaveValidationTypeID	numeric	10	Y		Reference to method by which leave was explained, or received

Constraints

Field Name	Constraint Type	Description
LearnerLeaveID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Learner	LearnerID	LearnerLeave	LearnerID
LeaveType	LeaveTypeID	LearnerLeave	LeaveTypeID
LeaveValidationType	LeaveValidationTypeID	LearnerLeave	LeaveValidationTypeID

SchoolSubject

Database	OSSMS
Table Description	Stores the list of subjects offered by the school – chooses from the list of available subjects in the Subject table
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SchoolSubjectID	Identity	10	N		Unique identifier
SubjectID	numeric	10	N		Reference to the subject
SchoolID	numeric	10	N		Reference to the school that offers the subject

Constraints

Field Name	Constraint Type	Description
SchoolSubjectID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Subject	SubjectID	SchoolSubject	SubjectID
SchoolSubject	SchoolSubjectID	SubjectAllocation	SchoolSubjectID
School	SchoolID	SchoolSubject	SchoolID
SchoolSubject	SchoolSubjectID	SubjectLearningOutcome	SchoolSubjectID
SchoolSubject	SchoolSubjectID	SubjectPromotion	SchoolSubjectID

SubjectAllocation

Database	OSSMS
Table Description	Stores the subject allocation information, which allocates educators to subjects on an annual basis. This table joins information via the per annum tables, so that historical data remains available.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SubjectAllocationID	Identity	10	N		Unique identifier
SchoolSubjectID	numeric	10	N		Reference to a subject offered by the school
EducatorPAID	numeric	10	N		Reference to an educator in a particular year
ClassroomID	numeric	10	Y		Reference to the classroom in which the subject is taught, if applicable

Constraints

Field Name	Constraint Type	Description
SubjectAllocationID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SchoolSubject	SchoolSubjectID	SubjectAllocation	SchoolSubjectID
EducatorPA	EducatorPAID	SubjectAllocation	EducatorPAID
SubjectAllocation	SubjectAllocationID	LearnerSubject	SubjectAllocationID
Classroom	ClassroomID	SubjectAllocation	ClassroomID
SubjectAllocation	SubjectAllocationID	TimetablePeriod	SubjectAllocationID

LearnerSubject

Database	OSSMS
Table Description	Stores the subject list for a learner, i.e. specifies the subjects (and the corresponding educator) that the learner is taking in a particular year. This table joins information via the per annum tables, so that historical data remains available.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerSubjectID	Identity	10	N		Unique identifier
SubjectAllocationID	numeric	10	N		Reference to a subject given by an educator in a specific year
LearnerPAID	numeric	10	N		Reference to a learner in a particular year
SubjectStatusID	numeric	10	N		Reference to the subject grade that the learner is taking the subject at, i.e. HG, LG, etc.

Constraints

Field Name	Constraint Type	Description
LearnerSubjectID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SubjectAllocation	SubjectAllocationID	LearnerSubject	SubjectAllocationID
LearnerPA	LearnerPAID	LearnerSubject	LearnerPAID
LearnerSubject	LearnerSubjectID	SubjectPerformance	LearnerSubjectID
SubjectStatus	SubjectStatusID	LearnerSubject	SubjectStatusID

TermPerformance

Database	OSSMS
Table Description	Stores term summary information for a learner.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
TermPerformanceID	Identity	10	N		Unique identifier
LearnerPAID	numeric	10	N		The annual learner data corresponding to this entry
TermNumber	smallint		N		The number of the term in the year, starting from 1
TermPercentage	float		Y		Term percentage mark for learner as a value between 0 and 100 (optional)
TermSymbol	char	1	Y		The symbol associated with the term percentage mark
PerformanceIndicator	smallint		Y		OBE term performance indicator (optional). Valid values are 1,2,3,4
PromotionDecisionID	numeric	10	Y		Promotion decision based on this term’s performance
ReportComment	varchar	256	Y		Optional field that will appear on the report card, if completed

Constraints

Field Name	Constraint Type	Description
TermPerformanceID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearnerPA	LearnerPAID	TermPerformance	LearnerPAID
PromotionDecision	PromotionDecisionID	TermPerformance	PromotionDecisionID
TermPerformance	TermPerformanceID	SubjectPerformance	TermPerformanceID

SubjectPerformance

Database	OSSMS
Table Description	Stores the performance of the learner for a particular subject in a particular term.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SubjectPerformanceID	Identity	10	N		Unique identifier
TermPerformanceID	numeric	10	N		Reference to the term performance summary to which this subject applies
LearnerSubjectID	numeric	10	N		Reference to the learner’s subject to which this entry applies
SubjectPercentage	float		Y		Subject percentage mark for learner as a value between 0 and 100 (optional)
SubjectSymbol	char	1	Y		The symbol associated with the subject percentage mark
PerformanceIndicator	smallint		Y		OBE term performance indicator (optional). Valid values are 1,2,3,4
ReportComment	varchar	256	Y		Optional field that will appear on the report card, if completed

Constraints

Field Name	Constraint Type	Description
SubjectPerformanceID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
TermPerformance	TermPerformanceID	SubjectPerformance	TermPerformanceID
LearnerSubject	LearnerSubjectID	SubjectPerformance	LearnerSubjectID
SubjectPerformance	SubjectPerformanceID	SubjectOutcomePerformance	SubjectPerformanceID
SubjectPerformance	SubjectPerformanceID	SubjectTaskPerformance	SubjectPerformanceID

SubjectOutcomePerformance

Database	OSSMS
Table Description	Stores the performance of the learner for a particular subject in a particular term on a per-outcome level.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SubjectOutcomePerformanceID	Identity	10	N		Unique identifier
SubjectPeformanceID	numeric	10	N		Subject performance data to which this outcome-based performance entry applies
LearningOutcomeID	numeric	10	N		Reference to the learning outcome to which this entry applies
PerformanceIndicator	smallint		N		OBE term performance indicator. Valid values are 1,2,3,4
ReportComment	varchar	256	Y		Optional field that will appear on the report card, if completed

Constraints

Field Name	Constraint Type	Description
SubjectOutcomePerformanceID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SubjectPerformance	SubjectPerformanceID	SubjectOutcomePerformance	SubjectPerformanceID
LearningOutcome	LearningOutcomeID	SubjectOutcomePerformance	LearningOutcomeID

SubjectTaskPerformance

Database	OSSMS
Table Description	Stores the performance of the learner for a particular subject in a particular term on a per-task level. i.e. Stores a list of tasks and their outcomes for a particular subject for a learner.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SubjectTaskPerformanceID	Identity	10	N		Unique identifier
SubjectPeformanceID	numeric	10	N		Subject performance data to which this task-based performance entry applies
LearningOutcomeID	numeric	10	Y		Reference to the learning outcome to which this entry applies
TaskDescription	varchar	30	Y		Description of the task (optional)
MarksAvailable	float		Y		Total marks available for this task (optional)
MarksAchieved	float		Y		Marks achieved for this task by the learner (optional)
Weighting	float		Y		A floating point number between 0 and 1 indicating the contribution of this task outcome towards the overall term mark (optional)
PerformanceIndicator	smallint		Y		OBE term performance indicator (optional). Valid values are 1,2,3,4
Comment	varchar	64	Y		Optional comment about the learner’s performance on the task. The comment does not appear on the report card.
TaskDate	date		Y		Date the task was carried out

Constraints

Field Name	Constraint Type	Description
SubjectTaskPerformanceID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SubjectPerformance	SubjectPerformanceID	SubjectTaskPerformance	SubjectPerformanceID
LearningOutcome	LearningOutcomeID	SubjectTaskPerformance	LearningOutcomeID

FeesPayment

Database	OSSMS
Table Description	Stores details about fee payments made by learners in a particular year. An entry should be inserted in this table every time a payment is made.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
FeesPaymentID	Identity	10	N		Unique identifier
LearnerPAID	numeric	10	N		Reference to the learner in a specific year to which this payment applies
Date	date		N		Date the payment was made
Amount	money		N		Payment amount
PaymentMethod	varchar	30	Y		Payment method (cash, cheque, credit card, etc.)

Constraints

Field Name	Constraint Type	Description
FeesPaymentID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearnerPA	LearnerPAID	FeesPayment	LearnerPAID

LearnerRepCouncil

Database	OSSMS
Table Description	Stores details about the learner representative council. Each entry refers to a learner in a specific year, so historical data is maintained. This also requires this table to be updated annually with current data.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerRepCouncilID	Identity	10	N		Unique identifier
LearnerPAID	numeric	10	N		Reference to the learner in a specific year to which this entry applies
NumVotes	smallint		Y		The number of votes this representative received
Role	varchar	128	Y		Description of the roles and responsibilities of this representative on the council

Constraints

Field Name	Constraint Type	Description
LearnerRepCouncilID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearnerPA	LearnerPAID	LearnerRepCouncil	LearnerPAID

StaffLeavePA

Database	OSSMS
Table Description	Stores details regarding annual staff leave.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
StaffLeavePAID	Identity	10	N		Unique identifier
StaffMemberID	numeric	10	N		Reference to staff member to which this entry corresponds
Year	char	4	N		Year in which this entry applies
NumLeaveDays	smallint		N	0	Total number of annual leave days due for the year
NumSickLeaveDays	smallint		N	0	Total number of sick leave days due for the year
NumOtherLeaveDays	smallint		Y		Total number of other / special leave days due for the year

Constraints

Field Name	Constraint Type	Description
StaffLeavePAID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
StaffMember	StaffMemberID	StaffLeavePA	StaffMemberID
StaffLeavePA	StaffLeavePAID	StaffLeave	StaffLeavePAID

StaffLeave

Database	OSSMS
Table Description	Stores details regarding actual staff leave taken.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
StaffLeaveID	Identity	10	N		Unique identifier
StaffLeavePAID	numeric	10	N		Reference to staff member leave info (could have referenced StaffMember instead, but this directly links up leave taken to the leave information in the year in which it applies)
LeaveStart	date		N		First date of leave taken (inclusive)
LeaveEnd	date		N		Last date of leave taken (inclusive)
LeaveTypeID	numeric	10	N		Reference to the reason for leave
LeaveValidationTypeID	numeric	10	Y		Reference to the method by which leave was explained, if applicable
Approved	boolean		Y		True if the leave was approved, false otherwise

Constraints

Field Name	Constraint Type	Description
StaffLeaveID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
StaffLeavePA	StaffLeavePAID	StaffLeave	StaffLeavePAID

TimetablePattern

Database	OSSMS
Table Description	Defines a timetable framework, which is adopted by particular grades.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
TimetablePatternID	Identity	10	N		Unique identifier
CycleLength	smallint		N		Number of days in a timetable cycle
PeriodsPerDay	smallint		Y		Number of periods in a day
PeriodLength	smallint		Y		Number of minutes per period

Constraints

Field Name	Constraint Type	Description
TimetablePatternID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
TimetablePattern	TimetablePatternID	GradePA	TimetablePatternID

TimetableDay

Database	OSSMS
Table Description	Stores information corresponding to a single timetable day.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
TimetableDayID	Identity	10	N		Unique identifier
GradePAID	numeric	10	N		Reference to the grade to which this entry applies
DayNumber	smallint		N		Number of the day in the cycle, starting from 1
NumPeriods	smallint		Y		Number of periods in this day. If not null, overrides the value of TimetablePattern.PeriodsPerDay

Constraints

Field Name	Constraint Type	Description
TimetableDayID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
TimetableDay	TimetableDayID	TimetablePeriod	TimetableDayID
GradePA	GradePAID	TimetableDay	GradePAID

TimetablePeriod

Database	OSSMS
Table Description	Stores information corresponding to a single timetable period.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
TimetablePeriodID	Identity	10	N		Unique identifier
TimetableDayID	numeric	10	N		Reference to the timetable day in which this period belongs
PeriodNumber	smallint		N		Number of the period in the day, starting from 1
PeriodLength	smallint		Y		Number of minutes in this period. If not null, overrides the value in TimetablePattern.PeriodLength
SubjectAllocationID	numeric	10	N		Reference to the subject allocation information for this entry, which defines the subject and educator
ClassroomID	numeric	10	Y		Reference to the classroom in which this lesson occurs. If not null, overrides the value in SubjectAllocation.ClassroomID

Constraints

Field Name	Constraint Type	Description
TimetablePeriodID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
TimetableDay	TimetableDayID	TimetablePeriod	TimetableDayID
SubjectAllocation	SubjectAllocationID	TimetablePeriod	SubjectAllocationID
Classroom	ClassroomID	TimetablePeriod	ClassroomID

SGBDetails

Database	OSSMS
Table Description	Stores information corresponding to the school governing body committee.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SGBDetailsPAID	Identity	10	N		Unique identifier
YearStarted	char	4	Y		Year in which the committee was started
DateLastElection	date		N		Date of the last election for committee members - needed to calculate which members in SGBMember are current
RoleDescription	varchar	255	Y		Description of the roles, duties and/or functions of the SGB

Constraints

Field Name	Constraint Type	Description
SGBDetailsID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SGBDetails	SGBDetailsID	School	SGBDetailsID
SGBDetails	SGBDetailsID	SGBMemberPA	SGBDetailsID
SGBDetails	SGBDetailsID	SGBMeeting	SGBDetailsID

SGBMemberPA

Database	OSSMS
Table Description	Stores information corresponding to a school governing body committee member.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SGBMemberID	Identity	10	N		Unique identifier
SGBDetailsID	numeric	10	N		Reference to the SGB committee to which this member belongs
Year	char	4	N		Year in which this entry applies
PersonID	numeric	10	N		Reference to person details corresponding to the member
NumVotes	smallint		Y		Number of votes this member received
RoleDescription	varchar	60	Y		Description of this member's roles, duties and/or functions on the SGB

Constraints

Field Name	Constraint Type	Description
SGBMemberPAID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SGBDetails	SGBDetailsID	SGBMemberPA	SGBDetailsID

SGBMeeting

Database	OSSMS
Table Description	Stores information corresponding to a school governing body committee meeting.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SGBMeetingID	Identity	10	N		Unique identifier
SGBDetailsID	numeric	10	N		Reference to the SGB committee to which this meeting corresponds
DateTime	datetime		N		Date and time of meeting
Venue	varchar	30	N		Meeting venue or location
Description	varchar	60	Y		Description of the purpose and/or outcomes of the meeting
Minutes	blob		Y		Optional binary attachment containing minutes of the meeting (expecting arbitrary external document format)

Constraints

Field Name	Constraint Type	Description
SGBMeetingID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SGBDetails	SGBDetailsID	SGBMeeting	SGBDetailsID

SchoolPlatoon

Database	OSSMS
Table Description	Stores information corresponding to the school platooning details.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SchoolPlatoonID	Identity	10	N		Unique identifier
HostSchoolName	varchar	30	N		Name of host school
DoubleShifts	boolean		Y		True if double shifts occur, false otherwise
Details	varchar	256	Y		Description of platooning activities

Constraints

Field Name	Constraint Type	Description
SchoolPlatoonID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SchoolPlatoon	SchoolPlatoonID	School	SchoolPlatoonID

SchoolStructure

Database	OSSMS
Table Description	Stores information corresponding to the school physical structure.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SchoolStructureID	Identity	10	N		Unique identifier
SchoolMap	blob		Y		Binary field storing map of school in external format (optional)
EmergencyProcedures	text		Y		Text field storing details of emergency procedures
NumUrinals	smallint		Y		Number of learner urinals
NumSeatsM	smallint		Y		Number of male learner seats
NumSeatsF	smallint		Y		Number of femal learner seats
NumStaffUrinals	smallint		Y		Number of staff urinals
NumStaffSeatsM	smallint		Y		Number of male staff seats
NumStaffSeatsF	smallint		Y		Number of female staff seats
NumDisabledSeatsM	smallint		Y		Number of male disabled seats (staff and learners)
NumDisabledSeatsF	smallint		Y		Number of female disabled seats (staff and learners)

Constraints

Field Name	Constraint Type	Description
SchoolStructureID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SchoolStructure	SchoolStructureID	School	SchoolStructureID

ExtraCurriculum

Database	OSSMS
Table Description	Stores extra-curricular activity information on a per-annum per-grade basis, so must be updated annually. (Note that the table is not named using the PA convention since it does not store the year itself, but references the year from the GradePA table.)
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
ExtraCurriculumID	Identity	10	N		Unique identifier
ECActivityID	numeric	10	N		Reference to the extra-curricular activity to which this entry applies
GradePAID	numeric	10	N		Reference to the grade and corresponding year to which this entry applies
StaffMemberID	numeric	10	Y		Reference to the staff member responsible for the administration, if applicable
PersonID	numeric	10	Y		Reference to the person responsible for the administration, if applicable (and is not a staff member)

Constraints

Field Name	Constraint Type	Description
ExtraCurriculumID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
ECActivity	ECActivityID	ExtraCurriculum	ECActivityID
GradePA	GradePAID	ExtraCurriculum	GradePAID
StaffMember	StaffMemberID	ExtraCurriculum	StaffMemberID
Person	PersonID	ExtraCurriculum	PersonID
ExtraCurriculum	ExtraCurriculumID	LearnerExtraCurriculum	ExtraCurriculumID

Fundraising

Database	OSSMS
Table Description	Stores information corresponding to the school's fundraising activities.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
FundraisingID	Identity	10	N		Unique identifier
SchoolID	numeric	10	N		Reference to school to which this activity corresponds
ActivityTitle	varchar	60	N		Title of fundraising activity
Description	varchar	256	Y		Description of fundraising activity
Date	date		Y		Date activity took place
PersonsResponsible	varchar	256	Y		Text field detailing the persons responsible for the activity
MoneyRaised	money		Y		Money raised in the event
Profit	money		Y		Profit made
Expenses	money		Y		Expenses incurred
Goal	varchar	60	Y		Free-form field for goal description

Constraints

Field Name	Constraint Type	Description
FundraisingID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
School	SchoolID	Fundraising	SchoolID

Newsletter

Database	OSSMS
Table Description	Stores information corresponding to the school's newletter publishing.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
NewsletterID	Identity	10	N		Unique identifier
SchoolID	numeric	10	N		Reference to school to which this newsletter corresponds
Date	date		N		Date of newsletter publication
Document	blob		Y		Binary data allowing the newsletter to be stored (in an external format)

Constraints

Field Name	Constraint Type	Description
NewsletterID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
School	SchoolID	Newsletter	SchoolID

StaffMedicalStatus

Database	OSSMS
Table Description	Stores information corresponding to staff members' medical status.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
StaffMedicalStatusID	Identity	10	N		Unique identifier
ChronicIllness	varchar	30	Y		Description of chronic illness
DoctorID	numeric	10	Y		Reference to personal and contact details of doctor

Constraints

Field Name	Constraint Type	Description
StaffMedicalStatusID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
StaffMedicalStatus	StaffMedicalStatusID	StaffMember	StaffMedicalStatusID
Person	PersonID	StaffMedicalStatus	DoctorID

LearnerExtraCurriculum

Database	OSSMS
Table Description	Stores extra-curricular activity information taken by each learner. (Note that this table references the ExtraCurriculum table which is a per-annum table, so it is not strictly necessary to reference LearnerPA rather than Learner, but this is done for consistency.)
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerExtraCurriculumID	Identity	10	N		Unique identifier
ExtraCurriculumID	numeric	10	N		Reference to the specific per-annum extra-curricular activity to which this entry applies
LearnerPAID	numeric	10	N		Reference to the learner to which this entry applies

Constraints

Field Name	Constraint Type	Description
LearnerExtraCurriculumID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
ExtraCurriculum	ExtraCurriculumID	LearnerExtraCurriculum	ExtraCurriculumID
LearnerPA	LearnerPAID	LearnerExtraCurriculum	LearnerPAID
LearnerExtraCurriculum	LearnerExtraCurriculumID	LearnerECAchievement	LearnerExtraCurriculumID

LearnerECAchievement

Database	OSSMS
Table Description	Stores learner achievements corresponding to their extra-curricular activities.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerECAchievementID	Identity	10	N		Unique identifier
LearnerExtraCurriculumID	numeric	10	N		Reference to learner's extra curriculum activity to which this entry applies
Date	date		N		Date of achievement
Description	varchar	60	N		Details of achievement

Constraints

Field Name	Constraint Type	Description
LearnerECAchievementID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearnerExtraCurriculum	LearnerExtraCurriculumID	LearnerECAchievement	LearnerExtraCurriculumID

LearnerIncident

Database	OSSMS
Table Description	Stores incidents corresponding to a learner.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerIncidentID	Identity	10	N		Unique identifier
LearnerPAID	numeric	10	N		Reference to learner in a particular year in which this incident occurred
Description	varchar	60	Y		Description of incident
IncidentTypeID	numeric	10	N		Reference to incident type

Constraints

Field Name	Constraint Type	Description
LearnerECAchievementID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
LearnerPA	LearnerPAID	LearnerIncident	LearnerPAID
IncidentType	IncidentTypeID	LearnerIncident	IncidentTypeID

LearnerFeesPayer

Database	OSSMS
Table Description	Stores details about the person responsible for fees payment for a particular learner. (Note that this information is not stored on a per-annum basis - please confirm.)
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
LearnerFeesPayerID	Identity	10	N		Unique identifier
PersonID	numeric	10	N		Reference to personal and contact details of payer
Occupation	varchar	30	Y		Occupation of person
Employer	varchar	30	Y		Employer name of person
EmployerTelephoneID	numeric	10	Y		Reference to telephone number of the employer
PaymentCycleID	numeric	10	Y		Reference to payment cycle

Constraints

Field Name	Constraint Type	Description
LearnerFeesPayerID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Person	PersonID	LearnerFeesPayer	PersonID
Telephone	TelephoneID	LearnerFeesPayer	EmployerTelephoneID
LearnerFeesPayer	LearnerFeesPayerID	Learner	LearnerFeesPayerID
PaymentCycle	PaymentCycleID	LearnerFeesPayer	PaymentCycleID

GlobalConfiguration

Database	OSSMS
Table Description	Stores global configuration information
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
GlobalConfigurationID	Identity	10	N		Unique identifier
Key	varchar	64	N		Name of the configuration parameter
Value	varchar	2048	N		Value of the configuration parameter

Constraints

Field Name	Constraint Type	Description
GlobalConfigurationID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name

SubjectLearningOutcome

Database	OSSMS
Table Description	Stores the specific learning outcomes that the school is evaluating, out of the list of available learning outcomes
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SubjectLearningOutcomeID	Identity	10	N		Unique identifier
SchoolSubjectID	numeric	10	N		Reference to the subject taught at the school to which this learning outcome refers
LearningOutcomeID	numeric	10	N		Reference to the learning outcome to which this entry refers

Constraints

Field Name	Constraint Type	Description
SubjectLearningOutcomeID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SchoolSubject	SchoolSubjectID	SubjectLearningOutcome	SchoolSubjectID
LearningOutcome	LearningOutcomeID	SubjectLearningOutcome	LearningOutcomeID

SubjectPromotion

Database	OSSMS
Table Description	Stores the OBE-based promotion requirements and level indicator conversion rules for a specific school subject
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SubjectPromotionID	Identity	10	N		Unique identifier
SchoolSubjectID	numeric	10	N		Reference to the subject taught at the school to which entry applies
PromotionCriteria	smallint		Y		Minimum performance indicator required to pass this subject (valid values are 1,2,3 and 4)
Level1UpperBound	smallint		Y		Upper bound of the percentage range which corresponds to a performance indicator of 1
Level2UpperBound	smallint		Y		As above, for performance indicator of 2
Level3UpperBound	smallint		Y		As above, for performance indicator of 3

Constraints

Field Name	Constraint Type	Description
SubjectPromotionID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
SchoolSubject	SchoolSubjectID	SubjectPromotion	SchoolSubjectID

SchoolPromotion

Database	OSSMS
Table Description	Stores global OBE-based promotion requirements and level indicator conversion rules. Note that specific entries for subjects in SubjectPromotion take precedence over values in this table.
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
SchoolPromotionID	Identity	10	N		Unique identifier
SchoolID	numeric	10	N		Reference to the school to which this entry applies
PromotionCriteria	smallint		Y		Minimum performance indicator required to pass this subject (valid values are 1,2,3 and 4)
Level1UpperBound	smallint		Y		Upper bound of the percentage range which corresponds to a performance indicator of 1
Level2UpperBound	smallint		Y		As above, for performance indicator of 2
Level3UpperBound	smallint		Y		As above, for performance indicator of 3

Constraints

Field Name	Constraint Type	Description
SchoolPromotionID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
School	SchoolID	SchoolPromotion	SchoolID

PerformanceHistory

Database	OSSMS
Table Description	Stores annual performance information for learners prior to their registration at this school
Type of Data	Read-Write

Field Definitions

Field Name	Data type	Length	Nulls	Defaults	Description
PerformanceHistoryID	Identity	10	N		Unique identifier
LearnerID	numeric	10	N		Reference to learner to which this entry applies
Year	char	4	N		Year to which this entry applies
GradeTypeID	numeric	10	N		Reference to grade to which this entry applies
YearPercentage	float		Y		Year percentage mark for learner as a value between 0 and 100
YearSymbol	char	1	Y		The symbol associated with the year percentage mark
PerformanceIndicator	smallint		Y		OBE performance indicator. Valid values are 1,2,3 and 4
PromotionDecisionID	numeric	10	Y		Promotion decision of learner during this year

Constraints

Field Name	Constraint Type	Description
PerformanceID	Primary Key

Relationships

Primary Key Table	Field Name	Foreign Key Table	Foreign Key Field Name
Learner	LearnerID	PerformanceHistory	LearnerID
GradeType	GradeTypeID	PerformanceHistory	GradeTypeID
PromotionDecision	PromotionDecisionID	PerformanceHistory	PromotionDecisionID