Tafe Buddy System Database Documentation

Download

Download latest version of the Tafe Buddy database set-up scripts and documentation..

The archive is also mirrored on the TafeSA OneDrive.

Within the archive you will find the schema*.sql file that creates the MySQL database structure which is documented below. The example*.sql file shows in what order (order is important due to the foreign key constraints) the data needs to be entered. The entity relationship diagram (ERD) is provided in different formats. A browsable version of the ERD can be found at the bottom of this page.

Table descriptions

campus

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
CampusCode 3 letter campus code 'ADL' CHAR(3) No PK(1)
CampusName name of the campus 'Adelaide City' VARCHAR(32) No

competency

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
TafeCompCode local TafeSA competency code 'TAAFA' VARCHAR(64) No PK(1)
NationalCompCode national competency code 'ICTDBS403' VARCHAR(64) No
CompetencyName official competency name 'Create basic databases' VARCHAR(128) No
Hours amount of required contact hours 40 INT No

qualification

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
QualCode IT internal code for the qualification 'D_SD15' VARCHAR(20) No PK(1)
NationalQualCode National qualification code 'ICT50715' VARCHAR(32) No
TafeQualCode Tafe qualification code 'TP00750' VARCHAR(32) No
QualName full name for qualification 'Diploma of Software Development' VARCHAR(100) No
TotalUnits total number of units 16 INT No
CoreUnits number of core units 10 INT No
ElectedUnits number of electives 16 INT No
ReqListedElectedUnits number of electives from listed electives 3 INT No

competency_type

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
CompTypeCode type code for the competency usage 'LE' VARCHAR(5) No PK(1)
CompTypeDescription description for competency usage type 'Listed Elective' VARCHAR(128) No

day_of_week

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
DayCode Number of the weekday (Mon:=1, Sun:=7) 1 INT(1) No PK(1)
DayShortName 3 letter abbreviation for the weekday 'Mon' CHAR(3) No
DayLongName Full name of for the weekday 'Monday' VARCHAR(9) No

term_datetime

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
TermCode Number of the term [1-4] 1 INT(1) No PK(2)
TermYear Year the term is 2016 INT(4) No PK(2)
StartDate Start date of the term 2016-02-15 DATE No
EndDate End date of the term 2016-04-10 DATE No
SemesterCode Number of the Semester [1-1] 1 INT(1) No

lecturer

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
LecturerID 9-digit identifier for a lecturer '000010581' CHAR(9) No PK(1)
GivenName Lecturer's given names 'Santi' VARCHAR(64) No
LastName Lecturer's last names 'Ruiz' VARCHAR(64) No
EmailAddress Lecturer's email address 'santi.ruiz@tafesa.edu.au' VARCHAR(64) No

student

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
StudentID 9-digit identifier for a student '000724247' CHAR(9) No PK(1)
GivenName Lecturer's given names 'Martin' VARCHAR(64) No
LastName Lecturer's last names 'Perez' VARCHAR(64) No
EmailAddress Lecturer's email address 'm_perez@hotmail.com' VARCHAR(64) No

subject

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
SubjectCode Code describing the subject '4JSB' VARCHAR(32) No PK(1)
SubjectDescription Detailed name of the subject 'Javascript Basics' VARCHAR(256) No

department

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
DepartmentCode Code for the department 'KJ' VARCHAR(8) No PK(1)
Department Department name 'Information Technology' VARCHAR(64) No

studyplan_qualification

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
StudyPlanCode Code describing the study plan 'SD5AS' VARCHAR(20) No PK(1)
QualCode Code describing the Qualification 'D_SD15' VARCHAR(20) No qualification.QualCode
Priority priority of the plan [the lower the higher the priority] 1 INT No

