Как составить базу предприятий

Как собрать базу организаций за час

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

В своей работе я использовал данные из открытых источников, а именно:

• Данные ФНС (архивы ЕГРЮЛ от ИТСОФТ)

• Портал Чекко

Большое спасибо ребятам из ИТСОФТ, они большие молодцы – данные должны быть открытыми. Однако, эти архивы ФНС РФ предоставляет за 300 000р. в год.

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

Итак, начнем.

1) Получаем входные данные

С сайта ЕГРЮЛ ИТСОФТ скачиваем данные ФНС:

• Архив доходов и расходов за 2011-2020 года;

• Архив кодов ОКВЭД;

• Архивы промежуточных данных (для удобства обновления данных);

• Архивы организаций и обновления к ним.

Из архива данных организаций получаем следующую таблицу:

Соединяем её с архивом доходов и расходов, выбрав при этом значения за 2020 год.

В архиве кодов ОКВЭД смотрим идентификаторы, которые соответствуют строительной сфере (3327 – 3286).

Фильтруем данные по следующим параметрам:

• Соответствие ОКВЭД;

• Регион – Свердловская область (66);

• Дата окончания деятельности – должна быть нулевая (0000-00-00);

• Доходы – выше 600 000 рублей за 2020 год.

После этих действий удаляем дублирующиеся записи и ненужные колонки в таблице.

Уже 2758 организаций!

2) Хорошо, теперь обогащаем данные

Парсим почту, сайт и телефоны с сайта Чекко. Это сделаем, подставляя ОГРН или ИНН в базовую ссылку поиска: «/search?query=». Советую поставить задержку между запросами в 0.5 секунды, чтобы получать корректные данные.

Собираем полученные данные воедино с основной таблицей. Вуаля! Вы получили актуальную базу данных строительных организаций Свердловской области!

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

Надеюсь, статья была интересная.

Как собрать базу организаций за час

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

В своей работе я использовал данные из открытых источников, а именно:

Данные ФНС (архивы ЕГРЮЛ от ИТСОФТ)

Большое спасибо ребятам из ИТСОФТ, они большие молодцы – данные должны быть открытыми. Однако, эти архивы ФНС РФ предоставляет за 300 000р. в год.

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

1) Получаем входные данные

С сайта ЕГРЮЛ ИТСОФТ скачиваем данные ФНС:

Архив доходов и расходов за 2011-2020 года;

Архив кодов ОКВЭД;

Архивы промежуточных данных (для удобства обновления данных);

Архивы организаций и обновления к ним.

Из архива данных организаций получаем следующую таблицу:

Данные организаций ФНС

Соединяем её с архивом доходов и расходов, выбрав при этом значения за 2020 год.

В архиве кодов ОКВЭД смотрим идентификаторы, которые соответствуют строительной сфере (3327 – 3286).

Фильтруем данные по следующим параметрам:

Регион – Свердловская область (66);

Дата окончания деятельности – должна быть нулевая (0000-00-00);

Доходы – выше 600 000 рублей за 2020 год.

После этих действий удаляем дублирующиеся записи и ненужные колонки в таблице.

Отфильтрованная база организаций

Уже 2758 организаций!

2) Хорошо, теперь обогащаем данные

Парсим почту, сайт и телефоны с сайта Чекко. Это сделаем, подставляя ОГРН или ИНН в базовую ссылку поиска: «/search?query=». Советую поставить задержку между запросами в 0.5 секунды, чтобы получать корректные данные.

Парсинг данных с Чекко

Собираем полученные данные воедино с основной таблицей. Вуаля! Вы получили актуальную базу данных строительных организаций Свердловской области!

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

Источник

Легально собрать базу: 10 способов

В Campaign Monitor посчитали, что на каждый вложенный в email-маркетинг доллар компания получает $44 прибыли. Бизнес вдохновляют такие показатели, но отсутствие базы мешает начать заниматься email-маркетингом.

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

Почему собирать, а не покупать

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

Рассылки, которые отправляют по купленным базам, — это спам. Мы часто пишем о том, почему слать спам вредно и по каким причинам email-сервисы банят спам-рассылки:

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

1. Ящики получателей забиты спамом. Эту базу явно купили не только вы, поэтому туда уже отправили достаточно спам-писем. Скорее всего, большинство ящиков окажутся переполнены.
2. База несвежая. Обычно в купленных базах более 50% адресов уже не активны: люди сменили почту, уволились или их ящики переполнены.
3. Вы покупаете не базу, а одну рассылку по ней. Второе письмо уже не дойдет до получателей, а ваш домен может не только попасть в черный список спамеров, но и оказаться в песочнице Google (сайт не будет отображаться в поиске).
4. Пользователи не открывают спам-рассылки. Даже рассылки, на которые пользователи подписываются, имеют открываемость 10-20%. Спам открывают только 1% получателей, а то и меньше.

Разберем пример. Вы покупаете базу потенциальных клиентов конкурента. Вот расходы на 1 рассылку:

Статья	Сумма
Покупка базы	$200
Подготовка письма: верстка, дизайн, текст	$50
Оплата за сервис рассылки	$100
Итого	$350

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

