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

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

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

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

Устанавливаем и запускаем Microsoft SQL Management Studio.
Видим окно "Connect to Server", где мы можем выбрать тип и имя сервера, а также аутентификацию - выбираем Windows Autentication - 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"
Диаграмма готова!

31 комментарий:

  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. Спасибо!
      Да, хочу переписать посты под что-то другое, а не эту студию, но пока времени нет)

      Удалить