Разработка базы данных учета пропусков учащихся и программы-клиента к ней

программа база данные

Проектирование БД — одна из наиболее сложных и ответственных задач, связанных с созданием информационной системы. В результате решения этой задачи должны быть определены содержание базы данных, эффективный для всех её будущих пользователей способ организации данных и инструментальные средства управления данными.

В крупных системах, проектирование баз данных требует особой тщательности, поскольку цена допущенных на этой стадии просчётов и ошибок особенно велика. Некоторые ошибки проектирования можно скорректировать позже в процессе эксплуатации с помощью средств реструктуризации и реорганизации базы данных, но такие операции являются весьма трудоемкими и дорогостоящими.

Основная цель процесса проектирования базы данных состоит в получении такого проекта, который удовлетворяет следующим требованиям:

1. Корректность схемы БД, база должна быть моделируемой, где каждому объекту программного обеспечения соответствуют данные в памяти ЭВМ, а каждому процессу — адекватные процедуры обработки данных.

2. Обеспечение ограничений (на объёмы внешней и оперативной памяти и другие ресурсы вычислительной системы).

3. Эффективность функционирования (соблюдение ограничений на время реакции системы на запрос и обновление данных).

4. Защита данных (от сбоев и несанкционированного доступа).

5. Простота и удобство эксплуатации.

6. Гибкость, т. е. возможность развития и адаптации к изменениям программного обеспечения или требований пользователей.

Удовлетворение первых 4-х требований обязательно для принятия проекта.

В настоящее время создан ряд систем автоматизации проектирования БД, но ввиду специфики разработки баз данных не стали пока массовым инструментом разработчиков.

В данной работе описано создание базы данных по учету пропусков учащихся.

Актуальность: Почему данная программа необходима учебному заведению? Она значительно упрощает работу по ведению пропусков занятий учащимися. Если в отсутствии базы данных приходилось вести подсчет пропущенных занятий вручную, то с её появлением данная работа возлагается на персональный компьютер, пользователю лишь остаётся вносить контрольные данные. Работа с программой не требует особых навыков, необходимо лишь периодически вводить новые данные о пропущенных занятиях, а затем программа автоматически выдает количество уважительных и неуважительных пропусков, а также их сумму.

Объект: Разработка баз данных.

Предмет: Разработка базы данных и системы учета пропусков учащихся.

Цель: Разработать базу данных учета пропусков учащихся и программу-клиент к ней.

Задачи:

1. Проанализировать предметную область.

2. Спроектировать базу данных по учету пропусков.

3. Разработать программу-клиент для взаимодействия с базой данных.

1. ЭТАПЫ ПРОЕКТИРОВАНИЯ БАЗЫ ДАННЫХ

программа база данные

1.1 Общие сведения об этапах проектирования

В простых случаях, описание программного обеспечения представляется на естественном языке, в более сложных, используется математический аппарат: таблицы, диаграммы, графы. Если анализ программного обеспечения выполняется несколькими специалистами, то они должны принять соглашения, касающиеся:

· используемых методов анализа предметной области;

· правил именования и обозначения объектов программного обеспечения, атрибутов и связей;

· содержания и формата создаваемых ими документов.

Процесс проектирования включает в себя следующие этапы:

1. Анализ предметной области

2. Информационно-логическое (концептуальное) проектирование.

3. Логическое проектирование БД.

4. Физическое проектирование БД.

Концептуальное и логическое проектирование базы данных включает три основных этапа. Задачей первого этапа является разбиение проекта на группу относительно небольших и более простых задач исходя из представлений о предметной области приложения, свойственных каждому из типов конечных пользователей. Результатом выполнения этого этапа является создание локальных концептуальных моделей данных, представляющих собой полное и точное отражение представлений о предметной области приложения отдельных типов пользователей.

На втором этапе локальные концептуальные модели данных преобразуются в локальные логические модели данных (для реляционной модели данных), состоящие из ER-диаграммы, реляционной схемы и сопроводительной документации. Затем корректность логических моделей данных проверяется с помощью правил нормализации. Нормализация представляет собой эффективное средство, позволяющее убедиться в структурной согласованности, логической целостности и минимальной избыточности принятой модели данных. Дополнительно модель данных проверяется с целью выявления возможности осуществления транзакций, которые будут выполняться пользователями создаваемого приложения. Все эти проверки позволяют получить необходимую уверенность в том, что принятая модель данных является вполне приемлемой. По завершении второго этапа каждая локальная логическая модель данных при необходимости может применяться для подготовки прототипов реализаций базы данных, предназначенных для отдельных типов пользователей приложения.

На третьем этапе выполняется объединение локальных логических моделей данных (отражающих представление о предметной области отдельных типов пользователей) в единую глобальную логическую модель данных всего предприятия (обобщающую представления о предметной области всех типов пользователей). Глобальная логическая модель проверяется по такому же принципу, как и локальные модели; это позволяет убедиться в том, что ее структура является правильной и поддерживает все необходимые транзакции. Этот этап обычно требуется, если приложение состоит более чем из одного представления.

[Коннолли Т., Бегг К. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. — 3-е изд. — М.: Вильямс, 2012. — 1436 с.]

В предлагаемой методологии проектирования существенная роль отводится конечным пользователям, которые постоянно привлекаются разработчиками для ознакомления и проверки создаваемых моделей данных и сопроводительной документации. Проектирование баз данных обычно представляет собой циклический процесс, имеющий конкретную точку начала, практически не имеющий конца и включающий неограниченное число циклов улучшений и доработок.

Этот процесс выглядит как четко определенная последовательность процедур, это ни в коей мере не означает, что разработка базы данных непременно должна выполняться именно таким образом. Весьма вероятно, что сведения, полученные при выполнении некоторого этапа, могут потребовать изменить решения, принятые на одном из предыдущих этапов. Практика предварительного анализа возможных результатов выполнения последующих этапов может оказаться полезной при выполнении начальных этапов разработки. Предлагаемую методологию следует рассматривать как общую схему, которая позволит повысить эффективность работы по проектированию баз данных.

