4.Эквивалентными являются следующие сущности:

«Телефон» в функции 1 «Учет кадров»;

«Телефон» в функции 2 «Прием учащихся»;

Составим из них сущность «Телефон»:

4. Эквивалентными являются следующие сущности:

«Инструмент» в функции 2 «Прием учащихся»;

«Инструмент» в функции 4 «Планирование учебного процесса»;

Составим из них сущность «Инструмент»:

5. Эквивалентными являются следующие сущности:

«Предмет» в функции 3 «Сдача экзаменов»;

«Предмет» в функции 4 «Планирование учебного процесса»;

«Предмет» в функции 5 «Проведение занятий».

Составим из них сущность «Предмет»:

4.2 Выявление категорий и синтез обобщающих сущностей

Сущностями-категориями являются сущности «Сотрудник», «Учащийся» и «Родственник», обобщающей для них сущностью является «Личность». Также сущностью-категорией является сущность «Преподаватель», обобщающей сущностью для нее выступает «Сотрудник».

4.3 Выявление и устранение дублирования атрибутов и связей

Дублирование атрибутов и связей не выявлено.

4.4 Графическое представление глобальной ER-модели

Графическое представление глобальной ER-модели представлено на странице 40. Также выявлена сущность-категория «Завуч», обобщающей для нее выступает сущность «Преподаватель».

4.5 Спецификация ограничений и правил поддержания целостности

Анализ данных на этом этапе не потребовал введения дополнительных ограничений и правил.

4.6 Вывод

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

5. ПРОЕКТИРОВАНИЕ РЕЛЯЦИОННОЙ SQL-МОДЕЛИ

Данный раздел посвящен проектированию реляционной SQL-модели. Здесь выполняется перевод глобальной ER-модели в реляционную форму, специфицируются ограничения и правила поддержания целостности на реляционном уровне, записывается SQL-код для создания реляционной модели.

5.1 Перевод глобальной ER-модели в реляционную форму

Устранение категорий

Из категорий «Сотрудник», «Учащийся», «Родственник» и обобщающей сущности «Личность», а также из категорий «Преподаватель» и «Завуч» составим следующие независимые сущности

Избавление от связей М:М

Заменим связь «многие ко многим» связью «один ко многим»

Замена явных связей 1:М неявными

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

В результате были получены следующие сущности:

1. Сотрудник (код сотрудника, ФИО, пол, адрес, стаж, дата рождения, код паспорта, дата выдачи, кем выдан);

1 Телефон (номер телефона, тип телефона, примечания);

2 Должность (код должности, название должности);

3 Специальность (код специальности, название специальности);

4 Контакт (код сотрудника, номер телефона);

5 Диплом (код диплома, код сотрудника, код специальности, название учебного заведения, дата окончания учебного заведения);

6 Карьера (код сотрудника, код должности, номер приказа, дата поступления, оклад, дата увольнения);

7 Учащийся (код учащегося, ФИО, номер учебного плана, дата рождения, пол, адрес, год обучения, код свидетельства о рождении, дата выдачи, кем выдано);

8 Родственник (код родственника, ФИО, место работы);

9 Приказ (номер приказа, дата подписания, код сотрудника);

10 Инструмент (код инструмента, название инструмента);

11 Льгота (код льготы, название льготы);

12 Контакты (код учащегося, номер телефона);

13 Семья (код учащегося, код родственника);

14 Уч-П (код учащегося, номер приказа, дата поступления);

15 Специализация (код учащегося, номер приказа, код инструмента);

16 Условия обучения (код учащегося, код льготы, плата за обучение, дата назначения льготы, дата снятия льготы);

17 Предмет (код предмета, название предмета, код сотрудника);

18 Экзамен (год, номер экзамена, код сотрудника, код предмета, код учащегося, дата, время начала, время окончания, кабинет, тип экзамена, оценка);

19 Учебный план (номер учебного плана, дата утверждения учебного плана, особенности, код сотрудника);

20 Четверть (год, номер четверти, номер учебного плана);

21 План_Четверть (номер учебного плана, год, номер четверти);

22 У-С (год, номер четверти, код предмета, количество часов);

23 Занятие (дата, время начала, время окончания, код сотрудника, код предмета, код учащегося, оценка).

5.2 Спецификация ограничений и правил поддержания целостности

Были выявлены следующие правила поддержания целостности:

- при удалении или изменении первичного ключа «код сотрудника» в родительской таблице «Сотрудник» автоматически удаляются или изменяются внешние ключи в следующих дочерних таблицах: «Контакт», «Диплом», «Карьера», «Уч-П», «Экзамен», «Учебный план», «Предмет», «Занятие»;

