Курс по SQL

[realeugene]

mustitz в сообщении #1322944 писал(а):

Ну... реляционная модель это теоретическая абстракция, в которой много чего нет :) И строить курс SQL основываясь исключительно на ней... Ну не знаю...

Вы что сказать-то хотите. Я кратко описал реляционную модель как основу реляционных СУБД, заметив, что в реальности бывают разные расширения. Вы меня начали поправлять, утверждая, что есть ROWID. Ну, бывает, конечно, но не в реляционной модели. Почему вы решили, что курс строится "исключительно на ней"? До хранимой бизнес-логики отсюда очень далеко. Но если вы не знакомы с основами... Ну, соболезную.

[rockclimber]

Продолжаем.

Откуда есть пошла реляционная модель.

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

Допустим, у нас есть телефонная книга:

Имя      Город          Телефон
--------------------------------------------------
Вася     Москва         1234567, 5643218
Петя     Нью-Васюки     9876543, 5616864, 5168646
Маша     Москва         5754566, 6468464
Катя     Нью-Васюки     9895757

Проблема этой таблицы в том, что в ней в некоторых ячейках находятся неатомарные значения. В статье "Первая нормальная форма" в Википедии вы можете прочитать, что "концепция «атомарности» является слишком неясной". Что есть, то есть, однако как-то атомизировать значения все равно придется. Это самое плохо формализуемое место в теории, потому что является ли значение атомарным или нет, зависит от задачи и от того, что вы собираетесь делать с вашими данными. Самые большие проблемы возникают со строками. С числами и датами проще, одно число - это одно атомарное значение в 99,99% случаев. В данном случае наша задача состоит в построении телефонной книги, поэтому отдельный телефонный номер представляет отдельную "единицу хранения". Можно исходить из следующих соображений: если вам надо позвонить человеку, вы будете извлекать из БД один номер за раз и звонить на него (а не на все номера через запятую сразу); если вам кто-то позвонил, то с какого-то одного номера за раз, и искать этот номер в БД должно быть проще по полному совпадению строки, а не делать поиск подстроки в строке для каждой ячейки.
Соответственно, надо таблицу переписать так, чтобы в каждой ячейке было атомарное значение:

Имя      Город          Телефон
--------------------------------------------------
Вася     Москва         1234567
Вася     Москва         5643218
Петя     Нью-Васюки     9876543
Петя     Нью-Васюки     5616864
Петя     Нью-Васюки     5168646
Маша     Москва         5754566
Маша     Москва         6468464
Катя     Нью-Васюки     9895757

Теперь у нас следующая проблема. Допустим, мы решили, что Вася - это не солидно, и надо записать его как "Василий Иванович". Но так как у нас несколько записей с таким именем, надо обновлять все. Недостатки такого способа хранения очевидны: фактически, у нас хранится избыточная информация, которую к тому же легко "испортить" (одного "Васю" заменить на "Василия Ивановича", а второго забыть). Тогда мы разбиваем табличку на две:

Имя      Телефон
------------------
Вася     1234567
Вася     5643218
Петя     9876543
Петя     5616864
Петя     5168646
Маша     5754566
Маша     6468464
Катя     9895757

Имя      Город      
--------------------
Вася     Москва     
Петя     Нью-Васюки 
Маша     Москва      
Катя     Нью-Васюки 

Вторая табличка является основной для имен, а первая только ссылается на вторую, то есть как бы говорит - "Это телефон вон той Кати из Нью-Васюков".
Далее у нас может возникнуть следующая проблема. В какой-то момент телефонная книга разрастется так, что там окажутся два Васи. Или даже два Василия Ивановича. Как их отличать? Можно добавить дату рождения. Или паспортные данные (если повезет их раздобыть). Самый простой способ - придумать какой-то простой числовой идентификатор (просто порядковый номер), и всех пересчитать. И телефоны заодно пересчитать:

ID Имя      Город      
-----------------------
1  Вася     Москва     
2  Петя     Нью-Васюки 
3  Маша     Москва      
4  Катя     Нью-Васюки 

