Проектирование реляционной базы данных

Министерство  общего и профессионального образования  РФ

Курский государственный технический университет 

Кафедра вычислительной техники 
 
 
 
 
 
 
 
 
 

КОНТРОЛЬНАЯ РАБОТА

по дисциплине

«Базы данных» 
 
 

Тема:  

ПРОЕКТИРОВАНИЕ

РЕЛЯЦИОННОЙ БАЗЫ ДАННЫХ 
 
 
 
 
 
 
 
 
 
 

Выполнил:

студент группы ВМ 81-Ф                                                             ______________ В. С. Островной 

Руководитель  работы:

Доцент кафедры  ВТ ______________ Е. Ю. Емельянова  
 
 
 
 
 
 

Курск 2010

Содержание

 
 
 
 
 
 
 
 
 
 

Цель  курсовой работы 

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

реляционных СУБД. 
 
 
 

Постановка  задачи

 

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

   Вариант №1. 

Задание: 

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

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

Диаграмма «Сущность-связь» 
 

Реляционная модель

 
 

 
 

 

Описание  доменов 

Имя домена Назначение  домена Столбцы, принадлежащие домену Базовый тип Ограничения целостности домена
TName Имена, фамилии Film.Nazwanie_Film

Rejisser.FIO_Rejisser

Avtor.FIO_Avtor

Artist.FIO_Artist

Nagrada.Nazvanie_Nagrada

 VARCHAR(50) NOT NULL
TInfo Информация Film.Info_Film

Rejisser.Info _Rejisser

Avtor.Info _Avtor

Artist.Info _Artist

Nagrada.Info _Nagrada

 VARCHAR (1000) NOT NULL

 

 

Описание  таблиц и ограничений  целостности

Таблица [ Film]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие  огран.

целостности

 Примечание
ID_Film DECIMAL (7,0) NOT NULL Авто-инкрем Primary key >0 Delete cascade,

Update cascade

 Код фильма
Nazwanie_Film VARCHAR См.домен     См.домен   Название  фильма
Janr_Film VARCHAR NOT NULL         Жанр  фильма
God_Vihoda_Film DECIMAL (4,0) NOT NULL     >1800   Год выхода
Info_Film VARCHAR См.домен     См.домен   Аннотация
Time_Film TIME NOT NULL     >0   Продолжительность
Folder_File VARCHAR NOT NULL         Путь  к файлу

 
Таблица [Rejisser]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие огран.

целостности

 Примечание
ID_Rejisser DECIMAL (7,0) NOT NULL Авто-инкрем Primary key >0 Delete cascade,

Update no action

 Код режиссёра
FIO_Rejisser VARCHAR См.домен     См.домен   Ф.И.О. режиссёра
Info_Rejisser VARCHAR См.домен     См.домен   Инфо  о режиссёре

 
Таблица [Avtor]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие  огран.

целостности

 Примечание
ID_Avtor DECIMAL (7,0) NOT NULL Авто-инкрем Primary key >0 Delete cascade,

Update no action

 Код автора
FIO_Avtor VARCHAR См.домен     См.домен   Ф.И.О. автора
Info_Avtor VARCHAR См.домен     См.домен   Инфо  об авторе
Таблица [Artist]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие  огран.

целостности

 Примечание
ID_Artist DECIMAL (7,0) NOT NULL Авто-инкрем Primary key >0 Delete cascade,

Update no action

 Код артиста
FIO_Artist VARCHAR См.домен     См.домен   Ф.И.О. артиста
Info_Artist VARCHAR См.домен     См.домен   Инфо  об артисте

 
Таблица [Nagrada]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие  огран.

целостности

 Примечание
ID_Nagrada DECIMAL (7,0) NOT NULL Авто-инкрем Primary key >0 Delete cascade,

Update no action

 Код награды
Nazvanie_Nagrada VARCHAR См.домен     См.домен   Название  награды
Info_Nagrada VARCHAR См.домен     См.домен   Инфо  о награде

 
Таблица [Film _Rejisser]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие  огран.

