ОП.08 - Практический экзамен - Вариант №2

База данных домашней коллекции книг

Необходимо разработать БД, в которой должны быть отражены следующие сущности:

Сущность «Книга»
- Атрибут «Наименование книги»
- Атрибут «Год издания»
Сущность «Автор»
- Атрибут «Имя автора»
Сущность «Жанр»
- Атрибут «Жанр книги»
Сущность «Полка»
- Атрибут «Название полки»

При этом, учесть, что:

У одного автора может быть несколько книг
У книги может быть только один автор
На одной полке может быть ни одной, одна или несколько книг
У книги может быть только один жанр

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

Необходимо импортировать в созданную БД данные:

exam_var_2_data.sql

DROP TABLE IF EXISTS tmp;
CREATE TABLE IF NOT EXISTS tmp(
  id        INTEGER  NOT NULL  PRIMARY KEY  AUTO_INCREMENT
  ,title    VARCHAR(255)
  ,author   VARCHAR(255)
  ,year     INTEGER
  ,genre    VARCHAR(255)
  ,shelf    VARCHAR(255)
);
INSERT INTO tmp(title,author,year,genre,shelf) VALUES
 ('Тысяча и одна ночь','Неизвестный автор',2000,'Сказки','Гостиная')
,('Божественная комедия','Данте Алигьери',2000,'Поэма','Гостиная')
,('Сказки','Ханс Кристиан Андерсен',2000,'Неизвестный жанр','Гостиная')
,('И пришло разрушение','Чинуа Ачебе',1958,'Неизвестный жанр','Гостиная')
,('Отец Горио','Оноре де Бальзак',1835,'Неизвестный жанр','Гостиная')
,('Трилогия','Сэмюэл Беккет',2000,'Неизвестный жанр','Гостиная')
,('Декамерон','Джованни Боккаччо',2000,'Неизвестный жанр','Гостиная')
,('Вымыслы','Хорхе Луис Борхес',2000,'Неизвестный жанр','Гостиная')
,('Грозовой перевал','Эмили Бронте',1847,'Неизвестный жанр','Гостиная')
,('Рамаяна','Вальмики',2000,'Неизвестный жанр','Гостиная')
,('Энеида','Вергилий',2000,'Неизвестный жанр','Гостиная')
,('Миссис Дэллоуэй','Вирджиния Вулф',1925,'Неизвестный жанр','Гостиная')
,('На маяк','Вирджиния Вулф',1927,'Неизвестный жанр','Гостиная')
,('Махабхарата','Вьяса',2000,'Неизвестный жанр','Гостиная')
,('Голод','Кнут Гамсун',1890,'Неизвестный жанр','Гостиная')
,('Цыганское романсеро','Федерико Гарсиа Лорка',1928,'Неизвестный жанр','Гостиная')
,('Сто лет одиночества','Габриэль Гарсиа Маркес',1967,'Неизвестный жанр','Гостиная')
,('Любовь во время чумы','Габриэль Гарсиа Маркес',1985,'Неизвестный жанр','Гостиная')
,('Фауст','Иоганн Вольфганг фон Гёте',1832,'Неизвестный жанр','Гостиная')
,('Тропы по большому сертану','Жуан Гимарайнс Роза',1956,'Неизвестный жанр','Гостиная')
,('Мёртвые души','Николай Васильевич Гоголь',1842,'Неизвестный жанр','Гостиная')
,('Жестяной барабан','Гюнтер Грасс',1959,'Неизвестный жанр','Гостиная')
,('Берлин Александрплац','Альфред Дёблин',1929,'Неизвестный жанр','Гостиная')
,('Улисс','Джеймс Джойс',1922,'Неизвестный жанр','Спальня')
,('Жак-фаталист и его хозяин','Дени Дидро',1796,'Неизвестный жанр','Спальня')
,('Большие надежды','Чарльз Диккенс',1861,'Неизвестный жанр','Спальня')
,('Преступление и наказание','Фёдор Достоевский',1866,'Роман','Спальня')
,('Идиот','Фёдор Достоевский',1869,'Роман','Спальня')
,('Бесы','Фёдор Достоевский',1872,'Роман','Спальня')
,('Братья Карамазовы','Фёдор Достоевский',1880,'Роман','Спальня')
,('Медея','Еврипид',2000,'Неизвестный жанр','Спальня')
,('Самопознание Дзено','Итало Звево',1923,'Неизвестный жанр','Спальня')
,('Кукольный дом','Генрик Ибсен',1879,'Неизвестный жанр','Спальня')
,('Стон горы','Ясунари Кавабата',1954,'Неизвестный жанр','Спальня')
,('Невероятные похождения Алексиса Зорбаса','Никос Казандзакис',1946,'Неизвестный жанр','Спальня')
,('Шакунтала','Калидаса',2000,'Неизвестный жанр','Спальня')
,('Посторонний','Альбер Камю',1942,'Неизвестный жанр','Спальня')
,('Рассказы','Франц Кафка',1924,'Неизвестный жанр','Спальня')
,('Процесс','Франц Кафка',1925,'Роман','Спальня')
,('Замок','Франц Кафка',1926,'Роман','Спальня')
,('Ностромо','Джозеф Конрад',1904,'Неизвестный жанр','Спальня')
,('Самостоятельные люди','Халлдор Кильян Лакснесс',2000,'Неизвестный жанр','Спальня')
,('Стихи','Джакомо Леопарди',1818,'Неизвестный жанр','Спальня')
,('Золотая тетрадь','Дорис Лессинг',1962,'Неизвестный жанр','Спальня')
,('Пеппи Длинныйчулок','Астрид Линдгрен',1945,'Неизвестный жанр','Гостиная')
,('Сыновья и любовники','Дэвид Герберт Лоуренс',1913,'Неизвестный жанр','Гостиная')
,('Записки сумасшедшего','Лу Синь',1918,'Неизвестный жанр','Гостиная')
,('Будденброки','Томас Манн',1901,'Неизвестный жанр','Гостиная')
,('Волшебная гора','Томас Манн',1924,'Неизвестный жанр','Гостиная')
,('Предания нашей улицы','Нагиб Махфуз',1959,'Неизвестный жанр','Гостиная')
,('Моби Дик','Герман Мелвилл',1851,'Неизвестный жанр','Гостиная')
,('Опыты','Мишель де Монтень',1595,'Неизвестный жанр','Гостиная')
,('История','Эльза Моранте',1974,'Неизвестный жанр','Гостиная')
,('Возлюбленная','Тони Моррисон',1987,'Неизвестный жанр','Гостиная')
,('Человек без свойств','Роберт Музиль',2000,'Неизвестный жанр','Гостиная')
,('Лолита','Владимир Набоков',1955,'Неизвестный жанр','Гостиная')
,('Метаморфозы','Публий Овидий Назон',2000,'Неизвестный жанр','Гостиная')
,('1984','Джордж Оруэлл',1949,'Антиутопия','Гостиная')
,('Книга непокоя','Фернанду Пессоа',2000,'Неизвестный жанр','Гостиная')
,('Рассказы','Эдгар Аллан По',2000,'Неизвестный жанр','Гостиная')
,('В поисках утраченного времени','Марсель Пруст',2000,'Неизвестный жанр','Гостиная')
,('Гаргантюа и Пантагрюэль','Франсуа Рабле',2000,'Неизвестный жанр','Гостиная')
,('Педро Парамо','Хуан Рульфо',1955,'Неизвестный жанр','Гостиная')
,('Маснави','Джалаладдин Руми',2000,'Неизвестный жанр','Гостиная')
,('Дети полуночи','Салман Рушди',1981,'Неизвестный жанр','Гостиная')
,('Гордость и предубеждение','Джейн Остин',1813,'Неизвестный жанр','Гостиная')
,('Бустан','Саади',1257,'Неизвестный жанр','Гостиная')
,('Сезон паломничества на Север','Тайиб Салих',1966,'Неизвестный жанр','Гостиная')
,('Слепота','Жозе Сарамаго',1995,'Неизвестный жанр','Гостиная')
,('Путешествия Гулливера','Джонатан Свифт',1726,'Неизвестный жанр','Гостиная')
,('Путешествие на край ночи','Луи-Фердинанд Селин',1932,'Неизвестный жанр','Гостиная')
,('Хитроумный идальго Дон Кихот Ламанчский','Мигель де Сервантес',2000,'Роман','Гостиная')
,('Повесть о блистательном принце Гэндзи','Мурасаки Сикибу',2000,'Неизвестный жанр','Рабочий кабинет')
,('Эдип-царь','Софокл',2000,'Неизвестный жанр','Рабочий кабинет')
,('Красное и чёрное','Стендаль',1830,'Неизвестный жанр','Рабочий кабинет')
,('Жизнь и мнения джентльмена Тристрама Шенди','Лоренс Стерн',1760,'Неизвестный жанр','Рабочий кабинет')
,('Приключения Гекльберри Финна','Марк Твен',1884,'Неизвестный жанр','Рабочий кабинет')
,('Война и мир','Лев Толстой',1869,'Роман','Рабочий кабинет')
,('Анна Каренина','Лев Толстой',1877,'Роман','Рабочий кабинет')
,('Смерть Ивана Ильича','Лев Толстой',1886,'Роман','Рабочий кабинет')
,('Листья травы','Уолт Уитмен',1855,'Неизвестный жанр','Рабочий кабинет')
,('Госпожа Бовари','Гюстав Флобер',1857,'Неизвестный жанр','Рабочий кабинет')
,('Воспитание чувств','Гюстав Флобер',1869,'Неизвестный жанр','Рабочий кабинет')
,('Авессалом-Авессалом!','Уильям Фолкнер',1936,'Неизвестный жанр','Рабочий кабинет')
,('Шум и ярость','Уильям Фолкнер',1929,'Неизвестный жанр','Рабочий кабинет')
,('Старик и море','Эрнест Хемингуэй',1952,'Неизвестный жанр','Рабочий кабинет')
,('Стихотворения','Пауль Целан',1952,'Неизвестный жанр','Рабочий кабинет')
,('Рассказы','Антон Чехов',1886,'Неизвестный жанр','Рабочий кабинет')
,('Кентерберийские рассказы','Джеффри Чосер',2000,'Неизвестный жанр','Рабочий кабинет')
,('Гамлет','Уильям Шекспир',1603,'Поэма','Рабочий кабинет')
,('Король Лир','Уильям Шекспир',1608,'Поэма','Рабочий кабинет')
,('Отелло','Уильям Шекспир',1609,'Поэма','Рабочий кабинет')
,('Мидлмарч','Джордж Элиот',1871,'Неизвестный жанр','Рабочий кабинет')
,('Человек-невидимка','Ральф Эллисон',1952,'Неизвестный жанр','Рабочий кабинет')
,('Воспоминания Адриана','Маргерит Юрсенар',1951,'Неизвестный жанр','Рабочий кабинет')
,('Война и мир','Лев Толстой',1869,'Неизвестный жанр','Рабочий кабинет')
,('1984','Джордж Оруэлл',1949,'Неизвестный жанр','Рабочий кабинет')
,('Лолита','Владимир Набоков',1955,'Неизвестный жанр','Рабочий кабинет')
,('Шум и ярость','Уильям Фолкнер',1929,'Неизвестный жанр','Рабочий кабинет')
,('Невидимка','Ральф Эллисон',1952,'Неизвестный жанр','Рабочий кабинет')
,('К маяку','Вирджиния Вульф',1927,'Неизвестный жанр','Рабочий кабинет')
,('Иллиада и Одиссея','Гомер',2000,'Поэма','Рабочий кабинет')
,('Гордость и предубеждение','Джейн Остин',1813,'Неизвестный жанр','Рабочий кабинет')
,('Божественная комедия','Данте Алигьери',1321,'Неизвестный жанр','Рабочий кабинет')
,('Кентерберийские рассказы','Джоффри Чосер',2000,'Неизвестный жанр','Рабочий кабинет')
,('Путешествия Гулливера','Джонатан Свифт',1726,'Неизвестный жанр','Рабочий кабинет')
,('Миддлмарч','Джордж Элиот',1874,'Неизвестный жанр','Рабочий кабинет')
,('Распад','Чинуа Ачебе',1958,'Неизвестный жанр','Рабочий кабинет')
,('Над пропастью во ржи','Джером Д. Сэлинджер',1951,'Неизвестный жанр','Рабочий кабинет')
,('Унесённые ветром','Маргарет Митчелл',1936,'Неизвестный жанр','Рабочий кабинет')
,('Сто лет одиночества','Габриэль Гарсия Маркес',1967,'Неизвестный жанр','Рабочий кабинет')
,('Великий Гэтсби','Фрэнсис Скотт Фитцджеральд',1925,'Неизвестный жанр','Рабочий кабинет')
,('Уловка 22','Джозеф Хеллер',1961,'Неизвестный жанр','Рабочий кабинет')
,('Возлюбленная','Тони Моррисон',1987,'Неизвестный жанр','Рабочий кабинет')
,('Гроздья гнева','Джон Стейнбек',1939,'Неизвестный жанр','Рабочий кабинет')
,('Дети полуночи','Салман Рушди',1981,'Неизвестный жанр','Рабочий кабинет')
,('Дивный новый мир','Олдос Хаксли',1932,'Неизвестный жанр','Рабочий кабинет')
,('Миссис Дэллоуэй','Вирджиния Вульф',1925,'Неизвестный жанр','Рабочий кабинет')
,('Сын Америки','Ричард Райт',1940,'Неизвестный жанр','Рабочий кабинет')
,('Демократия в Америке','Алексис де Токвиль',1835,'Неизвестный жанр','Рабочий кабинет')
,('Происхождение видов','Чарльз Дарвин',1859,'Неизвестный жанр','Рабочий кабинет')
,('Общественный договор','Жан-Жак Руссо',1762,'Неизвестный жанр','Рабочий кабинет')
,('Капитал','Карл Маркс',1867,'Неизвестный жанр','Рабочий кабинет')
,('Государь','Никколо Макиавелли',1532,'Неизвестный жанр','Рабочий кабинет')
,('Левиафан','Томас Гоббс',1651,'Неизвестный жанр','Рабочий кабинет')
,('Властелин колец','Джон Толкиен',1954,'Неизвестный жанр','Рабочий кабинет')
,('Винни-Пух','Алан Александр Милн',1926,'Неизвестный жанр','Рабочий кабинет')
,('Лев, колдунья и платяной шкаф','Клайв Льюис',1950,'Неизвестный жанр','Рабочий кабинет')
,('Поездка в Индию','Эдуард Форстер',1924,'Неизвестный жанр','Рабочий кабинет')
,('В дороге','Джон Керуак',1957,'Неизвестный жанр','Рабочий кабинет')
,('Убить пересмешника','Харпер Ли',1960,'Неизвестный жанр','Рабочий кабинет')
,('Заводной апельсин','Энтони Берджесс',1962,'Неизвестный жанр','Рабочий кабинет')
,('Свет в августе','Уильям Фолкнер',1932,'Неизвестный жанр','Рабочий кабинет')
,('Души чёрного народа','Уильям Дю Бойс',1903,'Неизвестный жанр','Рабочий кабинет')
,('Безбрежное Саргассово море','Джин Рис',1966,'Неизвестный жанр','Рабочий кабинет')
,('Мадам Бовари','Гюстав Флобер',1857,'Неизвестный жанр','Гостиная')
,('Потерянный рай','Джон Милтон',1667,'Неизвестный жанр','Гостиная')
,('Анна Каренина','Лев Толстой',1877,'Неизвестный жанр','Гостиная')
,('Гамлет','Уильям Шекспир',1603,'Неизвестный жанр','Гостиная')
,('Король Лир','Уильям Шекспир',1608,'Неизвестный жанр','Гостиная')
,('Отелло','Уильям Шекспир',1622,'Неизвестный жанр','Гостиная')
,('Сонеты','Уильям Шекспир',1609,'Неизвестный жанр','Гостиная')
,('Листья травы','Уолт Уитмен',1855,'Неизвестный жанр','Гостиная')
,('Приключения Гекльберри Финна','Марк Твен',1885,'Неизвестный жанр','Гостиная')
,('Ким','Редьярд Киплинг',1901,'Неизвестный жанр','Гостиная')
,('Франкенштейн','Мэри Шелли',1818,'Неизвестный жанр','Гостиная')
,('Песнь Соломона','Тони Моррисон',1977,'Неизвестный жанр','Гостиная')
,('Пролетая над гнездом кукушки','Кен Кизи',1962,'Неизвестный жанр','Гостиная')
,('По ком звонит колокол','Эрнест Хемингуэй',1940,'Неизвестный жанр','Гостиная')
,('Бойня номер пять','Курт Воннегут',1969,'Неизвестный жанр','Гостиная')
,('Скотный двор','Джордж Оруэлл',1945,'Неизвестный жанр','Гостиная')
,('Повелитель мух','Уильям Голдинг',1954,'Неизвестный жанр','Гостиная')
,('Хладнокровное убийство','Труман Капоте',1965,'Неизвестный жанр','Гостиная')
,('Золотая тетрадь','Дорис Лессинг',1962,'Неизвестный жанр','Гостиная')
,('В поисках потерянного времени','Марсель Пруст',1913,'Неизвестный жанр','Гостиная')
,('Большой сон','Реймонд Чандлер',1939,'Неизвестный жанр','Гостиная')
,('Когда я умирала','Уильям Фолкнер',1930,'Неизвестный жанр','Гостиная')
,('Фиеста','Эрнест Хемингуэй',1926,'Неизвестный жанр','Гостиная')
,('Я, Клавдий','Роберт Грейвз',1934,'Неизвестный жанр','Гостиная')
,('Сердце — одинокий охотник','Карсон Маккаллерс',1940,'Неизвестный жанр','Гостиная')
,('Сыновья и любовники','Дэвид Лоуренс',1913,'Неизвестный жанр','Гостиная')
,('И вся королевская рать','Роберт Пенн Уоррен',1946,'Неизвестный жанр','Гостиная')
,('Иди, вещай с горы','Джеймс Болдуин',1953,'Неизвестный жанр','Гостиная')
,('Паутина Шарлотты','Эдвин Уайт',1952,'Неизвестный жанр','Гостиная')
,('Сердце тьмы','Джозеф Конрад',1902,'Неизвестный жанр','Гостиная')
,('Ночь','Эли Визель',1958,'Неизвестный жанр','Гостиная')
,('Кролик, беги','Джон Апдайк',1960,'Неизвестный жанр','Гостиная')
,('Эпоха невинности','Эдит Уортон',1920,'Неизвестный жанр','Гостиная')
,('Случай портного','Филип Рот',1969,'Неизвестный жанр','Гостиная')
,('Американская трагедия','Теодор Драйзер',1925,'Неизвестный жанр','Спальня')
,('День саранчи','Натаниэль Уэст',1939,'Неизвестный жанр','Спальня')
,('Тропик рака','Генри Миллер',1934,'Неизвестный жанр','Спальня')
,('Мальтийский сокол','Дэшиел Хэммет',1930,'Неизвестный жанр','Спальня')
,('Тёмные начала','Филип Пулман',1995,'Неизвестный жанр','Спальня')
,('Смерть пришла архиепископу','Уилла Кейтер',1927,'Неизвестный жанр','Спальня')
,('Толкования снов','Зигмунд Фрейд',1900,'Неизвестный жанр','Спальня')
,('Воспитание Генри Адамса','Генри Адамс',1918,'Неизвестный жанр','Спальня')
,('Цитатник','Мао Цзедун',1964,'Неизвестный жанр','Спальня')
,('Многообразие религиозного опыта','Уильям Джемс',1902,'Неизвестный жанр','Спальня')
,('Возвращение в Брайдсхед','Ивлин Во',1945,'Неизвестный жанр','Спальня')
,('Безмолвная весна','Рейчел Карсон',1962,'Неизвестный жанр','Спальня')
,('Общая теория занятости, процента и денег','Джон Кейнс',1936,'Неизвестный жанр','Спальня')
,('Лорд Джим','Джозеф Конрад',1900,'Неизвестный жанр','Спальня')
,('Простимся со всем этим','Роберт Грейвз',1929,'Неизвестный жанр','Спальня')
,('Общество изобилия','Джон Кеннет Гэлбрейт',1958,'Неизвестный жанр','Спальня')
,('Ветер в ивах','Кеннет Грэм',1908,'Неизвестный жанр','Спальня')
,('Автобиография Малколма Икс','Алекс Хейли и Малколм Икс',1965,'Неизвестный жанр','Спальня')
,('Выдающиеся викторианцы','Литтон Стречи',1918,'Неизвестный жанр','Спальня')
,('Цвет пурпурный','Элис Уокер',1982,'Неизвестный жанр','Спальня')
,('Вторая мировая война','Уинстон Черчилль',1948,'Неизвестный жанр','Спальня')
;