- при удалении или изменении первичного ключа «код учащегося» в родительской таблице «Учащийся» автоматически удаляются или изменяются внешние ключи в следующих дочерних таблицах: «Контакты», «Уч-П», «Специализация», «Условия обучения», «Экзамен», «Занятие»;

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

- при удалении или изменении первичного ключа «код предмета» в родительской таблице «Предмет» автоматически удаляются или изменяются внешние ключи в следующих дочерних сущностях «Экзамен», «У-С», «Занятие»;

-при удалении или изменении первичного ключа «номер учебного плана» в родительской таблице «Учебный план» автоматически удаляются или изменяются внешние ключи следующих дочерних таблиц: «Учащийся», «Четверть»;

- строго запрещается удалять или изменять первичный ключ «номер четверти» родительской сущности «Четверть»;

- при удалении или изменении первичного ключа «код специальности» в родительской таблице «Специальность» автоматически удаляется или изменяется внешний ключ дочерней таблицы «Диплом»;

- при удалении или изменении первичного ключа «код родственника» в родительской таблице «Родственник» автоматически удаляются или изменяются внешние ключи в дочерней таблице «Семья»;

- при удалении или изменении первичного ключа «номер приказа» в родительской таблице «Приказ» автоматически удаляются или изменяются внешние ключи в дочерней таблице «Уч-П»;

- при удалении или изменении первичного ключа «код должности» в родительской таблице «Должность» автоматически удаляются или изменяются внешние ключи в дочерней таблице «Карьера»;

- при удалении или изменении первичного ключа «код инструмента» в родительской таблице «Инструмент» автоматически удаляются или изменяются внешние ключи в дочерних таблицах «Специализация» и «Учебный план»;

- при удалении или изменении первичного ключа «код льготы» в родительской таблице «Льгота» автоматически удаляются или изменяются внешние ключи в дочерней таблице «Условия обучения»;

5.3 SQL-код для создания реляционной модели

1) CREATE TABLE Сотрудник

(

код_сотрудника INT NOT NULL,

ФИО CHAR(25) NOT NULL,

пол CHAR(1) NOT NULL,

адрес CHAR(15) NOT NULL,

стаж INT NOT NULL,

дата_рождения DATE NOT NULL,

код_паспорта CHAR(15) NOT NULL,

дата_выдачи DATE NULL,

кем_выдан CHAR(10) NULL,

PRIMARY KEY (код_сотрудника, код_паспорта),

CHECK (пол IN ('М', 'Ж')),

CHECK (дата_рождения > CURRENT_DATE () - 100 AND дата_рождения < CURRENT_DATE () - 16)

);

2) CREATE TABLE Телефон

(

номер_телефона CHAR(10) NOT NULL,

тип_телефона CHAR(10) NULL,

примечания CHAR(15) NULL,

PRIMARY KEY (номер_телефона)

);

3) CREATE TABLE Должность

(

код_должности INT NOT NULL,

название_должности CHAR(10) NOT NULL,

PRIMARY KEY (код_должности)

);

4) CREATE TABLE Специальность

(

код_специальности INT NOT NULL,

название_специальности CHAR(10) NOT NULL,

PRIMARY KEY (код_специальности)

);

5) CREATE TABLE контакт

(

код_сотрудника INT,

номер_телефона CHAR(10),

PRIMARY KEY (код_сотрудника, номер_телефона),

FOREIGN KEY (код_сотрудника) REFERENCES Сотрудник ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (номер_телефона) REFERENCES Телефон ON

DELETE CASCADE ON UPDATE CASCADE

);

6) CREATE TABLE Диплом

(

код_диплома CHAR(15) NOT NULL,

код_сотрудника INT,

код_специальности INT,

название_учебного_заведения CHAR(25) NOT NULL,

дата_окончания_учебного_заведения DATE NOT NULL,

PRIMARY KEY (код_диплома, код_сотрудника),

FOREIGN KEY (код_сотрудника) REFERENCES Сотрудник ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_специальности) REFERENCES Специальность

ON DELETE CSCADE ON UPDATE CASCADE);

7) CREATE TABLE Карьера

(

код_сотрудника INT,

код_должности INT,

номер_приказа INT NOT NULL,

дата_поступления DATE NOT NULL,

оклад INT NOT NULL,

дата_увольнения DATE NULL,

PRIMARY KEY (код_сотрудника, код_должности),

FOREIGN KEY (код_сотрудника) REFERENCES Сотрудник ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_должности) REFERENCES Должность ON

DELETE CASCADE ON UPDATE CASCADE

);

8) CREATE TABLE Учащийся