целостности

 Примечание
ID_Film DECIMAL (7,0) См.домен     См.домен Foreign key Код фильма
ID_Rejisser DECIMAL (7,0) См.домен     См.домен Foreign key Код режиссёра

 
Таблица [Film _Avtor]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие  огран.

целостности

 Примечание
ID_Film DECIMAL (7,0) См.домен     См.домен Foreign key Код фильма
ID_Avtor DECIMAL (7,0) См.домен     См.домен Foreign key Код автора

 
Таблица [Film _Artist]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие  огран.

целостности

 Примечание
ID_Film DECIMAL (7,0) См.домен     См.домен Foreign key Код фильма
ID_Artist DECIMAL (7,0) См.домен     См.домен Foreign key Код артиста

 
Таблица [Film _Nagrada]
Имя столбца Тип NULL / NOT NULL Default Primary key / Unique Check Foreign key и

другие  огран.

целостности

 Примечание
ID_Film DECIMAL (7,0) См.домен     См.домен Foreign key Код фильма
ID_Nagrada DECIMAL (7,0) См.домен     См.домен Foreign key Код награды

 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

 

     

Отложенные  ограничения целостности 

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

    Столбцы и ограничения целостности для  них: 

    Таблица [ Film]   столбец God_Vihoda_Film

    Значение  не должно быть меньше 1887. Т.к. до 1888 года фильмы не выходили. 

    Таблица [ Film]   столбец Time_Film

    Значение  не должно быть меньше 0. Т.к. любой фильм  не может иметь нулевую продолжительность. 

    Таблица [ Film]   столбец Janr_Film

      Значения будут сверяться со множеством допустимых значений: 

Анимация, Боевик, Вестерн, Документальный, Криминал, Экстрим, Детектив, Триллер, Приключения, Ужасы, Комедия, Чёрная комедия, Пародия, Драма, Мелодрама, Трагедия, Трагикомедия, Катастрофа, Фантастика, Мистика, Исторический, Мюзикл, Спектакль, Биография, Военные. 

Создание  базы данных методом  интерактивного конструирования. 

Этап 1. Создание новой базы данных

Создал файл .GDB новой, пока пустой, базы данных с помощью IB Expert. 

 
 

Этап 2. Регистрация базы данных 

 
 

Этап 3. Подключение к базе данных

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

 
 

Этап 4. Создание доменов 
 

 

CREATE DOMAIN "TName" AS

VARCHAR(50) CHARACTER SET WIN1251

NOT NULL

COLLATE WIN1251; 

CREATE DOMAIN "TInfo" AS

VARCHAR(1000) CHARACTER SET WIN1251

NOT NULL

COLLATE WIN1251; 
 
 

Этап  5. Создание таблиц 

Таблица [ Film]  

 
 

CREATE TABLE "Film" (

    "ID_Film"          DECIMAL(7,0) NOT NULL,

    "Nazwanie_Film"    "TName" NOT NULL /* "TName" = VARCHAR(50) NOT NULL */,

    "Janr_Film"        VARCHAR(50) NOT NULL,

    "God_Vihoda_Film"  DECIMAL(4,0) NOT NULL,

    "Info_Film"        "TInfo" NOT NULL /* "TInfo" = VARCHAR(1000) NOT NULL */,

    "Time_Film"        TIME NOT NULL,

    "Folder_File"      VARCHAR(255) NOT NULL

);

ALTER TABLE "Film" ADD CHECK ("Janr_Film" IN ( 'Анимация', 'Боевик', 'Вестерн', 'Документальный', 'Криминал', 'Экстрим', 'Детектив', 'Триллер', 'Приключения', 'Ужасы', 'Комедия', 'Чёрная комедия', 'Пародия', 'Драма', 'Мелодрама', 'Трагедия', 'Трагикомедия', 'Катастрофа', 'Фантастика', 'Мистика', 'Исторический', 'Мюзикл', 'Спектакль', 'Биография', 'Военные'));

ALTER TABLE "Film" ADD CHECK ("God_Vihoda_Film">1887);