Кликабельность такой рассылки будет еще ниже. По данным Campaign Monitor у рассылок, на которые пользователи подписываются, кликабельность около 3%. У спама она будет стремиться к нулю.

Вот примерные результаты, которые вы получите:

• Отправлено — 10 000.
• Доставлено — 5 000.
• Открытия — 100.
• Клики — 3.

В итоге вы купите 3 клика на сайт за 350 долларов. А если кто-нибудь из получателей пожалуется, вы еще заплатите штраф за спам-рассылку. Такие расчеты показывают, что спам нерентабелен.

Почему же компании продолжают отправлять письма по холодным базам:

1. Не считают эффективность рассылок. Расчеты из примера показывают, что спам нерентабелен. Но не все компании считают эффективность рассылок. Некоторым важно получить заказы любой ценой и из любых источников. На практике такой подход вредит бизнесу.
2. Переносят практики из других сфер на свой бизнес. Есть один сегмент получателей, которые лояльно относятся к спаму. Это закупщики и поставщики крупных производственных предприятий. Они всегда рады получить прайс с меньшей ценой. При этом не важно, подписывались они на обновления или нет. Перенос этой практики в другие ниши не дает эффективного результата. Чтобы не нарушать закон, лучше отправлять письма поставщикам вручную, или сначала написать им личное письмо с предложением дать согласие на рассылку с прайсами.

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

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

Эти письма из папки «Спам» хорошо знакомы тем, кто часто оставляет свои контакты и дает почту для получения коммерческих предложений

10 способов собрать базу

Если у вас нет базы, но есть сайт, который посещает более 100 человек в месяц, то вы можете собирать контакты, установив форму подписки. Подписываться будут от 1 до 10% посетителей в месяц. К примеру, по результатам экспериментов агентства Email Soldiers, конверсия в подписку составила 2%.

В UniSender есть 2 вида форм подписки:

• встроенные формы;
• всплывающие формы.

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

Вот какие попапы с подпиской использует Nike

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

Форма подписки на статьи блога UniSender находится внизу страницы. Подпишутся только те, кто дочитал до конца и кому интересна тема рассылок

Чтобы форма работала, нужно сообщить подписчикам:

1. О чем будет рассылка. Хорошие идеи: интересные новости по теме, полезные советы, новые услуги и товары и, конечно, акции со скидками и бонусами.
2. Какой бонус они получат за подписку. Интернет-магазины часто предлагают скидку или подарок. Выберите такой бонус, который понравится вашей аудитории.
3. Как часто будут приходить письма. Предсказуемость рассылки — это важно. Пользователи не будут переживать, что вы начнете отправлять письма каждый день, и будут ждать рассылку согласно обещанному графику.

SMS и телефон

Если вы делаете SMS и Viber-рассылки, вы можете дарить людям подарки за то, что они оставят вам свой email, и предлагать им email-коммуникации. Чем это хорошо: электронное письмо — это более удобный и дешевый способ уведомить пользователя об акции, чем короткие SMS-сообщения.

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

В SMS или Viber-сообщении можно предложить бонус за email или подписку на рассылку. Те, кто подпишутся, получат его на почту

Если вы продаете по телефону, можете запрашивать email для оформления заказа и брать согласие на рассылку устно. Возможно, ваши менеджеры и так это делают, тогда база у вас уже есть. Осталось собрать email-адреса и залить их в UniSender. Но прежде чем делать рассылки по этой базе, вам обязательно нужно попросить согласие на рассылку в письме.

Еще вы можете давать клиентам скидку за заполнение анкеты по телефону. Продвинутый способ — скидочная карта, привязанная к номеру телефона, и в анкете на ее получение обязательное поле — email.

После сбора email вы можете загрузить базу в сервис через импорт контактов или настроить интеграцию вашей CRM-системы с UniSender.

Несколько популярных интеграций с CRM:

Также интегрировать CRM c UniSender можно с помощью Zapier. Этот сервис позволяет без программиста передавать данные из одного сервиса в другой в том случае, если они добавлены в Zapier.

Офлайн

До карантина 2020 года базу активно собирали в офлайне.

Популярный способ пополнения базы, особенно если email-рассылку ведет отдел продаж, — сбор визиток на мероприятиях. На выставках вы собираете визитки у посетителей, а потом делаете по этим контактам рассылку.

Еще один способ сбора базы в офлайне — заполнение анкет клиентами при покупке или посещении офиса/точки самовывоза. Анкеты могут участвовать в розыгрыше, быть частью скидочной программы или за них можно давать бонусы.

С базами, собранными в офлайне, нужно работать особым образом:

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

Лид-магниты

Популярный и самый эффективный способ сбора базы — это получение контактов с помощью лид-магнитов. Для этого у вас должен быть сайт или хотя бы чат в Telegram.

Лид-магнит — это любой бесплатный бонус, для получения которого нужно оставить свои контакты. Это может быть презентация, доступ к вебинару, электронная книга, участие в розыгрыше или получение скидки от магазина. Пользователи оставляют заявку, получают обещанное, и дальше вы начинаете отправлять им письма.

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