(

код_учащегося INT NOT NULL,

ФИО CHAR (25) NOT NULL,

дата_рождения DATE NOT NULL,

пол CHAR(1) NOT NULL,

год_обучения INT NOT NULL,

адрес CHAR(25) NOT NULL,

код_свидетельства_о_рождении CHAR (15) NOT NULL,

дата_выдачи DATE NULL,

кем_выдано CHAR (25) NULL,

номер_учебного_плана INT NOT NULL,

PRIMARY KEY (код_учащегося, код_свидетельства),

FOREIGN KEY (номер_учебного_плана) REFERENCES Учебный план ON DELETE CASCADE ON UPDATE CASADE,

CHECK (пол IN ('М', 'Ж')),

CHECK (год_обучения >=1 AND год_обучения <=7),

CHECK (дата_рождения > CURRENT_DATE () - 100 AND дата_рождения < CURRENT_DATE () - 5)

);

9) CREATE TABLE Родственник

(

код_родственника INT NOT NULL,

ФИО CHAR(25) NOT NULL,

место_работы CHAR (25) NULL,

PRIMARY KEY (код_родственника)

);

10) CREATE TABLE Приказ

(

номер_приказа INT NOT NULL,

дата_подписания DATE NOT NULL,

код_сотрудника INT,

PRIMARY KEY (номер_приказа),

FOREIGN KEY (код_сотрудника) REFERENCES Сотрудник ON DELETE CASCADE ON UPDATE CASCADE

);

11) CREATE TABLE Инструмент

(

код_инструмента INT NOT NULL,

название_инструмента CHAR(15) NOT NULL,

PRIMARY KEY (код_инструмента)

);

12) CREATE TABLE Льгота

(

код_льготы INT NOT NULL,

название_льготы CHAR (25) NOT NULL,

PRIMARY KEY (код_льготы)

);

13) CREATE TABLE Контакты

(

код_учащегося INT,

номер_телефона CHAR (10),

PRIMARY KEY (код_учащегося, номер_телефона),

FOREIGN KEY (код_учащегося) REFERENCES Учащийся ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (номер_телефона) REFERENCES Телефон ON

DELETE CASCADE ON UPDATE CASCADE

);

14) CREATE TABLE Уч-П

(

код_учащегося INT,

номер_приказа INT,

дата_поступления DATE NOT NULL,

PRIMARY KEY (код_учащегося, номер_приказа),

FOREIGN KEY (код_учащегося) REFERENCES Учащийся ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (номер_приказа) REFERENCES Приказ ON

DELETE CASCADE ON UPDATE CASCADE

);

15) CREATE TABLE Специализация

(код_учащегося INT,

номер_приказа INT,

код _инструмента INT,

PRIMARY KEY (код_учащегося, номер_приказа),

FOREIGN KEY (код_учащегося) REFERENCES Учащийся ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (номер_приказа) REFERENCES Приказ ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_инструмента) REFERENCES Интрумент ON

DELETE CASCADE ON UPDATE CASCADE);

16) CREATE TABLE Условия_обучения

(

код_учащегося INT,

код_льготы INT,

плата_за_обучение NUMERIC NOT NULL,

дата_назначения_льготы DATE NULL,

дата_снятия_льготы DATE NULL,

PRIMARY KEY (код_учащегося, код_льготы),

FOREIGN KEY (код_учащегося) REFERENCES Учащийся ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_льготы) REFERENCES Льгота ON

DELETE CASCADE ON UPDATE CASCADE

);

17) CREATE TABLE Семья

(

код_учащегося INT,

код_родственника INT,

PRIMARY KEY (код_учащегося, код_родственника),

FOREIGN KEY (код_учащегося) REFERENCES Учащийся ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_родственника) REFERENCES Родственник ON

DELETE CASCADE ON UPDATE CASCADE

);

18) CREATE TABLE Предмет

(

код_предмета INT NOT NULL,

название_предмета CHAR(25) NOT NULL,

код_сотрудника INT,

PRIMARY KEY (код_предмета),

FOREIGN KEY (код_сотрудника) REFERENCES Сотрудник ON DELETE CASCADE ON UPDATE CASCADE

);

19) CREATE TABLE Экзамен

(

год INT NOT NULL,

номер_экзамена INT NOT NULL,

код_сотрудника INT,

код_предмета INT,

код_учащегося INT,

дата DATE NOT NULL,

время_начала TIME NOT NULL,

время_окончания TIME NOT NULL,

кабинет INT NOT NULL,

тип_экзамена CHAR(15) NOT NULL,

оценка INT,

PRIMARY KEY (год, номер_экзамена),

FOREIGN KEY (код_сотрудника) REFERENCES Сотрудник ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_предмета) REFERENCES Предмет ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_учащегося) REFERENCES Учащийся ON

