Как составить сложный SQL запрос на создание таблиц со связями и объединением запросов?

Question

[Дмитрий]

Собственно задача:

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

Структуру базы описать в виде SQL-скрипта создания нужных таблиц и связей.

Информация о книге

* Название - текст

* Автор - текст

* Статус = [свободна, взята]

* Кем взята, если взята

* Когда взята, если взята - дата

* До какого числа, если взята - дата

Если книга имеется более чем в одном экземпляре, это необходимо отразить в базе.

Информация о читателе

* Имя - текст

* Фамилия - текст

* Служебная информация = [постоянный читатель, редкий читатель, читатель только читального зала]

Информация о взятых книгах

Какой документ был предъявлен при выдаче книги = [Усы, Лапы, Хвост]

Информация о документах

Усы:

* серия документа - 4 цифры, обязательно к заполнению

* номер документа - 6 цифр, обязательно к заполнению

* кем выдан - текст

* когда выдан - дата

Лапы:

* серия документа - набор из цифр и латинских букв длиной от 2 до 5 символов, необязательно к заполнению

* номер документа - набор из цифр и латинских букв длиной от 6 до 8 символов, обязательно к заполнению

* кем выдан - текст

Хвост:

* номер документа - набор из цифр и латинских букв длиной от 6 до 8 символов, обязательно к заполнению

* дата начала срока действия - дата

* дата окончания срока действия – дата

Попытался что-то написать, но сильно сомневаюсь в корректности. Особенно не понятна последняя часть с разными видами данных и условий для документов. Спасибо.

UPD.
Что получилось самостоятельно на данный момент:

CREATE TABLE books ( book_id int (10) AUTO_INCREMENT NOT NULL, book_name VARCHAR(255) NOT NULL, book_author VARCHAR (255) NOT NULL, book_status ENUM (‘Y’,’N’) NOT NULL default(Y), book_owner INT(11), book_take_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP, book_return_date DATETIME NOT NULL, FOREIGN KEY (book_owner) REFERENCES users (user_id)))
UNION
CREATE TABLE users ( user_id int (10) AUTO_INCREMENT NOT NULL, user_firstname VARCHAR(255) NOT NULL, user_lastname VARCHAR (255) NOT NULL, user_info ENUM(‘regular_reader’, ’rare_reader’ , ’reading_room’) NOT NULL))
UNION
CREATE TABLE busy_book ( id int (10) AUTO_INCREMENT NOT NULL, bbook_id INT(11) NOT NULL, bbook_document VARCHAR (255) NOT NULL, user_info ENUM(‘regular_reader’, ’rare_reader’ , ’reading_room’) NOT NULL, FOREIGN KEY (bbook_id) REFERENCES books(book_id))

Дальше понял, что уже все не так :(

Answer 1

[Сергей Протько]

www.w3schools.com/sql/sql_create_table.asp
www.w3schools.com/sql/sql_foreignkey.asp

Comments

[Дмитрий]

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

[Андрей Буров]

@another_dream Дык покажите что получилось

[Дмитрий]

@BuriK666 Обновил вопрос

[Сергей Протько]

@another_dream ну... как то так и выходит. А как вы предлагаете "уменьшить количество кода"?

[Дмитрий]

@Fesor Я не привык делать это с помощью скриптов, поэтому не уверен в качестве. Не должно ли быть кода меньше в несколько раз? Возможно какие-то функции есть стандартные.

[Сергей Протько]

@another_dream на чистом SQL меньше не получить.

Answer 2

[pi314]

offtop: откуда в деревне Простоквашино взялась городская библиотека?

А по сути - два соображения:

1. Скрипты создания таблиц некорректно называть SQL, т.к. SQL - язык запросов к данным, а его расширение для манипуляции структурой БД, на котором пишутся эти скрипты, называется DDL (Data Definition Language).

2. И, наконец, с непривычки решать такие задачи лучше графически... А потом уже писать скрипты.

Comments

[Дмитрий]

Очень, конечно, тактично подошли к вопросу :) Насчет первого пункта, я с вами согласен, но в данном случае задача ясна и понятна, остальное уже нечто вроде придирок, но не суть :) По началу думал решить задачу графически с помощью UML схемы классов, тут вы правы, это проще, конечно, но опять же -- некоторые моменты просто не могу понять. Например, последняя часть про типы документов. Вообще не представляю как в таблице можно такое реализовать, чтобы было несколько типов документов и у каждого были свои столбцы с уникальными условиями, вроде обязательно к заполнению или нет. Как вариант -- 10 столбцов(4 + 3 + 3) или я ошибаюсь?

[pi314]

@another_dream Видите ли, если бы Вы привели нарисованый UML или ERD, можно бы было понять, что непонятно или подсказать, что неправильно... a так - это как партия в шахматы по переписке. У меня, лично, точно терпения не хватит... уж извините.

Answer 3

[Sergey Romanov]

Зачем использовать UNION? нужно просто создавать таблици одну за другой и правильно расставить FOREIGN KEY.

Тка же учтите что таблици должны быть типа Innodb так как MyISAM не поддреживает FOREIGN KEY.

Но лучше всего скачайте MySQL Workbench и там визуально сделайте вашу базу, на выходе получите SQL запрос который ее создает, со всемми ссылками с поля на поле.

Comments

[Дмитрий]

Не знал о Workbench. Спасибо!