Подписные страницы

Еще один масштабный способ сбора базы — подписные страницы. Подписная страница — лендинг, который продает не услугу, а рассылку. Если вы занимаетесь сложными или дорогими продуктами, где цикл принятия решения долгий (туризм, продажа квартир и автомобилей, b2b услуги) продажа через рассылку может быть более эффективна.

Чтобы страница собирала контакты, вам нужно придумать, как привести туда посетителей. Вы можете разместить ссылку на главной сайта, сделать попап или поставить ссылку в футер. Также можно рекламировать страницу в соцсетях или постить ее в Stories Instagram*, если у вас много подписчиков.

Кроме того, ссылку на подписную страницу можно отправлять через Viber или SMS.

Пример подписной страницы c лид-магнитом

Подписная страница рассылки

Подписную страницу можно сделать двумя способами. Вы можете написать техническое задание на создание страницы на сайте, а потом в эту страницу встроить форму подписки UniSender. Другой вариант — сделать страницу самостоятельно на конструкторе лендингов. Для этого подойдут конструкторы WIX, Tilda, LP Generator или Readymag. Мы рекомендуем использовать Tilda, потому что у нее есть простая интеграция с UniSender.

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

Акции

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

Если у вас есть блог, запустить email-маркетинг гораздо проще. Новые статьи блога — это отличный инфоповод для рассылки. Если подписчики не читали старые статьи, а они актуальны, их также можно использовать.

Даже если вы ведете блог или колонку на платформе или медиа (например, в Medium), а сайта у вас нет, вы можете сделать подписную страницу на Tilda и поставить эту ссылку в статьи или в описание профиля.

Маркетолог Наталья Бабаева даже запустила платную рассылку «Бабаева копает инновации», куда собирает трафик со своего блога в Medium и канала в Telegram «Бабаева, к доске!»:

Чат-бот

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

Или создайте простого чат-бота с помощью Unisender, чтобы собирать email у тех, чьи телефонные номера или токены в мессенджере у нас уже есть.

Контакты можно собирать в список вручную или добавить в чат-бот сценарий автоматизации сбора базы и передавать контакты в Unisender через Zapier или API.

Мобильное приложение

Если у вас есть мобильное приложение, которым можно пользоваться без регистрации, — в нем тоже можно собирать подписчиков. Для этого добавьте в приложение форму подписки.

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

Что нужно знать, запуская сбор базы

Юридическая сторона вопроса. C 2018 года данные пользователей стали очень ценными, поэтому ужесточилось международное законодательство в области спама. В Российской Федерации тоже есть аналогичный закон.

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

Самый легальный способ получить согласие на рассылку — предложить заполнить форму на сайте и отправить письмо-подтверждение. Пользователь перейдет по ссылке из письма и тем самым подтвердит свое желание получать ваши письма. Такая подписка называется двойное подтверждение (double opt-in).

Но по факту компании пользуются и single opt-in, то есть подпиской считается заполнение формы или оформление заказа в интернет-магазине. Если вы решите использовать single opt-in, будьте готовы доказать в суде, что вы законно получили согласие на рассылку для этого контакта.

Техническая сторона вопроса. У email-сервисов из-за спама возникают технические проблемы. Почтовые провайдеры блокируют все письма, отправленные через сервис, когда замечают среди его рассылок спам. Поэтому сервисы рассылок разрешают отправлять письма только на те базы, контакты которых вы собрали через заказы или подписку. UniSender не исключение. Читайте статью о том, почему UniSender отклоняет рассылки.

Согласие на подписку и эффективность рассылок. Согласие на подписку необходимо по закону. Но такой подход к рассылкам эффективен и с точки зрения маркетинга.

Чаще всего с рассылок покупают активные читатели. Поэтому важно отправлять письма только тем, кому они интересны, и делать простую и явную отписку от рассылки. При этом подписчики, которые подтвердили свою почту (double opt-in), точно готовы получать и читать письма, а не просто удалять их, не открывая.

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

Источник

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

А если вы знакомы с SQL и вас не остановили предыдущие термины, на всякий случай напомним, что:

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

***

Есть минимум два требования, которые должны быть соблюдены при проектировании структуры БД:

1. Сохранить всю информацию после разделения её на таблицы.
2. Минимизировать избыточность того, как эта информация хранится.

Примечание Второй пункт важен не только из-за того, что избыточность влияет на размер БД. Чаще всего при обновлении данных нужно обработать много строк. В таком случае вы рискуете просто забыть обновить некоторые из них, что приведёт к коллизиям внутри БД.

Ниже перечислены некоторые рекомендации, которые помогут добиться эффективной структуры:

• используйте хотя бы третью нормальную форму;
• создавайте ограничения для входных данных;
• не храните ФИО в одном поле, также как и полный адрес;
• установите для себя правила именования таблиц и полей.

Используйте хотя бы третью нормальную форму

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

Нормальных форм существует целых 6 штук, однако обычно соблюдают всего лишь 3 и для начала этого более чем достаточно.

Первая нормальная форма

