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

>---
 
 
 
 

3. Разработка  информационных таблиц

Таблица ПРЕПОДАВАТЕЛИ

 
 

Таблица ДИСЦИПЛИНЫ

 

Таблица ПРЕПОДАВАЕМЫЕ_ДИСЦИПЛИНЫ

 

Таблица ГРУППЫ

 

Таблица СТУДЕНТЫ

 

Таблица ПЛАН

 

Таблица КОНТРОЛЬНЫЕ

 

4. Создание  средствами SQL базы данных

• Создаем таблицу ДИСЦИПЛИНА

CREATE TABLE ДИСЦИПЛИНА (

       Дисциплина           VARCHAR(100) NOT NULL,

       CONSTRAINT PrimaryKey

              PRIMARY KEY (Дисциплина)

); 
 

• Создаем таблицу  ПРЕПОДАВАТЕЛИ

CREATE TABLE ПРЕПОДАВАТЕЛИ  (

       Научная_степень      CHAR(18) NULL,

       ФИО_преподавателя   VARCHAR(100) NOT NULL,

       Кол_во_часов         INTEGER NULL,

       Оклад                INTEGER NULL,

       CONSTRAINT PrimaryKey

              PRIMARY KEY (ФИО_преподавателя)

); 
 

• Создаем таблицу  ПРЕПОДАВАЕМЫЕ_ ДИСЦИПЛИНЫ

CREATE TABLE ПРЕПОДАВАЕМЫЕ_ДИСЦИПЛИНЫ (

       Дисциплина           VARCHAR(100) NOT NULL,

       ФИО_преподавателя   VARCHAR(100) NOT NULL,

       CONSTRAINT ПРЕПОДАВАЕМЫЕ_ДИСЦИПЛИНЫ

             PRIMARY KEY (Дисциплина, ФИО_преподавателя),

       CONSTRAINT

FOREIGN KEY (Дисциплина) REFERENCES ДИСЦИПЛИНА,

       CONSTRAINT

              FOREIGN KEY (ФИО_переподавателя) REFERENCES ПРЕПОДАВАТЕЛИ

); 

• Создаем таблицу  ГРУППА

CREATE TABLE ГРУППА (

       Курс                 INTEGER NULL,

       Группа               LONG NOT NULL,

       ФИО_преподавателя   VARCHAR(100) NOT NULL,

       CONSTRAINT PrimaryKey PRIMARY KEY (Группа),

       CONSTRAINT СПЕЦИАЛЬНОСТЬГРУППА

              FOREIGN KEY (ФИО_преподавателя) REFERENCES ПРЕПОДАВАТЕЛИ

); 

• Создаем таблицу  ПЛАН

CREATE TABLE ПЛАН (

       Дисциплина           VARCHAR(100) NOT NULL,

       Номер_контрольной    INTEGER NOT NULL,

       Название_контрольной VARCHAR(100) NULL,

       CONSTRAINT PrimaryKey   PRIMARY KEY (Номер_контрольной),

       CONSTRAINT ДИСЦИПЛИНАПЛАН

              FOREIGN KEY (Дисциплина) REFERENCES ДИСЦИПЛИНА

); 

• Создаем таблицу  СТУДЕНТ

CREATE TABLE СТУДЕНТ (

       Группа               LONG NOT NULL,

       НомерЗачетки         VARCHAR(6) NOT NULL,

       ФИО                  VARCHAR(100) NULL,

       Форма_обучения       VARCHAR(8) NULL,

       CONSTRAINT PrimaryKey PRIMARY KEY (НомерЗачетки),

       CONSTRAINT ГРУППАСТУДЕНТ

              FOREIGN KEY (Группа)  REFERENCES ГРУППА

); 

• Создаем таблицу  КОНТРОЛЬНЫЕ

