Реализация серверной части. Язык запросов SQL.
Министерство образования и науки РФ
Государственное образовательное учреждение высшего
профессионального образования
«Ярославский государственный технический университет»
Кафедра «Кибернетики»
Отчет защищен
с оценкой
Преподаватель
______Харитонов А.П.
Разработка информационной системы, использующей базу данных.
Отчет о лабораторной работе
по курсу “Информационные технологии”
ЯГТУ 220301.65-003 ЛР
Отчет выполнил
студент гр. МА-24
______Петров К.О.
29.04.2012
2012
Задание
Разработать нормализованную структуру базы данных которая должна содержать информацию о преподавателях (ФИО, факультет и кафедра, должность, ученая степень, адрес, телефон, стаж и др.), о читаемых курсах (название, каким группам и в каком объеме (число лекционных, практических, лабораторных и др. занятий, форма контроля успеваемости и др.) читаются курсы).
Разработка структуры БД
Уточнение задач
1)Назначение : БД будет использоваться бухгалтерией для начисления з/п, отделом кадров для хранения информации о преподавателях, учебным отделом для составления расписания.
2)Функции : Каждый преподаватель может просматривать информацию о расписании занятий (название, число лекционных практических, лабораторных и др. занятий, форма контроля успеваемости и др.). Бухгалтерия может просматривать информацию о преподавателе для начисления з/п(должность, ученая степень, стаж, количество читаемых курсов и др.). Учебный отдел будет пользоваться БД для формирования расписания.
|
|
|
3)Информация : В БД должна содержаться информация о преподавателях: ФИО, факультет и кафедра, должность, ученая степень, адрес(город и текущий адрес проживания), телефон, стаж, табельный номер(для упрощения предоставления информации),а так же информация о читаемых курсах: название, число лекционных, практических, лабораторных занятий, форма контроля успеваемости.
Сущности
Выделим две сущности: преподаватели и курсы, которые представлены на рисунке 1. В каждой сущности выделим атрибуты, т.е. свойства, признаки каждой из сущностей.
Преподаватели Курсы
| ФИО Факультет Кафедра Должность Ученая степень Адрес Город Телефон Стаж Табельный № преподавателя |
| Предмет Группа Лекции Практические занятия Лабораторные работы Контроль Специльность Факультет Семестр Табельный № преподавателя |
|
|
|
Рисунок 1.
Каждый преподаватель ведет курсы у нескольких групп и эти группы посещают курсы по определенному предмету только у одного преподавателя. Связь «один ко многим».
Нормализация
Приведение к 1НФ.
1 требование (требование простой структуры).
Каждую сущность с атрибутами представил в виде множества строк. Объединённых ячеек нет.
2 требование (отсутствие одинаковых по смыслу полей). Повторяющихся полей нет.
3 требование (отсутствие составных полей).
В таблице «Курсы» есть одинаковые поля, такие как количество лекционных, лабораторных и практических часов, но не следует объединять их в единое поле «количество часов», так как в таблице будет много повторений.
4 требование (в табл. не должно быть одинаковых записей).
В таблице нет одинаковых записей.
Приведение ко 2НФ.
1) Определяем у каждой сущности первичный ключ:
Для сущности «Преподаватели» выберем в качестве первичного ключа «табельный номер преподавателя» (табельный номер уникален и не может повториться). А для сущности «Курсы» выберем «№ предмета» (номер сам по себе уникален и не может повториться).
2) Анализируем зависимости между полями таблицы:
|
|
|
Поле специальность, факультет и группа зависят от поля № группы, т.к. является свойством или атрибутом группы. Поэтому следует разбить таблицу на две части.
3) Разделение таблицы в соответствии с выделенными смысловыми зависимостями:
Вводим семантически незначащие первичные ключи: «Код города», «№ контроля», «№ группы»
Приведение к 3НФ.
Транзитивных зависимостей между полями таблиц нет. Таблицы приведены ко 2НФ, а значит приведены и к 3НФ. Рисунок 2.
Преподаватели Курсы Группы
| Табельный № преподавателя | № предмета | № группы | ||
| ФИО Кафедра Должность Стаж Факультет Ученая степень Адрес Телефон Код города |
| Предмет № группы Лекции Практические занятия Лабораторные работы Семестр № контроля Табельный № преподавателя |
| Группа Специальность Факультет |
Город Форма контроля
| Код города | № контроля | |
| Город | Контроль |
Рисунок 2.
|
|
|
Проектирование таблиц.
В итоге получилось 5 таблиц.
Таблица 1 «Преподаватели» :
| Название поля | Тип данных | Правило | |
| Tab_№ | Number(9) | PK | |
| FIO1 | Varchar2(50) | Обязательное поле | |
| Cafedra | Varchar2(30) | Обязательное поле | |
| Fakultet | Varchar2(30) | Обязательное поле | |
| Doljnost | Varchar2(30) | Обязательное поле | |
| Staj | Number(2) | >0 | |
| Uchenaya_Step | Varchar2(30) | ||
| Adres | Varchar2(50) | Обязательное поле | |
| Telefon | Number(30) | >0 | |
| Cod_goroda | Number(10) | FK | |
В таблице 1 будет храниться информация о преподавателях. Таблица имеет следующие поля:
Tab_№ - первичный ключ таблицы – должен быть уникальным и не превышать 9 знаков.
FIO1 – определяет ФИО преподавателя.
Cafedra – определяет кафедру на которой работает преподаватель.
Fakultet – определяет на каком факультете работает преподаватель.
Doljnost – определяет на какой должности работает преподаватель.
Staj – определяет стаж работы преподавателя. Не должен превышать 2 знаков.
Uchenaya_Step – определяет имеется ли у преподавателя ученая степень.
Adres – определяет адрес по которому проживает преподаватель (улица, дом, кв).
Telefon – определяет номер телефона преподавателя. Не должен превышать 30 цифр, т.к. телефонов может быть несколько (домашний и мобильный).
Cod_goroda – определяет код города в котором проживает преподаватель. Не должен превышать 10 знаков.
Используемые типы данных:
VARCHAR2(L) – символьная строка переменной длины с максимальной длиной L байт.
Number(P,S) – число, имеющее точность P и масштаб S.
Таблица 2 «Город» :
| Название поля | Тип данных | Правило |
| Cod_goroda | Number(10) | PK |
| Gorod | Varchar2(20) | Обязательное поле |
В таблице 2 будет храниться информация о городах. Таблица имеет следующие поля:
Cod_goroda – первичный ключ таблицы – должен быть уникальным и не превышать 10 знаков.
Gorod – определяет город в котором проживает преподаватель.
Используемые типы данных:
VARCHAR2(L) – символьная строка переменной длины с максимальной длиной L байт.
Number(P,S) – число, имеющее точность P и масштаб S.
Таблица 3 «Курсы»:
| Название поля | Тип данных | Правило | |
| №_predmet | Number(9) | PK | |
| Predmet | Varchar2(50) | Обязательное поле | |
| №_group | Number(9) | FK | |
| Lektions | Number(2) | >0 | |
| Practics | Number(2) | >0 | |
| Laboratorian | Number(2) | >0 | |
| Semestr | Number(2) | Обязательное поле, >0, <15 | |
| Tab_№ | Number(9) | FK | |
| №_kontrol | Number(9) | FK | |
В таблице 3 будет храниться информация о читаемых курсах. Таблица имеет следующие поля:
№_predmet - первичный ключ таблицы – должен быть уникальным и не превышать 9 знаков.
Predmet – определяет предмет который ведет преподаватель у группы.
№_group – определяет номер группы у которой ведет преподаватель данный предмет. Не должен превышать 9 знаков.
Lektions – определяет количество лекционных часов, читаемых группе по данному предмету. Не должен превышать 2 знаков.
Practics - определяет количество практических часов, проводимых у группы по данному предмету. Не должен превышать 2 знаков.
Laboratorian - определяет количество лабораторных часов, проводимых у группы по данному предмету. Не должен превышать 2 знаков.
Semestr – определяет в каком семестре читается данный предмет у группы. Не должен превышать 2 знаков.
Tab_№ - определяет табельный номер преподавателя, который ведет данный предмет у определенной группы. Не должен превышать 9 знаков.
№_kontrol – определяет номер контроля группы по данному предмету.
Используемые типы данных:
VARCHAR2(L) – символьная строка переменной длины с максимальной длиной L байт.
Number(P,S) – число, имеющее точность P и масштаб S.
Таблица 4 «Группы» :
| Название поля | Тип данных | Правило |
| №_group | Number(9) | PK |
| Group_g | Varchar2(9) | Обязательное поле |
| Specialnost | varchar2(50) | Обязательное поле |
| Fakultet1 | Varchar2(30) | Обязательное поле |
В таблице 4 будет храниться информация о группах. Таблица имеет следующие поля:
№_group - первичный ключ таблицы – должен быть уникальным и не превышать 9 знаков.
Group_g – определяет группу.
Specialnost – определяет специальность, которой обучают группу.
Fakultet1 – определяет факультет на котором обучается группа.
Используемые типы данных:
VARCHAR2(L) – символьная строка переменной длины с максимальной длиной L байт.
Number(P,S) – число, имеющее точность P и масштаб S.
Таблица 5 «Форма контроля» :
| Название поля | Тип данных | Правило |
| №_kontrol | Number(9) | PK |
| Kontrol | Varchar2(30) | Обязательное поле |
В таблице 5 будет храниться информация о контроле группы. Таблица имеет следующие поля:
№_kontrol - первичный ключ таблицы – должен быть уникальным и не превышать 9 знаков.
Kontrol – определяет контроль группы.
Используемые типы данных:
VARCHAR2(L) – символьная строка переменной длины с максимальной длиной L байт.
Number(P,S) – число, имеющее точность P и масштаб S.
Реализация серверной части. Язык запросов SQL.
Создание таблиц.
Создадим таблицу «Преподаватели»( KO_Prepodavateli):
Create table KO_Prepodavateli
(Tab_№ Number(9) constraint PK_Tab_№ Primary key,
FIO1 Varchar2(50) constraint nn_FIO1 NOT NULL,
Cafedra Varchar2(30) constraint nn_cafedra NOT NULL,
Fakultet Varchar2(30) constraint nn_fakultet NOT NULL,
Doljnost Varchar2(30) constraint nn_doljnost NOT NULL,
Staj Number(2) check (staj>0),
Uchenaya_Step Varchar2(30),
Adres Varchar2(50) constraint nn_Adres NOT NULL,
Telefon Number(30) check (telefon>0),
Cod_goroda number(10) constraint FK_cod_goroda references KO_Gorod(cod_goroda));
Create table KO_Prepodavateli – данный запрос создает таблицу «Преподаватели». Далее в скобках указывается, из каких полей будет состоять данная таблица и какой тип данных каждого из полей:
Поле Tab_№ является первичным ключом и имеет тип Number(9), т.е. число состоящее из 9 цифр.
Поле FIO1 имеет тип Varchar2(50), т.е. строка длиной 50 символов. Это поле не может быть пустым, т.к. у преподавателя обязательно должно быть ФИО.
Поле Cafedra имеет тип Varchar2(30), т.е. строка длиной 30 символов. Это поле не может быть пустым, т.к. преподаватель должен быть закреплен за какой-либо кафедрой в зависимости от своей специальности.
Поле Fakultet имеет тип Varchar2(30), т.е. строка длиной 30 символов. Это поле не может быть пустым, т.к. преподаватель должен быть закреплен за каким-либо факультетом, отведенным ему для обучения.
Поле Doljnost имеет тип Varchar2(30), т.е. строка длиной 30 символов. Это поле не может быть пустым, т.к. преподаватель взят на работу и это уже означает, что у него должна быть должность. Без должности работать невозможно.
Поле Staj имеет тип Number(2), т.е. строка состоящая из 2 цифр. Это поле не может быть отрицательным, т.к. стаж меньше нуля не бывает.
Поле Uchenaya_Step имеет тип Varchar2(30), т.е. строка длиной 30 символов.
Поле Adres имеет тип Varchar2(50), т.е. строка длиной 50 символов. Это поле не может быть пустым, т.к. у преподавателя в паспорте обязательно должно быть прописано место проживания.
Поле Telefon имеет тип Number(30), т.е. строка состоящая из 30 символов. Это поле не может быть отрицательным, т.к. телефон меньше нуля не может быть.
Поле Cod_goroda имеет тип number(10), т.е. строка состоящая из 10 цифр. Это оле не может быть пустым. Является внешним ключом, т.е. ссылка на соответствующее поле таблицы «Город».
Некоторые поля требуют установления ограничений целостности с помощью ключевого слова CONSTRAINT:
PRIMARY KEY – первичный ключ;
CHECK (<ЛВ>) – ограничение поля, где ЛВ – логическое выражение, определяющее это ограничение;
REFERENCES <ИТ> (<ПК>) – внешний ключ для связи с таблицей <ИТ> по ключу <ПК>;
FOREIGN KEY – внешний ключ, т.е. ссылка на соответствующее поле другой таблицы.
Если таких полей несколько, то они перечисляются в скобках через запятую после ключевого слова CONSTRAINT и указанного ограничения целостности. Данная операция проводится после перечисления полей.
Так как создание таблиц остальных таблиц существенно не отличается от создания таблицы «Преподаватели», то приводить подробное описание запросов для следующих таблиц не обязательно.
Создадим таблицу «Город»(KO_Gorod):
Сreate table KO_Gorod
(Cod_goroda number(10) constraint PK_cod_goroda Primary key,
Gorod Varchar2(20) constraint nn_Gorod not null);
Создадим таблицу «Курсы»(KO_curs):
Create table KO_curs
(№_predmet Number(9) constraint PR_№_predmet primary key,
Predmet varchar2(50) constraint nn_Predmet NOT NULL,
№_group number(9) constraint FK_№_group references KO_group(№_group),
lektions number(2) check (lektions>0),
practics number(2) check (practics>0),
laboratorian number(2) check (laboratorian>0),
Semestr number(2) constraint nn_Semestr NOT NULL check(semestr>0 and semestr<15),
tab_№ number(9) constraint FK_tab_№ references KO_Prepodavateli(Tab_№),
№_kontrol number(9) constraint FK_№_kontrol references KO_Form_Kontrol(№_kontrol));
Создадим таблицу «Группы»( KO_group):
Create table KO_group
(№_group number(9) constraint PK_№_group primary key,
group_g varchar2(9) constraint nn_group_g NOT NULL,
specialnost varchar2(50) constraint nn_specialnost NOT NULL,
fakultet1 Varchar2(30) constraint nn_fakultet1 NOT NULL);
Создадим таблицу «Форма контроля»( KO_Form_kontrol):
Create table KO_Form_kontrol
(№_kontrol number(9) constraint PK_№_kontrol primary key,
kontrol varchar2(30) constraint nn_kontrol not null);
Работа с данными.
Запросы SQL на заполнение таблиц данными выглядят следующим образом.
Заполнение таблицы KO_Form_Kontrol данными:
Для заполнения таблицы сначала вводится команда INSERT INTO, затем название таблицы, которую будем заполнять данными. Далее в скобках перечисляем названия всех полей таблицы. После скобки используем команду VALUES, где в скобках, в кавычках через запятую указываются значения полей:
Insert Into KO_Form_Kontrol(№_kontrol,kontrol)
values (44152,'zachet');
Insert Into KO_Form_Kontrol(№_kontrol,kontrol)
values (44445,'ekzamen');
Insert Into KO_Form_Kontrol(№_kontrol,kontrol)
values (123345,'zachet');
Insert Into KO_Form_Kontrol(№_kontrol,kontrol)
values (113222,'ekzamen');
Insert Into KO_Form_Kontrol(№_kontrol,kontrol)
values (99003,'zachet');
Остальные таблицы заполняются аналогично.
Заполнение таблицы KO_Gorod :
Insert Into KO_Gorod(Cod_goroda,Gorod)
values (4900,'Yaroslavl');
Insert Into KO_Gorod(Cod_goroda,Gorod)
values (4942,'Kostroma');
Insert Into KO_Gorod(Cod_goroda,Gorod)
values (2399,'Danilov');
Insert Into KO_Gorod(Cod_goroda,Gorod)
values (74522,'Moskva');
Insert Into KO_Gorod(Cod_goroda,Gorod)
values (90032,'Ekaterinburg');
Заполнение таблицы KO_Prepodavateli :
Insert Into KO_Prepodavateli(Tab_№,FIO1,Cafedra,Fakultet,Doljnost,Staj, Uchenaya_Step,Adres,Telefon,cod_goroda)
values (117455000,'Roitenberg Vladimir Shleimovich','Vushaya Matematica', 'Mashinostroitelniy','Prepodavatel',2,'docent','Malanova 6 2',456100,4900);
Insert Into KO_Prepodavateli(Tab_№,FIO1,Cafedra,Fakultet,Doljnost,Staj, Uchenaya_Step,Adres,Telefon,cod_goroda)
values (104932210,'Ivanov Petr Alekseevich','Fizika','Mashinostroitelniy', 'Stajer',1,'Professor','Fefilova 41 15',679921,90032);
Insert Into KO_Prepodavateli(Tab_№,FIO1,Cafedra,Fakultet,Doljnost,Staj, Uchenaya_Step,Adres,Telefon,cod_goroda)
values (442311098,'Agafonov Denis Vladimirovich','Materialovedenie', 'Stroitelniy','Stajer',1,'Professor','Putina 18 2',535523,2399);
Insert Into KO_Prepodavateli(Tab_№,FIO1,Cafedra,Fakultet,Doljnost,Staj, Uchenaya_Step,Adres,Telefon,cod_goroda)
values (199032553,'Geraskov Vadim Evgrafovich','Teoriticheskaya mechanika', 'Mashinostroitelniy','Prepodavatel',9,'Docent','Viluhina 22 4',334433,4900);
Insert Into KO_Prepodavateli(Tab_№,FIO1,Cafedra,Fakultet,Doljnost,Staj, Uchenaya_Step,Adres,Telefon,cod_goroda)
values (799099953,'Zuev Aleksandr Evgenievich','Fizicheskoe vospitanie', 'Mashinostroitelniy','Prepodavatel',15,'Professor','Begovaya 24 4',453209,4942);
Заполнение таблицы KO_group :
Insert Into KO_group(№_group,group_g,specialnost,fakultet1)
values (11943,'MA 24','avtomatizacia','mashinostroitelniy');
Insert Into KO_group(№_group,group_g,specialnost,fakultet1)
values (12333,'MA 14','avtomatizacia','mashinostroitelniy');
Insert Into KO_group(№_group,group_g,specialnost,fakultet1)
values (92334,'CA 319','Avtomobilnie dorogi','Stroitelniy');
Insert Into KO_group(№_group,group_g,specialnost,fakultet1)
values (442234,'MT 47','technologia mashinostroenia','mashinostroitelniy');
Insert Into KO_group(№_group,group_g,specialnost,fakultet1)
values (1133213,'MT 48','technologia mashinostroenia','mashinostroitelniy');
Заполнение таблицы KO_Curs :
Insert Into KO_Curs(№_predmet,predmet,№_group,lektions,practics,semestr, tab_№,№_kontrol)
values(112,'Matematica',11943,24,12,3,117455000,44152);
Insert Into KO_Curs(№_predmet,predmet,№_group,lektions,practics,laboratorian, semestr,tab_№,№_kontrol)
values(155,'Fizika',12333,38,16,12,2,104932210,44445);
Insert Into KO_Curs(№_predmet,predmet,№_group,lektions,practics,laboratorian, semestr,tab_№,№_kontrol)
values(7990,'Materialovedenie',442234,32,8,6,7,442311098,113222);
Insert Into KO_Curs(№_predmet,predmet,№_group,practics,semestr,tab_№, №_kontrol)
values(12,'Fizkultura',1133213,42,8,799099953,123345);
Insert Into KO_Curs(№_predmet,predmet,№_group,lektions,practics,laboratorian, semestr,tab_№,№_kontrol)
values(44231,'Teoriticheskaya mechanika',92334,34,16,4,6,199032553,99003);
Познакомимся с командой Select. Она выполняет запрос по одной или нескольким таблицам, возвращая строки и столбцы данных.
Пример 1:
Написать запросы на вывод данных всех наших таблиц.
Команда: select * from KO_Prepodavateli
Результат: 
Рисунок 3.
Команда: select * from KO_Gorod
Результат:
Рисунок 4.
Команда: select * from KO_Form_Kontrol
Результат:
Рисунок 5.
Команда: select * from KO_group
Результат:
Рисунок 6.
Команда: select * from KO_Curs
Результат:
Рисунок 7.
Пример 2:
Получить названия предметов, отсортированных по количеству практических занятий.
Команда: SELECT Predmet FROM KO_curs ORDER BY Practics
Результат:
Рисунок 8.
Пример 3:
Определить суммарный стаж преподавателей, код города которых выше 5000.
Команда: SELECT SUM(staj) FROM KO_prepodavateli WHERE cod_goroda>5000
Результат:
Рисунок 9.
Пример 4:
Представить информацию о преподавателях в виде: ФИО преподавателя, читаемый курс и форма контроля по этому предмету.
Команда: SELECT KO_prepodavateli.FIO1, KO_curs.Predmet , KO_form_kontrol.Kontrol FROM KO_prepodavateli, KO_curs, KO_form_kontrol
WHERE (KO_prepodavateli.Tab_№ = KO_curs.Tab_№) and (KO_form_kontrol.№_kontrol = KO_curs.№_kontrol);
Результат:
Рисунок 10.
Удаление данных из таблицы происходит командой DELETE.
Пример:
Из таблицы Курсы (KO_curs) удаляются записи для которых lektions>33
Команда: DELETE FROM KO_curs WHERE lektions>33
Если запрос был написан верно, будет возвращена строка: 1 строка удалена.
Результат:
Рисунок 11.
Модификация данных происходит с помощью команды UPDATE.
Пример:
Требуется записать стаж преподавателей по кварталам, а не по годам, как это записано. Т.е. необходимо умножить на 4 стаж.
Команда: UPDATE KO_Prepodavateli SET Staj=Staj*4
Результат:
Рисунок 12.
Разработка клиентской части.
После запуска программы появляется главная форма, с которой можно получить доступ к базе данных и к ее функциям. При выборе функции добавление преподавателя выводится соответствующая форма, позволяющая перейти на главную форму или перейти к форме, выводящей всю информацию о выбранном преподавателе, или добавить город, если такового нет в списке предложенного, или добавить преподавателя, заполнив перед этим предложенные поля. При выборе функции добавления группы выводится соответствующая форма, позволяющая перейти на главную форму или добавить группу, предварительно заполнив предложенные поля. При выборе функции добавления предмета выводится соответствующая форма, позволяющая перейти на главную форму или добавить форму контроля, если нужной формы нет в списке предложенного, или добавить предмет, предварительно заполнив предложенные поля, или перейти к списку предметов. При выборе функции списка предметов выводится соответствующая форма, позволяющая перейти на главную форму или добавить предмет, или просмотреть список предметов у выбранной группы в выбранном семестре. При выборе функции информации о преподавателе выводится соответствующая форма, позволяющая перейти на главную форму, или добавить преподавателя, если такового нет в списке предложенного, или просмотреть всю имеющуюся информацию о выбранном преподавателе.
Представим нашу логику в виде схемы(рисунок 13):
Дата добавления: 2018-05-12; просмотров: 235; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!