[Кузнецов С. Д. Основы баз данных. — 2-е изд. — М.: Интернет-Университет Информационных Технологий; БИНОМ. Лаборатория знаний, 2011. — 484 с.]

Физическое проектирование базы данных. Описание конкретной реализации базы данных, размещаемой во внешней памяти. Физический проект описывает базовые отношения, определяет организацию файлов и состав индексов, применяемых для обеспечения эффективного доступа к данным, а также регламентирует все соответствующие ограничения целостности и меры защиты.

Физическое проектирование базы данных предусматривает принятие разработчиком окончательного решения о способах реализации создаваемой базы. Поэтому физическое проектирование обязательно производится с учетом всех особенностей используемой СУБД. Между этапами физического и логического проектирования всегда имеется определенная обратная связь, поскольку решения, принятые на этапе физического проектирования с целью повышения производительности разрабатываемой системы, могут потребовать определенной корректировки логической модели данных.

1.2 Концептуальное (инфологическое) проектирование

Инфологический подход не предоставляет формальных способов моделирования реальности, однако он закладывает основы методологии проектирования БД.

Первой задачей инфологического проектирования является определение предметной области системы, позволяющее изучить информационные потребности будущих пользователей. Другая задача этого этапа — анализ предметной области, который призван сформировать взгляд с позиций сообщества будущих пользователей базы данных. Анализ предметной области выполняется разработчиком базы данных совместно со специалистом в данной предметной области.

Инфологическая модель предметной области представляет собой описание ее структуры и динамики, характера информационных потребностей пользователей системы в терминах, понятных пользователю и независимых от реализации системы. Более того, инфологическая модель не должна зависеть от модели данных, которая будет использована при создании базы данных.

Обычно описание предметной области выражается в терминах не отдельных объектов и связей между ними, а их типов, связанных с ними ограничений целостности и тех процессов, которые приводят к переходу предметной области из одного состояния в другое. Такое описание может быть представлено любым способом, допускающим однозначную интерпретацию.

Существуют разные подходы к инфологическому проектированию.

1. Функциональный подход к проектированию базы данных.

Этот метод является наиболее распространённым. Он реализует принцип «от задач» и применяется в том случае, когда известны функции некоторой группы лиц или комплекса задач, для обслуживания информационных потребностей которых, создаётся рассматриваемая база данных.

2. Предметный подход к проектированию базы данных.

Предметный подход применяется в тех случаях, когда у разработчиков есть чёткое представление о самой предметной области и о том, какую именно информацию они хотели бы хранить в базе данных, а структура запросов не определена или определена не полностью. Тогда основное внимание уделяется исследованию предметной области и наиболее адекватному её отображению в базе данных с учётом самого широкого спектра информационных запросов к ней.

3. Проектирование с использованием метода «сущность-связь».

Метод «сущность-связь» (Entity-Relation, ER-method) был разработан в 1976 г. П. Ченом (Chen P.P.). Он является комбинацией двух предыдущих и обладает достоинствами обоих. Этап инфологического проектирования начинается с моделирования программного обеспечения. Проектировщик разбивает предметную область на ряд локальных областей, каждая из которых (в идеале) включает в себя информацию, достаточную для обеспечения информационных потребностей одной группы будущих пользователей или решения отдельной задачи. Каждое локальное представление моделируется отдельно, а затем выполняется их объединение. Выбор локального представления зависит от масштабов предметной области. Обычно она разбивается на локальные области так, чтобы каждая из них соответствовала отдельному внешнему приложению и содержала 6−7 сущностей (т.е. объектов, о которых в системе будет накапливаться информация).

[Хомоненко А. Д. Базы данных: учеб. для вузов / В. М. Циганков, М. Г. Мальцев. СПб.: Корона Принт, 2011 г. — 736 с.

Энсор, Д.; Стивенсон, Й. Проектирования баз данных; Киев: BHV, 2010. — 560 c.]

Объекты, существование которых не зависит от существования других сущностей, называются базовыми, остальные — зависимыми.

Для каждой сущности определяются атрибуты, которые делятся на два типа: идентифицирующие и описательные. Идентифицирующие атрибуты входят в состав ключа (или ключей) и позволяют однозначно распознавать экземпляры сущности. Первичный ключ базового объекта не может содержать неопределённые значения атрибутов (null). Первичный ключ должен включать в свой состав минимально необходимое для идентификации количество атрибутов. Описательные атрибуты заключают в себе свойства сущности, интересующие пользователей.

Спецификация атрибута состоит из его названия, указания типа данных и описания ограничений целостности — множества значений, которые может принимать данный атрибут.

Далее осуществляется спецификация связей: выявляются связи между сущностями внутри локального представления. Каждая связь именуется. Кроме спецификации связей типа «сущность — сущность», выполняется спецификация связей типа «сущность — атрибут» и «атрибут — атрибут» для отношений между атрибутами, которые относятся к одной и той же сущности или к одной и той же связи типа «сущность — сущность».

При объединении проектировщик может формировать конструкции, производные по отношению к тем, которые были использованы в локальных представлениях. Цель введения подобных абстракций:

· объединение в единое целое фрагментарных представлений о различных свойствах одного и того же объекта;

· введение абстрактных понятий, удобных для решения задач системы, установление их связи с более конкретными понятиями модели;

· образование классов и подклассов подобных объектов (например, класс «изделие» и подклассы типов изделий, производимых на предприятии).

При небольшом количестве локальных областей (не более пяти) объединение выполняется за один шаг. В противном случае обычно выполняют бинарное объединение. При объединении представлений используют три основополагающие концепции:

1. Идентичность. Два или более элементов модели идентичны, если они имеют одинаковое семантическое значение.

2. Агрегация. Позволяет рассматривать связь между элементами как новый элемент. Например, связь экзамен между сущностями студент, дисциплина, преподаватель может быть представлена агрегированной сущностью экзамен с атрибутами Название дисциплины, Фамилия преподавателя, Фамилия студента, Оценка.

3.Обобщение. Позволяет образовывать многоуровневую иерархию обобщений.