ALTER TABLE "Film" ADD CHECK ("Time_Film">0);

ALTER TABLE "Film" ADD CONSTRAINT "PK_Film" PRIMARY KEY ("ID_Film"); 
 

Таблица [Rejisser]

 

CREATE TABLE "Rejisser" (

    "ID_Rejisser"    DECIMAL(7,0) NOT NULL,

    "FIO_Rejisser"   "TName" /* "TName" = VARCHAR(50) NOT NULL */,

    "Info_Rejisser"  "TInfo" /* "TInfo" = VARCHAR(1000) NOT NULL */

);

ALTER TABLE "Rejisser" ADD CHECK ("ID_Rejisser">0);

ALTER TABLE "Rejisser" ADD CONSTRAINT "PK_Rejisser" PRIMARY KEY ("ID_Rejisser"); 

Таблица [Avtor]

 
 
 
 
 

CREATE TABLE "Avtor" (

    "ID_Avtor"    DECIMAL(7,0) NOT NULL,

    "FIO_Avtor"   "TName" /* "TName" = VARCHAR(50) NOT NULL */,

    "Info_Avtor"  "TInfo" /* "TInfo" = VARCHAR(1000) NOT NULL */

);

ALTER TABLE "Avtor" ADD CONSTRAINT "PK_Avtor" PRIMARY KEY ("ID_Avtor"); 

Таблица [Artist]

 

CREATE TABLE "Artist" (

    "ID_Artist"    DECIMAL(7,0) NOT NULL,

    "FIO_Artist"   "TName" /* "TName" = VARCHAR(50) NOT NULL */,

    "Info_Artist"  "TInfo" /* "TInfo" = VARCHAR(1000) NOT NULL */

);

ALTER TABLE "Artist" ADD CONSTRAINT "PK_Artist" PRIMARY KEY ("ID_Artist"); 

Таблица [Nagrada]

 

CREATE TABLE "Nagrada" (

    "ID_Nagrada"        DECIMAL(7,0) NOT NULL,

    "Nazvanie_Nagrada"  "TName" NOT NULL /* "TName" = VARCHAR(50) NOT NULL */,

    "Info_Nagrada"      "TInfo" NOT NULL /* "TInfo" = VARCHAR(1000) NOT NULL */

);

ALTER TABLE "Nagrada" ADD CONSTRAINT "PK_Nagrada" PRIMARY KEY ("ID_Nagrada"); 

Таблица [Film _Rejisser]

 

 

CREATE TABLE "Film _Rejisser" (

    "ID_Film"      DECIMAL(7,0) NOT NULL,

    "ID_Rejisser"  DECIMAL(7,0) NOT NULL

);

ALTER TABLE "Film _Rejisser" ADD CONSTRAINT "FKR_ID _Rejisser" FOREIGN KEY ("ID_Rejisser") REFERENCES "Rejisser" ("ID_Rejisser") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Film _Rejisser" ADD CONSTRAINT "FKR_ID_Film" FOREIGN KEY ("ID_Film") REFERENCES "Film" ("ID_Film") ON DELETE CASCADE ON UPDATE CASCADE; 

Таблица [Film _Avtor]

 

 

CREATE TABLE "Film _Avtor" (

    "ID_Film"   DECIMAL(7,0) NOT NULL,

    "ID_Avtor"  DECIMAL(7,0) NOT NULL

);

ALTER TABLE "Film _Avtor" ADD CONSTRAINT "FKA_ID _Avtor" FOREIGN KEY ("ID_Avtor") REFERENCES "Avtor" ("ID_Avtor") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Film _Avtor" ADD CONSTRAINT "FKA_ID_Film" FOREIGN KEY ("ID_Film") REFERENCES "Film" ("ID_Film") ON DELETE CASCADE ON UPDATE CASCADE; 
 

Таблица [Film _Artist]

 

 

CREATE TABLE "Film _Artist" (

    "ID_Film"    DECIMAL(7,0) NOT NULL,

    "ID_Artist"  DECIMAL(7,0) NOT NULL

);

