Материал из Linuxformat.

OpenOffice.org Base Освоим суть построения баз данных с нуля

Содержание 1 Base: Данные в порядке 1.1 Открытое решение 1.1.1 Города [Cities] 1.1.2 Люди [People] 1.2 Первичный или внешний? 1.3 Ответьте на мой запрос 1.4 Связываем таблицы 1.5 Применим это на практике 1.5.1 DVDs 1.5.2 Films 1.5.3 Поклон Джорджу Булю 1.5.4 А как с актерами? 1.5.5 Роли [Roles] 1.5.6 Актеры [Actors] 1.6 Когда не хватает select

Base: Данные в порядке

Если вы не придумали, как бы поэффективнее управлять своей коллекцией DVD или списком контактов, Боб Мосс может показать вам, как это делается.

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

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

Не думайте, что разработка баз данных – это какая-то черная магия в исполнении хакеров из подземного бункера: в реальности их мощь доступна даже рядовому пользователю. Если вы взялись за наш урок, то вполне вероятно, что вы пока не собираетесь иметь дело с миллионами строк данных ради разработки корпоративной системы; поэтому процесс обучения будет не слишком сложен. Но даже приобретение навыка создания собственной базы данных для инвентаризации домашнего скарба, коллекции DVD или списка контактов может оказаться бесценным. Для целей урока мы предположим, что вы не знаете ровно ничего, и осветим только самое необходимое для ввода в тему.

Открытое решение

Те, кто сталкивался с базами данных ранее в Microsoft Access, сочтут OpenOffice.org Base более чем адекватной кросс-платформенной заменой. Большинство дистрибутивов не устанавливают эту часть OpenOffice.org по умолчанию, и вам придется сперва добавить ее через свой менеджер пакетов.

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

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

Исходные данные

Имя [First name]	Фамилия [Second name]	Город [City]
Джон	Смит	Лондон
Джон	Смит	Ливерпуль
Том	Джонс	Лондон
Джо	Блоггс	Бристоль

В общем, имея пустую базу данных, создайте новую таблицу в Режиме дизайна – это чтобы мы могли задать столбцы в таблице и типы данных для них, так же, как при работе с электронными таблицами. Наша первая таблица будет содержать названия городов, и нам понадобятся две колонки: CityID и City. Чтобы настроить CityID как первичный ключ, щелкните по нему правой кнопкой и выберите пункт «Первичный ключ». Неплохо также позаботиться, чтобы CityID было полем типа Integer (целое число), и включить для этого поля автоматическую нумерацию, чтобы при добавлении нового города не вводить первичный ключ (т. е. порядковый номер) вручную.

Cохраните это… вот вы и создали свою первую таблицу, для хранения названий городов. Чтобы ввести в нее данные, просто откройте таблицу с основной вкладки и добавляйте значения в поле City. Поле CityID будет заполняться автоматически при каждом добавлении новой записи.

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

Теперь сделаем нечто поумнее: свяжем людей с городами их проживания. В режиме дизайна, вам нужно будет создать новую таблицу с четырьмя полями. Одно назовем NameID – это поле типа Integer с автоматической нумерацией будет являться нашим первичным ключом. Следующая колонка получит имя CityID и будет тоже типа Integer. У нее не должно быть автоматической нумерации, так как это другой тип ключей и тут могут встречаться повторы. Также добавьте две текстовые колонки для имен [first name] и фамилий [second name]. Сохраните эту таблицу и откройте ее заново в стандартном просмотре для добавления новых записей.

Теперь свяжем CityID из таблицы Города [Cities] с CityID в таблице Люди [People]. Для этого перейдите на Сервис > Связи и добавьте туда обе таблицы. Затем для создания связи нужно щелкнуть и перетянуть CityID с первой таблицы на вторую. Вы должны увидеть 1 у таблицы Cities и n у таблицы Names, что показывает связь «один-со-многими».

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

Города [Cities]

