Эта база данных предназначена для информационной системы для администрирования ассистентов преподавателей докторантуры и пакетов финансирования докторантуры в iSchool.
Управляющее резюме
Это задание представляет собой предварительный проект базы данных для приложения информационной системы, которое облегчит администрирование ассистентов преподавателей и пакетов финансирования докторантуры в iSchool. Мы собрали функциональные требования к системе, опросив Кэтрин Шиджак, бизнес-директора, который в настоящее время курирует эти две функции, используя ряд электронных таблиц Excel.
Из ее обзора вовлеченных людей и процессов мы смогли сформулировать большое количество потенциальных запросов, которые должна была обработать система. Мы отсортировали и объединили их в десять основных запросов, чтобы помочь в разработке концептуальной модели данных «сущность-связь» для предметной области. Наша ER-диаграмма отражает то, как различные задействованные сущности связаны друг с другом, а также атрибуты каждой из них, которые необходимо хранить, чтобы эффективно генерировать ответы на все типовые запросы, которые, в свою очередь, поддерживают бизнес-процессы.
Из диаграммы ER мы смогли сгенерировать набор отношений на основе атрибутов, мощностей и ограничений отношений и сущностей. Набор отношений отражает логическую реализацию структуры и хранения задействованных данных, чтобы информационная система могла поддерживать бизнес-процессы. Для ER-диаграммы и реляционной модели мы также перечислили требования и ограничения, которые не удалось зафиксировать соответственно.
Чтобы не слишком усложнять первоначальную разработку базы данных, мы решили сосредоточиться на удовлетворении требований, связанных с подавляющим большинством административных задач текущей системы: аспирантов и кораблей T/A. После того, как этот первоначальный проект будет протестирован и проверен, мы добавим возможность обрабатывать варианты использования, которые встречаются гораздо реже (например, администрирование сессионных ассистентов-инструкторов, которые могут получать TAships, но не являются аспирантами, а также возможность работать с научными ассистентами в дополнение к кораблям T / A).
Примеры запросов
1. Перечислите всех аспирантов по группам.
2. Перечислите все обязательства PhD, связанные с последующим назначением CUPE на факультете, а также их обязательства (с точки зрения задолженностей по часам) и степень их выполнения.
3. Перечислите все обязательства факультета по пакету финансирования докторской степени, а также их обязательства (с точки зрения задолженностей по часам) и степень их выполнения.
4. Как каждый год заполнялись пакеты финансирования CUPE для последующих назначений и докторантуры для каждого человека (т. е. какие предложения о часах доставки T / A были сделаны и каковы были детали этих предложений).
5. Перечислите все суда T/A на определенный срок, а также общее количество соответствующих часов, часть часов, которые были назначены, и те, которые еще доступны для назначения.
6. Для конкретного аспиранта в определенный срок, какие суда T/A были предложены для выполнения обязательств CUPE SA перед ним, и каков был статус предложения (принято/отклонено)?
7. Для конкретного аспиранта в определенный семестр, какие корабли T/A были предложены для выполнения обязательств по пакету финансирования PhD, и каков статус предложения (принято/отклонено)?
8. Каково максимальное количество часов T/A, отработанных в первые два года программы для конкретного аспиранта?
9. Перечислите всех претендентов на участие в программе T/A на конкретный курс в определенный семестр вместе с соответствующими оценками инструкторов.
10. Перечислите все заявки на T/A, которые не были оценены преподавателем курса.
11. Перечислите все Т/Д для конкретного курса в конкретном семестре.
Требования к данным
1. С некоторыми курсами связаны T/Aship, но каждый T/Aship связан только с одним курсом.
2. T/Aships могут быть предложены одному или нескольким аспирантам; Аспирантам может быть предложена одна или несколько T/Aships; T / Aships имеют общее количество часов, которые заполнены принятыми предложениями для одного или нескольких докторов наук; T / Aships имеют соответствующее описание обязанностей и требуемой квалификации; они должны рекламироваться в течение 20 дней.
3. T/Aships обычно назначаются автоматически на основе регистрационных номеров, но в особых обстоятельствах их можно создать с помощью процедуры ручной отмены.
4. Кандидаты наук могут подать заявку на одну или несколько T/Aships; На T/Aships может подать заявку один или несколько докторов наук.
5. Курсы ведет главный инструктор, который оценивает каждую заявку на T/Aship для своих курсов.
6. Кандидаты находятся в определенном году своей программы, в настоящее время активны или нет, имеют хорошую репутацию или нет, могут получить отсрочку на один год, принадлежат к определенной единице переговоров CUPE, являются частью когорты финансирования докторантуры на основе начала год, должен декларировать доход за год, и он должен быть ниже порога, чтобы претендовать на финансирование; должен подавать заявку на финансирование SSRC и OGS каждый год, чтобы иметь право на получение пакета финансирования PhD; могут иметь пакет финансирования PhD и требования к последующему назначению CUPE, которые должны выполняться факультетом каждый семестр.
7. Предложения T/Aship состоят из определенного количества часов по ставке оплаты, предусмотренной одним соответствующим коллективным договором о последующем назначении CUPE.
8. Обязательства факультета перед каждым аспирантом определяются на основе стартовой когорты аспирантов и их переговорной единицы CUPE.
9. Предлагаемые часы T / Aship соответствуют требованиям CUPE Subsection Agreement и требованиям пакета финансирования PhD для человека.
10. Предложения T/Aships кандидатам наук могут быть приняты или отклонены кандидатом наук; в случае отказа TAship может быть предложен другому кандидату наук.
11. Суммы стипендии пакета финансирования PhD на проживание, обучение и сборы, а также часть гранта определяются когортой даты начала.
12. Коллективные договоры Группы переговоров CUPE определяют количество часов последующего назначения CUPE, причитающихся Факультету, и ставки заработной платы.
ER-диаграмма
Требования, которые нельзя смоделировать с помощью er-диаграммы/предположений
1. Одной функциональной зависимостью, которая не отражается на диаграмме ER, является переменное количество часов, из которых состоит каждое T/Aship. Это число связано с зачислением в класс и увеличивается на фиксированные суммы (например, зачисление ‹ 35 = 0 часов, зачисление 36–70 = 100 часов и т. д.).
2. Корабли T / A рассматривались как состоящие из фиксированного количества часов, которые можно предлагать аспирантам блоками. Размеры предлагаемых блоков не считаются фиксированными и не моделируются диаграммой ER.
3. Объекты «преподаватели» и «аспиранты» должны рассматриваться как дочерние объекты основных отношений «Персонал» iSchool. Эти отношения «ISA» будут смоделированы на более широкой диаграмме ER, которая охватывает больше прикладных модулей информационной системы студенческих служб.
4. Бизнес-менеджер службы поддержки студентов, с которым мы беседовали, попросил предоставить отчет, в котором перечислялись бы и перечислялись суммы и даты, когда грантовые средства выплачивались три раза в год докторам наук с пакетами финансирования. Это не было смоделировано на диаграмме ER, так как это потребовало бы получения информации из других связанных с финансами модулей более крупного приложения Student Services.
5. Поскольку не было явного упоминания о том, что обязанности и квалификация для корабля T/A определяются соответствующим инструктором курса, эта взаимосвязь не моделировалась.
6. Запрос на поле «примечания», в котором системные администраторы могли бы записывать неструктурированную информацию о сущностях и связях на ER-диаграмме, не моделировался.
ER-в-реляционный
Набор отношений и их ключи
Следующий набор отношений был отображен на диаграмме ER с учетом атрибутов, мощностей и ограничений отношений и сущностей. Реляционная модель отражает логическую реализацию того, как структурировать и хранить задействованные данные, чтобы предполагаемая информационная система могла поддерживать бизнес-процессы.
Ограничения
Ограничения участия, не отраженные в реляционной модели:
- У каждого TAship есть связанный курс, но курс может привести или не привести к TAship.
- Каждый курс в основном преподается инструктором, но инструктор может вести курс, а может и не преподавать.
- Каждое предложение TAship основано на требованиях коллективного договора CUPE.
- Каждый аспирант защищен обязательствами преподавателей, определенными CUPE и требованиями к пакету финансирования.
- Каждая заявка на TAship оценивается инструктором.
Ограничения предметной области также не включены в диаграмму реляционной модели.
Операторы запросов и переводы SQL
1. Перечислите всех аспирантов по группам.
ВЫБЕРИТЕ H.cohort_year_program_start, H.phd_id
ОТ phd_students H
ГРУППИРОВАТЬ ПО H.cohort_year_program_start, H.phd_id
ЗАКАЗАТЬ ПО H.cohort_year_program_start
2. Перечислите все обязательства PhD, связанные с последующим назначением CUPE на факультете, а также их обязательства (с точки зрения задолженностей по часам) и степень их выполнения.
SELECT H.phd_id, I.cupe_unit_sa_hours_owed, SUM(G.hours_offered) AS «Предлагаемые часы», I.cupe_unit_sa_hours_owed — SUM(G.hours_offered) AS «Оставшиеся обязательства преподавателей»
ОТ phd_students H, cupe_reqs I, предлагаемое F, taship_offers G
ГДЕ H.is_owed_cupe_subsequent_appointment_obligations = «1» И H.cupe_unit = I.cupe_bargaining_unit И H.phd_id = F.phd_id И F.offer_id = G.offer_id
ГРУППИРОВАТЬ ПО H.phd_id
3. Перечислите все обязательства факультета по пакету финансирования докторской степени, а также их обязательства (в денежном выражении) и степень их выполнения.
SELECT H.phd_id, J.cohort_living_allowance AS «Обязательство пакета финансирования», SUM(G.hours_offered * G.rate_of_pay) AS «Финансирование, предлагаемое через TAships», J.cohort_living_allowance — SUM(G.hours_offered * G.rate_of_pay) AS «Оставшиеся обязательства преподавателей»
ОТ phd_students H, phd_funding_reqs J, предложено F, taship_offers G
ГДЕ H.is_owed_phd_funding_package_obligations = «1» И
H.cohort_year_program_start = J.cohort_year И
H.phd_id = F.phd_id И F.offer_id = G.offer_id
ГРУППА ПО H.phd_id
4. Каким образом были заполнены последующие назначения CUPE и пакеты финансирования PhD для каждого человека в каждом году (т. е. какие предложения часов T / A были успешно сделаны, и каковы были детали этих предложений).
ВЫБЕРИТЕ *
ОТ предложил F
ГДЕ F. accept_status = «принято»
ГРУППИРОВАТЬ ПО F. taship_id
5. Перечислите все суда T/A на определенный срок, а также общее количество соответствующих часов, часть часов, которые были назначены, и те, которые еще доступны для назначения.
ВЫБЕРИТЕD.taship_id, A.term, D.total_hours, G.hours_offered, D.total_hours — G.hours_offered AS «Hours_left»
ОТtaships D, курсы A, предлагаемые F, taship_offers G
ГДЕA.course_id = D.course_id И D.taship_id = F.taship_id И F.offer_id = G.offer_id
6. Для конкретного аспиранта в определенный срок, какие суда T/A были предложены для выполнения обязательств CUPE SA перед ним, и каков был статус предложения (принято/отклонено)?
ВЫБЕРИТЕ H.phd_id, F.offer_id, F.acceptance_status
ОТ phd_students H, предложено F
ГДЕ H.phd_id = F.phd_id И H. is_owed_cupe_subsequent_appointment_obligations = ‘1’
7. Для конкретного аспиранта в определенный семестр, какие корабли T/A были предложены для выполнения обязательств по пакету финансирования PhD, и каков статус предложения (принято/отклонено)?
ВЫБЕРИТЕ H.phd_id, F.offer_id, F.acceptance_status
ОТ phd_students H, предложено F
ГДЕ H.phd_id = F.phd_id AND H.is_owed_phd_funding_package_obligations = ‘1’
8. Из всех TAships, в которых участвовал конкретный аспирант (Quinto39), сколько часов самое продолжительное?
ВЫБЕРИТЕ МАКС. (G.hours_offered)
ОТ taship_offers G, предложено F
ГДЕ G.offer_id = F.offer_id И F.acceptance_status = «принято» И F.PhD_id = «Quinto39»
9. Перечислите всех претендентов на участие в программе T/A на конкретный курс в определенный семестр вместе с соответствующими оценками инструкторов.
ВЫБЕРИТЕ S.phd_id, C.course_id, C.term, T.instructor_assessment
Курсы FROM C, taship_applications T, отправлено S
ГДЕ C.instructor_id = T.instructor_id И T.application_id = S.application_id
10. Перечислите все заявки на T/A, которые не были оценены преподавателем курса.
Выбрать *
От taship_applications T
Где T.instructor_assessment = ""
11. Перечислите все Т/Д для конкретного курса в конкретном семестре.
Выберите F.phd_id, T.course_id, T.term
От taship_offers O, предложено F, taships T
Где O.offer_id = F.offer_id И F.taship_id = T.taship_id И F.acceptance_status = «принято»
12. Дополнительные операторы SQL
Удаление 1:
УДАЛИТЬ
ОТ phd_students S
ГДЕ S.cohort_year = 2010
Удаление 2:
УДАЛИТЬ
ОТ phd_students S
ГДЕ S.phd_id = «Williams05»
Вставка 1:
ВСТАВИТЬ В Taship_applications (application_id, instructor_assessment, instructor_id)
ЗНАЧЕНИЯ («7HUY8O», «», «Shade02»)
Вставка 2:
ВСТАВИТЬ В Taship_applications (application_id, instructor_assessment, instructor_id)
ЗНАЧЕНИЯ («8HU90H», «», «Shade02»)
Обновление 1:
ОБНОВЛЕНИЕ phd_students S
УСТАНОВИТЕ S.standing_status = 0
ГДЕ S.phd_id = «Williams05»
Обновление 2:
ОБНОВЛЕНИЕ phd_students S
УСТАНОВИТЕ S.active_status = 0
ГДЕ S.phd_id = «Williams05»
Обратите внимание на допущение: в ряде запросов указывается хронологический компонент (т. е. информация запроса, связанная с конкретным термином). Согласно примечанию к нашей диаграмме ER Задания 1, мы решили упростить ситуацию, исключив пока этот компонент. Предполагается, что как только базовые запросы будут проверены, добавление уточнения по времени будет несложным.
