пятница, 13 января 2012 г.

Создание схемы Базы Данных

Человек запоминает 10% услышанного, 20% увиденного и 70% того, что он сделал сам. А чтобы закрепить успех, необходимо научить других тому, что знаешь сам.
Правда, до учителей многим (мне, в частности) далеко, но можно хотя бы записать то, что узнал, для таких же новичков - пошагово. При написании текста внезапно понимаешь, что не все так просто и ты за сутки половину забыл :) Но я попробую закрепить новые знания.
Итак, основная задача - в автоматическом тесте при подготовке данных изменить базу, а не ползать через GUI.
Чтобы изменить базу, надо вначале понять, как нам к ней обращаться. Чтобы к ней обращаться, надо знать, как она строится.
Разберемся на примере базы книг. Мы хотим создать диаграмму Базу Данных имеющихся в наличии книг. У каждой книги есть какой-то жанр и какой-то автор.
Причем у каждой книги должен быть строго один жанр и один или несколько авторов.

Где будет создавать? В специальном приложении.

Устанавливаем и запускаем Microsoft SQL Management Studio.
Видим окно "Connect to Server", где мы можем выбрать тип и имя сервера, а также аутентификацию - выбираем Windows Authentication - Connect.
Слева появилось меню - Object Explorer.
Разворачиваем наш сервер - видим следующий список
Databases (правый клик) - New Database
Создаем базу книг -  Books.
Разворачиваем нашу базу.
Database Diagram - New Database Diagram
Когда мы рисуем диаграмму и добавляем туда таблицы, они появляются в разделе таблиц - повторяться нам не придется.
Когда мы создаем новую диаграмму, открывается пустое окно. Щелкаем правой кнопкой - New Table.
Начнем с книг. Называем таблицу Book (называть таблицы принято в единственном числе).
Заполняем ее, как на рисунке, только пока - без "ключика" слева от поля "id".

Что мы сделали? Мы сделали таблицу книг, сказав, что колонками у нее будут - id, название и описание. Имена колонкам дали.
Data Type - тип данных, id - число, поэтому int. С другой стороны, это не просто число. Id должно увеличиваться, желательно само. Это называется автоинкрементное поле - задаем начальное значение и инкремент, шаг, на который поле будет увеличиваться с каждой новой записью в базе.

Чтобы сделать id автоинкрементным, открываем свойства этой колонки.
View - Properties Window.
В свойствах нас интересует Identity Specification.
Разворачиваем пункт, говорим, что да - поле "самовозрастающее", ставим "Yes".
Указываем инкремент и Seed (начальное значение)


Название и описание - строки. Указываем им тип "nvarchar(50)" и ручками меняем число "50" на любое другое количество символов.
Allow Nulls - в этой колонке мы указываем, будет ли поле обязательным для заполнения. То есть если галка стоит - "вводить null разрешено", а "вводить null" = "не вводить ничего". Id и имя - обязательные поля, они есть у каждой книги. Описание можно оставить необязательным - ставим там галку.

Книги создали. Правый клик - New Table - Genre.

Что должно быть у жанра? Id и имя. Оба поля обязательны.
По аналогии создаем таблицу авторов:


Ок, таблицы есть. Теперь надо настроить связи между ними.
Как настраиваются связи? С помощью ключей. Почитать о них можно в вики, в строке "Ключи" приведенной таблички.
Нас интересуют:
1. PK - primary key, первичный ключ.
Он накладывается на всю таблицу, но мы можем выбрать одну или несколько колонок.
Выбираем одну колонку - ключ говорит о том, что каждое значение в ячейках этой колонки уникальное.
Выбираем несколько колонок - ключ говорит о том, что комбинации строк по колонкам уникальны.
2. FK - foreign key, внешний ключ.
Нужен для связки двух таблиц в разных соотношениях (1:1, 1:N, N:N)
Этот ключ указываем в "дочерней" таблице, то есть в той, которая ссылается.
Обозначим наши ключи. Для этого нам помогут следующие действия:
Set Primare key - назначить PK
Relationships - назначить FK

Во всех трех таблицах назначаем колонку "Id" первичным ключом - Set Primare key.
Слева от строки появятся ключи, как на картинках выше.