CityID	City
0	Лондон
1	Бристоль
2	Манчестер
3	Ливерпуль

Люди [People]

NameID	CityID	FirstName	SecondName
0	0	Джон	Смит
1	3	Джон	Смит
2	0	Том	Джонс
3	1	Джо	Блоггс

Первичный или внешний?

В мире баз данных существует два типа ключей, о которых следует знать. Первый, который мы назвали «первичный ключ» – это поле ID (идентификатор), которое обязано быть в каждой одиночной таблице. Оно предоставляет уникальное значение ID для каждой записи.

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

Ответьте на мой запрос

Теперь применим секретное оружие баз данных – запрос ‘select’. Мы уже немало поработали над организацией данных, но пока что таблицы все-таки отображают вещи более интуитивным для нас способом. Однако мы можем применить запрос select для сбора нужных данных из базы и отображения их в более дружелюбном к просмотру и редактированию виде.

Для этого просто щелкните на иконке Запросы в левой части окна базы данных и затем создайте новый запрос в режиме дизайна. Пользователям Microsoft Access это уже знакомо, а для остальных скажем, что нужно всего-навсего добавить в запрос обе таблицы. Далее добавьте поля First Name и Second Name в первые два столбца, используя соответствующие выпадающие списки, и отметьте поле City как третий столбец. Поля ID ни в какие столбцы добавлять незачем, так как нам нужно видеть содержимое базы, а не ее структуру.

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

Связываем таблицы

Следующий тип ключа – это «внешний [foreign] » ключ, который является более абстрактной идеей. Представьте себе две таблицы: одна содержит список имен, а другая – список городов. Понятно, что каждый человек может жить только в одном городе, тогда как в каждом городе живет много людей. В реляционной теории это известно как связь «один-ко-многим», и наш внешний ключ позволяет нам ее смоделировать.

В таблицу Люди мы добавили новый столбец, CityID, и для каждого человека указали в нем значение соответствующего первичного ключа города, где он живет. Мы допускаем наличие дубликатов в этом поле, поскольку знаем, что в каждом городе может проживать более одного человека. Затем мы связываем поле CityID в этой таблице с первичным ключом в таблице City.

В данном случае NameID в таблице Names и CityID в таблице City – это наши первичные ключи, а поле CityID в таблице Names – внешний ключ. Вы заметите, что у нас есть два Джона Смита, но они живут в разных городах. Один из Джонов Смитов и Том Джонс живут в Лондоне, так как значения их полей внешнего ключа одинаковы.

Применим это на практике

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

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

Далее обдумаем ключевые связи в этой базе данных. На одном DVD по идее может быть только один фильм [в России бывает и не так, но этот случай мы не рассматриваем, – прим. ред.], но у вас может найтись не одна копия этого фильма. Значит, в таблице DVD нужен внешний ключ, так как название фильма будет нашими повторяющимися данными. Мы можем также задать в таблице, просмотрен фильм или нет – для любого фильма это происходит только один раз, сколько бы ни было у вас DVD с его копиями (например, если вы видели фильм Крепкий орешек, это касается всех имеющихся у вас копий). Можно также задать в таблице DVD, есть ли фильм в наличии у вас дома: скажем, вы одолжили кому-нибудь диск с Криминальным чтивом, но он не последний – у вас остались другие его копии.

Итак, нам снова понадобятся две таблицы: одна будет содержать данные обо всех имеющихся у вас DVD, а другая – информацию о каждом фильме. Они будут выглядеть как таблицы, приведенные далее.

DVDs

Ключ?	Имя столбца	Тип данных	Описание
PK [Первичный]	DVDID	Integer	Наш первичный ключ (сделайте ему автонумерацию)
FK [Внешний]	FilmID	Integer	Внешний ключ фильма
	LendStatus	Boolean	Отдан другу? Да/Нет

Films

Ключ?	Имя столбца	Тип данных	Описание
PK	FilmID	Integer	Наш первичный ключ (сделайте ему автонумерацию)
	FilmName	Text	Да, вы угадали: это название фильма
	SeenIt	Boolean	Просмотрен? Да/Нет