ID Имя          Телефон
------------------------
1  Вася         1234567
2  Вася         5643218
3  Петя         9876543
4  Петя         5616864
5  Петя         5168646
6  Маша         5754566
7  Маша         6468464
8  Катя         9895757

Кроме того, из первой из этих таблиц можно выделить еще одну таблицу с городами, а в таблице с именами оставить просто ссылку на номер города:

ID  Город      
-----------------------
1   Москва     
2   Нью-Васюки 

ID Имя      ID Города      
-----------------------
1  Вася     1     
2  Петя     2
3  Маша     1      
4  Катя     2

И с телефонами ту же замену проделать:

ID ID человека  Телефон
------------------------
1  1            1234567
2  1            5643218
3  2            9876543
4  2            5616864
5  2            5168646
6  3            5754566
7  3            6468464
8  4            9895757

Немножко терминологии (для тех, кто решит подтянуть теорию до приемлемого уровня). Чисто теоретические понятия, которые за пределами книг по теории почти не встречаются:
Отношение - таблица
Кортеж - строка таблицы
Атрибут - столбец таблицы
Потенциальный ключ - атрибут или несколько атрибутов, позволяющие однозначно определить строку в таблице. Возможна ситуация, когда сущестует несколько разных потенциальных ключей.
Первичный ключ - один из потенциальных ключей, выбранных в качестве основного.
Нормальные формы - свойства таблиц отношений, характеризующие степень избыточности. Существуют в количестве аж восьми штук (с номерами с первой по шестую, а третья и пятая имеют подформы без номеров). На практике достаточно уметь отличать третью форму от более низших, а так же вовремя останавливаться. Обозначаются 1НФ, 2НФ, 3НФ...
В примерах выше: первая табличка не соответствует никакой нормальной форме, вторая - соответствует первой нормальной форме, третья - соответствует третьей нормальной форме. (Вторую я проскочил, потому что так проще и короче).

Понятия чуть ближе к практике:
Естественный ключ - потенциальный ключ, состоящий только из "ествественных" данных (например, ФИО и дата рождения в случае человека в нашем примере).
Суррогатный ключ - искусственно придуманный ключ, значения которого не имеют практического смысла за пределами базы данных (порядковые номера ID в последних примерах). Естественный ключ и суррогатный ключ - это понятия не столько из реляционной теории, сколько из практики ее применения. Никаких теоретических преимуществ у суррогатных ключей нет, это чисто практическая штука.
Справочник - простая таблица, имеющая часто всего два столбца - ID (суррогатный ключ) и какое-нибудь значение. Пример - таблица со списком городов в последнем примере.
Master-Detail (не имеет устоявшегося перевода) - две связанных таблицы, одна "главная", другая - "подчиненная". В примере выше, список людей - это "master", список телефонов - "detail". Detail как бы дает более подробную (детальную) информацию.

Немного ближе к практике

Выше мы разбили одну табличку на две (пример 3) и успокоились. В теории этого достаточно (кажется), но на практике теперь надо следить, чтобы, например, если Васю переименуют в одной таблице, то он переименовался и во второй (а проще, конечно, не менять ни там, ни там). Или чтобы в список телефонов не добавили телефон несуществующего человека. Для этого (но не только) есть такая вещь, как ограничения целостности (см. стартовый пост).
Какие ограничения бывают:
1. Ограничение уникальности. Как мы помним, для однозначной идентификации записи нужен первичный ключ, а первичный ключ должен содержать уникальные значения.
2. Внешний ключ. Если у нас есть две связанные таблицы (как в примере выше), можно (и даже нужно) запретить записывать телефон несуществующего человека (ввиду полной бессмысленности этого действия).
3. NOT NULL. Ограничение запрещает принимать значение NULL, то есть значение всегда должно быть известно и заполнено.
4. Первичный ключ. Фактически, комбинация уникальности и NOT NULL.
5. Ограничения на значения данных в столбце. Например, "нельзя указывать отрицательное число", значение строки может быть только "Да", "Нет" и "Не знаю", и т. п.

Вот теперь можно начинать создавать таблицы и заполнять их данными.

[gevaraweb]

