Отчет по Курсовой Работе по курсу: Базы данных Студент группы с-55 Волкова Н. М. Проверил

Московский государственный институт электроники и математики

(Технический университет)

Кафедра ИКТ

Отчет по Курсовой Работе

по курсу: Базы данных

Выполнила:

Студент группы С-55

Волкова Н.М.

Проверил:

Шурупов Д.В.

Москва 2011

o база данных должна быть адекватна существующему миру или какой-то специфичной области;

o не менее 5 таблиц;

o таблицы не должны жить сами по себе, каждая должна быть связана хотя бы с одной другой;

o все имена (таблиц и атрибутов) должны отражать суть, которая за ними скрывается.
- Спроектировать базу данных:

o концептуальная модель;

o логическая модель;

o реализация на SQL.

o сформулировать в виде пар: «Понятное простому человеку текстовое описание» — «Запрос на SQL»;

o запрос должен иметь логичное обоснование, зачем он может потребоваться;

o каждый запрос обращается как минимум к 2 таблицам;

o в отчѐте представить пример выполнения каждого запроса на реальной БД.
- Отчѐт, презентация, защита.

Предметная область:

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

1. 
   Выделяем сущности:
   

книга (books),

сотрудник (workers),

автор (authors),

отсутствующая книга (absent_books).

2. 
   Устанавливаем связи в нотации воронья лапка.
   
3. 
   Строим ЕR-диаграмму.
   

Получаем данную концептуальную модель:

В диаграмме присутствуют связи «много - ко многому» поэтому для правильного проектирования базы данных необходимо завести дополнительные таблицы.

1. 
   Изобразим все полученные таблицы:
   

books (книги),

authors (авторы),

workers (сотрудники),

absent_books (отсутствующие книги),

records (журнал),

orders (заказы),

authors_of_books (авторы книг),

authors_of_absent_books (авторы отсутствующих книг).

2. 
   Установим взаимосвязи между таблицами.
   
3. 
   Назначим первичные и внешние ключи.
   
4. 
   Опишем атрибуты объектов.
   
   

Получаем данную логическую модель:

+----------------+------------------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------------+------------------------------+------+-----+---------+----------------+

| id_absent_book | bigint(20) unsigned | NO | PRI | NULL | auto_increment |

| name_book | varchar(100) | NO | | NULL | |

| edition_date | year(4) | NO | | NULL | |

| genre | enum('fiction','scientific') | YES | | NULL | |

+----------------+------------------------------+------+-----+---------+----------------+

Для таблицы авторы:

+------------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+------------+---------------------+------+-----+---------+----------------+

| id_author | bigint(20) unsigned | NO | PRI | NULL | auto_increment |

| surname | varchar(100) | NO | | NULL | |

| name | varchar(100) | NO | | NULL | |

| birth_date | mediumint(5) | YES | | NULL | |

| death_date | mediumint(5) | YES | | NULL | |

| country | varchar(100) | YES | | NULL | |

+------------+---------------------+------+-----+---------+----------------+

Для таблицы авторы отсутствующих книг:

+----------------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------------+---------------------+------+-----+---------+----------------+

| id_2 | bigint(20) unsigned | NO | PRI | NULL | auto_increment |

| id_absent_book | bigint(20) unsigned | NO | MUL | NULL | |

| id_author | bigint(20) unsigned | NO | MUL | NULL | |

+----------------+---------------------+------+-----+---------+----------------+

Для таблицы авторы книг:

+-----------+---------------------+------+-----+---------+-------+

| Field | Type | Null | Key | Default | Extra |

+-----------+---------------------+------+-----+---------+-------+

| id_1 | bigint(20) unsigned | NO | PRI | NULL | |

| id_book | bigint(20) unsigned | NO | MUL | NULL | |

| id_author | bigint(20) unsigned | NO | MUL | NULL | |

+-----------+---------------------+------+-----+---------+-------+

Для таблицы книги:

+---------------+------------------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+---------------+------------------------------+------+-----+---------+----------------+

| id_book | bigint(20) unsigned | NO | PRI | NULL | auto_increment |

| name_book | varchar(100) | NO | | NULL | |

| edition_date | year(4) | NO | | NULL | |

| edition_place | varchar(100) | NO | | NULL | |

| genre | enum('fiction','scientific') | YES | | NULL | |

+---------------+------------------------------+------+-----+---------+----------------+

Для таблицы заказы:
mysql> describe orders;

+----------------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------------+---------------------+------+-----+---------+----------------+

| id_order | bigint(20) unsigned | NO | PRI | NULL | auto_increment |