Поклон Джорджу Булю

Вы заметите в этих таблицах новый тип данных – Boolean. Он гарантирует нам, что на вопрос, находится ли DVD у нас дома или был ли просмотрен фильм, мы можем иметь только ответ: Да или Нет. И опять, легко сделать запрос select, который достанет нам эту информацию и отобразит в дружественном виде, поэтому задайте нужные связи в Сервис > Связи, как было показано в предыдущем примере.

А как с актерами?

Это следующий шаг, который можно проделать с вашей базой коллекции DVD, так как вы, вероятно, пожелаете выполнять поиск по актеру или режиссеру – их имени, биографическим данным и т. п. Задача более чем практическая, но мы столкнемся с весьма интересной проблемой. В фильме играет более одного актера, но актер мог сниматься более чем в одном фильме. То есть нельзя просто создать таблицу Актеры [Actors] и добавить внешний ключ FilmID, поскольку в этой таблице потребуется более чем одна запись для одного и того же актера. Это называется связью «многие-ко-многим», а такое базы данных не очень-то любят. Они работают со связью «один-ко-многим», и если мы хотим создать эффективную базу данных, нужно решить эту проблему.

Чтобы ободрить вас, скажу, что не все потеряно. Ситуацию разрулит добавление таблицы Роли [Roles]. Мы знаем, что для роли маловероятно появиться дважды и что в фильме имеется более чем одна роль. Но роль (обычно) играется только одним актером, поэтому создание такой таблицы дает нам элегантное решение. Процесс, который мы только что описали, разработчики баз данных называют «нормализацией», и полное пояснение, как это работает и зачем мы это сделали, выходит за рамки данного урока.

Итак, чтобы ввести наше решение в действие, нужны две дополнительные таблицы: Роли [Roles] и Актеры [Actors].

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

Это может показаться неочевидным (и даже бессмысленным, если у вас всего-то и есть, что DVD с Терминатором и Арнольдом Шварценеггером), но эта база данных богата возможностями. С ее помощью можно поискать, какой DVD вы одолжили, задав соответствующие параметры в запросе. Она может даже предложить вам DVD для просмотра, основываясь на фильмах, которые вы уже видели. Или вы можете создать ряд форм (обэтом мы на уроке не говорили), чтобы сделать ввод и вывод данных более дружелюбным.

Еще лучше будет предусмотреть импорт информации прямо с web-сервиса вроде IMDb. Вы можете найти созданные базы данных на LXFDVD этого месяца, и вам будет легко приспособить их под ваши нужды или расширить за пределы данного урока. Возможности безграничны, и если вы сделаете нечто особо полезное, поделитесь с нами через http://forum.liuxformat.ru.

Роли [Roles]

Ключ?	Имя колонки	Тип данных	Описание
PK	RoleID	Integer	Наш первичный ключ
FK	FilmID	Integer	Внешний ключ к таблице фильмов
FK	ActorID	Integer	Внешний ключ к таблице актеров
	RoleName	Text	Имя персонажа

Актеры [Actors]

Ключ?	Имя колонки	Тип данных	Описание
PK	ActorID	Integer	Это наш первичный ключ
	ActorName	Text	Имя актера

Когда не хватает select

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

Ограничением OOo Base является отсутствие соответствующих графических инструментов, что дезориентирует пользователей Microsoft Access, давно уже имеющих эту функциональность. Пользователи OOo Base лишены такого счастья и вынуждены писать запросы вручную в их SQL-представлении.

А что такое SQL? Это сокращение от «Structured Query Language» [Язык структурированных запросов]; его можно использовать для выполнения запросов прямо через движок базы данных, без графических оболочек. Как правило, это прерогатива разработчиков баз данных, поскольку запросы могут быть очень сложными, и одной неправильной строчкой легко угробить всю информацию.

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

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