rockclimber, спасибо за уроки! Такой вопрос (для пущего правдоподобия добавлю столбец с фамилией). Пусть дана последняя структура:

rockclimber в сообщении #1324294 писал(а):

ID  Город      
-----------------------
1   Москва     
2   Нью-Васюки 
3   Рио-де-Жанейро 

ID Имя      Фамилия      ID Города      
-------------------------------------
1  Вася     Петров     1     
2  Петя     Иванов     2
3  Маша     Белова     1      
4  Катя     Пушкина    2

ID ID человека  Телефон
-------------------------------
1  1            1234567
2  1            5643218
3  2            9876543
4  2            5616864
5  2            5168646
6  3            5754566
7  3            6468464
8  4            9895757

Добавлю таблицу, например, командировок

ID ID чел.  ID Города  Год
-------------------------------
1     3      1      2011
2     1      3      2012
3     2      2      2012
4     3      3      2013
5     4      1      2014
6     3      2      2014
7     2      2      2015
8     1      1      2016

Т.е., например, первая строка означает, что Маша Белова ездила в командировку в Москву в 2011г.
Так вот какой вопрос меня мучает. Если Маша Белова выйдет замуж, и станет Машей Черновой во второй таблице, автоматически во всех отчетах она станет Машей Черновой (при предложенной структуре). Иногда это очень удобно. Но иногда нужно, чтобы в старых отчетах она оставалась Беловой, а в новых - становилась Черновой. Как быть?

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

ID    чел     ID чел.  ID Города  Год
-----------------------------------------
1     Белова     3      1      2011
2     Петров     1      3      2012
3     Иванов     2      2      2012
4     Белова     3      3      2013
5     Пушкина    4      1      2014
6     Чернова    3      2      2014
7     Иванов     2      2      2015
8     Петров     1      1      2016

Зачем я оставил третью колонку? Чтобы всегда можно было заменить старую фамилию на новую. Но пока писал вопрос, понял, что можно разбить таблицу людей на более атомарные. Вот второе решение:

Таблица человеки
ID    ФИО           
--------------------
1  Вася Петров 
2  Петя Иванов
3  Маша Чернова (Белова)
4  Катя Пушкина

Таблица ипостаси
ID Имя      Фамилия      ID Города  ID Человека     
-------------------------------------------------------
1  Вася     Петров         1         1         
2  Петя     Иванов         2         2     
3  Маша     Белова         1         3   // Первая ипостась Маши           
4  Катя     Пушкина        2         4     
3  Маша     Чернова        1         3   // Вторая ипостась Маши     

Тогда все нормально, командировки опишутся как

ID ID ипос.  ID Города  Год
-------------------------------
1     3      1      2011   // Здесь Маша поехала как Белова в отчете
2     1      3      2012
3     2      2      2012
4     3      3      2013   // Здесь Маша поехала как Белова в отчете
5     4      1      2014
6     5      2      2014   // Здесь Маша поехала как Чернова в отчете
7     2      2      2015
8     1      1      2016

Сорри за много букв. Как правильно делать? Если считаете, что вопрос преждевременный в рамках уроков, можете пока проигнорировать.

[iifat]

gevaraweb в сообщении #1324468 писал(а):

Как правильно делать?

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

[rockclimber]

Забивать на нормальные формы не нужно. Это довольно стандартная ситуация - хранение истории изменений какой-либо сущности. Есть два основных варианта:
1 - создание дополнительной "исторической" таблицы, куда скидывать предыдущее "состояние" (и дату изменения) автоматически, триггером на изменение
2 - добавление двух столбцов с датами, которые задают период актуальности. То есть пока Маша - Чернова, у вас начало периода действия стоит с даты рождения, а конец - ну, например, NULL. Потом, когда Маша приносит свидетельство о браке, вы добавляете новую запись, где она уже Белова начиная с даты регистрации брака, а в предыдущей записи ставите дату окончания действия предыдущей фамилии.