DELETE CASCADE ON UPDATE CASCADE,

CHECK (оценка>=2 AND оценка<=5),

CHECK (время_начала < время_окончания)

);

20) CREATE TABLE Учебный_план

(

номер_учебного_плана INT NOT NULL,

дата_утверждения_учебного_плана DATE NULL,

особенности MEMO NOT NULL,

код_сотрудника INT,

PRIMARY KEY (номер_учебного_плана),

FOREIGN KEY (код_сотрудника) REFERENCES Сотрудник ON

DELETE CASCADE ON UPDATE CASCADE,

);

21) CREATE TABLE Четверть

(

год INT,

номер_четверти INT,

номер_учебного_плана INT,

PRIMARY KEY (год, номер_четверти),

FOREIGN KEY (номер_учебного_плана) REFERENCES

Учебный_план ON DELETE CASCADE ON UPDATE CASCADE

);

22) CREATE TABLE Ч-П

(

год INT,

номер_четверти INT,

код_предмета INT,

количество_часов NUMERIC NOT NULL,

PRIMARY KEY (год, номер_четверти, код_предмета),

FOREIGN KEY (год, номер_четверти) REFERENCES Четверть ON

DELETE RESTRICT ON UPDATE RESTRICT,

FOREIGN KEY (код_предмета) REFERENCES Предмет ON

DELETE CASCADE ON UPDATE CASCADE

);

23) CREATE TABLE Занятие

(

дата DATE NOT NULL,

время_начала TIME NOT NULL,

время_окончания TIME NOT NULL,

код_сотрудника INT,

код_предмета INT,

код_учащегося INT,

оценка INT NOT NULL,

PRIMARY KEY (дата, время_начала, время_окончания,

код_сотрудника),

FOREIGN KEY (код_сотрудника) REFERENCES Сотрудник ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_предмета) REFERENCES Предмет ON

DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (код_учащегося) REFERENCES Учащийся ON

DELETE CASCADE ON UPDATE CASCADE,

CHECK (оценка >=2 AND оценка <=5),

CHECK (время_начала < время_окончания)

);

24) CREATE TABLE План_Четверть

(

номер_учебного_плана INT,

год INT,

номер_четверти INT,

PRIMARY KEY(номер_учебного_плана, год, номер_четверти),

FOREIGN KEY (номер_учебного_плана) REFERENCES Учебный_план

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (год) REFERENCES Четверть

ON DELETE CASCADE ON UPDATE CASCADE,

FOREIGN KEY (номер_четверти) REFERENCES Четверть

ON DELETE CASCADE ON UPDATE CASCADE

);

25) CREATE TABLE Уволенные_сотрудники

(

код_сотрудника INT,

ФИО CHAR(25),

дата_ увольнения DATE,

PRIMARY KEY (код_сотрудника)

);

Дополнительные ограничения

1) Для сотрудника дата_поступления - дата_ рождения >=16

CREATE ASSERTION рожд_пост_сотр CHECK

(NOT EXISTS

(SELECT *

FROM Сотрудник, Карьера

WHERE Сотрудник.код_сотрудника= Карьера.код_сотрудника AND Карьера.дата_поступления - Сотрудник.дата_рождения <16));

2) Для сотрудника

дата_поступления>=дата_окончания_учебного_заведения

CREATE ASSERTION пост_оконч_сотр CHECK

( NOT EXISTS

(SELECT *

FROM Карьера, Диплом

WHERE Карьера.код_сотрудника= Диплом.код_сотрудника AND Карьера.дата_поступления<диплом.дата_окончания_учебного_заведения));

3) Для учащегося дата_поступления - дата_рождения >=5

CREATE ASSERTION рожд_пост_уч CHECK

( NOT EXISTS

(SELECT *

FROM Учащийся, Уч-П

WHERE Учащийся.код_учащегося= Уч-П.код_учащегося AND

Уч-П.дата_поступления - Учащийся.дата_рождения <5));

4) При увольнении сотрудника табельные номера следующих за ним в списке сотрудников уменьшаются на 1

CREATE TRIGGER SCODE

ON Сотрудник

FOR DELETE

AS

UPDATE Сотрудник ON

SET Сотрудник.код_сотрудника= Сотрудник.код_сотрудника - 1

FROM Сотрудник, DELETED

WHERE Сотрудник.код_сотрудника > DELETED.код_сотрудника;

5) При исключении учащегося коды следующих за ним в списке учащихся уменьшаются на 1

