[Specification]  [Database]  [SQL Schema]  [Grades+Rules]  [Examples]  [Testing]  [Submitting]  [Fixes+Updates]

Introduction

This document contains a description of the MyMyUNSW database 2023 version.

Background

UNSW handles its administrative information using a version of the Peoplesoft product called Campus Solutions. This system is normally accessed via the MyUNSW portal and is maintained by the NSS unit in UNSW IT, and so it's variously called PeopleSoft, Campus Solutions, MyUNSW and NSS. The database behind the system is hosted on a large Oracle server, and throughout this document will be referred to as the NSS database.

The PeopleSoft system was installed in 2000 and has been modified/extended over the years to encompass:

• human resources (staff/employees, payroll, etc.)
• financials (purchases, income/expenditure, etc.)
• academic (students, courses, classes, enrolment, etc.)

The current NSS database is separate from the UNSW Handbook, which records all of the official information related to course and program/degree requirements. To support on-line enrolment, NSS does represent some course information, such as pre-requisites, co-requisites and exclusions, as well as enrolment quotas. However, it maintains this information independently to the Handbook, which leads to potential inconsistency. Worse, NSS maintains no information at all about program/degree requirements, which means that students cannot use NSS to monitor their progress through their degree.

The MyMyUNSW database aims to implement a superset of the contents of the NSS database, including:

• people: staff, students
• infrastructure: buildings, rooms, facilities
• organisation: faculties, schools, centres
• academic: programs, streams, subjects, courses, classes

Note: there are two places in this schema where we deviate from current UNSW terminology. A stream is a new term that refers to what the current UNSW Handbook calls a plan or specialisation. Streams also encompass the collections of courses that comprise majors and minors in many degrees. Also, in our schema, we use the terms subject and course to talk about a unit of study (subject) and a particular offering of a subject in a given semester (course). UNSW confusingly calls both of these a course, although it also sometimes also uses the term course offering for the second.

Data Model for UNSW

This section aims to give an overview of the entire UNSW data model. Our data model does not include all of these ideas, or deals with them under a different name.

• there are two main kinds of people in the system: staff and students; all people have certain basic information associated with them (e.g. name); staff have additional information related to their employment; students have additional information related to the degree that they are studying; there are also people who are neither staff nor students that UNSW wants to record (e.g. members of the University Council)
• UNSW runs a number of teaching terms each year (these are also called sessions or semesters)
• a subject is a unit of study in a particular area (e.g. introductory programming, database systems, etc.); a subject is defined primarily by its syllabus
• a course is a particular offering of a subject in a particular teaching term; it has a course convener (also called lecturer-in-charge), and a MyExperience rating
• a program is a named program of study leading to one or more degrees
• a stream specifies the precise requirements for study in a specific area; it is used to implement the notions of major and minor
• in a particular program, students choose at least one stream from a range of possible streams (in a double degree, they will choose two streams, one from each set of streams for the constituent degrees); the program will specify precisely what are the allowed/required combinations of streams
• to satisfy the requirements of the program, a student must satisfy the requirements of all the streams that they enrol in from the program; in addition, there may be requirements from the program itself (e.g. general education, total units of credit completed, etc.)
• there are several different types of requirements:
  • subject requirements (core, prescribed elective, free elective, Gen Ed)
  • stream requirements (e.g. must take one from the BCom majors)
  • UOC requirements (e.g. overall plan needs at least 144 UC)
• in specifying requirements, we frequently need to deal with sets of academic objects (either programs or streams or subjects); we call these (generically) academic groupings
• we use subject groups in specifying subject requirements; each subject group has a name (e.g. "level 3/4 COMP courses") and an associated set of subjects
• similarly for stream groups (e.g. "set of BA majors") and program groups (e.g. "all programs offered by CSE")
• a subject requirement specifies: a subject group, a number of UOC associated with the group, whether this number is a minimum or maximum requirement; this is flexible enough to allow us to describe:
  • core requirements (group size 1, must complete 1 course from the group)
  • alternatives (several related courses, must complete 1 of them)
  • professional electives (set of courses from one area, must complete k of them)
  • limitations (e.g. no more than 72 UC of level 1 courses)
