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

Федеральное агентство по образованию                                     Государственное образовательное учреждение высшего профессионального образования

Уфимский Государственный Авиационный Технический Университет

                                                ПОЯСНИТЕЛЬНАЯ ЗАПИСКА

    к курсовому проекту

     по дисциплине <<Базы Данных>>

Группа  ПИЭ-501бз

Студент                 ___________    _________      Петкау Я.А.    

                                  (подпись)                        (дата)               (Ф.И.О)

Принял                  ___________    _________       Гарифулин Т.А.

                                                  (подпись)           (дата)               (Ф.И.О)

              _______________

                                                                                                  (оценка)

Уфа 2012

Введение

Цель любой информационной системы – обработка данных об объектах реального мира. Основные идеи современной информационной технологии базируются на концепции баз данных (БД).

База данных (БД) - это поименованная совокупность структурированных данных, относящихся к определенной предметной области.

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

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

Структурирование - это введение соглашений о способах представления данных. Неструктурированными называют данные, записанные, например, в текстовом файле.

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

Понятие базы данных тесно связано с такими понятиями структурных элементов, как поле, запись, файл (таблица).

Практическая часть

Задание 1. Следующую таблицу привести к третьей нормальной форме (с указанием первичных ключей):

Аспирант (Код_аспиранта, ФИО_аспиранта, Код_специальности, Назв_специальности, Код_кафедры, Назв_кафедры, Научн_руководитель, Дата_поступления, Номер_приказа, Категория, Код_договора _на_обучение, Форма_обучения)

В результате нормализации образуется три таблицы: Аспирант, Специальность, Кафедра со следующими полями:

Аспирант:

Код_аспиранта,

ФИО_аспиранта,

Научн_руководитель;

Код_договора _на_обучение,

Форма_обучения,

Категория,

Дата_поступления,

Номер_приказа.

Специальность:

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

Назв_специальности;

Кафедра:

Код_кафедры,

Назв_кафедры;

Задание 2. Записать команды SQL для создания нормализованных таблиц и связей ссылочной целостности между ними. Учесть следующие ограничения:

      Категория аспирант: контрактник, бюджетник;

      Форма обучения: очная, заочная.

CREATE TABLE Аспирант(

  КодАспир NCHAR( 10 )NOT NULL,

  ФИОАспир NCHAR( 100 )NOT NULL,

  ФИОНаучРук ( 100 )NOT NULL,

  КодДоговора NCHAR( 10 )NULL,

  ФормаОбуч NCHAR( 10 )NOT NULL,

  Категория NCHAR( 15 )NOT NULL,

  ДатаПоступ DATETIME( 8 )NOT NULL,

  НомерПриказа NCHAR( 10 )NOT NULL,

  КодСпециальности NCHAR( 10 )NOT NULL,

CONSTRAINT ka PRIMARYKEY  (КодАспир),

CONSTRAINT cf CHECK (ФормаОбуч IN ('очная','заочная')),

CONSTRAINT ck CHECK (Категория IN ('контрактник','бюджетник')),

CONSTRAINT kk1 FOREIGNKEY (КодКафедры) REFERENCES Кафедра (КодКафедры)

  ON DELETE NO ACTION

  ON UPDATE CASCADE,

CONSTRAINT fks FOREIGNKEY (КодСпециальности) REFERENCES Специальность (КодСпециальности)

  ON DELETE NO ACTION

  ON UPDATE CASCADE

)

CREATE TABLE Кафедра(

  КодКафедры NCHAR( 10 )NOT NULL,

  НазвКафедры NCHAR( 100 )NOT NULL,

CONSTRAINT kk PRIMARYKEY  (КодКафедры)

)

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

  КодСпециальности NCHAR( 10 )NOT NULL,

  НазвСпециальности NCHAR (100) NOT NULL,

  КодКафедры NCHAR( 10 )NOT NULL,

CONSTRAINT ks PRIMARYKEY  (КодСпециальности),

CONSTRAINT fkk FOREIGNKEY (КодКафедры) REFERENCES Кафедра (КодКафедры)

  ON DELETE NO ACTION

  ON UPDATE CASCADE

)