Задачи

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

Необходимо написать соответствующие SQL-запросы для выполнения следующих действий с базой данных:

Для каждого автора показать список его книг (отсортировать по автору)
Вывести количество изданных за год книг у одного любого автора
Найти автора с самым большим числом книг
Найти все дублирующиеся книги в рамках одного автора, удалить лишние, оставив только одну (на примере одного любого набора дублей)
Вывести авторов всех книг из любого местоположения на выбор
Вывести пять самых старых книг
Вывести общее количество книг на любой полке

Совет

Рекомендуемый порядок разбора данных:

Жанр (первичный ключ id генерируемый)
Полка (первичный ключ id генерируемый)
Автор (первичный ключ id генерируемый)
Книга (можно использовать id из tmp как первичный ключ)

Результат

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

ОП.08 - Вариант 2 - Иванов И.И.

где вместо Иванов И.И. должны быть указаны фамилия и инициалы сдающего.

Результат выполнения задания должен быть оформлен в виде файлов:

Файл дампа итоговой БД в формате SQL, должен называться: db_2.sql
Файл последовательных SQL-запросов для каждого из пунктов задания, должен называться: task_2.sql, каждый запрос должен отделяться комментарием с номером соответствующего пункта задания
Файл, отражающий ERD-диаграмму созданной БД со всеми связями, должен называться erd_2.png