Для примера будем использовать отношение сотрудники_отделы_проекты. В нём есть информация о номере сотрудника, его фамилии, номере отдела, в котором он работает, номере телефона отдела и так далее.

Это отношение, как и любое другое, автоматически находится в первой нормальной форме:

• в отношении нет одинаковых кортежей;
• кортежи не упорядочены;
• атрибуты не упорядочены и различаются по наименованию;
• все значения атрибутов атомарны.

Вторая нормальная форма

В нашем случае у таблицы выше имеется сложный (составной) ключ {Н_СОТР, Н_ПРО}. От части ключа Н_СОТР зависят неключевые атрибуты ФАМ, Н_ОТД, ТЕЛ. От части ключа Н_ПРО зависит неключевой атрибут ПРОЕКТ. А вот атрибут Н_ЗАДАН зависит от всего составного ключа, так как сотрудник может выполнять одно задание в одном проекте.

Поэтому для приведения отношения ко второй нормальной форме из отношения сотрудники_отделы_проекты нужно выделить два отношения сотрудники_отделы и проекты, а исходное отношение оставим отношением задания.

Наконец, третья нормальная форма

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

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

Отношение сотрудники_отделы не находится в третьей нормальной форме, так как имеется зависимость неключевых атрибутов, таких как зависимость номера телефона от номера отдела. Поэтому декомпозируем отношение сотрудники_отделы на два отношения — сотрудники и отделы:

Используйте проверочные ограничения

База данных — это не просто набор таблиц. В неё встроено много инструментов, которые помогут с сохранностью и качеством данных.

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

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

Выражения ON DELETE и ON UPDATE внешних ключей используются для указания действий, которые будут выполняться при удалении строк родительской таблицы (ON DELETE) или изменении родительского ключа (ON UPDATE). Не пренебрегайте ими.

Стоит убедиться, что обязательность заполнения (NOT NULL) проверяется для полей, которые строго не должны оставаться пустыми.

Используйте CHECK, чтобы убедиться, что значения входят в диапазон (например чтобы цена не была отрицательной).

Не храните ФИО в одном поле, также как и полный адрес

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

Установите для себя правила именования таблиц и полей

Сложно работать с данными, которые выглядят как-то так: user.firstName, user.last_name, user.birthDate. Конечно, каждый программист в праве сам выбирать для себя стиль наименования, но для SQL рекомендуется выбрать наименование с подчёркиванием. Потому что не все SQL-движки одинаково работают с заглавными буквами, а помещать всё в кавычки бывает утомительно.

Ещё нужно определиться как будут называться таблицы — во множественном числе (users) или в единственном (user). Каждая базовая структура в БД обычно настроена на множественное число, поэтому и именовать таблицы стоит соответственно.

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

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

Перевод статьи «A humble guide to database schema design»

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

Внешний вид рабочей области программы – таблица. А реляционная база данных структурирует информацию в строки и столбцы. Несмотря на то что стандартный пакет MS Office имеет отдельное приложение для создания и ведения баз данных – Microsoft Access, пользователи активно используют Microsoft Excel для этих же целей. Ведь возможности программы позволяют: сортировать; форматировать; фильтровать; редактировать; систематизировать и структурировать информацию.

То есть все то, что необходимо для работы с базами данных. Единственный нюанс: программа Excel – это универсальный аналитический инструмент, который больше подходит для сложных расчетов, вычислений, сортировки и даже для сохранения структурированных данных, но в небольших объемах (не более миллиона записей в одной таблице, у версии 2010-го года выпуска ).

Структура базы данных – таблица Excel

База данных – набор данных, распределенных по строкам и столбцам для удобного поиска, систематизации и редактирования. Как сделать базу данных в Excel?

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

Запись – строка в базе данных (БД), включающая информацию об одном объекте.

Поле – столбец в БД, содержащий однотипные данные обо всех объектах.

Записи и поля БД соответствуют строкам и столбцам стандартной таблицы Microsoft Excel.

Если Вы умеете делать простые таблицы, то создать БД не составит труда.



Создание базы данных в Excel: пошаговая инструкция

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

Как создать базу данных клиентов в Excel:

1. Вводим названия полей БД (заголовки столбцов).



2. Вводим данные в поля БД. Следим за форматом ячеек. Если числа – то числа во всем столбце. Данные вводятся так же, как и в обычной таблице. Если данные в какой-то ячейке – итог действий со значениями других ячеек, то заносим формулу.



3. Чтобы пользоваться БД, обращаемся к инструментам вкладки «Данные».



4. Присвоим БД имя. Выделяем диапазон с данными – от первой ячейки до последней. Правая кнопка мыши – имя диапазона. Даем любое имя. В примере – БД1. Проверяем, чтобы диапазон был правильным.

Основная работа – внесение информации в БД – выполнена. Чтобы этой информацией было удобно пользоваться, необходимо выделить нужное, отфильтровать, отсортировать данные.

Как вести базу клиентов в Excel

Чтобы упростить поиск данных в базе, упорядочим их. Для этой цели подойдет инструмент «Сортировка».