CREATE TRIGGER UCODE

ON Учащийся

FOR DELETE

AS

UPDATE Учащийся ON

SET Учащийся.код_учащегося= Учащийся.код_учащегося - 1

FROM Учащийся, DELETED

WHERE Учащийся.код_учащегося > DELETED.код_учащегося;

Триггера для Microsoft SQL Server

6) Данные об уволенных сотрудниках заносятся в таблицу «Уволенные_сотрудники»

CREATE TRIGGER Увольнение

ON Сотрудник

FOR DELETE

AS

INSERT INTO Уволенные_сотрудники

VALUES (DELETED.код_сотрудника, DELETED.ФИО, DELETED.дата_увольнения);

7) Из таблицы «Уволенные_сотрудники» удаляются данные о сотруднике, уволенном более одного месяца назад

CREATE TRIGGER Проверка

ON Уволенные_сотрудники

FOR UPDATE

AS

DELETE

FROM Уволенные_сотрудники

WHERE CURRENT_DATE() - дата_увольнения >=30;

5.4 Вывод

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

6. ПРОЕКТИРОВАНИЕ ПРЕДСТАВЛЕНИЙ ДЛЯ АВТОМАТИЗИРУЕМЫХ ФУНКЦИЙ

Данный раздел посвящен проектированию представлений для автоматизируемых функций. Здесь выполняется определение путей доступа к данным для автоматизируемых функций, записывается SQL-код локальных просмотров для автоматизируемых функций.

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

Функция 1 «учет кадров»

Способы реализации представления выбираются в виде просмотра (View).

Форма реализации представления выбирается в виде единой виртуальной таблицы.

Функция 2 «прием учащихся»

Способы реализации представления выбираются в виде просмотра (View).

Форма реализации представления выбирается в виде иерархии таблиц.

Функция 3 «сдача экзаменов»

Способы реализации представления выбираются в виде хранимой процедуры (Stored Procedure).

Форма реализации представления выбирается в виде единой виртуальной таблицы.

Функция 4 «планирование учебного процесса»

Способы реализации представления выбираются в виде хранимой процедуры (Stored Procedure).

Форма реализации представления выбирается в виде иерархии таблиц.

Функция 5 «проведение занятий»

Способы реализации представления выбираются в виде просмотра (View).

Форма реализации представления выбирается в виде единой виртуальной таблицы.

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

Модели соединения таблиц базы данных для реализации представлений для функции 1 «учет кадров» представлены на рис.6.1.1.

Рис. 6.1.1

Модели соединения таблиц базы данных для реализации представлений для функции 2 «прием учащихся» представлены на рис.6.1.2.



Рис.6.1.2

Модели соединения таблиц базы данных для реализации представлений для функции 3 «сдача экзаменов» представлены на рис.6.1.3.



Рис. 6.1.3

Модели соединения таблиц базы данных для реализации представлений для функции 4 «планирование учебного процесса» представлены на рис.6.1.4.



Рис.6.1.4

Модели соединения таблиц базы данных для реализации представлений для функции 5 «проведение занятий» представлены на рис.6.1.5.



Рис.6.1.5

6.3 SQL-код для реализации представлений

Функция 1 «учет кадров»

CREATE VIEW Учет_кадров AS

SELECT Сотрудник.*, Контакт.номер_телефона, Телефон.тип_телефона,

Телефон.примечания, Диплом.код_диплома, Диплом.код_специальности,

Специальность.название_специальности,

Диплом.название_учебного_заведения,

Диплом.дата_окончания_учебного_заведения,

Карьера.код_должности, Должность.название_должности,

Карьера.номер_приказа, Карьера.дата_поступления,

Карьера.оклад, Карьера.дата_увольнения

FROM ((Сотрудник LEFT OUTER JOIN (Контакт LEFT OUTER JOIN Телефон ON Контакт.номер_телефона=Телефон.номер_телефона) ON Сотрудник.код_сотрудника=Контакт.код_сотрудника) LEFT OUTER JOIN

(Диплом LEFT OUTER JOIN Специальность ON Диплом.код_специальности=Специальность.код_специальности) ON Сотрудник.код_сотрудника=Диплом.код_сотрудника) LEFT OUTER JOIN (Карьера LEFT OUTER JOIN Должность ON Карьера.код_должности=Должность.код_должности) ON Сотрудник.код_сотрудника=Карьера.код_сотрудника;

Функция 2 «прием учащихся»

CREATE VIEW V_Учащийся AS

SELECT Учащийся.код_учащегося, Учащийся.ФИО,

Учащийся.код_свидетельства_о_рождении

FROM Учащийся

