Database Design Project: iSchool Student Award Database
This database is for an Information System to Administer PhD Teaching Assistantships and PhD Funding Packages at the iSchool.
Executive Summary
This assignment is a preliminary database design for an information system application which would facilitate the administration of PhD Teaching Assistantships and PhD Funding Packages at the iSchool. We gathered the functional requirements for the system by interviewing the Business Officer who currently oversees these two functions using a number of Excel spreadsheets, Katherine Shyjak.
From her overview of the people and processes involved we were able to formulate a large number of potential queries that the system would have to handle. We sorted and amalgamated these into ten basic queries to help guide the development of an entity-relationship conceptual data model for the domain. Our ER diagram reflects the way the various entities involved are related to one another, along with the attributes of each which must be stored in order to efficiently generate responses to all the sample queries which in turn support the business processes.
From the ER diagram, we were able to generate set of relations based on the attributes, cardinalities, and constraints of the relationships and entities. The set of relations reflect a logical implementation for the structure and storage the data involved so that the information system can support the business processes. For the ER diagram and the relational model, we have also enumerated requirements and constraints which could not be captured, respectively.
In order, not to overly complicate the initial development of the database, we chose to concentrate on satisfying requirements related to the vast majority of the administrative focus of the current system: PhD Students and T/A ships. Once this initial design is tested and verified, we would add in the capability to handle use cases which are far more infrequent (for example, administration of Sessional Instructional Assistants who may receive TAships but who are not PhD Students, as well as the capability to handle Research Assistantships in addition to T/A ships).
Sample Queries
1. List all PhD students per Cohort.
2. List all PhDs owed CUPE Subsequent Appointment commitments by the Faculty, along with what the obligations are (in terms of hours owed), and the degree to which they have been satisfied.
3. List all PhDs owed PhD Funding Package commitments by the Faculty, along with what the obligations are (in terms of hours owed), and the degree to which they have been satisfied.
4. How were CUPE Subsequent Appointments and PhD Funding Packages filled for each individual in each year (i.e. what offers of T/A ship hours were made, and what were the details of those offers).
5. List all T/A ships for a specific term, along with the total number of associated hours, the portion of the hours which have been assigned and those which are still available to be assigned.
6. For a specific PhD student in a specific term, what T/A ships were offered to satisfy the CUPE SA obligations owed to them, and what was the status of the offer (accepted/declined)?
7. For a specific PhD student in a specific term, what T/A ships were offered to satisfy the PhD Funding Package obligations owed to them, and what is the status of the offer (accepted/declined)?
8. What is the maximum number of T/A hours worked in the first two years of the program for a specific PhD student?
9. List all T/A ship applicants for a specific course in a specific term, along with the associated Instructor assessments.
10. List all the T/A applications which have not been assessed by the course instructor.
11. List the all the T/As for specific course in a specific term.
Data Requirements
1. Some courses have T/Aships associated with them, but each T/Aship is associated with only one course.
2. T/Aships may be offered to one or more PhD Students; PhD students may be offered one or more T/Aships; T/Aships have a total number of hours which are filled by accepted offers to one or more PhDs; T/Aships have an associated description of duties and required qualifications; they have to be advertised for 20 days.
3. T/Aships are usually established automatically based enrolment numbers, but can be created through a manual override procedure in special circumstances.
4. PhDs can apply for one or more T/Aships; T/Aships can be applied for by one or more PhDs.
5. Courses are taught by a primary instructor who assesses each T/Aship application for his or her courses.
6. PhDs are in a specific year of their program, are currently active or not, are in good standing or not, may take a single deferral year, belong to a specific CUPE bargaining unit, are part of a PhD funding cohort based on start year, must declare income for the year and have it be below a threshold to qualify for funding; must apply for SSRC and OGS funding every year in order to be eligible for a PhD Funding Package; may have PhD Funding Package and CUPE Subsequent Appointment requirements which have to be met by the Faculty each term.
7. T/Aship offers consist of a specific number of hours at a rate of pay stipulated by one relevant CUPE Subsequent Appointment collective agreement.
8. Obligations owed to every PhD student by the Faculty are determined based on the PhD student’s starting cohort and their CUPE bargaining unit.
9. T/Aship hours offered satisfy CUPE Subsequent Agreement requirements and PhD Funding Package requirements for the individual
10. Offers of T/Aships to PhDs can be accepted or declined by the PhD; if declined, the TAship can be offered to another PhD.
11. PhD Funding Package stipend amounts for living allowance, tuition and fees, and grant portion are determined by start date cohort.
12. CUPE Bargaining Unit collective agreements determine CUPE Subsequent Appointment hours owed by the Faculty, and rates of pay.
ER-diagram
Requirements that cannot be modelled by the er-diagram / assumptions
1. One functional dependency which is not captured by the ER diagram is the variable number of hours which each T/Aship consists of. This number is related to the enrolment for the class and increments in fixed amounts (i.e. enrolment < 35 = 0 hours, enrolment 36–70 = 100 hours, etc.).
2. T/A ships have been treated as consisting of a fixed number of hours which are available to be offered in blocks to PhD students. The sizes of the blocks offered are not treated as fixed, and are not modelled by the ER diagram.
3. Both Instructors and PhD Students entities are to be considered sub-entities of the iSchool’s main Personnel relation. These “ISA” relationships would be modelled in a broader focus ER diagram which encompassed more of the Student Services information system application modules.
4. The Student Services Business Officer we interviewed asked for reporting capability which would enumerate and itemize the amounts and dates which grant monies were disbursed three times per year to PhDs with Funding Packages. This has not been modeled in the ER diagram as it would necessitate drawing information from other finance-related modules of the larger Student Services application.
5. Since there was no explicit mention that the duties and qualifications for a T/A ship are determined by the associated course instructor, that relationship has not been modelled.
6. The request for a “notes” field where system administrators could record non-structured information about the entities and relationships in the ER diagram has not been modelled.
ER-to-Relational
The set of relations and their keys
The following set of relations were mapped from the ER diagram, considering the attributes, cardinalities, and constraints of the relationships and entities. The relational model reflects a logical implementation of the how to structure and store the data involved so that the intended information system can support the business processes.
Constraints
Participation constraints not reflected in the relational model:
- Each TAship has a related course, but a course may or may not give rise to a TAship.
- Each course is taught primarily by an instructor, but an instructor may or may not teach a course.
- Each TAship offer is based on the CUPE bargaining unit collective agreement requirements.
- Each PhD student is protected by the faculty obligations determined by CUPE and Funding package requirements.
- Each TAship application is assessed by an instructor.
The domain constraints are also not included in the relational model diagram.
Query Statements and SQL Translations
1. List all PhD students per Cohort.
SELECT H.cohort_year_program_start, H.phd_id
FROM phd_students H
GROUP BY H.cohort_year_program_start, H.phd_id
ORDER BY H.cohort_year_program_start
2. List all PhDs owed CUPE Subsequent Appointment commitments by the Faculty, along with what the obligations are (in terms of hours owed), and the degree to which they have been satisfied.
SELECT H.phd_id, I.cupe_unit_sa_hours_owed, SUM(G.hours_offered) AS “Hours Offered”, I.cupe_unit_sa_hours_owed — SUM(G.hours_offered) AS “Faculty Obligation Remaining”
FROM phd_students H, cupe_reqs I, offered F, taship_offers G
WHERE H.is_owed_cupe_subsequent_appointment_obligations = “1” AND H.cupe_unit = I.cupe_bargaining_unit AND H.phd_id = F.phd_id AND F.offer_id = G.offer_id
GROUP BY H.phd_id
3. List all PhDs owed PhD Funding Package commitments by the Faculty, along with what the obligations are (in terms of money owed), and the degree to which they have been satisfied.
SELECT H.phd_id, J.cohort_living_allowance AS “Funding Package Obligation”, SUM(G.hours_offered * G.rate_of_pay) AS “Funding Offered through TAships”, J.cohort_living_allowance — SUM(G.hours_offered * G.rate_of_pay) AS “Faculty Obligation Remaining”
FROM phd_students H, phd_funding_reqs J, offered F, taship_offers G
WHERE H.is_owed_phd_funding_package_obligations = “1” AND
H.cohort_year_program_start = J.cohort_year AND
H.phd_id = F.phd_id AND F.offer_id = G.offer_id
GROUP BY H.phd_id
4. How were CUPE Subsequent Appointments and PhD Funding Packages filled for each individual in each year (i.e. what offers of T/A ship hours were made successfully, and what were the details of those offers).
SELECT *
FROM offered F
WHERE F. acceptance_status = “accepted”
GROUP BY F. taship_id
5. List all T/A ships for a specific term, along with the total number of associated hours, the portion of the hours which have been assigned and those which are still available to be assigned.
SELECT D.taship_id, A.term, D.total_hours, G.hours_offered, D.total_hours — G.hours_offered AS “Hours_left”
FROM taships D, courses A, offered F, taship_offers G
WHERE A.course_id = D.course_id AND D.taship_id = F.taship_id AND F.offer_id = G.offer_id
6. For a specific PhD student in a specific term, what T/A ships were offered to satisfy the CUPE SA obligations owed to them, and what was the status of the offer (accepted/declined)?
SELECT H.phd_id, F.offer_id, F.acceptance_status
FROM phd_students H, offered F
WHERE H.phd_id = F.phd_id AND H. is_owed_cupe_subsequent_appointment_obligations = ‘1’
7. For a specific PhD student in a specific term, what T/A ships were offered to satisfy the PhD Funding Package obligations owed to them, and what is the status of the offer (accepted/declined)?
SELECT H.phd_id, F.offer_id, F.acceptance_status
FROM phd_students H, offered F
WHERE H.phd_id = F.phd_id AND H.is_owed_phd_funding_package_obligations = ‘1’
8. Of all the TAships which a specific PhD student (Quinto39) has participated in, how many hours is the longest one?
SELECT MAX (G.hours_offered)
FROM taship_offers G, offered F
WHERE G.offer_id = F.offer_id AND F.acceptance_status = “accepted” AND F.PhD_id = “Quinto39”
9. List all T/A ship applicants for a specific course in a specific term, along with the associated Instructor assessments.
SELECT S.phd_id, C.course_id, C.term, T.instructor_assessment
FROM courses C, taship_applications T, submitted S
WHERE C.instructor_id = T.instructor_id AND T.application_id = S.application_id
10. List all the T/A applications which have not been assessed by the course instructor.
Select *
From taship_applications T
Where T.instructor_assessment = “”
11. List the all the T/As for specific course in a specific term.
Select F.phd_id, T.course_id, T.term
From taship_offers O, offered F, taships T
Where O.offer_id = F.offer_id AND F.taship_id = T.taship_id AND F.acceptance_status = “accepted”
12. Supplementary SQL Statements
Deletion 1:
DELETE
FROM phd_students S
WHERE S.cohort_year = 2010
Deletion 2:
DELETE
FROM phd_students S
WHERE S.phd_id = “Williams05”
Insertion 1:
INSERT INTO Taship_applications (application_id, instructor_assessment, instructor_id)
VALUES (“7HUY8O”, “”, “Shade02”)
Insertion 2:
INSERT INTO Taship_applications (application_id, instructor_assessment, instructor_id)
VALUES (“8HU90H”, “”, “Shade02”)
Update 1:
UPDATE phd_students S
SET S.standing_status = 0
WHERE S.phd_id = “Williams05”
Update 2:
UPDATE phd_students S
SET S.active_status = 0
WHERE S.phd_id = “Williams05”
Please note the assumption: A number of the queries specify a chronological component (i.e. query information related to a specific term). As per the note on our Assignment 1 ER diagram, we have chosen to simplify things by leaving out this component for now. It is assumed that once the base queries are validated, adding the time-specific qualification would straight-forward.