CREATE TABLE КОНТРОЛЬНЫЕ (

       НомерЗачетки         VARCHAR(6) NOT NULL,

       Номер_контрольной    INTEGER NOT NULL,

       Оценка               INTEGER NULL,

       Дата_проведения      DATE NULL,

       CONSTRAINT PrimaryKey PRIMARY KEY (НомерЗачетки, Номер_контрольной),

       CONSTRAINT

              FOREIGN KEY (Номер_контрольной)   REFERENCES ПЛАН,

       CONSTRAINT СТУДЕНТЭКЗАМЕН

             FOREIGN KEY (НомерЗачетки) REFERENCES СТУДЕНТ

); 

5. Примеры  SQL-запросов на вставку и удаление  данных

• Вставка данных о новом студенте

INSERT INTO СТУДЕНТ (НомерЗачетки, Группа, ФИО, Форма_обучения)

VALUES    (“001356”, СКС-52, “Иванов И.И.”, “Контракт”) 

• Удалить всех студентов, у которых стоит хотя бы одна двойка по контрольной работе.

DELETE FROM СТУДЕНТ

WHERE НомерЗачетки = (SELECT НомерЗачетки FROM КОНТРОЛЬНЫЕ WHERE Оценка = 2) 

6. Изменение записей в таблице.

• Перевести всех студентов группы СКС-28 на второй курс

UPDATE ГРУППА

SET Курс = 2

WHERE Группа = 'СКС-28' 
 

7. Выборку  данных из таблицы

• Выбрать всех студентов имеющих двойки по контрольным работам для формирования списка должников. Список сортируем по возрастанию группы, а там где группы одинаковые, то по возрастанию первого поля (СТУДЕНТ.ФИО)

SELECT СТУДЕНТ.ФИО, СТУДЕНТ.Группа, КОНТРОЛЬНЫЕ.Оценка, ПЛАН.Дисциплина, ПЛАН.Название_контрольной

FROM ДИСЦИПЛИНА INNER JOIN (ПЛАН INNER JOIN (СТУДЕНТ INNER JOIN КОНТРОЛЬНЫЕ ON СТУДЕНТ.НомерЗачетки = КОНТРОЛЬНЫЕ.НомерЗачетки)

     ON ПЛАН.Номер_контрольной = КОНТРОЛЬНЫЕ.Номер_контрольной) ON ДИСЦИПЛИНА.Дисциплина = ПЛАН.Дисциплина

WHERE КОНТРОЛЬНЫЕ.Оценка=2

ORDER BY СТУДЕНТ.Группа ASC, 1 ASC 

• Выбрать всех студентов всех групп в список, который отсортирован по группе а там где группы одинаковые, то по возрастанию первого поля (СТУДЕНТ.ФИО), с расчетом среднего балла каждого студента

SELECT СТУДЕНТ.ФИО,  СТУДЕНТ.Группа, Sum([КОНТРОЛЬНЫЕ]![Оценка])/Count([КОНТРОЛЬНЫЕ]![Номер_контрольной]) AS СРЕДНИЙ БАЛАА

FROM СТУДЕНТ INNER JOIN КОНТРОЛЬНЫЕ ON СТУДЕНТ.НомерЗачетки = КОНТРОЛЬНЫЕ.НомерЗачетки

GROUP BY СТУДЕНТ.ФИО,  СТУДЕНТ.Группа

ORDER BY СТУДЕНТ.Группа ASC, 1 ASC 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

ЛИТЕРАТУРА

1. Джен Л. Харрингтон. Проектирование реляционных баз данных Лори, 2006 г.
2. Джеффри Д. Ульман, Дженнифер Уидом. Основы реляционных баз данных, Лори, М, 2006 г.
3. Корнелл П. Анализ данных в Excel. Просто как дважды два / П. Корнелл; пер. с англ. – М.: Эксмо, 2007. – 224 с.: ил. – (Просто как дважды два).
4. On-line библиотека. Литература по SQL. http://www.xserver.ru/computer/database/sql/
5. On-line библиотека. Литература по компьютерной безопасности. http://www.xserver.ru/computer/raznoe/bezopasn/