CREATE VIEW V_Обучение AS

SELECT код_инструмента, название_инструмента, номер_приказа, дата_подписания, код_сотрудника, ФИО, дата_поступления

FROM (Уч_П LEFT OUTER JOIN (Приказ LEFT OUTER JOIN Сотрудник

ON Приказ.код_сотрудника=Сотрудник.код_сотрудника) ON Уч_П.номер_приказа=Приказ.номер_приказа) LEFT OUTER JOIN (Специализация LEFT OUTER JOIN Инструмент ON Специализация.код_инструмента=Инструмент.код_инструмента) ON Уч_П.код_учащегося=Специализация.код_учащегося

WHERE Уч_П.код_учащегося=@код_учащегося;

CREATE VIEW V_Родственник AS

SELECT код_родственника, ФИО, место_работы

FROM Семья LEFT OUTER JOIN Родственник ON Семья.код_родственника=Родственник.код_родственника

WHERE Семья.код_учащегося=@код_учащегося;

CREATE VIEW V_Контакты AS

SELECT номер_телефона, код_телефона, примечания

FROM Контакты LEFT OUTER JOIN Телефон ON Контакты.номер_телефона=Телефон.номер_телефона

WHERE Контакты.код_учашегося=@код_учащегося;

CREATE VIEW V_Льгота AS

SELECT код_льготы, название_льготы, плата_за_обучение, дата_назначения_льготы, дата_снятия_льготы

FROM Условия_обучения LEFT OUTER JOIN Льгота ON Условия_обучения.код_льготы=Льгота.код_льготы

WHERE Условия_обучения.код_учащегося=@код_учащегося;

Функция 3 «сдача экзаменов»

CREATE PROCEDURE Сдача_экзаменов

@год INT OUTPUT

@номер_экзамена INT OUTPUT

@дата DATE OUTPUT

@время_начала TIME OUTPUT

@время_окончания TIME OUTPUT

@кабинет INT OUTPUT

@тип_экзамена CHAR(15) OUTPUT

@код_сотрудника INT OUTPUT

@ФИО_сотрудника CHAR (25) OUTPUT

@код_учащегося INT OUTPUT

@ФИО_учащегося CHAR(25) OUTPUT

@код_предмета INT OUTPUT

@название_предмета CHAR(10) OUTPUT

@оценка INT OUTPUT

SELECT @дата=Экзамен.дата, @время_начала=Экзамен. время_начала,

@время_окончания=Экзамен. время_окончания,

@кабинет=Экзамен.кабинет, @тип_экзамена=Экзамен.тип_экзамена,

@код_сотрудника=Предмет.код_сотрудника,

@ФИО_сотрудника=Сотрудник.ФИО,

@код_учащегося=Экзаменуемый.код_учащегося,

@ФИО_учащегося=Учащийся.ФИО,

@код_предмета=Предмет.код_предмета,

@название_предмета=Предмет.название_предмета,

@оценка=Экзамен.оценка,

@год =Экзамен.год,

@номер_экзамена=Экзамен.номер_экзамена

FROM (Экзамен LEFT OUTER JOIN (Предмет LEFT OUTER JOIN Сотрудник ON Предмет.код_сотрудника=Сотрудник.код_сотрудника) ON Экзамен.код_предмета=Предмет.код_предмета) LEFT OUTER JOIN (Экзаменуемый LEFT OUTER JOIN Учащийся ON Экзаменуемый.код_учащегося=Учащийся.код_учащегося) ON Экзамен.год=Экзаменуемый.год AND Экзамен.номер_экзамена=Экзаменуемый.номер_экзамена

Функция 4 «планирование учебного процесса»

CREATE STORED PROCEDURE Учебный_процесс

@номер_учебного_плана INT INPUT

@дата_утверждения_учебного_плана DATE OUTPUT

@особенности MEMO OUTPUT

@код_сотрудника INT OUTPUT

@ФИО_сотрудника CHAR (25) OUTPUT

SELECT @дата_утверждения_учебного_плана=Учебный_план.дата_утверждения_учеб-ного_плана, @особенности=Учебный_план.особенности,

@код_сорудника=Учебный_план.код_сотрудника,

@ФИО_сотрудника=Сотрудник.ФИО

FROM Учащийся LEFT OUTER JOIN Сотрудник ON Учебный_план.код_сотрудника=Сотрудник.код_сотрудника;

CREATE STORED PROCEDURE V_Ч_Пр

@номер_учебного_плана INT INPUT

@год INT OUTPUT

@номер_четверти INT OUTPUT

@код_предмета INT OUTPUT

@название_предмета CHAR (15) OUTPUT

