4. Пример проектирования реляционной базы данных
4. 1 Концептуальное проектирование
4.1.1 Анализ предметной области
В качестве примера возьмем базу данных компании, которая занимается издательской деятельностью.
База данных создаётся для информационного обслуживания редакторов, менеджеров и других сотрудников компании. БД должна содержать данные о сотрудниках компании, книгах, авторах, финансовом состоянии компании и предоставлять возможность получать разнообразные отчёты.
В соответствии с предметной областью система строится с учётом следующих особенностей:
каждая книга издаётся в рамках контракта;
книга может быть написана несколькими авторами;
контракт подписывается одним менеджером и всеми авторами книги;
каждый автор может написать несколько книг (по разным контрактам);
порядок, в котором авторы указаны на обложке, влияет на размер гонорара;
если сотрудник является редактором, то он может работать одновременно над несколькими книгами;
у каждой книги может быть несколько редакторов, один из них – ответственный редактор;
каждый заказ оформляется на одного заказчика;
в заказе на покупку может быть перечислено несколько книг.
Выделим базовые сущности этой предметной области:
Сотрудники компании. Атрибуты сотрудников – ФИО, табельный номер, пол, дата рождения, паспортные данные, ИНН, должность, оклад, домашний адрес и телефоны. Для редакторов необходимо хранить сведения о редактируемых книгах; для менеджеров – сведения о подписанных контрактах.
Авторы. Атрибуты авторов – ФИО, ИНН (индивидуальный номер налогоплательщика), паспортные данные, домашний адрес, телефоны. Для авторов необходимо хранить сведения о написанных книгах.
Книги. Атрибуты книги – авторы, название, тираж, дата выхода, цена одного экземпляра, общие затраты на издание, авторский гонорар.
Контракты будем рассматривать как связь между авторами, книгами и менеджерами. Атрибуты контракта – номер, дата подписания и участники.
Для отражения финансового положения компании в системе нужно учитывать заказы на книги. Для заказа необходимо хранить номер заказа, заказчика, адрес заказчика, дату поступления заказа, дату его выполнения, список заказанных книг с указанием количества экземпляров.
ER–диаграмма издательской компании приведена на рис. 2 (базовые сущности на рисунках выделены полужирным шрифтом).
Рисунок 2 - ER–диаграмма издательской компании
42.1.2. Анализ информационных задач и круга пользователей системы
Система создаётся для обслуживания следующих групп пользователей:
администрация (дирекция);
менеджеры;
редакторы;
сотрудники компании, обслуживающие заказы.
Определим границы информационной поддержки пользователей:
1) Функциональные возможности:
ведение БД (запись, чтение, модификация, удаление в архив);
обеспечение логической непротиворечивости БД;
обеспечение защиты данных от несанкционированного или случайного доступа (определение прав доступа);
реализация наиболее часто встречающихся запросов в готовом виде;
предоставление возможности сформировать произвольный запрос на языке манипулирования данными.
2) Готовые запросы:
получение списка всех текущих проектов (книг, находящихся в печати и в продаже);
получение списка редакторов, работающих над книгами;
получение полной информации о книге (проекте);
получение сведений о конкретном авторе (с перечнем всех книг);
получение информации о продажах (по одному или по всем проектам);
определение общей прибыли от продаж по текущим проектам;
определение размера гонорара автора по конкретному проекту.
Задание по работе: по заданному описанию предметной области построить концептуальную модель базы данных :
Выделите типы сущностей;
Выделите типы связей и определите для них показатели кардинальности и степень участия сторон;
Выделите атрибуты и свяжите их типами сущностей и связей;
Определите потенциальные и первичные ключи сущностей;
Нарисуйте ER-диаграмму.
и проанализируйте информационные задачи и группы пользователей.
Вариант1.
Задача – информационная поддержка деятельности железнодорожных касс. БД должна осуществлять:
ведение списка рейсов и билетов на них с указанием класса;
учёт забронированных мест;
ведение архива пассажиров за последний месяц.
Необходимо предусмотреть:
продажу билетов в оба конца;
поиск места на рейс в соответствии с требованиями заказчика;
получение списка свободных мест на рейс;
выдачу информации по конкретному рейсу;
получение списка проданных мест;
проверку наличия брони по имени клиента и/или названию организации.
Вариант2.
Задача – информационная поддержка деятельности спортивного клуба. БД должна осуществлять:
ведение списков спортсменов и тренеров;
учёт проводимых соревнований (с ведением их архива);
учёт травм, полученных спортсменами.
Необходимо предусмотреть:
возможность перехода спортсмена от одного тренера к другому;
составление рейтингов спортсменов;
составление рейтингов тренеров;
выдачу информации по соревнованиям;
выдачу информации по конкретному спортсмену;
подбор возможных кандидатур на участие в соревнованиях (соответствующего уровня мастерства, возраста и без травм).
Вариант3.
В базе данных учета заказов должна содержаться следующая информация о клиентах, деталях и заказах :
Для каждого клиента : номер клиента ( уникальный), адрес доставки ( возможно несколько для одного клиента), балансовый счет ( сумма к оплате), максимальный размер кредита, скидка.
Для каждого заказа: номер клиента, адрес доставки, дата выполнения заказа, номер детали, заказанное количество.
Для каждой детали: номер детали( уникальный), заводы-изготовители, запас деталей на каждом заводе, максимальное количество деталей на складе для каждого завода, описание детали.
Для внутреннего учета также вводится величина «оставшееся количество», связанное с каждой детальной строкой каждого заказа. Именно эта величина устанавливается равной заказанному количеству данной детали и последовательно уменьшается до нуля по мере частичного выполнения данной поставки.
Вариант4.
Задача – информационная поддержка деятельности поликлиники. БД должна осуществлять:
ведение медицинских карт пациентов;
учёт рецептов, направлений на анализы, процедур;
учёт платных услуг с выдачей счёта на оплату;
ведение очередей на приём к специалистам по направлениям лечащих врачей.
Необходимо предусмотреть:
определение посещаемости отдельных кабинетов (нагрузки врачей);
подсчёт количества заболевших за день для определения наступления эпидемии.
Вариант5.
Задача – информационная поддержка деятельности адвокатской конторы. БД должна осуществлять:
ведение списка адвокатов;
ведение списка клиентов;
ведение архива законченных дел.
Необходимо предусмотреть:
получение списка текущих клиентов для конкретного адвоката;
определение эффективности защиты (максимальный срок минус полученный срок) с учётом оправданий, условных сроков и штрафов;
определение неэффективности защиты (полученный срок минус минимальный срок);
подсчёт суммы гонораров (по отдельных делам) в текущем году;
получение для конкретного адвоката списка текущих клиентов, которых он защищал ранее (из архива, с указанием полученных сроков и статей).
Вариант6.
Описание предметной области:
В компании несколько отделов.
В каждом отделе есть некоторое количество сотрудников, занятых в нескольких проектах и размещающихся в нескольких офисах.
Каждый сотрудник имеет план работы, т.е. несколько заданий, которые он должен выполнить. Для каждого такого задания существует ведомость, содержащая перечень денежных сумм, полученных сотрудником за выполнение этого задания.
В каждом офисе установлено несколько телефонов.
В базе данных должна храниться следующая информация.
Для каждого сотрудника: номер отдела ( уникальный), его бюджет и личный номер сотрудника, возглавляющего отдел ( уникальный).
Для каждого отдела: личный номер сотрудника ( уникальный), номер текущего проекта, номер офиса, номер телефона, название выполняемого задания вместе с датой и размером выплат, проведенных в качестве оплаты за выполнение данного задания.
Для каждого проекта : номер проекта ( уникальный) и его бюджет.
Для каждого офиса : номер офиса ( уникальный), площадь в квадратных футах, номера всех установленных в нем телефонов.
Вариант7.
Задача – информационная поддержка деятельности гостиницы.
БД должна осуществлять:
ведение списка постояльцев;
учёт забронированных мест;
ведение архива выбывших постояльцев за последний год.
Необходимо предусмотреть:
получение списка свободных номеров (по количеству мест и классу);
получение списка номеров (мест), освобождающихся сегодня и завтра;
выдачу информации по конкретному номеру;
автоматизацию выдачи счетов на оплату номера и услуг;
получение списка забронированных номеров;
проверку наличия брони по имени клиента и/или названию организации
Вариант8.
Предметная область представлена следующей информацией:
- сведения о компании, осуществляющей авиарейс;
- сведения о самолетах (модель, технические характеристики)
- сведения об авиарейсах, для каждого из них предусмотреть номер, дни выполнения, время отправления и прибытия;
- маршрут авиарейса, место прибытия и отправления, наличие мест, список пассажиров.
Пользователь имеет возможность просмотра, добавления, изменения и удаления
следующей информации:
- информация о расписании рейсов и наличии свободных мест;
- информация о маршрутах рейсов;
- информация о компаниях, предоставляющих услуги по перевозке пассажиров.
Вариант9.
Задача- информационная поддержка деятельности склада.
База данных должна содержать информацию о наименовании товара, его поставщике, количестве, цене товара, конечном сроке реализации, сроке хранения на складе. Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10, то в 1,5 раза. Ведомость уценки товаров должна содержать информацию: наименование товара, количество товара(шт.), цена товара до уценки, срок хранения товара, цена товара после уценки, общая стоимость товаров после уценки.
Вариант10.
База данных должна содержать информацию о наименовании товара, его
поставщике, количестве, цене товара, конечном сроке реализации, сроке хранения на складе. Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10, то в 1,5 раза. Ведомость уценки товаров должна содержать информацию: наименование товара, количество товара(шт.), цена товара до уценки, срок хранения товара, цена товара после уценки, общая стоимость товаров после уценки.
Вариант11.
Задача – информационная поддержка деятельности аптечного склада.
В аптечном складе хранятся лекарства. Сведения о лекарствах содержатся в специальной ведомости: наименование лекарственного препарата; количество (в шт.); цена; срок хранения на складе (в месяцах). Лекарства поступают на склад ежедневно от разных поставщиков, отпускаются два раза в неделю по предварительным заказам аптек. Выяснить, сколько стоит самый дорогой и самый дешевый препарат; сколько препаратов хранится на складе более 3 месяцев; сколько стоят все препараты, хранящиеся на складе, отыскать препараты, остаток которых равен нулю , ниже требуемого по заказам.
Вариант12.
База данных должна содержать следующую информацию:
Материки и их характеристики.
Страны: название, размер, столица, географическое положение, численность населения, государственный язык, форма правления, структура народонаселения, их вероисповедование, климат, среднегодовая температура.
Реки : название, длина, глубина, географическое положение
Озера: длина, глубина, географическое положение
Океаны, моря и их характеристики. Горы и их характеристики. Водопады и их характеристики.
По имеющейся информации нужно обеспечить выполнение следующих запросов: в какой стране на указанном материке проживает наибольшее число населения. Составить таблицу для каждого материка по числу населения каждой национальности. Отыскать самое глубокое озеро, страну не имеющую морей, страны, имеющие водопады. Составить таблицу для каждой страны : реки, их число, горы, моря, озера. Отыскать страны, имеющие среднегодовую температуру ниже средней, выше средней.
Вариант13.
Описание предметной области:
В базе данных хранятся сведения обо всех студентах очной формы обучения: регистрационный номер студента, имя и фамилия, домашний адрес( улица, город, почтовый индекс), дата рождения, пол, национальность, курящий( да или нет), особые запросы, любые дополнительные комментарии, текущий статус( поселен, ожидает поселения), курс обучения. Эти сведения о студентах связаны со сведениями об аренде комнат и списке студентов, ожидающих заселения. Студенты могут арендовать комнату в корпусах университетского общежития или в отдельных квартирах для студентов. При зачислении студента в университет к нему прикрепляется один из сотрудников, который называется куратором. Куратор следит за благополучием и успеваемостью студента. Обо всех кураторах хранится такая информация: полное имя, должность, название факультета, внутренний номер телефона и номер кабинета.
Каждое общежитие имеет свое название, адрес, номер телефона и менеджера, который руководит им. Общежитие имеет только одноместные комнаты, которые характеризуются номером комнаты и арендной платой, а также номером места жительства, который является уникальным для всех общежитий данной службы расселения и используется при аренде комнаты студентом.
Служба расселения предлагает студентам возможность поселяться в отдельных, полностью меблированных квартирах для групп из 3,4 или 5 студентов ( с отдельными спальными комнатами для каждого из них). Об этих квартирах хранится следующая информация: уникальный для всех квартир номер, адрес квартиры, а также количество спальных комнат в ней. Каждая спальная комната квартиры характеризуется месячной арендной платой, номером комнаты, а также номером места жительства, который является уникальным для всех квартир данной службы расселения и используется при аренде комнаты студентом.
Студент может арендовать комнату в общежитии или в квартире на разные промежутки времени. Договора об аренде заключаются в начале каждого учебного года, причем их минимальная продолжительность может равняться одному семестру ( 17 недель), а максимальная – одному году, включая 1-ый,2-ой семестры, а также летний семестр. Каждому отдельному договору аренды между студентом и службой расселения присваивается уникальный номер. О каждом договоре хранится следующая информация: его номер, продолжительность аренды ( количество семестров), имя и регистрационный номер студента, номер места жительства, номер комнаты, адрес общежития или квартиры, дата предполагаемого заселения, а также дата предполагаемого выселения.
Вариант14.
База данных должна содержать информацию о наименовании товара, его поставщике, количестве, цене товара, конечном сроке реализации, сроке хранения на складе.
Торговый склад производит уценку хранящейся продукции. Если продукция хранится на складе дольше 10 месяцев, то она уценивается в 2 раза, а если срок хранения превысил 6 месяцев, но не достиг 10, то в 1,5 раза. Ведомость уценки товаров должна содержать информацию: наименование товара, количество товара(шт.), цена товара до уценки, срок хранения товара, цена товара после уценки, общая стоимость товаров после уценки. Выяснить максимальный минимальный сроки хранения товаров на складе; максимальную и минимальную цены товаров до уценки и после уценки .
Вариант15.
База данных должна содержать следующую информацию: руководители- ф.и.о., должность, степень, звание, место работы , предлагаемые ими темы дипломных проектов.
Студенты – ф.и.о., группа, специальность, форма обучения, консультанты по экономике, охране труда, нормоконтроль, по компьютерному проектированию. За каждым студентом необходимо закрепить тему дипломного проекта или студент выбирает сам. Каждый руководитель может иметь не более 7 дипломников, один и тот же преподаватель может быть руководителем и консультантом по компьютерному проектированию также не более чем у 7 дипломников. Для каждой специальности и формы обучения необходимо формировать отдельный приказ заданной формы. Кроме того необходимо считать сколько дипломников имеет каждый преподаватель, какой специальности, какой формы обучения, кто еще может взять дипломника, т.е. имеет свободное место.
Вариант16.
База данных должна содержать следующую информацию:
Телефоны и адреса родственников, друзей ,знакомых, соседей, сослуживцев, а также их место работы, профессию, должность, день рождения, увлечения. По.мере необходимости информация корректируется.
Кроме этого записная книжка содержит перечень мероприятий на ближайшие десять дней и хранит историю их в течении года. 1 января нового года вся информация о мероприятиях заносится в архив.
Необходимо ежедневно к 7.00 формировать напоминание о сегодняшних мероприятиях и кого необходимо сегодня поздравить. Для родственников и друзей напоминание делать за неделю до дня рождения. Провести анализ имеющейся информации: кто родился в один день, сколько человек каждой профессии, разных возрастных групп и т.д.