SUMS Data Dictionary

History:

First version created by Claire Reed (Autumn 2004)

Comments added by Jim Briggs (Autumn 2004 and Summer 2005)

Second version by Steve Powell (Summer 2005)

Table Name:  AUDIT

Description:  Holds project audit information, records date of any change together with a reason

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Audit_ID

Y

Number

  

Primary Key

Person_Making_Change_ID

Y

Number

  

Foreign Key

- To PERSON.Person_ID

Project_Being_Changed_ID

Y

Number

  

Foreign Key

- To FINAL_PROJECT.

Project_ID

Reason

Y

Varchar2

256

  

Date_Altered

Y

Date/Time

    

Item_changed[JSB1] 

Y

Varchar2

128

  

Old_value_of_item

Y

CLOB

    

New_value_of_item

Y

CLOB

    

[SJP2] Table Name:  COHORT

Description:  Holds cohort information, project unit and project start/end date

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Cohort_ID

Y

Number

  

Primary Key

Cohort_Description

Y

Varchar2

128

  

Unit_ID

Y

Number

  

Foreign Key

– To UNIT.Unit_ID

Project_Start_Date

Y

Date

    

Project_End_Date

Y

Date

    

Table Name:  COHORT_COORDINATORS

Description:  Associates staff in the capacity of coordinator with cohorts

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Co_Cord_ID

Y

Number

  

Primary Key

Cohort_ID

Y

Number

  

Foreign Key

– To COHORT.Cohort_ID

Person_ID

Y

Number

  

Foreign Key

- To PERSON.Person_ID

Table Name:  FINAL_PROJECT

Description:  Holds details of a student's project

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Project_ID

Y

Number

  

Primary Key

Student_ID

Y

Number

  

Foreign Key

– To STUDENT.Student_ID

Cohort_ID

Y

Number

  

Foreign Key

– To COHORT.Cohort_ID

Unit_ID

Y

Number

  

Foreign Key

- To UNIT.Unit_ID

Project_Title

Y

Varchar2

50

  

Project_Submitted_Date

Y

Date

    

Late_Hand_In

N

Char

1

Y = ‘Yes’

Unit_Exam_Board_Decision

Y

Date

    

Final_Mark

Y

Number

3

  

Project_Comments

N

CLOB

    

Project_Status

Y

Varchar2

3

Current status of project in the marking process

[SJP3] Table Name:  MARKER_CAPACITY

Description:  Holds details of supervisor(s) of a project

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Marker_Capacity_ID

Y

Number

  

Primary Key

Project_ID

Y

Number

  

Foreign Key

– To FINAL_PROJECT.Project_ID

Marker_Type_ID

Y

Number

  

Foreign Key

- To MARKER_TYPE.Marker_Type_ID

Person_ID

Y

Number

  

Foreign Key

- To PERSON.Person_ID

Allocated_Date

Y

Date

    

Unallocated_ Date

N

Date

    

Table Name:  MARKER_TYPE

Description: Holds the different types of marker that could mark a project.

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Marker_Type_ID

Y

Number

  

Primary Key

Marker_Description

Y

Varchar2

64

  

Table Name:  MARKS

Description:  Holds marking information

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Mark_ID

Y

Number

  

Primary Key

Person_ID

(Staff)

Y

Number

  

Foreign Key

– To PERSON.Person_ID

Project_ID

Y

Number

  

Foreign Key

– To PROJECT.Project_ID

Marker_Capacity_ID

Y

Number

  

Foreign Key

- To MARKER_CAPACITY.

Marker_Capacity_ID[SJP4] 

Mark

Y

Number

3

  

Adjustment_Applied

Y

Number

    

Mark­_Date

Y

Date

    

General_Comments

Y

CLOB

    

Comments_For_Examiners

N

CLOB

    

General_Comments_ExamBoard

N

CLOB

    

Plagiarism_Unfair_Act

N

Char

1

Y = ‘Yes’

Plagiarism_Comments

N