@количество_часов NUMERIC OUTPUT

@код_сотрудника INT OUTPUT

@ФИО CHAR (25) OUTPUT

SELECT @год=План_Четверть.год, @номер_четверти=План_Четверть.номер_четверти, @код_предмета=Ч_П.код_предмета, @название_предмета=Предмет.название_предмета, @количество_часов=Ч_П.количество_часов, @код_сотрудника=Предмет.код_сотрудника, @ФИО=Сотрудник.ФИО

FROM План_Четверть LEFT OUTER JOIN (Четверть LEFT OUTER JOIN (Ч_П LEFT OUTER JOIN (Предмет LEFT OUTER JOIN Сотрудник ON Предмет.код_сотрудника=Сотрудник.код_сотрудника) ON Ч_П.код_предмета=Предмет.код_предмета)

ON Четверть.год=Ч_П.год AND Четверть.номер_четверти=Ч_П.номер_четверти) ON План_Четверть.год=Четверть.год AND План_Четверть.номер_четверти=Четверть.номер_четверти

WHERE План_Четверть.номер_учебного_плана=@номер_учебного_плана;

CREATE STORED PROCEDURE V_план_учащийся

@номер_учебного_плана INT INPUT

@код_учащегося INT OUTPUT

@ФИО_учащегося CHAR (25) OUTPUT

SELECT @код_учащегося=Уч_Пл.код_учащегося,

@ФИО_учащегося=Учащийся.ФИО

FROM Уч_Пл LEFT OUTER JOIN Учащийся ON Уч_Пл.код_учащегося=Учащийся.код_учащегося

WHERE Уч_Пл.номер_учебного_плана=@номер_учебного_плана;

Функция 5 «проведение занятий»

CREATE VIEW V_Занятие AS

SELECT Занятие.дата, Занятие.время_начала, Занятие.время_окончания, Занятие.кабинет, Занятие.код_предмета, Предмет.название_предмета, Занятие.код_сотрудника, Сотрудник.ФИО, Занятие.код_учащегося, Учащийся.ФИО, Занятие.оценка

FROM ((Занятие LEFT OUTER JOIN Предмет ON Занятие.код_предмета=Предмет.Код_предмета) LEFT OUTER JOIN Сотрудник ON Занятие.код_сотрудника=Сотрудник.код_сотрудника) LEFT OUTER JOIN Учащийся ON Занятие.код_учащегося=Учащийся.код_учащегося;

6.4 Вывод

В данном разделе были спроектированы представления для автоматизируемых функций. Здесь было выполнено определение способов и форм представления, записан SQL-код для реализации представлений для автоматизируемых функций

7. ПРОЕКТИРОВАНИЕ ДОСТУПА К БАЗЕ ДАННЫХ ИЗ ЛОКАЛЬНЫХ ФУНКЦИЙ

Функция 1 «Учет кадров»

Пусть на сервере имеется представление «Учет_кадров» (созданное на предыдущем этапе). Получить информацию о сотрудниках музыкальной школы, используя технологию Pass-Through SQL из среды Visual FoxPro. Предполагается, что в клиентской базе данных Visual FoxPro имеется именованное соединение Education, настроенное для доступа к базе данных сервера.

h = SQLConnect ('Education')

x = ' SELECT * FROM Учет_кадров'

ret = SQLExec (h,x)

BROWSE

= SQLDisconnect (h)

Функция 2 «прием учащихся»

Пусть на сервере имеются представления V_Учащийся, V_Обучение, V_Родственник, V_Телефон и V_Льгота (созданные на предыдущем этапе). Получить информацию об учащихся, используя технологию ADO из серверных сценариев JScript. Предполагается, что для соединения с сервером используется источник данных ODBC Education, идентификатор пользователя User127, пароль SQL92.

<% @ LANGUAGE = 'JScript' %>

<%

var kod_st = Request ('код_учащегося') (1);

var con, rs, x

con = Server.CreateObject ("ADODB.Connection");

con.Open (' DSN = Education' , 'user_ID = User127', 'PSW = SQL92')

x = ' SELECT ФИО, год_обучения FROM V_Учащийся

WHERE код_учащегося = ' + kod_st

rs = Con.Execute (x)

%>

<HTML>

<BODY>

<h1> Результаты запроса </h1>

<table>

<% while (!rs.EOF)

{%>

<%rsMoveNext ()%>

<% } %}

<tr>

<td> <% = rs.Fields('ФИО') %> </td>

<td> <% = rs.Fields('год_обучения') %> </td>

<td> <A href = "V_Обучение" ? код_учащегося = <%Response.Write(kod_st) %> > Обучение </A> </td>