1. Выделяем тот диапазон, который нужно отсортировать. Для целей нашей выдуманной компании – столбец «Дата заключения договора». Вызываем инструмент «Сортировка».



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

Данные в таблице распределились по сроку заключения договора.

Теперь менеджер видит, с кем пора перезаключить договор. А с какими компаниями продолжаем сотрудничество.

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

1. Одновременным нажатием кнопок Ctrl + F или Shift + F5. Появится окно поиска «Найти и заменить».



2. Функцией «Найти и выделить» («биноклем») в главном меню.

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

В программе Excel чаще всего применяются 2 фильтра:

• Автофильтр;
• фильтр по выделенному диапазону.

Автофильтр предлагает пользователю выбрать параметр фильтрации из готового списка.

1. На вкладке «Данные» нажимаем кнопку «Фильтр».



2. После нажатия в шапке таблицы появляются стрелки вниз. Они сигнализируют о включении «Автофильтра».



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



4. Жмем «ОК». В примере мы скроем клиентов, с которыми заключали договоры в прошлом и текущем году.



5. Чтобы задать условие для фильтрации поля типа «больше», «меньше», «равно» и т.п. числа, в списке фильтра нужно выбрать команду «Числовые фильтры».



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

Готово!

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

1. Выделяем те данные, информация о которых должна остаться в базе видной. В нашем случае находим в столбце страна – «РБ». Щелкаем по ячейке правой кнопкой мыши.



2. Выполняем последовательно команду: «фильтр – фильтр по значению выделенной ячейки». Готово.

Если в БД содержится финансовая информация, можно найти сумму по разным параметрам:

• сумма (суммировать данные);
• счет (подсчитать число ячеек с числовыми данными);
• среднее значение (подсчитать среднее арифметическое);
• максимальные и минимальные значения в выделенном диапазоне;
• произведение (результат умножения данных);
• стандартное отклонение и дисперсия по выборке.

Порядок работы с финансовой информацией в БД:

1. Выделить диапазон БД. Переходим на вкладку «Данные» – «Промежуточные итоги».



2. В открывшемся диалоге выбираем параметры вычислений.

Инструменты на вкладке «Данные» позволяют сегментировать БД. Сгруппировать информацию с точки зрения актуальности для целей фирмы. Выделение групп покупателей услуг и товаров поможет маркетинговому продвижению продукта.

Готовые образцы шаблонов для ведения клиентской базы по сегментам.

1. Шаблон для менеджера, позволяющий контролировать результат обзвона клиентов. Скачать шаблон для клиентской базы Excel. Образец:



2. Простейший шаблон.Клиентская база в Excel скачать бесплатно. Образец:

Шаблоны можно подстраивать «под себя», сокращать, расширять и редактировать.

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

• анализ предметной области для определения состава и содержания информации, обрабатываемой информационной системой, а также пользовательских потребностей;
• построение концептуальной модели предметной области, заключающееся в выявлении сущностей и связей между ними, а также отображение этой информации в виде ER-диаграммы;
• физическое проектирование базы данных и ее реализация в MS SQL Server.

1 Анализ предметной области

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

1. репертуар и расписание проката кинотеатра должен кто-то вносить в систему — соответствующую роль назовем «Менеджер»;
2. посетитель и кассир должны иметь возможность просматривать расписание, при этом интересно расписание, начиная с некоторого момента времени (например, текущего времени). Составлять оно может по-разному:
   1. расписание показа всех фильмов, упорядоченное по времени;
   2. расписание прокатов в отдельных залах кинотеатра;
   3. расписание проката определенного фильма.

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

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

Каждая сущность, кроме hall_row содержит поле id, которое идентифицирует объект. У сущности hall_row поле id не нужно, так как в одном и том же зале кинотеатра (id_hall) не могут повторяться номера рядов (number).

Когда пользователь выберет зал и прокат — система должна отобразить заполненность зала, при этом надо отобразить конфигурацию зала с пометкой занятых и свободных мест. Под конфигурацией зала тут имеется ввиду, что разные залы имеют разный размер, а ряды зала могут иметь различное количество мест. Поэтому в базе данных зал (hall) составляется из рядов (hall_row), одним из параметров которых является вместимость (capacity).

2 Построение концептуальной модели

Выше были отображены основные сущности, но не отображены роли пользователей, хотя их тоже должна хранить система. Они показаны ниже на ER-диаграмме в нотации Чена [1].

На диаграмме выделены роли кассира и менеджера, а также основные отношения между сущностями. На диаграмме нет роли администратора, но его роль заключается в:

1. создании всех таблиц базы;
2. добавлении залов и рядов в них;
3. добавлении кассиров и менеджеров.

На диаграмме не отражена роль посетителя, так как:

1. билет не содержит информации о том, кто его купил (посетитель может подарить билет другу);
2. система вообще не хранит информацию о посетителях;
3. покупку билета он осуществляет через общение с кассиром вне системы;
4. никакие данные в базе посетитель самостоятельно изменить не может.

На диаграмме проставлены кратности связей, например, видно, что один менеджер может добавить много (N) прокатов. В этой базе не оказалось связей типа N:M, сложных или рекурсивных связей — такие связи являются препятствиями в проектировании и решаются изменением ее структуры.