crn_detail

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
CRN course registration Number '13077' VARCHAR(32) No PK(3)
TafeCompCode local TafeSA competency code 'TAAFA' VARCHAR(64) No competency.TafeCompCode
TermCodeStart Number of the term CRN start[1-4] 1 INT(1) No PK(3) term_datetime.TermCode
TermYearStart Year the CRN starts 2016 INT(4) No PK(3) term_datetime.TermYear
TermCodeEnd Number of the term CRN end[1-4] 2 INT(1) No
TermYearEnd Year the CRN ends 2016 INT(4) No
SubjectCode Code describing the subject '4JSB' VARCHAR(32) No subject.SubjectCode
CampusCode 3 letter campus code 'ADL' CHAR(3) No campus.CampusCode
LecturerID 9-digit identifier for a lecturer '000010581' VARCHAR(10) No lecturer.LecturerID
DepartmentCode Code for the department 'KJ' VARCHAR(10) No department.DepartmentCode
FreezeDate Date the enrolments are frozen '2018-08-16' DATE No
DateCreated Date this CRN was imported '2018-06-06' DATE No

crn_session_timetable

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
CRN course registration Number '13077' VARCHAR(32) No PK(3) crn_detail.CRN
TermCodeStart Number of the term CRN start[1-4] 1 INT(1) No crn_detail.TermCodeStart
TermYearStart Year the CRN starts 2016 INT(4) No crn_detail.TermYearStart
DayCode Number of the weekday (Mon:=1, Sun:=7) 1 INT(1) No PK(3) day_of_week.DayCode
StartTime Time the session starts '9:00' TIME No PK(3)
EndTime Time the session ends '11:00' TIME No
Room Room description 'A.103' VARCHAR(16) Yes
Building Building description 'ADL-A' VARCHAR(16) Yes
CampusCode 3 letter campus code 'ADL' CHAR(3) No campus.CampusCode

studyplan_subject

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
StudyPlanCode Code describing the study plan 'SD5AS' VARCHAR(20) No PK(2) studyplan_qualification.StudyPlanCode
SubjectCode Code describing the subject '4JSB' VARCHAR(32) No PK(2) subject.SubjectCode
TimingSemester The ideal semester this subject should be taught 2 INT No
TimingSemesterTerm The ideal term of the semester this subject should be taught [1 or 2] 2 INT No

subject_qualification

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
SubjectCode Code describing the subject '4JSB' VARCHAR(32) No PK(2) subject.SubjectCode
QualCode Code describing the Qualification 'D_SD15' VARCHAR(20) No PK(2) qualification.QualCode
CompTypeCode type code for the competency usage 'LE' VARCHAR(5) No competency_type.CompTypeCode

subject_competency

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
SubjectCode Code describing the subject '4JSB' VARCHAR(32) No subject.SubjectCode
TafeCompCode local TafeSA competency code 'TAAFA' VARCHAR(64) No competency.TafeCompCode

student_studyplan

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
StudentID 9-digit identifier for a student '000724247' VARCHAR(9) No PK(2) student.StudentID
QualCode IT internal code for the qualification 'D_SD15' VARCHAR(20) No PK(2) qualification.QualCode
TermCodeStart Number of the term [1-4] 1 INT(1) No term_datetime.TermCode
TermYearStart Year the term is 2016 INT(4) No term_datetime.TermYear
EnrolmentType Type of enrolment 'Trainee' VARCHAR(20) No

student_grade

Column Name Description Example Type Nullable Primary Key Foreign Key Reference
StudentID 9-digit identifier for a student '000724247' VARCHAR(9) No PK(4) student.StudentID
CRN course registration Number '13077' VARCHAR(32) No PK(4) crn_detail.CRN
TermCode Number of the term CRN start[1-4] 1 INT(1) No PK(4) crn_detail.TermCodeStart
TermYear Year the CRN starts 2016 INT(4) No PK(4) crn_detail.TermYearStart
Grade Grade the student got for the CRN 'P' VARCHAR(2) Yes
GradeDate Date when the grade was awarded '2016-04-12' DATE Yes

Entity Relationship Diagram