• in terms of the ideas above, programs and streams are defined as collections of requirements; a particular student must satisfy all of the requirements before they are regarded as having completed the program or stream
• how to determine whether a student has satisfied requirements depends on the type of requirement:
  • for UOC, use the course enrolment information
  • for course requirements, use the course enrolment information
  • for miscellaneous, must explicitly record that the student has met them
    (because there's no other data that will allow us to work it out)
• at any given time, each student is enrolled in one program, one or more streams (associated with the program), and generally several courses and classes within those courses; we need to record all four kinds of enrolment
• for enrolment in a program, it is useful to know when the student's enrolment commenced, when it ended (if it has ended), and their current status (e.g. active, on leave, etc.)
• over their lifetime, students may enrol in several programs, each with associated streams and courses
• a schedule describes when in a stream particular courses should be taken; in our terms, it will relate subject groups to streams and associate specific (year,semester) combinations with them
• sometimes, we wish to allow a student to vary from the standard requirements of their degree plans; there are three types of substitutions:
  • substitution: replace one course by another within a program
  • advanced standing: get credit for a course from elsewhere (or from a partly-completed UNSW degree) to use in place of some course in a program
  • exemption: get recognition for having studied a course elsewhere so that this can be used as a pre-requisite for further study at UNSW

Our Data Model

The above describes the entirety of the data that UNSW uses to manage academic/study matters. We don't consider other aspects such as Human Resources, or even all of the above (e.g. nothing about Variations). Below, we talk about the relatvely simple data model used in this assignment.

We give an ER overview and then describe each table in detail. Note that the ER diagram does not include all of the relationships in the schema (e.g. Course->Convenor, Requirement->Stream). And the ER representation of Stream enrolment is debatable; use the SQL Schema as the definitive version of the schema.

ER Overview

Terms Table

Descibes UNSW trimesters (from Academic Cakendar)

• id ... unique integer primary key
• code ... term code (e.g. 19T1, 20T2)
• starting ... date when term starts,
• ending ... date when term ends
• description ... long form description of term name

Countries Table

Gives brief information about all countries in the world

• id ... unique integer primary key
• code ... three-letter country code (unique)
• name ... full name of country (unique)

People Table

Describes people in the databasem both staff and students

• id ... unique integer primary key
• zid ... student ID, should be unique and not null
• family_name ... family name
• given_names ... given names (space-separated)
• full_name ... combination of given and family names
• origin ... foreign key to reference country-of-origin

Students Table

Decribes individual students

• id ... unique integer primary key referencing Paople table
• status ... indicates resident status (AUS, INTL, ...)

Staff Table

Notes that a particular person is also a staff member

• id ... unique integer primary key referencing Paople table

Orgunits Table

Describes organisational units within UNSW

• id ... unique integer primary key
• code ... unique symbolic code for identifying units (e.g. COMPSC)
• name ... e.g. "School of Physics", "Faculty of Engineering"
• utype ... kind of unit (e.g. "faculty", "school")
• parent ... implements hierarchy of organisational units

Subjects Table

Describes individual subjects (minimal handbook entry)

• id ... unique integer primary key
• code ... subject code (e.g. "COMP3311")
• title ... name of subject (e.g. "Database Systems")
• uoc ... units of credit awarded for completing the course
• career ... e.g. postgrad, undergrad, research
• owner ... the organisational unit (typically a school) that owns/teaches the subject

Courses Table

Describes offerings of subjects in particular term

• id ... unique integer primary key
• subject ... reference to the subject being offered
• term ... reference to the term when the subject is offered
• convenor ... reference to the staff member who teaches the course
• satisfact ... MyExperience satisfaction score (fake)
• nresponses ... number of responses to the survey

Note that the convenor may be NULL if we don't know who they are. The satisfaction score and number of respones my be NULL if there are hardly any students enrolled in the course.

Streams Table

Describes minimal global information for a stream

• id ... unique integer primary key
• code ... stream code (e.g. "COMPA1", "SENGAH")
• name ... stream name (e.g. "Software Engineering")

Programs Table

Describes minimal global information for a program

• id ... unique integer primary key
• code ... program code (.e.g "3707", "3778", "8543")
• name ... program name (e.g. "Computer Science")

Program_enrolments Table

Describes student enrolment in a program in a given term:

• id ... unique integer primary key
• student ... reference to the student enrolled in the program
• term ... reference to the term during which enrollled
• program ... reference to the Programs table entry for the programs

Stream_enrolments Table

Describes student enrolment in streams:

• part_of ... references the program enrolment that the stream is part of
• stream ... reference to the Streams table entry for the stream

Note that the information about the student and the term enrolled is held in the referenced Program_enrolments table.

Course_enrolments Table

Describes student enrolment in courses:

• student ... reference to the student enrolled in the course
• course ... reference to the course enrolled in
• mark ... score of subject, in the range 0..100
• grade ... one of the possible UNSW grades (e.g. "FL", "HD")

Note that both grade and mark are NULL while the student in enrolled in a course.

Requirements Table

Describes rules for completing streams and programs:

• id ... a unique integer id, used for referencing the requirement
• name ... brief description of what the requirement represents
• rtype ... one of the types described in the Grades and Rules page
• min_req ... min requirement to satisfy this requirement (could be UOC or a count)
• max_req ... max requirement for this requirement (could be UOC or a count)
• acadobjs ... academic objects (courses or streams) associated with the requirement
• for_stream ... which stream the requirement applies to
• for_program ... which program the requirement applies to

Note that only one of for_stream or for_program wil be not null (i.e. a requirement applies either to a stream or to a program).