Для формирования схемы данных необходимо сначала дополнить ER-диаграмму реквизитами сущностей (уточнить ее) — результат приведен на рисунке.

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

• система не должна позволять продавать несколько билетов на одно и то же место при одном показе фильма. Это значит, что вторичным ключем для Билета должен быть кортеж (id_screening, row, seat). Однако, тогда нет необходимости в id билета — на билеты не ссылается ни одна таблица, это поле может быть удалено. Изначально id был добавлен потому, что обычно на билетах в кинотеатрах печатается номер;
• билет хранит поле id_hall, это было сделано для того, чтобы посетитель кинотеатра мог найти свой кинозал. Однако, билет, выдаваемый пользователю — это не тоже самое, что информация о билетах, хранимая в базе данных. Билет базы данных хранит также поле id_screening, а Показ уже ссылается на id_hall. Таким образом, в базе нет смысла хранить id_hall в таблице билетов.

Исправленная ER-диаграмма приведена ниже:

Таблица менеджеров и кассиров не объединены в таблицу Users так как вопросы разграничения прав доступа в различных СУБД решаются по-разному. Так, в MS SQL пользователи добавляются с помощью специальных запросов типа:

CREATE LOGIN Manager_Name WITH PASSWORD='Some Passwrd';

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

3 Физическое проектирование

ER-диаграмма отражает основные таблицы, связи и атрибуты, на ее основе можно построить модель БД. На ER-диаграммы нет стандарта, но есть ряд нотаций (Чена, IDEFIX, Мартина и т.п.) [2], но на модель предметной области не удалось найти ни стандарта, ни нотаций. Однако, в ходе построения такой диаграммы обязательно выделяются ключевые поля (внешние и внутренние), иногда — индексы и типы данных. Схема базы данных, приведенная на рисунке, выполнена с использованием открытого инструмента plantuml [3], при этом:

1. для связей используется нотация Мартина («вороньи лапки»);
2. таблицы изображены прямоугольниками, разделенными на 3 секции:
   1. имя таблицы;
   2. внутренние ключи (помечаются маркером);
   3. остальные поля, при этом обязательные поля помечаются маркером.

3.1 Составление и нормализация реляционных отношений

Схема отношения «Билеты» (tickets):

Наименование поля	Тип поля	Размер поля	Значение по умолчанию	Ограничения	Ключ или индекс
id_screening	int	4	IDENTITY(1,1)	обязательное поле	первичный ключ (составной)
row	smallint	2	—	обязательное поле	первичный ключ (составной)
seat	smallint	2	—	обязательное поле	первичный ключ (составной)

Схема отношения «Прокаты» (screening):

Наименование поля	Тип поля	Размер поля	Значение по умолчанию	Ограничения	Ключ или индекс
id	int	4	IDENTITY(1,1)	обязательное поле	Первичный ключ, уникальный
hall_id	int	4	—	обязательное поле	Внешний ключ к hall
film_id	int	4	—	обязательное поле	Внешний ключ к film
time	datetime	8	—	обязательное поле	—

Схема отношения «Кинозалы» (hall):

Наименование поля	Тип поля	Размер поля	Значение по умолчанию	Ограничения	Ключ или индекс
id	int	4	IDENTITY(1,1)	обязательное поле	первичный ключ
name	varchar	100	—	обязательное поле	первичный ключ

Схема отношения «Ряд кинозала» (hall_row):

Наименование поля	Тип поля	Размер поля	Значение по умолчанию	Ограничения	Ключ или индекс
id_hall	int	4	IDENTITY(1,1)	обязательное поле	первичный ключ (составной)
number	smallint	2	—	обязательное поле	первичный ключ (составной)
capacity	smallint	2	—	обязательное поле	—

Схема отношения «Фильмы» (film):

Наименование поля	Тип поля	Размер поля	Значение по умолчанию	Ограничения	Ключ или индекс
id	int	4	IDENTITY(1,1)	обязательное поле	первичный ключ
name	varchar	255	—	обязательное поле	—
description	varchar	2000	NULL	необязательное поле	—

При выборе типов данных и описании их размеров использовалась документация [4]. Для ряда полей, где известно что значениями будут целые числа в небольшом диапазоне используется тип smallint. Для строковых полей используется varchar, однако мог бы использоваться и тип char, критично это только для поля film.description. Дело в том, что описания фильмов бывают длинными, поэтому при создании таблицы надо указать заранее «достаточный» размер поля, например 2000 символов. Однако, согласно документации, при использовании типа char, под все описания фильмов будет выделено 2000 символов, а при использовании varchar более короткие описания будут потреблять меньше памяти — ровно столько, сколько необходимо.
Разработанная схема БД находится в:

1. первой нормальной форме, так как в качестве доменов выступают только скалярные значения и информация в таблицах не дублируется. Почти во всех таблицах есть идентификатор (id), а в остальных — в качестве первичного ключа выступает кортеж (набор полей);
2. во второй и третьей нормальных формах, так как каждый не ключевой атрибут неприводимо и нетранзитивно зависит от первичного ключа. Для всех таблиц нашей БД это очевидно — количество мест в ряду зависит только от пары (номер зала, номер ряда) и никаким другим образом вывести его из информации в базе нельзя.