ALTER TABLE "Film _Artist" ADD CONSTRAINT "FKART_ID _Artist" FOREIGN KEY ("ID_Artist") REFERENCES "Artist" ("ID_Artist") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Film _Artist" ADD CONSTRAINT "FKArt_ID_Film" FOREIGN KEY ("ID_Film") REFERENCES "Film" ("ID_Film") ON DELETE CASCADE ON UPDATE CASCADE; 
 

Таблица [Film _Nagrada]

 

 

CREATE TABLE "Film _Nagrada" (

    "ID_Film"     DECIMAL(7,0) NOT NULL,

    "ID_Nagrada"  DECIMAL(7,0) NOT NULL

);

ALTER TABLE "Film _Nagrada" ADD CONSTRAINT "FKNagr_ID_Film" FOREIGN KEY ("ID_Film") REFERENCES "Film" ("ID_Film") ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE "Film _Nagrada" ADD CONSTRAINT "FKNagr_ID_Nagrada" FOREIGN KEY ("ID_Nagrada") REFERENCES "Nagrada" ("ID_Nagrada") ON DELETE CASCADE ON UPDATE CASCADE; 

Этап  6. Заполнение таблиц 

Таблица [Film]  

 
 

Таблица [Artist] 

 

Таблица [Rejisser] 

 

Таблица [Avtor] 

 

Таблица [Nagrada] 

 
 
 
 
 
 

Таблица [Film _Nagrada]           

 

Таблица [Film _Artist] 

 

SQL – запросы на выборку данных 

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

Select F.”Nazwanie_Film”,  F.”God_Vihoda_Film”, R.”FIO_Rejisser”, A.”FIO_Artist” 

From “Film” F inner join “Film_Rejisser” FR on F.”ID_Film”=FR.”ID_Film”

“Rejisser” R inner join “Film_Rejisser” FR1 on R.”ID_Rejisser”=FR1.”ID_Rejisser”

“Film” F inner join “Film_Artist” FA on F.”ID_Film”=FA”ID_Film”

“Artist” A inner join “Film_Artist” FA1 on A.”ID_Artist”=FA1.”ID_Artistr” 

Where F.”God_Vihoda_Film”  >1997 
 

   Запрос  для вывода пользователю названий фильмов  с указанием режиссёра, где режиссёр является одновременно и автором. 

Select F.”Nazwanie_Film”, R.”FIO_Rejisser” 

From “Film”  F inner join “Film_Rejisser” FR on F.”ID_Film”=FR.”ID_Film”

“Rejisser” R inner join “Film_Rejisser” FR1 on R.”ID_Rejisser”=FR1.”ID_Rejisser”

“Film” F inner join “Film_Avtor” FAv on F.”ID_Film”=FAv”ID_Film”

“Avtor” Av inner join “Film_Avtor” FAv1 on Av.”ID_Artist”=FAv1.”ID_Artistr” 

Where R.”FIO_Rejisser”=Av.”FIO_Avtor” 
 

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

Select F.”Nazwanie_Film”

From “Film”  F

Where F.”Time_Film”  >1:30:00 
 
 
 
 
 
 
 
 
 
 
 

Вывод 

    В ходе выполнения контрольной работы научился проектировать реляционные базы данных; изучил операторы языка SQL для создания баз данных и таблиц; создал таблицы учебной базы данных под управлением реляционной СУБД. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Библиографический список 

  1. Хомоненко А., Цыганков В., Мальцев М. Базы данных: Учебник для вузов / Под ред. А.Д.Хомоненко. – СПб.: Корона Принт, 2000г.
  2. Карпова Т., Базы данных: модели, разработка, реализация. – СПб.: Питер, 2001.
  3. Крёнке Д. Теория и практика построения баз данных: Уч. пособие. 8-е изд.– СПб.: Питер, 2003
  4. Глушаков С.В., Ломотько Д.В. Базы данных: Уч. курс, – Харьков, 2000

Проектирование реляционной базы данных