Разберемся со связями.
У одной книги может быть только один жанр.
Одному жанру может соответстветствовать много книг.
В таблице "Book" добавляем еще одну колонку - "genreId", значение int, поле обязательно для заполнения. Так как книга без жанра быть не может.
В таблице "Book" в колонке "genreId" открываем Relationships. В открывшемся окне при необходимости меняем название. Делается это в свойстве Identity - Name

Название заполняем по шаблону
FK_Таблица, которая ссылается_Таблица, на которую ссылаемся

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

Мы говорим, что в колонку "genreId" таблицы "Book" передается значение "id" таблицы "Genre"
Сохраняем - видим связь между таблицами


Знак ключа говорит о том, что от этой таблицы мы берем одно значение, которое соответствует многим значениям другой таблицы, так как с ее стороны мы видим "бесконечность".
Это реализация 1:N или 1:1 (в этом случае внешний вид связи будет выглядеть как "ключик - ключик").
Чтобы реализовать связь N:N, нам понадобится ассоциативная таблица. Мы не можем сделать "бесконечность" к "бесконечности" напрямую, поэтому для такой связи таблиц мы создаем промежуточную таблицу, в которую обе исходные входят по указанному выше принципу "один ко многим".
New Table - BookAuthorship.
Комбинация идентификаторов "книга + автор" должна быть уникальными (ставим РК) и обязательными для заполнения
Тип данных bit возвращает значение "истина" при вводе 1 и "ложь" при вводе 0.
Relationships - создаем два внешних ключа. Которые сопоставляют:
"bookId" в таблице "BookAuthorship" с "id" в таблице "Book"
"аutorId" в таблице "BookAuthorship" с "id" в таблице "Autor"
Диаграмма готова!