Таким образом, схема базы данных находится в нормальной форме Бойса-Кодда [5].

3.2 Инсталляция MS SQL Server и создание пустой базы

Был скачан и проинсталлирован MS SQL Server 2014 [6], так как работа выполнялась на 32х-разрядном компьютере, а более новые версии программы не поддерживают такую архитектуру. При установке была выбрана «Установка нового изолированного экземпляра SQL Server» с параметрами по умолчанию. Как показано на рисунке, при установке задано имя экземпляра «my_project».

В результате, на компьютер была установлена программа SQL Server Management Studio, внутри которой выбирается имя сервера, как показано ниже:

После выбора сервера в обозревателе объектов отобразились компоненты сервера, в том числе вкладка «базы данных». В контекстом меню был выбран пункт добавления базы, в качестве имени указано «my_db», как показано на рисунке:

3.3 Формирование таблиц

После создания базы данных в обозревателе объектов появилась возможно добавить в базу данных таблицы. На рисунке ниже показан процесс добавления таблицы hall — видно, что указываются имена полей и их типы, а для ключевого поля указано «начальное значение» и «шаг приращения».

Ключевые поля добавляются в таблицы с помощью контекстоного меню, выпадающего после клика по полю правой кнопкой. Однако, для таблиц с составными и внешними ключами, например hall_row сделать это через графический интерфейс не получилось. В нем были созданы только заготовки таблиц, для них были сгенерированы скрипты T-SQL и дополнены соответствующими параметрами. Для генерации T-SQL скрипта для таблицы в меню выбирается «создать скрипт для таблицы -> используя DROP и CREATE». Сгенерированные скрипты были поправлены, в результате получено следующее:

USE [my_db]
GO

DROP TABLE [dbo].[hall_row]
GO

DROP TABLE [dbo].[tickets]
GO

DROP TABLE [dbo].[screening]
GO

DROP TABLE [dbo].[hall]
GO

DROP TABLE [dbo].[film]
GO

CREATE TABLE [dbo].[film](
  id int IDENTITY(1,1) NOT NULL,
  name varchar(255) NOT NULL,
  description varchar(2000) NOT NULL,
  CONSTRAINT [PK_film] PRIMARY KEY CLUSTERED 
  (
    id ASC
  )
)
GO


CREATE TABLE [dbo].[hall](
  id int IDENTITY(1,1) NOT NULL,
  name nvarchar(100) NOT NULL,
  CONSTRAINT [PK_hall] PRIMARY KEY CLUSTERED 
  (
    id ASC
  ) 
)
GO

CREATE TABLE [dbo].[screening](
  id int IDENTITY(1,1) NOT NULL,
  hall_id int NOT NULL,
  film_id int NOT NULL,
  time datetime NOT NULL,

  FOREIGN KEY (hall_id)  REFERENCES hall (id),
  FOREIGN KEY (film_id)  REFERENCES film (id),
  
  CONSTRAINT [PK_screening] PRIMARY KEY CLUSTERED 
  (
    id ASC
  )
)
GO

CREATE TABLE [dbo].[hall_row](
  id_hall int NOT NULL,
  number smallint NOT NULL,
  capacity smallint NOT NULL,

  FOREIGN KEY (id_hall)  REFERENCES hall (id),

  CONSTRAINT [PK_hall_row] PRIMARY KEY CLUSTERED 
  (
    id_hall, number
  )
)
GO

CREATE TABLE [dbo].[tickets](
  id_screening int NOT NULL,
  row smallint NOT NULL,
  seat smallint NOT NULL,
  cost int NOT NULL,

  FOREIGN KEY (id_screening)  REFERENCES screening (id),

  CONSTRAINT [PK_ticket] PRIMARY KEY CLUSTERED 
  (
    id_screening, row, seat
  )
)
GO

Измененный скрипт был запущен в MS SQL Management Studio, в результате были обновлены таблицы. Затем, на их основе сгенерирована схема базы данных:

3.4 Наполнение базы

Для наполнения базы был создан такой запрос (приведен фрагмент):

INSERT INTO [dbo].[film] (name, description)
  VALUES ('Багратион', '«Багратион» — советский двухсерийный историко-биографический фильм 1985 года о жизни прославленного российского полководца Петра Ивановича Багратиона — героя Отечественной войны 1812 года. Совместное производство «Грузия-фильм» и «Мосфильм». Режиссёры Гиули Чохонелидзе и Караман Мгеладзе. Премьера — декабрь 1985 года. ')

INSERT INTO [dbo].[hall] (name) VALUES ('красный зал')
INSERT INTO [dbo].[hall] (name) VALUES ('желтый зал')
INSERT INTO [dbo].[hall] (name) VALUES ('синий зал')