CLOB

    

Unfair_Act_Notes

N

CLOB

    

Table Name: PRIZE_CATEGORIES

Description: Holds information about possible prize categories

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Prize_Cat_ID

Y

Number

  

Primary Key

Prize_Cat_Name

Y

Varchar2

16

  

Prize_Value

N

Number

    

Prize_Cat_Criteria

N

Varchar2

1024

Optional notes about prize background and project characteristics which would indicate eligibility.

Table Name: PRIZE_NOMINATIONS

Description: Holds information about prize nominations

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Prize_Nom_ID

Y

Number

  

Primary Key

Prize_Cat_ID

Y

Number

  

Foreign Key

– To PRIZE_CATEGORIES.Prize_Cat_ID

Mark_ID

Y

Number

  

Foreign Key

– To MARKS.Mark_ID

Supporting comments

N

Varchar2

1024

  

Table Name:  PERSON

Description: Holds information about an individual person

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Person_ID

Y

Number

  

Primary Key

User_Name

Y

Varchar2

16

  

Password

Y

Varchar2

16

  

First_Name

Y

Varchar2

64

  

Surname

Y

Varchar2

64

  

Table Name:  PERSON_ROLES

Description: Holds E-mail addresses for an individual person

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Role

Y

Number

  

Foreign Key - To ROLE.Role_ID

Person_ID

Y

Number

  

Foreign Key - To PERSON.Person_ID

[SJP5] Table Name:  PERSON_EMAIL

Description: Holds E-mail addresses for an individual person

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

E_Mail_ID

Y

Number

  

Primary Key

Person_ID

Y

Number

  

Foreign Key - To PERSON.Person_ID

E_Mail_Add

Y

Varchar2

128

  

E_Mail_Suspended

N

Char

1

Y = ‘Yes’

Table Name:  ROLES

Description:  Holds person role information

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Role_ID

Y

Number

  

Primary Key

Role_Description

Y

Varchar2

32

  

Table Name:  STAFF

Description: Holds university staff specific details

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Staff_No

Y

Varchar2

16

Primary Key

Person_ID

Y

Number

  

Foreign Key - To PERSON.Person_ID

Table Name:  STUDENT

Description: Holds student specific details

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Student_ID

Y

Number

  

Primary Key

Hemis_No

Y

Varchar2

16

  

Person_ID

Y

Number

  

Foreign Key - To PERSON.Person_ID

Degree_Stream

Y

Varchar2

16

  

Final_Year

Y

Varchar2

16

i.e.2004/2005[JSB6] 

Table Name:  TEL_NUMBER

Description:  Holds person telephone numbers

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Telephone_Number_ID

Y

Number

  

Primary Key

Person_ID

Y

Number

  

Foreign Key – To PERSON.Person_ID

Tel_Number

Y

Varchar2

20

  

Table Name:  UNIT

Description:             Holds project unit codes

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Unit_ID

Y

Number

  

Primary Key

Unit_Code

Y

Varchar2

16

  

Unit_Name

Y

      

Academic year first run

Y

      

Academic year last run

N

      

Table Name:  MARK_FORMS[SJP7] 

Description:  Holds the collection of mark forms for various projects

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

FormID

Y

Number

  

Primary Key

FormName

Y

Varchar2

255

  

FormBegin

N

Timestamp

16

  

FormEnd

N

Timestamp

    

Table Name:  MARK_CATEGORIES

Description:  Holds the collection of mark categories

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

CatID

Y

Number

  

Primary Key

CatName

Y

Varchar2

30

  

CatLongName

Y

Varchar2

100

  

CatDescription

Y

CLOB

    

CatBegin

Y

Timestamp

    

CatEnd

N

Timestamp

    

COG_ID

Y

Number

  

Foreign Key – To CATEGORY_OPTIONS_GROUP.COG_ID

Option_Type

Y

Char

1

R – Radio

S – Select

C - Checkbox

Table Name:  CATEGORY_OPTIONS_GROUP