| id_ticket | bigint(20) unsigned | NO | MUL | NULL | |

| id_absent_book | bigint(20) unsigned | NO | MUL | NULL | |

+----------------+---------------------+------+-----+---------+----------------+

Для таблицы читатели:
mysql> describe readers;

+----------------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+----------------+---------------------+------+-----+---------+----------------+

| id_ticket | bigint(20) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(100) | NO | | NULL | |

| surname | varchar(100) | NO | | NULL | |

| BD | date | NO | | NULL | |

| address_street | varchar(100) | NO | | NULL | |

| home | mediumint(5) | NO | | NULL | |

| room | mediumint(5) | NO | | NULL | |

| phone | varchar(10) | NO | | NULL | |

+----------------+---------------------+------+-----+---------+----------------+

Для таблицы журнал:

+------------------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+------------------+---------------------+------+-----+---------+----------------+

| id_record | bigint(20) unsigned | NO | PRI | NULL | auto_increment |

| id_ticket | bigint(20) unsigned | NO | MUL | NULL | |

| id_book | bigint(20) unsigned | NO | MUL | NULL | |

| id_worker_give | bigint(20) unsigned | NO | MUL | NULL | |

| id_worker_accept | bigint(20) unsigned | YES | MUL | NULL | |

| delivery_date | date | NO | | NULL | |

| return_date | date | YES | | NULL | |

+------------------+---------------------+------+-----+---------+----------------+

Для таблицы сотрудники:

+-----------+---------------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+---------------------+------+-----+---------+----------------+

| id_worker | bigint(20) unsigned | NO | PRI | NULL | auto_increment |

| name | varchar(100) | NO | | NULL | |

| surname | varchar(100) | NO | | NULL | |

| BD | date | NO | | NULL | |

| phone | varchar(10) | NO | | NULL | |

+-----------+---------------------+------+-----+---------+----------------+

mysql> show tables;

+-------------------------+

| Tables_in_library |

+-------------------------+

| absent_books |

| authors |

| authors_of_absent_books |

| authors_of_books |

| books |

| orders |

| readers |

| records |

| workers |

+-------------------------+
Cоздание таблиц:

CREATE TABLE `absent_books` (

`id_absent_book` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`name_book` varchar(100) NOT NULL,

`edition_date` year(4) NOT NULL,

`genre` enum('fiction','scientific') DEFAULT NULL,

PRIMARY KEY (`id_absent_book`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

CREATE TABLE `authors` (

`id_author` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`surname` varchar(100) NOT NULL,

`name` varchar(100) NOT NULL,

`birth_date` mediumint(5) DEFAULT NULL,

`death_date` mediumint(5) DEFAULT NULL,

`country` varchar(100) DEFAULT NULL,

PRIMARY KEY (`id_author`)

) ENGINE=InnoDB AUTO_INCREMENT=36 DEFAULT CHARSET=utf8

CREATE TABLE `authors_of_absent_books` (

`id_2` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`id_absent_book` bigint(20) unsigned NOT NULL,

`id_author` bigint(20) unsigned NOT NULL,

PRIMARY KEY (`id_2`),

KEY `authors_of_absent_books_authors` (`id_author`),

KEY `authors_of_absent_books_absent_books` (`id_absent_book`),

CONSTRAINT `authors_of_absent_books_absent_books` FOREIGN KEY (`id_absent_book`) REFERENCES `absent_books` (`id_absent_book`),

CONSTRAINT `authors_of_absent_books_authors` FOREIGN KEY (`id_author`) REFERENCES `authors` (`id_author`)

) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=utf8

CREATE TABLE `authors_of_books` (

`id_1` bigint(20) unsigned NOT NULL,

`id_book` bigint(20) unsigned NOT NULL,

`id_author` bigint(20) unsigned NOT NULL,

PRIMARY KEY (`id_1`),

KEY `authors_of_books_absent_authors` (`id_author`),

KEY `authors_of_books__books` (`id_book`),

CONSTRAINT `authors_of_books_absent_authors` FOREIGN KEY (`id_author`) REFERENCES `authors` (`id_author`),

CONSTRAINT `authors_of_books__books` FOREIGN KEY (`id_book`) REFERENCES `books` (`id_book`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `books` (

`id_book` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`name_book` varchar(100) NOT NULL,

`edition_date` year(4) NOT NULL,

`edition_place` varchar(100) NOT NULL,

`genre` enum('fiction','scientific') DEFAULT NULL,

PRIMARY KEY (`id_book`)

) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8

CREATE TABLE `orders` (

`id_order` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`id_ticket` bigint(20) unsigned NOT NULL,

`id_absent_book` bigint(20) unsigned NOT NULL,

PRIMARY KEY (`id_order`),

KEY `orders_readers` (`id_ticket`),

KEY `orders_absent_books` (`id_absent_book`),

CONSTRAINT `orders_absent_books` FOREIGN KEY (`id_absent_book`) REFERENCES `absent_books` (`id_absent_book`),

CONSTRAINT `orders_readers` FOREIGN KEY (`id_ticket`) REFERENCES `readers` (`id_ticket`)

) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8

CREATE TABLE `readers` (

`id_ticket` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`surname` varchar(100) NOT NULL,

`BD` date NOT NULL,

`address_street` varchar(100) NOT NULL,

`home` mediumint(5) NOT NULL,

`room` mediumint(5) NOT NULL,

`phone` varchar(10) NOT NULL,

PRIMARY KEY (`id_ticket`)

) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8

CREATE TABLE `records` (

`id_record` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`id_ticket` bigint(20) unsigned NOT NULL,

`id_book` bigint(20) unsigned NOT NULL,

`id_worker_give` bigint(20) unsigned NOT NULL,

`id_worker_accept` bigint(20) unsigned DEFAULT NULL,

`delivery_date` date NOT NULL,

`return_date` date DEFAULT NULL,

PRIMARY KEY (`id_record`),

KEY `records_readers` (`id_ticket`),

KEY `records_books` (`id_book`),

KEY `records_worker_give` (`id_worker_give`),

KEY `records_worker_accept` (`id_worker_accept`),

CONSTRAINT `records_books` FOREIGN KEY (`id_book`) REFERENCES `books` (`id_book`),

CONSTRAINT `records_readers` FOREIGN KEY (`id_ticket`) REFERENCES `readers` (`id_ticket`),

CONSTRAINT `records_worker_accept` FOREIGN KEY (`id_worker_accept`) REFERENCES `workers` (`id_worker`),

CONSTRAINT `records_worker_give` FOREIGN KEY (`id_worker_give`) REFERENCES `workers` (`id_worker`)

) ENGINE=InnoDB AUTO_INCREMENT=74 DEFAULT CHARSET=utf8

CREATE TABLE `workers` (

`id_worker` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`name` varchar(100) NOT NULL,

`surname` varchar(100) NOT NULL,

`BD` date NOT NULL,

`phone` varchar(10) NOT NULL,

PRIMARY KEY (`id_worker`)

) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

После внесения данных в таблицы по средствам команды

INSERT INTO `my_table` (`field1` type1, `field2` type2, …) VALUES (‘…’, ‘…’);

на выходе получим следующие таблицы с данными:

Таблица сотрудники:
mysql> select*from workers;

+-----------+--------+----------+------------+------------+

| id_worker | name | surname | BD | phone |

+-----------+--------+----------+------------+------------+

| 1 | George | Hill | 1969-12-07 | 9067175511 |

| 2 | Sandra | Scott | 1972-06-19 | 9067144499 |

| 3 | Donna | Adams | 1977-11-07 | 9067099955 |

| 4 | Steven | Baker | 1965-07-13 | 9067075511 |

| 5 | Carol | Gonzalez | 1983-05-01 | 9067088822 |

| 6 | Edward | Nelson | 1987-12-23 | 9067072299 |

| 7 | Sharon | Carter | 1980-09-10 | 9067072211 |

+-----------+--------+----------+------------+------------+

Таблица заказы:
mysql> select*from orders;

+----------+-----------+----------------+

| id_order | id_ticket | id_absent_book |

+----------+-----------+----------------+

| 1 | 2 | 2 |

| 2 | 2 | 6 |

| 3 | 2 | 10 |

| 4 | 3 | 2 |

| 5 | 3 | 1 |

| 6 | 4 | 1 |

| 7 | 6 | 15 |

| 8 | 6 | 7 |

| 9 | 7 | 15 |

| 10 | 9 | 4 |

| 11 | 9 | 9 |

| 12 | 14 | 3 |

| 13 | 14 | 14 |

| 14 | 15 | 8 |

| 15 | 15 | 2 |

| 16 | 15 | 5 |

| 17 | 20 | 5 |

| 18 | 20 | 11 |

| 19 | 22 | 4 |

| 20 | 22 | 10 |

| 21 | 22 | 15 |

| 22 | 25 | 12 |

| 23 | 25 | 7 |

| 24 | 26 | 13 |

| 25 | 26 | 8 |

| 26 | 29 | 11 |

| 27 | 29 | 14 |

+----------+-----------+----------------+

Таблица журнал:

Таблица авторы книг:

+------+---------+-----------+

| id_1 | id_book | id_author |

+------+---------+-----------+

| 1 | 1 | 1 |

| 2 | 2 | 1 |

| 3 | 3 | 2 |

| 4 | 4 | 2 |

| 5 | 5 | 4 |

| 6 | 6 | 4 |

| 7 | 7 | 4 |

| 8 | 8 | 3 |

| 9 | 9 | 3 |

| 10 | 10 | 5 |

| 11 | 11 | 5 |

| 12 | 12 | 6 |

| 13 | 13 | 7 |

| 14 | 14 | 7 |

| 15 | 15 | 2 |

| 16 | 16 | 2 |

| 17 | 17 | 8 |

| 18 | 18 | 9 |

| 19 | 19 | 5 |

| 20 | 20 | 5 |

| 21 | 21 | 10 |

| 22 | 22 | 11 |

| 23 | 23 | 11 |

| 24 | 24 | 2 |

| 25 | 25 | 12 |

| 26 | 26 | 12 |

| 27 | 27 | 12 |

| 28 | 28 | 8 |

| 29 | 29 | 8 |

| 30 | 30 | 8 |

| 31 | 31 | 13 |

| 32 | 32 | 13 |

| 33 | 33 | 13 |

| 34 | 34 | 14 |

| 35 | 35 | 14 |

| 36 | 36 | 14 |

| 37 | 37 | 15 |

| 38 | 38 | 15 |

| 39 | 39 | 16 |

| 40 | 40 | 16 |

| 41 | 41 | 17 |

| 42 | 42 | 17 |

| 43 | 43 | 18 |

| 44 | 44 | 18 |

| 45 | 45 | 19 |

| 46 | 46 | 19 |

| 47 | 47 | 20 |

| 48 | 48 | 20 |

| 49 | 49 | 20 |

| 50 | 50 | 20 |

| 51 | 51 | 21 |

| 52 | 52 | 22 |

| 53 | 53 | 22 |

| 54 | 54 | 23 |

| 55 | 55 | 23 |

| 56 | 56 | 23 |

| 57 | 57 | 24 |

| 58 | 58 | 24 |

| 59 | 59 | 24 |

| 60 | 60 | 24 |

+------+---------+-----------+

Таблица авторы отсутствующих книг:

+------+----------------+-----------+

| id_2 | id_absent_book | id_author |

+------+----------------+-----------+

| 1 | 1 | 2 |

| 2 | 2 | 34 |

| 3 | 3 | 35 |

| 4 | 4 | 25 |

| 5 | 5 | 5 |

| 6 | 6 | 26 |

| 7 | 7 | 13 |

| 8 | 8 | 27 |

| 9 | 9 | 28 |

| 10 | 10 | 7 |

| 11 | 11 | 29 |

| 12 | 12 | 30 |

| 13 | 13 | 31 |

| 14 | 14 | 32 |

| 15 | 15 | 33 |

+------+----------------+-----------+

Таблица авторы:
mysql> select*from authors;

+-----------+----------------+-----------+------------+------------+---------+

| id_author | surname | name | birth_date | death_date | country |

+-----------+----------------+-----------+------------+------------+---------+

| 1 | Bulgakov | Michael | 1891 | 1940 | Russia |

| 2 | Dostoevsky | Feodor | 1821 | 1881 | Russia |

| 3 | Pushkin | Alexander | 1799 | 1837 | Russia |

| 4 | Sent-Ekzjuperi | Antoine | 1900 | 1944 | Russia |

| 5 | Tolstoi | Lev | 1828 | 1910 | Russia |

| 6 | Duma | Alexander | 1802 | 1870 | France |

| 7 | Chekhov | Anton | 1860 | 1904 | Russia |

| 8 | Gogol | Nikolay | 1809 | 1852 | Russia |

| 9 | Shakespeare | William | 1564 | 1616 | England |

| 10 | Tolkien | John | 1892 | 1973 | England |

| 11 | Turgenev | Ivan | 1818 | 1883 | Russia |

| 12 | Rowling | Joanne | 1965 | 0 | England |

| 13 | Oruell | George | 1903 | 1950 | England |

| 14 | Pasternak | Boris | 1890 | 1960 | Russia |

| 15 | Camus | Albert | 1913 | 1960 | France |

| 16 | Lee | Harper | 1926 | 0 | USA |

| 17 | Collins | William | 1824 | 1889 | England |

| 18 | Shumihin | Sergey | 1887 | 1999 | Russia |

| 19 | Gmurman | Vctor | 1980 | 0 | Russia |

| 20 | Hoking | Stephen | 1968 | 0 | USA |

| 21 | Zhukov | Alexander | 1965 | 0 | Russia |

| 22 | Tarasevich | Yury | 1959 | 0 | Russia |

| 23 | Privalov | Ivan | 1978 | 0 | Russia |

| 24 | Miller | Frederic | 1867 | 1925 | France |

| 25 | Kuprin | Alexander | 1870 | 1938 | Russia |

| 26 | Scott | Walter | 1771 | 1832 | England |

| 27 | Murakami | Haruki | 1949 | 0 | Japan |

| 28 | Joyce | James | 1882 | 1941 | England |

| 29 | Faulz | John | 1926 | 2005 | England |

| 30 | Drexler | Eric | 1955 | 0 | Germany |

| 31 | Hacking | Jan | 1971 | 0 | USA |

| 32 | Soros | George | 1930 | 1998 | USA |

| 33 | Wilson | Anton | 1932 | 2007 | USA |

| 34 | Ahmatova | Anna | 1889 | 1966 | Russia |

| 35 | King | Stephen | 1947 | 0 | USA |

+-----------+----------------+-----------+------------+------------+---------+

Таблица отсутствующие книги:

+----------------+---------------------------------+--------------+------------+

| id_absent_book | name_book | edition_date | genre |

+----------------+---------------------------------+--------------+------------+

| 1 | Poor_people | 2000 | fiction |

| 2 | Requiem | 2001 | fiction |

| 3 | Green_mile | 2010 | fiction |

| 4 | Olesya | 2005 | fiction |

| 5 | Revival | 2003 | fiction |

| 6 | Ivanhoe | 2000 | fiction |

| 7 | Farmyard | 2008 | fiction |

| 8 | Norwegian_wood | 2010 | fiction |

| 9 | Uliss | 2010 | fiction |

| 10 | Charry_garden | 2008 | fiction |

| 11 | Magician | 2005 | fiction |

| 12 | Engines_of_creation | 2011 | scientific |

| 13 | Representation_and_intervention | 2011 | scientific |

| 14 | Crisis_of_world_capitalism | 2010 | scientific |

| 15 | Quantum_psychology | 2011 | scientific |

+----------------+---------------------------------+--------------+------------+

1. 
   Вывести имена всех авторов всех книг, которые были взяты читателями
   

FROM `authors`

WHERE `authors`.`id_author`=

ANY(SELECT `authors_of_books`.`id_author`

FROM `authors_of_books`

WHERE `authors_of_books`.`id_book`=

ANY(SELECT `records`.`id_book`

FROM `records`

WHERE `return_date`='0000-00-00'));

Получаем при использовании запроса:

| surname | name |

+------------+----------+

| Bulgakov | Michael |

| Dostoevsky | Feodor |

| Rowling | Joanne |

| Pasternak | Boris |

| Camus | Albert |

| Shumihin | Sergey |

| Gmurman | Vctor |

| Miller | Frederic |

+------------+----------+

2. 
   Вывести имена сотрудников, выдававших соответствующие книги, и
   

же месяце её вернул.

FROM `records`

LEFT JOIN `workers`

ON `workers`.`id_worker` = `records`.`id_worker_give`

LEFT JOIN `readers`

ON `readers`.`id_ticket` = `records`.`id_ticket`

WHERE date_format(`records`.`delivery_date`, "%Y-%m") = '2011-06'

AND date_format(`records`.`return_date`, "%Y-%m") = '2011-06';

| name | phone |

+-------+------------+

| Donna | 9067911144 |

| Donna | 9067911144 |

| Donna | 9067988877 |

+-------+------------+

3. 
   Вывести имя читателя и название книги для каждой заказанной
   

FROM `orders`

LEFT JOIN `readers`

ON `readers`.`id_ticket` = `orders`.`id_ticket`

LEFT JOIN `absent_books`

ON `absent_books`.`id_absent_book` = `orders`.`id_absent_book`

LEFT JOIN `authors_of_absent_books`

ON `authors_of_absent_books`.`id_absent_book` = `absent_books`.`id_absent_book`

LEFT JOIN `authors`

ON `authors`.`id_author` = `authors_of_absent_books`.`id_author`

WHERE `authors`.`country` = 'USA';
Получаем при использовании запроса:
+---------+---------------------------------+

| name | name_book |

+---------+---------------------------------+

| Maria | Green_mile |

| Karen | Representation_and_intervention |

| Maria | Crisis_of_world_capitalism |

| Donald | Crisis_of_world_capitalism |

| Linda | Quantum_psychology |

| Michael | Quantum_psychology |

| Lisa | Quantum_psychology |