Решение модуля 2.1 из курса «Интерактивный тренажер по SQL»

Разобрали ответы и решения урока 2.1(Связи между таблицами) под курс «Интерактивный тренажер по SQL».

Предыдущий разбор модуля 1.7 следующий разбор модуль 2.2

Добавить новую характеристику книги – ее жанр, если считать, что каждая книга относится к одному жанру, то есть между ними определена связь «один ко многим». Расположите в правильном порядке этапы связывания таблицы с жанрами (genre) и таблицы book.

Добавить новую характеристику книги – ее жанр, если считать, что каждая книга может относиться к нескольким жанрам, а каждый жанр включает несколько книг, то есть между ними определена связь «многие ко многим».
Расположите в правильном порядке этапы связывания таблиц genre и book.

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

• В группе учатся студенты, каждый студент может учиться только в одной группе: студент <— группа (группа одна а студентов много)
• Студенты изучают несколько дисциплин, одну и ту же дисциплину могут изучать несколько студентов: студент <—> дисциплина (студентов много, дисциплин много)
• В библиотеке студент может взять несколько книг, одну и ту же книгу могут взять несколько студентов (в разное время): студент <—> книга (студентов много, книг много)
• Каждый студент родился в определенном городе, в одном городе родились несколько студентов: город —> студент (город один, студентов много)
• Каждый город относится к определенной стране, в каждой стране есть несколько городов: страна —> город (страна одна, городов много)

Дана таблица trip. Выберите одну или несколько схем, которые позволяют правильно представить информацию из этой таблицы в виде нескольких связанных таблиц.

Создать таблицу author следующей структуры:

author_id INT PRIMARY KEY AUTO_INCREMENT
name_author VARCHAR(50)

CREATE TABLE author (
    author_id int PRIMARY KEY AUTO_INCREMENT,
    name_author varchar(50));

Заполнить таблицу author. В нее включить следующих авторов:

Булгаков М.А.
Достоевский Ф.М.
Есенин С.А.
Пастернак Б.Л.

INSERT INTO author (name_author)
VALUES ('Булгаков М.А.'), ('Достоевский Ф.М.'), ('Есенин С.А.'), ('Пастернак Б.Л.')

Перепишите запрос на создание таблицы book , чтобы ее структура соответствовала структуре, показанной на логической схеме (таблица genre уже создана, порядок следования столбцов — как на логической схеме в таблице book, genre_id  — внешний ключ) . Для genre_id ограничение о недопустимости пустых значений не задавать. В качестве главной таблицы для описания поля  genre_id использовать таблицу genre следующей структуры:

genre_id INT PRIMARY KEY AUTO_INCREMENT
name_genre VARCHAR(30)

CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(50), 
    author_id INT NOT NULL,
    genre_id INT,
    price DECIMAL(8, 2), 
    amount INT, 
    FOREIGN KEY (author_id)  REFERENCES author (author_id),
    FOREIGN KEY (genre_id)  REFERENCES genre (genre_id)
);

Создать таблицу book той же структуры, что и на предыдущем шаге. Будем считать, что при удалении автора из таблицы author, должны удаляться все записи о книгах из таблицы book, написанные этим автором. А при удалении жанра из таблицы genre для соответствующей записи book установить значение Null в столбце genre_id. 

CREATE TABLE book (
    book_id INT PRIMARY KEY AUTO_INCREMENT, 
    title VARCHAR(50), 
    author_id INT NOT NULL, 
    genre_id INT,
    price DECIMAL(8,2), 
    amount INT, 
    FOREIGN KEY (author_id)  REFERENCES author (author_id) ON DELETE CASCADE,
    FOREIGN KEY (genre_id)  REFERENCES genre (genre_id) ON DELETE SET NULL
);

Для каждой строки таблицы book занесите значения в поля author_id и genre_id. Считать, что книга Есенина относится к жанру «Поэзия», остальные книги – к жанру «Роман».
Через запятую перечислены значения полей book_id, title, author_id, genre_id, price, amount каждой записи таблицы book. Заполните пропуски.

Добавьте три последние записи (с ключевыми значениями 6, 7, в таблицу book, первые 5 записей уже добавлены:

INSERT INTO book(title, author_id, genre_id, price, amount)
VALUES ('Стихотворения и поэмы', 3, 2, 650.00, 15),
       ('Черный человек', 3, 2, 570.20, 6),
       ('Лирика', 4, 2, 518.99, 2)