Задание 3. Написать SQL-код для создания триггера «trigger-aspir» таблицы «Аспирант», который должен срабатывать при попытке вставки новых или изменении существующих записей. Правило, которое реализует триггер: Код_договора_на_обучение содержит значение, отличное от NULL, только в том случае, если аспирант-контрактник.

create trigger trigger-aspir on Аспирант

for insert, update

as

  declare @КодДоговора int

  declare @Категория int

begin transaction

  select @Категория=Категория from inserted

  if (@Категория='бюджетник')

  begin

    set @КодДоговора IS NULL

    commit transaction

  end

Задание 4. Для таблиц из пп.1,2 написать SQL-код для создания представления «view_asp», воспроизводящего исходную таблицу из нормализованных.

Аспирант (Код_аспиранта, ФИО_аспиранта, Код_специальности, Назв_специальности, Код_кафедры, Назв_кафедры, Научн_руководитель, Дата_поступления, Номер_приказа, Категория, Код_договора _на_обучение, Форма_обучения)

CREATE VIEW view_asp

AS

SELECT    

  Аспирант.КодАспир,

  Аспирант.ФИОАспир,

  Аспирант.КодСпециальности, 

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

  Специальность.КодКафедры,

  Кафедра.НазвКафедры,

  Аспирант.ФИОНаучРук,

  Аспирант.ДатаПоступ,

  Аспирант.НомерПриказа,

  Аспирант.Категория,

  Аспирант.КодДоговора,

  Аспирант.ФормаОбуч

FROM

  Кафедра

INNER JOIN

     (Cпециальность

INNER JOIN

  Аспирант

              ON

       Аспирант.КодСпециальности = Специальность.КодСпециальности)

ON

  Специальность.КодКафедры = Кафедра.КодКафедры

Задание 5. Для таблиц из пп.1,2 написать SQL-код для создания хранимой процедуры «asp_data», которая при вводе номера приказа выводит данные об аспирантах, зачисленных согласно нему.

CREATE PROCEDURE asp_data

  @НомерПриказа nchar(10)

AS

BEGIN

  SET NOCOUNT ON;

    SELECT    

      Аспирант.КодАспир,

      Аспирант.ФИОАспир,

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

      Кафедра.НазвКафедры,

      Аспирант.ФИОНаучРук,

      Аспирант.ДатаПоступ,

      Аспирант.НомерПриказа,

      Аспирант.Категория,

      Аспирант.КодДоговора,

      Аспирант.ФормаОбуч

    FROM

      Кафедра

    INNER JOIN

         (Cпециальность

    INNER JOIN

      Аспирант

                  ON

           Аспирант.КодСпециальности = Специальность.КодСпециальности)

    ON

      Специальность.КодКафедры = Кафедра.КодКафедры

    WHERE

      Аспирант.НомерПриказа=@НомерПриказа

END

Задание 6. Для таблиц из пп.1,2 написать SQL-запрос на выборку всех данных об аспирантах очной формы обучения кафедры АСУ, обучающихся за счет бюджетных средств.

select

  Аспирант.КодАспир,

  Аспирант.ФИОАспир,

  Аспирант.КодСпециальности, 

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

  Аспирант.КодКафедры,

  Кафедра.НазвКафедры,

  Аспирант.ФИОНаучРук,

  Аспирант.ДатаПоступ,

  Аспирант.НомерПриказа,

  Аспирант.Категория,

  Аспирант.КодДоговора,

  Аспирант.ФормаОбуч

FROM

  Кафедра

INNER JOIN

     (Cпециальность

INNER JOIN

  Аспирант

              ON

       Аспирант.КодСпециальности = Специальность.КодСпециальности)

ON

  Специальность.КодКафедры = Кафедра.КодКафедры

where

  Кафедра.НазвКафедры='АСУ' AND Аспирант.ФормаОбуч='очная' AND Аспирант.Категория='бюджетник'

Задание 7. База данных содержит таблицы:

s —— поставщики                                p —— товары                                             sp —— поставки

Написать SQL-запрос на выборку данных обо всех поставщиках, которые не поставляют ни одного товара из тех, что поставляет поставщик с s_no = «s2».

select

  s.*

from

  s

where

  s.s_no not in (

    select

      distinct(sp.s_no)

    from

      sp

    where

      sp.p_no in (

        select

          distinct(sp.p_no)

        from

          sp

        where

          sp.s_no = 's2'

     )

  )