Каждый вариант имеет свои достоинства и недостатки, надо выбирать по ситуации. Первый вариант плох тем, что если в одном отчете вам надо будет выбирать и старые данные, и новые, запросы будут слишком усложняться. Этот вариант больше подходит для ситуаций, когда вам в 99% случаев нужны актуальные данные, а историю вы храните на всякий пожарный, просто "чтобы было".
Недостаток второго - усложняется первичный ключ, и суррогатным ключом не обойдешься, придется даты тоже включать в состав ключа, и таскать по всем таблицам. Это не очень удобно, можно сделать гибридый вариант: основная информация будет лежать в одной таблице, где на каждого человека строго по одной записи, а в дополнительной таблице хранить изменяемые данные с датами, и для вывода в отчет использовать эту историческую таблицу. Теперь вы можете довольно легко делать разные отчеты для разных ситуаций. Если вам надо, например, сформировать заново отчет за тот год, когда она была Чернова, вы просто укажете условие "взять строку, где начало периода меньше отчетной даты, а конец - больше". А если надо указать в каждой строке отчета фамилию, которая была в соответствующем году, то условие будет примерно такое же, только сравнивать период действия имени надо будет с датой поездки в командировку.

-- 05.07.2018, 01:28 --

gevaraweb в сообщении #1324468 писал(а):

Сначала я думал - можно забить на нормальные формы

Тут вот еще какой момент. Здесь есть две разные задачи, они имеют дело в двумя разными сущностями. Первая сущность - это человек с неизменяемыми атрибутами (с именем, фамилией и т. п.). Даже если имя поменяется, оно не имеет никакого значения. Взяли новое - забыли про старое, и никаких проблем.
И другая сущность - это человек с изменяемым именем. Тут сущностей уже фактически две (человек и имя), каждая живет своей жизнью и имеет важное значение.
Бытовая логика подсказывает, что эти сущности похожи, но появление вопросов такого рода как у вас (когда кажется, что и такое решение плохо, и такое) - признак того, что, возможно, сущности все-таки разные.

[Geen]

rockclimber в сообщении #1324476 писал(а):

Первая сущность ....
И другая сущность

Я бы описывал не так: есть индивид, и есть его документ...

[gevaraweb]

Ага, понятно, спасибо. Т.е., например, первая сущность - индивид. Туда кидаем неизменяемые данные, и данные, которые хоть и могут измениться, но интересны всегда только актуальные. Например, ФИО, дата рождения, место рождения, телефон.

Вторая сущность - работник. Там поля - фамилия, имя, отчество, адрес, паспортные данные, должность, ученая степень, ученое звание, С и По.

ЗЫ. Должность может в другую таблицу придется кидать, в зависимости от задачи, тут погорячился.
ЗЫЗЫ. Тогда и слово работник - неудачное...

[rockclimber]

Geen в сообщении #1324481 писал(а):

rockclimber в сообщении #1324476 писал(а):

Первая сущность ....
И другая сущность

Я бы описывал не так: есть индивид, и есть его документ...

Документ - это еще одна сущность... У него и срок действия часто задан намного жестче, чем имя - конечная дата действия часто известна уже в момент выдачи.

[realeugene]

rockclimber в сообщении #1324294 писал(а):

Допустим, мы решили, что Вася - это не солидно, и надо записать его как "Василий Иванович".

В этом месте неприятность немного иная: первичным ключом является пара (Имя, Телефон), а атрибут Город зависит только от части ключа Имя, так как предполагается, что каждый человек находится только в одном городе, но он в этом городе может быть доступен по нескольким телефонам. Нарушена вторая нормальная форма. В результате сложности возникнут при попытке поменять Город: Вася может наполовину переехать в Нью-Васюки, но наполовину остаться в Москве, что нарушит целостность базы (будет нарушен подразумеваемый инвариант, что каждый человек сидит только в одном городе). Что касается замены имени, то всегда при изменении атрибута, входящего как часть первичного ключа, приходится править все кортежи с таким атрибутом. Проведённая на этом шаге нормализация только усугубила эту проблему, так как теперь, чтобы изменить Имя у Васи, нужно править уже целых две таблицы, в которые Имя входит в первичные ключи.