37 комментариев:

  1. Рекомендую писать в инструктивном ключе, с перечнем задач, которые надо выполнить, уточняя "где именно и какую кнопку нажимать" более мелким шрифтом.

    В данном случае наблюдаю обратное: сперва происходит демонстрация интерфейса, затем объяснение, что эта кнопка делает, затем пример применения, вроде "предположим, что мы хотим вывести на экран содержимое всей таблицы".

    Одно в другое заворачивается плотным клубком, порядка и последовательности нет.

    Разве если я запомню все кнопки и выпадающие меню, и сохраню их у себя в памяти, то впоследствии смогу делать все самостоятельно? Вроде бы да, ведь мне же сказали о существовании кнопки :) На деле - нет, потому что нарушен принцип изложения от общего к частностям.

    ОтветитьУдалить
  2. И еще рекомендую не учить других (все кругом уже ученые), а писать себе в блог сугубо свой конспект того, что важно лично для себя.

    Тогда (вероятно) получится не лекция, а рассуждение; или же уместная подсказка, а не прямое указание.

    ОтветитьУдалить
  3. Так я в первых абзацах написала, что никого не учу, а именно конспектирую.

    Зато мне будет потом, где подсмотреть эту информацию :)
    А порядок пошаговый. Общая цель написана вначале.

    ОтветитьУдалить
  4. Нет, порядок не пошаговый.

    Общая цель написана вначале, и там она и осталась. Остальной текст перегружен мелкими детальками, которые отвлекают на себя все процессорное время.

    Цитирую:

    // Чтобы изменить базу, надо вначале понять, как нам к ней обращаться. Чтобы к ней обращаться, надо знать, как она строится.

    Разберемся на примере базы книг. У книги должен быть строго один жанр и один или несколько авторов.

    Устанавливаем и запускаем Microsoft SQL Management Studio. //

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

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

    Вторая запись начинается так: "Теперь надо к ней как-то обратиться. А прежде чем обращаться к БД, надо ее заполнить. Мы создали таблицы, но не заполнили их."

    Ээээ! Почему предлагается туда-сюда прыгать по шагам? Почему автор сперва не разложил эти шаги последовательно?

    Есть в тексте много безусловно лишних слов, которые образуют "сорочий фон". Например: "Чтобы изменить базу, надо вначале понять, как нам к ней обращаться. Чтобы к ней обращаться, надо знать, как она строится."

    Предлагаю так:

    Заголовок: "Принцип построения базы данных".

    Далее текст: "Предположим, что у нас есть некий перечень книг. У каждой книги есть определенные атрибуты: жанр, автор (или авторы)."

    Принцип понятен?

    Еще неприятные мелочи: "Основная задача - при подготовке данных изменить базу".

    Изменить базу? Или изменить определенные данные в БД?

    ОтветитьУдалить
    Ответы
    1. Ок, я согласна, "устанавливаем и запускаем" немного выпало из контекста, подправила.

      Остальное - как уже оговаривалось, я не ставлю перед собой цели научить других, я закрепляю свои знания.

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

      Таааак, нужна диаграмма - открываем приложение, создаем базу, создаем диаграмму и тд.

      Мне, лично мне, такой ход мыслей более приятен. Когда я впервые что-то вижу, ну, например, айпад недавно купила, что-то настроить надо было. Нашла именно такое же пошаговое описание с картинками. И быстро все сделала. А будь там описание для продвинутых, ничего бы не поняла, куда он зашел, где эту функцию нашел и тд.

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

      А так как я делаю "конспект" для себя же (и подобных мне), то уж извините.

      Опять же, да! Базу мы заполняем во втором описании. Потому что иначе нам просто не к чему будет обращаться.
      Эта статья названа "создание СХЕМЫ". Схему создали. Заполнение это уже немного другое...

      А насчет "сорочьего" текста - уж извините, я не книжку пишу, а собственный блог. Тут мне не надо быть краткой и лаконичной. Тут я пишу так, как хочу, как умею, как мне нравится :)

      Кому-то неудобно читать, ну чтож. А картинки "ненужные" - это ведь вампиры, они притягивают взгляд похлеще самого клевого описания. На то они и нужны, пробежать глазами по тексту, выцепить нужную картинку и, если надо будет, пробежаться глазами рядом с ней, почитать пояснения.

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

      Удалить
  5. Все это шаманство можно было превратить в несколько строк скрипта на TSQL. Вот это было намного интереснее и полезнее.

    ОтветитьУдалить
  6. Полезнее - не знаю, спорить не берусь, а интереснее...
    За пару строк вы же диаграмму не нарисуете? А визуализация всегда понятнее...

    ОтветитьУдалить
  7. Подскажите, как поставить два ключа в одной таблице?

    ОтветитьУдалить
    Ответы
    1. 2 ключа какого типа? Дайте пример

      Удалить
    2. Ольга, ну вот у вас в ассоциативной таблице PK стоит у двух колонок - bookId и authorID: http://4.bp.blogspot.com/-tI7nrHwIFxM/Tw_x-_692QI/AAAAAAAAAJY/v7akoMOfBjg/s320/1.jpg
      При попытке так же сделать, ключ оказывается только у одной колонки, при попытке создания на другой, он исчезает со старого места и появляется на новом. По крайней мере в MS SQL Server Management Studio 2014 так

      Удалить
    3. Одновременно выделяете две строки и присваиваете им ключ

      Удалить
  8. А какое имя сервера нужно вводить?

    ОтветитьУдалить
    Ответы
    1. Где именно? Покажите скриншот)

      Удалить
    2. Как запустить?
      http://s015.radikal.ru/i333/1509/42/95592e99b87a.png

      Удалить
    3. А что именно он там предлагает? То и попробуйте выбрать) Или введите любое имя, попробуйте

      Удалить
    4. У меня такая же проблема, не могу запустить программу, какие данные нужно вводить, подскажите, пожалуйста? Какое имя сервера нужно вводить? Он ничего не предлагает и выбрать ничего не дает:((

      Удалить
    5. Тестировщик должен уметь гуглить :) https://technet.microsoft.com/ru-ru/library/ms345332(v=sql.105).aspx

      Удалить
    6. Ольга, спасибо большое за ответ! Подскажите, пожалуйста еще. Надо ли отдельно скачивать компоненты Database engine? Дело в том, что, когда я, согласно инструкции пытаюсь зарегистрировать локальные серверы, программа пишет об ошибке:
      ЗАГОЛОВОК: Локальные серверы не найдены
      ------------------------------

      Локальные серверы типа "Компонент Database Engine" не найдены.

      ------------------------------
      КНОПКИ:

      ОК
      ------------------------------

      Удалить
    7. Нет, отдельно вроде ничего скачивать не надо :) А скриншоты можно до ошибки и саму?
      Кстати, если возникает столько проблем в майкрософтом, то можно установить xampp — http://selenium2.ru/articles/136-installing-xampp.html, там гораздо проще :)

      Удалить
    8. Ольга, а как вам можно показать скриншоты?

      Удалить
  9. Ольга, здравствуйте! Сделать все по ссылке https://technet.microsoft.com/ru-ru/library/ms345332(v=sql.105).aspx не получается. Проблема в пункте 4. При выборе "Зарегистрировать локальные серверы" знакомлюсь вот с этой ошибкой: http://prntscr.com/90v3vn
    Подскажите, пожалуйста, что я сделал неправильно?

    ОтветитьУдалить
    Ответы
    1. Боюсь, пока не смогу ответить, поэтому рекомендую установить XAMMP — http://selenium2.ru/articles/136-installing-xampp.html,

      Удалить
    2. Все, я сдаюсь. То ли у меня руки из одного места, но пользуясь документацией на сайте мелкософта, форумами и гуглом я так и не смог исправить ошибку. Удалял, ставил заново, проверял службы - все работает, ковярял, вертел. Иду ставить MySQL. Биллу Гейтсу долгих лет желаю.

      Удалить
    3. Вот я тоже в мелкософтом 100 лет не работала, а XAMMP недавно устанавливала, там проще =)

      Удалить
    4. Все конечно хорошо, но Майкрософтовский СКУЭль ставится далеко не у всех, ХАММПовский ставится влет, но у Вас описан интерфейс по работе с Майкрософотом, а в ХАМППе я потерялся... :-(

      Удалить
    5. У меня есть в планах статья по ХАМПП, но она, увы. не первоприоритетная :-)

      Удалить
  10. Ольга, доброго вечера! Подскажите, пожалуйста, в чем может быть ошибка. При попытке:
    Relationships - создаем два внешних ключа. Которые сопоставляют:
    "bookId" в таблице "BookAuthorship" с "id" в таблице "Book"
    "аutorId" в таблице "BookAuthorship" с "id" в таблице "Autor"
    Программа выдает ошибку: Столбцы в таблице "BookAuthorship" не совпадают с существующим первичным ключом или ограничением Unique. Справка не помогла, всё равно не получается, уже часа полтора мучаюсь и не могу понять, где косяк

    ОтветитьУдалить
    Ответы
    1. Добрый день! Извиняюсь, пропустила комментарий. Тут хотелось бы видеть ваши скриншоты) На что настроено Unique и какие столбцы есть в BookAuthorship

      Удалить
  11. В статье есть ссылка только на Management Studio, сам SQL при этом не установится и работать вы не сможете. Поэтому дополнительно ставим "Microsoft® SQL Server® 2008 Express" с официального сайта https://www.microsoft.com/ru-ru/download/details.aspx?id=1695

    ОтветитьУдалить
    Ответы
    1. Спасибо!
      Да, хочу переписать посты под что-то другое, а не эту студию, но пока времени нет)

      Удалить
  12. Столкнулся с нестандартной проблемой. Не могу выделить две ячейки, чтобы поставить им PK, т.к. кнопки ctrl у меня не работают. Пробовал и с другой клавиатуры, и с электронной клавиатуры - почему-то в SQL результата зажатия кнопки ctrl нет. Подскажите, пожалуйста, есть ли еще какой-либо способ выделения двух ячеек или как мне обойти эту проблему (Курсором мыши выделить нельзя). Заранее спасибо!

    ОтветитьУдалить
  13. Давно уже не работала с этим инструментом, так что не подскажу)

    ОтветитьУдалить
  14. Ольга здравствуйте, поправлю немножко Вас в грамматике:
    Вместо Autentication - Authentication замените

    ОтветитьУдалить
  15. Ольга,добрый день! Очень полезная статья, но мне как начинающему сложно без картинок(( У меня почему-то не все открываются, а понять, что там изображено трудно. Возможно как то добавить картинки? Заранее спасибо!

    ОтветитьУдалить
    Ответы
    1. Добрый) Вроде исправила глюк блоггера, но картинки отображаться должны были частично, поэтому не знаю, исправила ли все нужные

      Удалить