На этапе объединения необходимо выявить и устранить все противоречия. Например, одинаковые названия семантически различных объектов или связей или несогласованные ограничения целостности на одни и те же атрибуты в разных приложениях. Устранение противоречий вызывает необходимость возврата к этапу моделирования локальных представлений с целью внесения в них соответствующих изменений.

По завершении объединения результаты проектирования представляют собой концептуальную инфологическую модель предметной области. Модели локальных представлений — это внешние инфологические модели.

На этапе анализа предметной области также решаются следующие задачи:

1. Определение правил (ограничений целостности), которым должны удовлетворять сущности предметной области, атрибуты сущностей и связи между ними. Часть этих правил реализуется в схеме базы данных (возможности реализации ограничений целостности в схеме базы данных определяются моделью данных той СУБД, которая будет выбрана для реализации проекта). Остальные правила реализуются с помощью программного обеспечения.

2. Выделение групп пользователей системы. Каждая группа выполняет определённые задачи и обладает разными правами доступа к системе.

3. Создание внешней спецификации тех функций (процессов), которые эта система будет выполнять. Например, для той же библиотечной системы это задачи поиска книг (по определённым критериям), выдачи/приёма книг, определение списка должников и т. д.

4. Семантическая объектная модель.

Команда разработчиков опрашивает пользователей, анализирует предоставленные ими отчеты, формы и запросы и на их основе строит пользовательскую модель данных. Эта модель данных в дальнейшем воплощается в структуре базы данных.

Конкретная форма модели данных зависит от конструкций, используемых для ее построения. Если используется модель «сущность-связь», конструируемая модель будет содержать сущности и связи. В случае использования семантической объектной модели конструируемая модель будет содержать семантические объекты и связанные с ними конструкции, которые описываются в данной главе.

Модель «сущность-связь» и семантическая объектная модель подобны двум различным линзам, сквозь которые смотрят разработчики баз данных при изучении и документировании пользовательских данных. Обе линзы действуют, и обе они, в конечном счете, воплощаются в определенной структуре базы данных. Однако, поскольку эти линзы не одинаковы и создают разные изображения, структура баз данных, полученных с их помощью, может несколько различаться.

Анализ данной системы учета пропусков производится на базе Карасукского педагогического колледжа. В данном учебном заведении подсчет пропущенных занятий ведется в каждой группе индивидуально, при помощи рапортичек посещаемости, которую ведёт староста группы. Рапортичка включает в себя фамилию учащегося и количество пропущенных им занятий.

Учет посещаемости студентов__________группы Число______месяц__________ день недели________________

На обратной стороне заполняется общее количество пропусков у каждого — указывая «Н». и «У».

Староста____________(________________)

В листе посещаемости необходимо указывать уважительные пропуски или неуважительные, если старосте или классному руководителю известна уважительная причина отсутствия учащегося, то пропуски вносятся в рапортичку, как уважительные, если нет, то они заведомо считаются неуважительными и по мере того, как учащиеся приносят документ, удостоверяющий невозможность своего присутствия на занятиях, данные меняются. После заполнения рапортичку отдают заместителю директора по учебной работе, который, в свою очередь, заносит данные в базу, по которой подводятся итоги.

Итого по группе Таблица 2.1 пропуски группы за период

Таблица 2.2 Сущности

Таблица 2.3 Связи сущностей

Рисунок 1.1 ER-диаграмма базы данных учёта пропусков

1.3 Логическое проектирование БД

На этапе логического проектирования разрабатывается логическая структура БД, соответствующая инфологической модели предметной области. Решение этой задачи существенно зависит от модели данных, поддерживаемой выбранной системе управления. Результатом выполнения этого этапа являются схемы баз данных концептуального и внешнего уровней архитектуры, составленные на языках определения данных (DDL) выбранной СУБД.

[Дейт К.Дж.

Введение

в системы баз данных. — 8-е изд. — М.: «Вильямс», 2011. — 1328 с. ]

Логическое проектирование базы данных для реляционной модели:

· Создание и проверка локальной логической модели данных на основе представления о предметной области каждого из типов пользователей.

· Устранение особенностей локальной логической модели, несовместимых с реляционной моделью (необязательный этап).

· Определение набора отношений исходя из структуры локальной логической модели данных.

· Проверка отношений с помощью правил нормализации.

· Проверка соответствия отношений требованиям пользовательских транзакций.

· Определение требований поддержки целостности данных.

· Обсуждение разработанных локальных логических моделей данных с конечными пользователями.

· Создание и проверка глобальной логической модели данных.

· Слияние локальных логических моделей данных в единую глобальную модель данных.

· Проверка глобальной логической модели данных.

· Проверка возможностей расширения модели в будущем.

· Обсуждение глобальной логической модели данных с пользователями.

Особенности проектирования реляционных баз данных

· Каждое отношение соответствует одной сущности предметной области и в него вносятся все атрибуты объекта, связанные с ним отношением 1:1.

· Связь типа 1: n реализуется с помощью внешнего ключа.

· Для реализации связи типа n: m между сущностями вводится дополнительное отношение, содержащее комбинации первичных ключей связанных отношений и атрибуты (свойства) этой связи.

Проектирование схемы БД должно решать задачи минимизации дублирования данных и упрощения процедур их обработки и обновления. При неправильно спроектированной схеме баз данных могут возникнуть аномалии выполнения операций включения, удаления и модификации данных. Эти аномалии обусловлены отсутствием средств явного представления типов множественных связей между объектами предметной области и неразвитостью средств описания ограничений целостности на уровне реляционной модели данных.

Выбор ключей.

Первичные ключи

Возможность однозначно определить строку в таблице является основополагающей для реляционного проектирования. Делается это путем назначения первичного ключа. Никакие две строки в одной таблице не могут иметь одинаковый первичный ключ. В модели, поскольку она является лишь моделью, мы, может быть, и не сможем уловить отличительные черты каждого экземпляра сущности, однако реляционная теория говорит, что каждая таблица должна иметь первичный ключ. Иногда приходится вводить искусственный идентификатор в форме суррогатного ключа. Это может быть просто числовая последовательность, которая начинается с единицы, обозначающей первую создаваемую строку, и увеличивается на единицу для каждой последующей строки. В некоторых случаях используются абсолютно бессмысленные для большинства людей последовательности чисел или букв. Например, номера банкнот.