<td> <A href = "V_Родственник" ? код_учащегося = <% Response.Write(kod_st) %> > Родственник </A> </td>

<td> <A href = "V_Телефон" ? код_учащегося = <% Response.Write(kod_st) %>> Телефон </A> </td>

<td> <A href = "V_Льгота" ? код_учащегося = <% Response.Write(kod_st) %>> Льгота </A></td>

</tr>

<rs.Close (); con.Close ();%>

</table>

</BODY>

</HTML>

Функция 3 «сдача экзаменов»

Пусть на сервере имеется представление «Сдача_экзаменов». Получить информацию об экзаменах в музыкальной школе, используя технологию ADO из среды Visual FoxPro. Предполагается, что для соединения с сервером используется источник данных ODBC Education, идентификатор пользователя User127, пароль SQL92.

con = CREATEOBJECT ("ADODB.Connection")

con.Open (' DSN = Education' , 'user_ID = User127', 'PSW = SQL92')

rs = con.Execute ('SELECT * FROM Сдача_экзаменов')

DO WHILE ! rs.EOF

? rs.Fields ('год'), rs.Fields ('номер_экзамена'), rs.Fields ('дата'),;

rs. Fields ('время_начала'), rs. Fields ('время_окончания'),;

rs. Fields ('кабинет'), rs.Fields ('тип_экзамена'), ;

rs.Fields ('код_сотрудника'), rs.Fields ('ФИО_сотрудника'),;

rs.Fields ('код_учащегося'), rs.Fields ('ФИО_учащегося'),;

rs.Fields ('код_предмета'), rs.Fields ('название_предмета'),;

rs.Fields ('оценка')

rs.MOVENEXT()

ENDDO

rs.Close (); con.Close ();

7.4 Вывод

В данном разделе был спроектирован доступ к базе данных из локальных функций. Для этого были использованы следующие технологии: Pass-Through SQL из среды Visual FoxPro, ADO из серверных сценариев JScript и ADO из среды Visual FoxPro.

ЗАКЛЮЧЕНИЕ

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

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

В разделе 2 в результате анализа информационного обеспечения функций были выявлены и сформулированы ограничения и правила поддержания целостности данных, которые должны быть учтены при дальнейшем проектировании. Общее число ограничений на уровне атрибутов составило 79 (в том числе динамических 1), на уровне кортежей -- 83 (динамические ограничения не выявлены), на уровне множеств кортежей -- 25 (динамические ограничения не были выявлены) и на уровне базы данных ограничения не были выявлены. Операционные правила были выявлены и сформулированы для уровня атрибутов -- 2 и для уровня множеств кортежей - 2.

В разделе 3 в результате проектирования локальных ER-моделей, соответствующих отдельным автоматизируемым функциям, были получены нормализованные локальных ER-модели, включающие от 4 до 6 сущностей в третьей нормальной форме. Разработанные спецификации ограничений и правил поддержания целостности включают все ограничения и правила, полученные на предыдущем этапе и трансформированные для локальных ER-моделей; анализ данных на этом этапе не потребовал дополнительного введения ограничений и правил.

В разделе 4 была получена глобальная модель данных, было приведено ее

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

В разделе 5 была спроектирована реляционная SQL-модель. Был выполнен перевод глобальной ER-модели в реляционную форму, специфицированы правила поддержания целостности на реляционном уровне, записан SQL-код для создания реляционной модели.

В разделе 6 были спроектированы представления для автоматизируемых функций. Здесь было выполнено определение способов и форм представления, записан SQL-код для реализации представлений для автоматизируемых функций

В разделе 7 был спроектирован доступ к базе данных из локальных функций. Для этого были использованы следующие технологии: Pass-Through SQL из среды Visual FoxPro, ADO из серверных сценариев JScript и ADO из среды Visual FoxPro.

СПИСОК ЛИТЕРАТУРЫ

1. Дейт К.Дж. Введение в системы баз данных: Пер. с англ. -- 6-е изд. -- Киев: Диалектика, 1998. -- 784 с.

2. Саймон А.Р. Стратегические технологии баз данных: менеджмент на 2000 год: Пер. с англ. -- М.: Финансы и статистика, 1999. -- 479 с.

3. Фаулер М., Скотт К. UML в кратком изложении. Применение стандартного языка объектного моделирования: Пер. с англ. -- М.: Мир, 1999. -- 191 с.

4. Маклаков С.В. BPwin и Erwin. CASE-средства разработки информационных систем. -- М.: ДИАЛОГ-МИФИ, 1999. -- 256 с.

Размещено на Allbest.ru

...