INSERT INTO [dbo].[hall_row] (id_hall ,number ,capacity) VALUES (1, 1, 10)
INSERT INTO [dbo].[hall_row] (id_hall ,number ,capacity) VALUES (1, 2, 15)
INSERT INTO [dbo].[hall_row] (id_hall ,number ,capacity) VALUES (1, 3, 20)

INSERT INTO [dbo].[screening] (hall_id ,film_id, time) VALUES (1, 1, '20210101 10:35:00 AM')
INSERT INTO [dbo].[screening] (hall_id ,film_id, time) VALUES (1, 1, '20210101 00:00:00 AM')
INSERT INTO [dbo].[screening] (hall_id ,film_id, time) VALUES (1, 2, '20210101 1:35:00 PM')

INSERT INTO [dbo].[tickets] (id_screening ,row ,seat ,cost) VALUES (1, 2, 3, 150)
INSERT INTO [dbo].[tickets] (id_screening ,row ,seat ,cost) VALUES (1, 3, 3, 200)
INSERT INTO [dbo].[tickets] (id_screening ,row ,seat ,cost) VALUES (1, 3, 5, 150)

% ...

Запрос выполняется успешно, а результаты его выполнения проверялись с помощью SELECT-запросов:

3.5 Проектирование наиболее востребованных запросов

Как отмечалось в разделе 1, при продаже билета посетитель кинотеатра устно передает кассиру номер и место. Кассир вводит эти данные в систему, которая не должна позволить продать билеты на несуществующие места. Для этого программа-клиент кассира должна получить вместимость ряда конкретного зала. Чтобы получить количество мест во втором ряду третьего зала надо выполнить запрос:

SELECT capacity FROM hall_row WHERE id_hall = 3 AND number = 2

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

Затем, программа-клиент должна проверить не продано ли это место. Для этого можно выполнить отдельный SELECT, но можно попробовать выполнить INSERT INTO и если место было ранее продано — запрос завершится с ошибкой, ведь на таблицу билетов наложены соответствующие ограничения.

Чтобы сформировать расписание показов фильмов, прокатываемых с определенного момента (обычно в запрос будет поставляться текущее время) можно использовать такой запрос:

SELECT * FROM film, screening WHERE 
time > '20210101 11:00:00 AM' AND screening.film_id = film.id;

в данном случае в запросе используется две таблицы, которые связываются по идентификатору. Выбираются названия фильмов, показ которых начинается после 11 часов 01.01.2021. Результат выполнения запроса:

Для получения расписания проката в конкретном зале кинотеатра надо добавить в запрос связь с третьей таблицей и ограничения на эту таблицу:

SELECT film.name, hall.id, screening.time FROM film, screening, hall WHERE 
time > '20210101 11:00:00 AM' AND screening.film_id = film.id 
AND screening.hall_id = hall.id AND hall.id = 2;

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

Для получения расписания проката конкретного фильма — можно вставить в запрос его идентификатор:

SELECT film.name, hall.name, screening.time FROM film, screening, hall WHERE 
time > '20210101 11:00:00 AM' AND screening.film_id = film.id 
AND screening.hall_id = hall.id AND film.id = 2;

Если вдруг нас интересуют фильмы, названия которых соответствует определенному шаблону — можно использовать оператор LIKE. Так, приведенный ниже запрос выбирает все фильмы, прокатываемые с определенного момента, названия которых начинаются с символа 'Б', шаблон '%' задает в T-SQL любое количество любых символов.

SELECT film.name, hall.name, screening.time FROM film, screening, hall WHERE 
time > '20210101 11:00:00 AM' AND screening.film_id = film.id 
AND screening.hall_id = hall.id AND film.name LIKE 'Б%'; 

Чтобы вывести расписание прокатов, упорядоченное по залу и дате нужно применить конструкцию ORDER BY:

SELECT film.name, hall.name, screening.time FROM film, screening, hall WHERE 
time > '20210101 11:00:00 AM' AND screening.film_id = film.id 
AND screening.hall_id = hall.id ORDER BY hall.name, screening.time;

Список полезной литературы

1. Учимся проектированию Entity Relationship — диаграмм // Хабр URL: https://habr.com/ru/post/440556/ (дата обращения: 02.01.2021).
2. Технологии баз данных. Лекция 3. Модель «Сущность-связь». URL: https://docplayer.ru/27886777-Model-sushchnost-svyaz-tehnologii-baz-dannyh-lekciya-3.html (дата обращения: 02.01.2021).
3. Entity Relationship Diagram. URL: https://plantuml.com/ru/ie-diagram (дата обращения: 03.01.2021).
4. Transact-SQL Reference (Database Engine) // Microsoft Docs URL: https://docs.microsoft.com/ru-ru/sql/t-sql/language-reference?view=sql-server-ver15 (дата обращения: 05.01.2021).
5. Нормализация отношений. Шесть нормальных форм // Хабр URL: https://habr.com/ru/post/254773/ (дата обращения: 05.01.2021).
6. Материалы для скачивания по SQL Server // Microsoft URL: https://www.microsoft.com/ru-ru/sql-server/sql-server-downloads (дата обращения: 05.01.2021).
7. Другой пример проектирования базы данных (MySQL). URL: https://pro-prof.com/forums/topic/db_example