Иногда бывает, что предполагаемый первичный ключ таблицы оказывается не достаточно уникальным, когда мы начинаем учитывать реализацию его в реляционной базе данных. Возьмем, к примеру, событие. Дата и время наступления события будут гарантированно уникальными, если определить их с достаточной степенью точности. Однако в базе данных Oracle время хранится с точностью до секунды, а наличие двух событий в секунду — явление весьма распространенное.

В некоторых ситуациях создают суррогатный ключ даже тогда, когда таблица имеет естественный первичный ключ. Это чисто проектное решение, принимаемое из практических соображений. Обычно так делают в случаях, если естественный ключ очень длинный или состоит из большого числа компонентов (столбцов).

Неуникальные (или почти уникальные) ключи

Ключ может быть уникальным в концептуальной модели, но при преобразовании ее в логическую модель, которую мы хотим реализовать в виде таблиц, он теряет свою уникальность. Например, если в ключ была включена метка даты и времени, то время округляется до секунд, поэтому, если мы создадим за одну секунду две строки с идентичными ключами, никакая метка времени не поможет.

Типы данных

Любые данные, используемые в программировании, имеют свои типы данных.

Реляционная модель требует, чтобы типы используемых данных были простыми.

Простые, или атомарные, типы данных не обладают внутренней структурой. Атомарные, значит, имеющие значения, которые рассматриваются как неделимые. Данные такого типа называют скалярами. К простым типам данных относятся следующие типы:

Логический.

Строковый.

Численный.

Различные языки программирования могут расширять и уточнять этот список, добавляя такие типы как:

Целый.

Вещественный.

Дата.

Время.

Денежный.

Перечислимый.

Интервальный.

Конечно, понятие атомарности относительно. Так, строковый тип данных можно рассматривать как одномерный массив символов, а целый тип данных — как набор битов. Важно лишь то, что при переходе на такой низкий уровень теряется семантика (смысл) данных. Если строку, выражающую, например, фамилию сотрудника, разложить в массив символов, то при этом теряется смысл такой строки как единого целого.

Нормализация отношений

В рамках реляционной модели данных Э. Ф. Коддом был разработан аппарат нормализации отношений и предложен механизм, позволяющий любое отношение преобразовать к третьей нормальной форме. Нормализация схемы отношения выполняется путём декомпозиции схемы.

[Туманов, В. Е. Основы проектирования реляционных баз данных; Бином, 2012. — 420 c.]

Первая нормальная форма (1НФ).

Отношение находится в 1НФ, тогда и только тогда, когда все поля содержат атомарные значения.

Таблица 2.4 Первая НФ

Вторая нормальная форма (2НФ).

Отношение находится во 2НФ, если оно приведено к 1НФ и каждый неключевой атрибут функционально полно зависит от составного ключа.

Для того чтобы привести отношение ко 2НФ, нужно:

построить его проекцию, исключив атрибуты, которые не находятся в функционально полной зависимости от составного ключа;

построить дополнительные проекции на часть составного ключа и атрибуты, функционально зависящие от этой части ключа.

Таблица 2.5 Вторая НФ

Третья нормальная форма (3НФ).

Отношение находится в 3НФ, если она удовлетворяет определению 2-ой НФ и не одно из её неключевых полей не зависит функционально от любого другого неключевого поля.

Таблица 2.5 Студенты

Рисунок 1.2 DDL диаграмма базы данных учёта пропусков студентов

1.4 Физическое проектирование БД

Этап физического проектирования заключается в увязке логической структуры базы данных и физической среды хранения с целью наиболее эффективного размещения данных, т. е. отображении логической структуры БД в структуру хранения. Решается вопрос размещения хранимых данных в пространстве памяти, выбора эффективных методов доступа к различным компонентам «физической» базы данных. Результаты этого этапа документируются в форме схемы хранения на языке определения хранимых данных. Принятые на этом этапе решения оказывают определяющее влияние на производительность системы.

Фактически проектирование БД имеет итерационный характер. В процессе функционирования системы становится возможным измерение её реальных характеристик, выявление «узких» мест. И если система не отвечает предъявляемым к ней требованиям, то обычно она подвергается реорганизации, т. е. модификации первоначально созданного проекта.

Методология физического проектирования базы данных Создание локальной физической модели данных предприятия на основе представления о предметной области каждого отдельного типа пользователей.

Физическая модель данных дополняется документацией, создаваемой в процессе разработки этой модели, включая словарь данных. Подробные сведения о сопроводительной документации, которая может быть подготовлена на различных этапах, приведены при описании этих этапов.

[Хаббард, Дж. Автоматизированное проектирование баз данных; М.: Мир, 2011. — 453 c.]

CREATE DATABASE `absences`;