Замечу, что рассматриваемое ограничение мне не кажется особо удачным, так как IRL один и тот же Вася может одновременно иметь рабочие места в различных городах с различными телефонами. Если выкинуть инвариант, что каждый человек приписан ровно к одному городу, то тогда оказывается, что исходное отношение уже находится во второй и третьей нормальных формах (2NF и 3NF).

-- 05.07.2018, 04:01 --

rockclimber в сообщении #1324294 писал(а):

ID Имя Город 
-----------------------
1 Вася Москва 
2 Петя Нью-Васюки 
3 Маша Москва 
4 Катя Нью-Васюки 

ID Имя Телефон
------------------------
1 Вася 1234567
2 Вася 5643218
3 Петя 9876543
4 Петя 5616864
5 Петя 5168646
6 Маша 5754566
7 Маша 6468464
8 Катя 9895757

А тут в табличке с телефонами, вообще, в ходе нашей нормализации утерян первичный ключ (и потеряна информация, которую мы хотели сохранить в БД), так как мы предположили, что атрибут Имя больше не идентифицирует определённого человека и, даже, завели первую табличку с численным идентификатором, в которую мы можем добавить двух различных Вась из одного и того же города. Что, кстати, само по себе не здорово, так как численный идентификатор - это плохой идентификатор для людей. Как люди будут различать различных Вась из одной и той же Москвы? "Вася №666"? Нужно завести хотя бы полное ФИО и название отдела в качестве подобного гуманного ключа.

-- 05.07.2018, 04:26 --

gevaraweb в сообщении #1324468 писал(а):

Но иногда нужно, чтобы в старых отчетах она оставалась Беловой, а в новых - становилась Черновой. Как быть?

Очевидно, всё зависит от того, для каких целей вам это может потребоваться? Может быть, Маша мужей меняет как перчатки? Или любит кататься в разные командировки инкогнито: вчера ездила как Маша Чернова, а завтра поедет по другому паспорту как Таня Рыженькая? Вообще говоря, первичные документы лучше чересчур не перенормализовывать: никто же не сможет предугадать, как именно неправильно могла напечатать Машину фамилию секретарша, и не потребуется ли вам сохранить об этой опечатке память? А для агрегированных отчётов, наверное, важна только актуальная фамилия.

[rockclimber]

realeugene в сообщении #1324495 писал(а):

Замечу, что рассматриваемое ограничение мне не кажется особо удачным, так как IRL один и тот же Вася может одновременно иметь рабочие места в различных городах с различными телефонами.

IRL практически любая задача на порядок сложнее адаптированного учебного примера. Ничего не поделаешь. Мне, возможно, следовало как-то словесно ограничить диапазон возможных данных.

realeugene в сообщении #1324495 писал(а):

А тут в табличке с телефонами, вообще, в ходе нашей нормализации утерян первичный ключ (и потеряна информация, которую мы хотели сохранить в БД), так как мы предположили, что атрибут Имя больше не идентифицирует определённого человека и, даже, завели первую табличку с численным идентификатором, в которую мы можем добавить двух различных Вась из одного и того же города. Что, кстати, само по себе не здорово, так как численный идентификатор - это плохой идентификатор для людей.

Численный идентификатор - плохой идентификатор практически для всего. Как я уже сказал -

rockclimber в сообщении #1324294 писал(а):

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

Главное неудобство естественных ключей в том, что вы должны ссылаться из другой таблицы на полный ключ (потому что часть ключа ключом не является). Допустим, у вас в таблице PERSON первичный ключ определен как (FIRST_NAME, LAST_NAME, BIRTHDATE). Тогда, делая ссылку на человека из таблицы PHONE_NUMBER, вы должны и в ней создать поля FIRST_NAME, LAST_NAME, BIRTHDATE. Потом вы создадите таблицу для, например, инвентаризации оборудования и будете заполнять, кому какое оборудование выдали. И туда эти три поля добавите. А потом вам понадобится сделать отчет, который должен выводить список сотрудников, их телефоны и список выданного им оборудования, и у вас будет в запросе:

select ...
  from person p join phone_number n on n.first_name = p.first_name and n.last_name = p.last_name and n.birthdate = p.birthdate
                  join equipment e on e.first_name = p.first_name and e.last_name = p.last_name and e.birthdate = p.birthdate

Вместо того, чтобы просто написать

select ...
  from person p join phone_number n on n.person_id = p.id
                  join equipment e on e.person_id = p.id

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

[realeugene]

rockclimber в сообщении #1324516 писал(а):

Главное неудобство естественных ключей в том, что вы должны ссылаться из другой таблицы на полный ключ (потому что часть ключа ключом не является). Допустим, у вас в таблице PERSON первичный ключ определен как (FIRST_NAME, LAST_NAME, BIRTHDATE). Тогда, делая ссылку на человека из таблицы PHONE_NUMBER, вы должны и в ней создать поля FIRST_NAME, LAST_NAME, BIRTHDATE. Потом вы создадите таблицу для, например, инвентаризации оборудования и будете заполнять, кому какое оборудование выдали. И туда эти три поля добавите. А потом вам понадобится сделать отчет, который должен выводить список сотрудников, их телефоны и список выданного им оборудования, и у вас будет в запросе...

Да, всё верно. ID сотрудника выкидывать я не предлагал. Это - важный ключ, однозначно идентифицирующий сотрудника в БД. Я лишь заметил, что если в организации возникли сложности с идентификацией людей, ID сотрудника решает эту проблему только для компьютера, но не для людей.


Кстати, хороший пример, иллюстрирующий, что для реляционной модели и SQL не важно, какие именно ключи выбраны в качестве первичных ключей: работать будет в любом случае и без потерь информации, если нормализация проведена корректно.

rockclimber в сообщении #1324516 писал(а):

и у вас будет в запросе:

select ...
from person p join phone_number n on n.first_name = p.first_name and n.last_name = p.last_name and n.birthdate = p.birthdate
join equipment e on e.first_name = p.first_name and e.last_name = p.last_name and e.birthdate = p.birthdate

Вместо того, чтобы просто написать

select ...
from person p join phone_number n on n.person_id = p.id
join equipment e on e.person_id = p.id

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

[beroal]

Вообще-то, понятия «естественный ключ» и «суррогатный ключ» нужно убрать из учебного курса. Достаточно написать, почему использовать ФИО и тому подобные атрибуты в качестве первичных ключей не стоит. Следующий шаг — сделать первичные ключи абстрактными, так что даже обсуждение разницы между «естественными» и «суррогатными» ключами теряет смысл. Но отрасль к этому ещё не готова.

[rockclimber]

beroal в сообщении #1324552 писал(а):

Следующий шаг — сделать первичные ключи абстрактными

Ээээ... А можно вас попросить уточнить, чем абстрактный ключ отличается от суррогатного?

[upgrade]

rockclimber
Как-то с ключами запутанно получается.
Есть связи "один ко многим" "многие к одному" и "многие к многим".
Ключи позволяют реализовывать такие связи. Ключ - это уникальный идентификатор строки, то есть это не нумерация, а именно идентификация. Тому же акцесс все равно какой номер у строки, он строку с номером $1$ может поставить после строки с номером $2$, так как строки (или векторы данных) в акцесс не идут "одна за другой", они все в общем котле.

[beroal]

rockclimber в сообщении #1324557 писал(а):

beroal в сообщении #1324552

wrote:
Следующий шаг — сделать первичные ключи абстрактными Ээээ... А можно вас попросить уточнить, чем абстрактный ключ отличается от суррогатного?

Да не вопрос, конечно, можно. Абстрактному ключу программист не назначает тип данных, не назначает автоинкремент или генератор значений ключа (зависит от СУБД). Всё равно эти параметры стандартны и не зависят от задачи.

upgrade в сообщении #1324581 писал(а):

Ключи позволяют реализовывать такие связи. Ключ - это уникальный идентификатор строки, то есть это не нумерация, а именно идентификация.

Вот и я о том же. Тем не менее, программисту всё ещё приходится указывать, что этот идентификатор есть натуральное число, хотя ему фиолетово, какой тип данных.