Description:  Associates a set of mark options as a group

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

COG_ID

Y

Number

  

Primary Key

COG_Desc

N

Varchar2

100

  

Table Name:  CATEGORY_OPTIONS

Description:             Holds marking options which can be grouped and associated with a category

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

CO_ID

Y

Number

  

Primary Key

COG_ID

Y

    

Foreign Key – To CATEGORY_OPTIONS_GROUP.COG_ID

CO_Index

Y

Number

    

CO_Label

Y

Varchar

30

  

CO_Selected_Value

Y

Number

    

Table Name:  OPTION_CRITERIA

Description:  Holds the collection of category option criterion

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Op_Cri_ID

Y

Number

  

Primary Key

Option

Y

Number

  

Foreign Key – To CATEGORY_OPTIONS.CO_ID

Mark_Category

Y

Number

  

Foreign Key – To MARK_CATEGORIES.CatID

Op_Cri_Positive

N

Varchar2

512

(stored as list html)

Op_Cri_Negative

N

Varchar2

512

(stored as list html)

Table Name:  MARK_FORM_CATEGORIES

Description:  Associates a number of mark categories with a form

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

MFCID

Y

Number

  

Primary Key

FormID

Y

Number

  

Foreign Key - To MARK_FORMS.FormID

CatID

Y

Number

  

Foreign Key - To MARK_CATEGORIES.CatID

CatIndex

N

Number

    

CatWeight

N

Number

    

MFCBegin

N

Timestamp

    

MFCEnd

N

Timestamp

    

Optional

N

Char

1[SJP8] 

  

Table Name:  CATEGORY_MARKS

Description:             Records the individual category marks assigned during the calculation of a project mark.

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Cat_Mark_ID

Y

Number

  

Primary Key

Mark_ID

Y

Number

  

Foreign Key - To MARKS.Mark_ID

MFCID

Y

Number

  

Foreign Key - To MARK_FORM_CATEGORIES.MFCID

Mark

Y

Number

    

Comment

N

CLOB

    

[SJP9] Table Name:  OPTION_MARKS

Description:             Records the options selected for a particular category during the calculation of a project mark.

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

Opt_Mark_ID

Y

Number

  

Primary Key

Mark_ID

Y

Number

  

Foreign Key - To MARKS.Mark_ID

MFCID

Y

Number

  

Foreign Key - To MARK_FORM_CATEGORIES.MFCID

CO_ID

Y

Number

  

Foreign Key - To CATEGORY_OPTIONS.CO_ID

Table Name: UNIT_MARK_FORMS

Description: Used to associate a marking form with a Unit. 

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

UMFID

Y

Number

  

Primary Key

Unit_ID

Y

Number

  

Foreign Key

– To UNIT.Unit_ID

FormID

Y

Number

  

Foreign Key - To MARK_FORMS.FormID

UMFBegin

Y

Timestamp

    

UMFEnd

N

Timestamp

    

Table Name: STATUSES

Description: Provides a text description to the project status codes. 

Attributes

Compulsory

Field

Field Type

Field Length

Additional Information

STATUS_CODE

Y

Varchar

3

  

STATUS

N

Varchar

100

Description of project status

Page: 1
 [JSB1]We need to know what changed in the record, and from what to what.

 [SJP2]No longer required (see tables listed from current implementation)

 [SJP3]No longer required (see tables listed from current implementation)

 [SJP4]This field should either be removed or made optional.  Not all marks will be entered by a designated marker, some might be entered by the Unit Cohort Coordinator for example.  Therefore relying on the “Person” relationship seems more appropriate.

 [SJP5]Added in response to comment JSB7

 [JSB6]Is this different from their cohort or their expected end date?

 [SJP7]

The following three tables exist in the current implementation of the SUMS database and supersede those mentioned above relating to mark categories and weightings.

 [SJP8]New field to allow categories to be made optional,

 [SJP9]Database did not provide any means of recording category marks / comments.  This table provides a means of storage.