CREATE TABLE IF NOT EXISTS `invalids` (

`s_id` int (10) unsigned NOT NULL,

`month` tinyint (3) unsigned NOT NULL,

`day` tinyint (3) unsigned NOT NULL,

`value` tinyint (3) unsigned NOT NULL,

PRIMARY KEY (`s_id`,`month`,`day`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Листинг 1.1 Запрос на создание таблицы invalids (неуважительные)

CREATE TABLE IF NOT EXISTS `students` (

`id` int (10) unsigned NOT NULL AUTO_INCREMENT,

`first_name` varchar (30) COLLATE utf8_unicode_ci NOT NULL,

`last_name` varchar (32) COLLATE utf8_unicode_ci NOT NULL,

`group` varchar (5) COLLATE utf8_unicode_ci NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=8;

Листинг 1.2 Запрос на создание таблицы students (студенты)

CREATE TABLE IF NOT EXISTS `valids` (

`s_id` int (10) unsigned NOT NULL,

`month` tinyint (3) unsigned NOT NULL,

`day` tinyint (3) unsigned NOT NULL,

`value` tinyint (3) unsigned NOT NULL,

PRIMARY KEY (`s_id`,`month`,`day`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Листинг 1.3 Запрос на создание таблицы valids (уважительные)

ALTER TABLE `invalids`

ADD CONSTRAINT `invalids_s_id_foreign` FOREIGN KEY (`s_id`) REFERENCES `students` (`id`);

Листинг 1.4 Запрос на создание внешнего ключа для таблицы invalids (неуважительные)

ALTER TABLE `valids`

ADD CONSTRAINT `valids_s_id_foreign` FOREIGN KEY (`s_id`) REFERENCES `students` (`id`) ON DELETE CASCADE;

Листинг 1.5 Запрос на создание внешнего ключа для таблицы valids (уважительные)

SELECT students. group,

v_tab_month.v_month,

i_tab_month.i_month,

v_tab_per.v_per,

i_tab_per.i_per,

COUNT (`students`.`id`) AS `members`

FROM students

LEFT JOIN

(SELECT `students`.`group`, SUM (`valids`.`value`) AS `v_month`

FROM students LEFT JOIN valids WHERE month = ?

GROUP BY students. group) AS v_tab_month

ON v_tab_month.group = students. group

LEFT JOIN

(SELECT `students`.`group`, SUM (`invalids`.`value`) AS `i_month`

FROM students LEFT JOIN invalids WHERE month = ?

GROUP BY students. group) AS i_tab_month

ON i_tab_month.group = students. group

LEFT JOIN

(SELECT `students`.`group`, SUM (`valids`.`value`) AS `v_per`

FROM students LEFT JOIN valids WHERE month >=? AND month <= ?

GROUP BY students. group) AS v_tab_per

ON v_tab_per.group = students. group

LEFT JOIN

(SELECT `students`.`group`, SUM (`invalids`.`value`) AS `i_per`

FROM students LEFT JOIN invalids WHERE month >=? AND month <= ?

GROUP BY students. group) AS i_tab_per

ON i_tab_per.group = students. group

GROUP BY students. group

Листинг 1.6 Запрос на выборку результатов по группам

REPLACE INTO valids VALUES (?,?,?,?);

REPLACE INTO invalids VALUES (?,?,?,?);

Листинг 1.7 Запрос на вставку или обновление пропусков.

DELETE FROM valids WHERE s_id =? AND month =? AND day = ?;

DELETE FROM valids WHERE s_id =? AND month =? AND day = ?;

Листинг 1.8 Запрос на удаление пропусков

DELETE FROM students WHERE id = ?;

Листинг 1.9 Запрос на удаление студента

UPDATE students SET first_name = ?, last_name = ?, group =? WHERE id = ?;

Листинг 1.10 Запрос на изменение данных о студенте

INSERT INTO students VALUES (NULL, ?, ?, ?);

Листинг 1.11 Запрос на добавление нового студента В процессе проектирования базы данных была проанализирована предметная область, на этапе концептуального проектирования была построена ER-диаграмма базы данных учёта пропусков, а на этапе логического проектирования — DDL диаграмма базы данных учёта пропусков студентов. В процессе выполнения данных операций были разработаны три таблицы. Одна для хранения данных по учащимся, две другие — для хранения пропусков. В результате физического проектирования базы данных были построены запросы для работы с таблицами уважительных и неуважительных пропусков.

2. РАЗРАБОТКА СИСТЕМЫ УЧЁТА ПРОПУСКОВ УЧАЩИХСЯ

2.1 Инструментальные средства разработки

Данная система является веб-приложением, для её работы необходим веб-сервер и веб-браузер в качестве клиента.

Ядром системы выступает фреймворк Laravel 4 который будет обеспечивать взаимодействие с базой данных, выполнять маршрутизацию запросов и вывод результата в браузер.

В качестве тестового веб сервера использовался WAMP. Он содержит все необходимые инструменты для разработки.

Для написания листингов использовалась IDE NetBeans 8.

Минимальные системные требования.

Процессор Pentium 333

ОЗУ 32 МБ

HDD 200 МБ

Apache 2.4.4

MySQL 5.6.12

PHP 5.3.4

Установка Запустить установщик WAMP server, следовать инструкциям. После установки скопировать продукт в папку www/propuski WAMP сервера.

Выполнить команду

php composer. phar update

Произойдёт загрузка дополнительных компонентов продукта.

В системном трее, щелкнуть левой кнопкой мыши по значку WAMP сервера. Выбрать Apache > Alias directories > Add an alias. В появившемся окне ввести название псевдонима, например: propuski. И путь к папке с продуктом, например: c:/wamp/www/propuski/public. После данных манипуляций продукт будет доступен из окна браузера по адресу http://localhost/propuski.

2.2 Интерфейс программы

Главная страница данной программы представляет собой несколько колонок. В заголовке указан номер группы, нажав на который программа переходит на страницу c более подробными данными. Далее располагается таблица с количеством пропущенных занятий за месяц и за период. Затем указывается количество человек в данной группе, а ниже таблица с расчетом пропусков на одного учащегося за месяц и период.

Рисунок 2.1 Главная страница На каждой странице отображается панель инструментов, которая содержит следующие пункты:

— Пропуски — позволяет вернуться на главный экран.

Рисунок 2.2 Пропуски

— Редактор групп — запускает мастер редактирования группы, который позволяет добавлять, удалять, изменять фамилию имя учащихся.

Рисунок 2.3 Редактор групп

— Период за который нужно рассчитать пропуски. Данный пункт состоит из двух колонок, начало периода и конец периода, нажав на которые, выводится список месяцев.

Рисунок 2.4 Период На странице, более развернуто, отображающей пропуски какой — либо группы, на панели инструментов появляется пункт «быстрая вставка». Этот заголовок позволяет добавлять учащихся в группу.

Рисунок 2.5 Быстрая вставка Также на данной странице есть боковая панель, на которой отображен список всех групп учебного заведения для быстрой навигации.

Рисунок 2.6 Список групп

Основную часть занимает отчет по пропускам каждого студента. Которые представляют из себя панель, состоящую из заголовка (с фамилией именем учащегося) и таблицей пропусков.

Рисунок 2.7 Отчет по пропускам каждого студента

[Минаси М. Графический интерфейс пользователя: секреты проектирования. — М.: Мир, 2010. — 453 с.]

[Мандел Т. Дизайн интерфейсов. — М.: ДМК Пресс, 2010. — 210 с.]

2.3 Разработка программной части сервера и клиента

Предварительная настройка Laravel

Для подключения к базе данных необходимо указать адрес нахождения сервера и тип системы управления базами данных. Данные по настройке подключения находятся appconfigdatabase.php.

Указываем тип базы данных в параметре default=> mysql

Подключение настраивается в параметре connections и разделе mysql

`host'=>'localhost' // база данных находится на том же компьютере, что и программа.

`database' => `absences'

Для формирования пустых таблиц настроим миграции, для этого введём команды в командной строке

Php artisan migrations: create_table_students

Php artisan migrations: create_table_valids

Php artisan migrations: create_table_invalids

После чего в папке databasemigrations появятся три файла, каждый для своей таблицы.

Листинг 2.1 Скрипт создания таблицы учеников

public function up ()

{

Schema:create ('students', function ($table)

{

$table->increments ('id');

$table->string ('first_name', 30);

$table->string ('last_name', 32);

$table->string ('group', 5);

});

}

Листинг 2.2 Скрипт создания таблицы уважительных пропусков

public function up ()

{

Schema:create ('valids', function ($table)

{

$table->integer ('s_id')->unsigned ();

$table->foreign ('s_id')->references ('id')->on ('students')->onDelete ('cascade');

$table->tinyInteger ('month')->unsigned ();

$table->tinyInteger ('day')->unsigned ();

$table->tinyInteger ('value')->unsigned ();

$table->primary (array ('s_id', 'month', 'day'));

});

}

Листинг 2.3 Скрипт создания таблицы неуважительных пропусков

public function up ()

{

Schema:create ('valids', function ($table)

{

$table->integer ('s_id')->unsigned ();

$table->foreign ('s_id')->references ('id')->on ('students')->onDelete ('cascade');

$table->tinyInteger ('month')->unsigned ();

$table->tinyInteger ('day')->unsigned ();

$table->tinyInteger ('value')->unsigned ();

$table->primary (array ('s_id', 'month', 'day'));

});

}

После написания скриптов необходимо выполнить команду

Php artisan migrate

В результате в базе данных будут созданы три таблицы.

Маршрутизация.

Маршрутизация отвечает за отправку запросов на сервер и выдачу данных на этот запрос.

За выдачу маршрутов отвечает файл routes. php, находящийся в папке app (приложение 1).

Файл маршрутизации сравнивает запросы указанные программой клиентом со своими. В случае совпадения запускает метод связанного этим запросом контролёра.

В данной программе были определены следующие маршруты:

Getзапросы на выдачу данных:

· /{period?} - запрос на выдачу данных о всех группах. Параметр period имеет формат [0−9]{2}-[0−9]{2} и отправляет на сервер период, за который нужно получить данные. «?» — означает, что данный параметр может указываться или нет.

· /group/{no}/{period} - запрос на выдачу данных по конкретной группе. Номер группы передается в параметре no. Параметр период, как в предыдущем запросе.

Post — запросы на вставку данных:

· /api/addpass — запрос на вставку пропусков. Данные по пропускам передаются в закрытом виде.

· /group/{no}/{period} - запрос на вставку ученика в указанную группу.

· /api/addstud — запрос на асинхронную вставку ученика.

Put — запросы на изменение данных:

· /api/{sid} - запрос на изменение данных об ученике.

· /api/group/migrate — запрос на перевод учеников на один год.

Delete — запросы на удаление:

· /api/{sid} - запрос на удаление указанного пользователя.

Контролёр

Контролёр используется для связи данных с представлениями. В данном продукте два контролёра: BaseController. php (приложение 2), GroupController. php (приложение 3), которые находятся в папке app/controllers.

BaseController — базовый контролёр, содержит общие методы для других контролёров.

__construct — метод запускается автоматически при инициализации класса, предварительно устанавливает период и формирует список групп.

getGroupListOnly — выдает сформированный список групп.

periodEncode — переделывает период под формат для хранения в базе данных.

periodDecode — переделывает период из формата бд в обычный формат.

setPeriod — устанавливает новый период.

setViewPeriod — передает данные о дате и периоде в представление.

switchYear — устанавливает год относительно текущего.

getDays — формирует календарь для текущего месяца.

GroupController — основной контролёр, отвечает за выдачу данных для всех маршрутов.

Index — отображает главный экран.

getAbsencesList — подсчитывает сумму пропусков по каждой из групп.

One — выводит список группы и пропусков по каждому ученику.

getStudentsInGroup — выводит список студентов.

getValids — выводит список уважительных пропусков для указанного ученика.

getInvalids — выводит список неуважительных пропусков для указанного ученика.

getValidsSum — подсчитывает сумму уважительных пропусков для указанного ученика.

getInvalidsSum — подсчитывает сумму неуважительных пропусков для указанного ученика.

getPeriodSum — подсчитывает сумму пропусков за период для указанного ученика.

addPass — добавляет пропуски за один день.

studentFastAdd — быстрая вставка нового ученика.

replasePass — заменяет пропуски указанного студента за день.

Модель — представлениеобъекта базы данных в виде класса.

Используется четыре модели: group. php, invalids. php, students. php, valids. php, которые находятся в папке app/models.

Group — модель оперирует данными из таблицы students и является расширением стандартной модели Eloquent, которая стилизует запросы к базе данных.

scopePrepareListOnly — сформирует запрос на выборку уникальных значений из таблицы students по полю group.

scopeGetAll — формирует запрос к базе данных на подсчет пропусков за указанный период по группам.

createView — используется для формирования представлений к предыдущему методу.

Invalids, valids — классы также являются расширением модели Eloquent и отвечают за обмен данными в таблицах invalids и valids, не содержат в себе никаких расширяющих методов.

Students — работает также, как предыдущие модели, но отвечает за таблицу students.

Представления.

Представления используются для визуализации данных в удобном формате для пользователя. За визуализацию отвечают три файла:

master.blade.php — является шаблоном, задает общие элементы для всех представлений (приложение 4).

group, blade. php — выводит таблицы пропусков для одной группы (приложение 5).

groups.blade.php — выводит суммы пропусков по группам (приложение 6).

Все представления написаны на языке HTML с вставками PHP кода.

За стилистическую составляющую отвечает шаблонизатор Bootstrap 3.1.1

2.4 Руководство пользователя

При первом запуске программы необходимо создать группу. Любая группа существует, пока в ней есть учащиеся. Список учеников формируется с помощью редактора групп.

Рисунок 2.8 Редактор групп В поле Группа находится её название, а в поле Добавить ученика фамилия и имя ученика, который добавляется в данную группу. Добавляя следующего учащегося вводить название группы не надо.

Чтобы добавить ученика в уже существующую группу нужно воспользоваться выпадающим списком справа от поля Группа, либо использовать форму быстрой вставки на экране редактирования пропусков.

После того, как создана группа, информация о ней появится на главном экране.

Для дальнейшей работы нужно щёлкнуть по заголовку группы, появится экран редактирования пропусков.

Рисунок 2.9 Редактирование пропусков Все пропуски вносятся в соответствующие поля таблиц. Данные сохраняются автоматически.

Для печати отчетов достаточно на нужном экране вызвать мастер печати браузера нажатием клавиши Ctrl+P, либо выбрать Сервис — Печать на панели инструментов браузера.

Рисунок 2.10 Печать отчётов

ЗАКЛЮЧЕНИЕ

В процессе работы над выпускной квалификационной работой была создана база данных «учета пропусков учащихся». В результате проведена следующая работа:

Проведен анализ предметной области и сформулированы требования к базе данных;

На основе сформулированных требований к базе данных разработана инфологическая модель базы данных, результатом которой является диаграмма сущность — связь;

На основе инфологической модели спроектирована физическая модель базы данных, результатом которой является схема данных;

Рассмотрена СУБД, в которой выполнялось создание базы данных;

Спроектированы таблицы в режиме SQL;

Спроектированы запросы в режиме SQL.

В процессе создания базы данных, также был изготовлен программный продукт — система учета пропусков учащихся. Данная программа имеет множество преимуществ:

Значительно упрощает работу по ведению пропусков учеников;

Полностью замещает кропотливую работу с бумажной документацией;

Выдает полный отчет по пропускам за определенный период времени;

1. Аткинсон, Леон MySQL. Библиотека профессионала; М.: Вильямс, 2013. — 624 c.

2. Грабер, Мартин SQL. Справочное руководство; М.: Лори; Издание 2-е, 2011. — 354 c.

3. David Cochran Twitter Bootstrap Web Development How-To. — Packt, 2012. — 68 с.

4. Грабер, Мартин Понимание SQL; М.: Лори, 2012. — 125 с.

5. Дейт К. Дж.

Введение

в системы баз данных. — 8-е изд. — М.: «Вильямс», 2011. — 1328 с.

6. Дюбуа, Поль MySQL; М.: Вильямс; Издание 2-е — Москва, 2010. — 185 c.

7. Каба М. MySQL; СПб.: Питер, 2011. — 113 с.

8. Коннолли Т., Бегг К. Базы данных. Проектирование, реализация и сопровождение. Теория и практика. — 3-е изд. — М.: Вильямс, 2012. — 1436 с.

9. Кузнецов Максим, Симдянов Игорь MySQL 5; БХВ-Петербург — Москва, 2010. — 502 c.

10. Кузнецов С. Д. Основы баз данных. — 2-е изд. — М.: Интернет-Университет Информационных Технологий; БИНОМ. Лаборатория знаний, 2011. — 484 с.

11. Мандел Т. Дизайн интерфейсов. — М.: ДМК Пресс, 2010. — 210 с.

12. Минаси М. Графический интерфейс пользователя: секреты проектирования. — М.: Мир, 2010. — 453 с.

13. Паутов А. Документация по MySQL. — М.: ДМК Пресс, 2012. — 157 с.

14. Shawn McCool Laravel Starter. — Packt Publishing, 2012. — 64 с.

15. Taylor Otwell Laravel: From Apprentice To Artisan; Leanpub, 2013. — 67 с.

16. Туманов, В. Е. Основы проектирования реляционных баз данных; Бином, 2012. — 420 c.

17. Хаббард, Дж. Автоматизированное проектирование баз данных; М.: Мир, 2011. — 453 c.

18. Хомоненко А. Д. Базы данных: учеб. для вузов / В. М. Циганков, М. Г. Мальцев. СПб.: Корона Принт, 2011 г. — 736 с.

19. Энсор, Д.; Стивенсон, Й. Проектирования баз данных; Киев: BHV, 2010. — 560 c.

20. Яргер, Р.Дж.; Риз, Дж.; Кинг, Т. MySQL: Базы данных для небольших предприятий и Интернета; СПб: Символ-Плюс, 2013. — 560 c.

21. Свободная общедоступная многоязычная универсальная энциклопедия: сайт организации «Фонд Викимедиа». [Электронный ресурс]. 2001. Дата обновления: 05.03.2013 URL: http://ru.wikipedia.org (дата обращения 05.03.2013).

22. Технологии баз данных: SQL, T-SQL, PL/SQL, реляционные БД: сайт компании Ionet Inc. [Электронный ресурс]. 2011. Дата обновления: 28.02.2013. URL: www.datasql.ru (дата обращения 28.02.2013).

23. THE PHP FRAMEWORK FOR WEB ARTISANS. [Электронный ресурс]. 2010. Дата обновления: 20.02.2013. URL: http://laravel.com (дата обращения 20.02.2013).

24. PHP: Hypertext Preprocessor. [Электронный ресурс]. 2009. Дата обновления: 18.03.2013. URL: http://php.net (дата обращения 18.03.2013).

25. The most popular front-end framework for developing responsive, mobile first projects on the web. [Электронный ресурс]. 2007. Дата обновления: 13.06.2013. URL: http://getbootstrap.com (дата обращения 13.06.2013).

ПРИЛОЖЕНИЯ

Приложение 1

Файл маршрутизации

Blade:setContentTags ('<%', '%>');

Blade:setEscapedContentTags ('<%%', '%%>');

Route:get ('/{period?}', 'GroupController@index')->

where ('period', '[0−9]{2}-[0−9]{2}');

Route:get ('/raw/{period?}',

'GroupController@rawIndex')->where ('period', '[0−9]{2}-[0−9]{2}');

Route:get ('/raw/grouplist', 'GroupController@rawGroupsList');

Route:get ('/group/{no}/{period?}',

'GroupController@one')->where ('period', '[0−9]{2}-[0−9]{2}');

Route:get ('/api/studentlist/{no?}', 'GroupController@studentList');

Route:post ('/api/addpass', 'GroupController@addPass');

Route:post ('/group/{no}/{period?}',

'GroupController@studentFastAdd')->

where ('period', '[0−9]{2}-[0−9]{2}');

Route:post ('/api/addstud', 'GroupController@addStud');

// Put

Route:put ('/api/{sid}', 'GroupController@editStud');

// /api/group/migrate

// Delete

Route:delete ('/api/students', 'GroupController@remStud');

// /api/{sid}

Приложение 2

Основной контролёр

class BaseController extends Controller {

private $groupListOnly;

protected $period;

protected $monthNames = array ('',

'Январь', 'Февраль', 'Март', 'Апрель', 'Май', 'Июнь', 'Июль',

'Август', 'Сентябрь', 'Октябрь', 'Ноябрь', 'Декабрь');

public function __construct ()

{

$this->groupListOnly = Group: prepareListOnly ()->

get ()->toArray ();

$this->period = array (9, date ('n'));

}

public function getGroupListOnly ()

{

return $this->groupListOnly;

}

public function periodEncode ($period)

{

$monthStruct = array ('', 5,6,7,8,9,10,11,12,1,2,3,4);

return array ($monthStruct[$period[0]],

$monthStruct[$period[1]]);

}

public function periodDecode ($period)

{

$monthStruct = array ('', 9,10,11,12,1,2,3,4,5,6,7,8);

return array ($monthStruct[$period[0]],

$monthStruct[$period[1]]);

}

public function setPeriod ($period)

{

$period = explode ('-', $period);

$this->period = array ((int)$period[0], (int)$period[1]);

}

public function setViewPeriod ()

{

View:share ('startMonthName',

$this->monthNames[$this->period[0]]);

View:share ('endMonthName',

$this->monthNames[$this->period[1]]);

View:share ('monthNames', $this->monthNames);

View:share ('monthStart', $this->period[0]);

View:share ('monthEnd', $this->period[1]);

View:share ('groupList',

Group:prepareListOnly ()->get ()->toArray ());

}

public function switchYear ()

{

$currentYear = date ('Y');

if ($this->period[1] >= 9 &&

$this->period[1] <= 12 and

date ('n') >= 1 && date ('n') <= 8) {

$currentYear—;

}

if ($this->period[1] >=1 &&

$this->period[1] <= 12 and

date ('n') >= 9 && date ('n') <= 12) {

$currentYear++;

}

return $currentYear;

}

public function getDays ()

{

$dinmonth = cal_days_in_month (CAL_GREGORIAN,

$this->period[1], $this->switchYear ());

$dow = array ('вс','пн','вт','ср','чт','пт','сб');

for ($day = 1; $day <= $dinmonth; $day++) {

$ts = strtotime ($this->switchYear (). '-' .

$this->period[1]. '-'. $day);

if (!date ('w', $ts)) {

continue;

}

$days[$day] = $dow[date ('w', $ts)];

}

return $days;

}

protected function setupLayout ()

{

if (! is_null ($this->layout))

{

$this->layout = View: make ($this->layout);

}

}

}

Приложение 3

Дополнительный контролёр

class GroupController extends BaseController {

protected $layout = 'layouts.master';

public function index ($period = false)

{

View:share ('title', 'Пропуски');

View:share ('baseLink', '');

$out = array (

'absences' => $this->getAbsencesList ($period)

);

$this->setViewPeriod ();

$this->layout->content = View: make ('groups', $out);

}

public function rawIndex ($period = false)

{

return Response: json ($this->getAbsencesList ($period));

}

private function getAbsencesList ($period = false)

{

if ($period) {

$this->setPeriod ($period);

}

View:share ('currentMonth', $this->monthNames[$this->period[1]]);

return Group: getAll ($this->periodEncode ($this->period))->get ()->toArray ();

}

public function one ($no, $period = false)

{

View:share ('title', 'Пропуски');

View:share ('baseLink', 'group/'.$no.'/');

if ($period) {

$this->setPeriod ($period);

}

$this->setViewPeriod ();

$out = array (

'groupList' => Group: prepareListOnly ()->get ()->toArray (),

'current' => $no,

'basicData' =>Group:getAll ($this->periodEncode ($this->period))->where ('students.group', '=', $no)

->get ()->toArray (),

'studentsList' => $this->getStudentsInGroup ($no),

'monthDays' => $this->getDays (),

);

$this->layout->content = View: make ('group', $out);

}

public function getStudentsInGroup ($no)

{

$studentsInGroup = Students: select (array ('id', DB: raw ('CONCAT (last_name, «», first_name) AS name')))

->where ('group', '=', $no)->get ()->toArray ();

foreach ($studentsInGroup as $student) {

$out[$student['id']] = array (

'name' => $student['name'],

'valids' => $this->getValids ($student['id']),

'invalids' => $this->getInvalids ($student['id']),

'total' => $this->getPeriodSum ($student['id']),

);

}

return $out;

}

public function getValids ($studId)

{

$valids = Valids: select (array ('day', 'value'))

->where ('month', '=', $this->periodEncode ($this->period)[1])

->where ('s_id', '=', $studId)->get ()->toArray ();

$out['total'] = $this->getValidsSum ($studId);

if (empty ($valids)) {

return $out;

}

foreach ($valids as $pass) {

$out[$pass['day']] = $pass['value'];