И.Д. Кособудский, Е.В. Третьяченко, И.Е. Шпак
Приложение А. Описание учебной базы данных
Связи между таблицами
В реляционных базах данных между таблицами, как уже было отмечено, существуют связи (отношения). Если между некоторыми сущностями существует связь, то факты из одной сущности ссылаются или некоторым образом связаны с фактами из другой сущности. Связь работает путем сопоставления первичного ключа одной таблицы (родительской сущности) с элементом внешнего ключа другой таблицы (дочерней сущности) [5]. Первичный ключ и соответствующий ему внешний ключ помогают реализовать отношение родитель-потомок между таблицами. В базе данных нужно хранить только актуальные, значимые связи.
Связи, как правило, могут различаться по типу (идентифицирующая, неидентифицирующая, полная и неполная категории, неспецифическая связь) и по, по мощности (один к одному, один ко многим и многие ко многим).,
допустимости пустых (NULL) значений.
Связь называется идентифицирующей, если экземпляр дочерней сущности идентифицируется (однозначно определяется) через ее связь с родительской сущностью. Атрибуты, составляющие первичный ключ родительской сущности, при этом входят в первичный ключ дочерней сущности. Дочерняя сущность при идентифицирующей связи всегда является зависимой.
Связь называется неидентифицирующей,если экземпляр дочерней сущности идентифицируется иначе, чем через связь с родительской сущностью. Атрибуты, составляющие первичный ключ родительской сущности, при этом входят в состав неключевых атрибутов дочерней сущности.
Мощность связи представляет собой отношение количества экземпляров родительской сущности к соответствующему количеству дочерней сущности. По мощности связи выделяют отношения «один к одному», «один ко многим», «многие ко многим».
При связи «один к одному» одной строке родительской таблицы может соответствовать не более одной строки дочерней таблицы (и наоборот). Такая связь создается, если оба связанных столбца являются первичными ключами или имеют ограничение, обеспечивающее их уникальность. Связи этого типа встречаются редко, поскольку связанную подобным образом информацию обычно удается поместить в одной таблице.
«Один ко многим» – наиболее распространенный вид связи. При этом типе связи одной строке родительской таблицы может соответствовать множество строк дочерней таблицы, но любой строке дочерней таблицы может соответствовать только одна строка родительской таблицы.
Обратимся к учебной базе данных. Все связи между таблицами учебной базы данных являются неидентифицирующими с мощностью «один ко многим». Рассмотрим, например, связь «один ко многим» между таблицами Street и Abonent (рис. 1.3).
Из рис. 1.3 следует, что в столбце StreetCD таблицы Abonent содержится идентификатор улицы, на которой проживает абонент. Столбец StreetCD в таблице Abonent представляет собой внешний ключ, ссылающийся на одноименный столбец таблицы Street. Доменом этого столбца (множеством значений, которые могут в нем храниться) является множество идентификаторов улиц, содержащихся в столбце StreetCD таблицы Street. Мощность отношения – «один ко многим», так как на одной и той же улице может проживать (и проживает) множество абонентов, но каждый абонент проживает только на одной определенной улице. Наименование улицы, на которой проживает, например, абонент Аксенов С.А., можно узнать, определив значение столбца StreetCD в строке таблицы Abonent со значением в столбце Fio, равным 'Аксенов С.А.' (число 3) и затем отыскав в таблице Street строку с таким же значением в столбце StreetCD (улица ВОЙКОВ ПЕРЕУЛОК). Например, чтобы найти всех абонентов, проживающих на улице ВОЙКОВ ПЕРЕУЛОК, следует запомнить значение столбца StreetCD для этой улицы (число 3), а потом просмотреть таблицу Abonent и найти все строки, в столбце StreetCD которых содержится число 3 (это строки для абонентов с номерами лицевых счетов '005488', '015527' и '115705').
Рис. 1.3.Связь «один ко многим» между таблицами Street и Abonent
Таким образом, отношение «один ко многим», существующее между улицами и проживающими на них абонентами, в реляционной модели реализовано в виде одинаковых значений данных, хранящихся в двух таблицах. Все отношения, существующие между таблицами реляционной базы данных, реализуются в таком виде.
При связи «многие ко многим» (неспецифическое отношение) одной строке родительской таблицы может соответствовать множество строк дочерней таблицы (и наоборот). Такая связь создается с помощью третьей таблицы, первичный ключ которой состоит из внешних ключей таблиц, связанных отношением «многие ко многим».
Расчет и оплата коммунальных платежей при кажущейся элементарности сервиса — важная составляющая повседневного быта граждан. Каждый из нас для удовлетворения своих жизненных потребностей пользуется жилищными услугами, расходует коммунальные ресурсы.Компании, оказывающие такие услуги и поставляющие коммунальные ресурсы, ежемесячно производят расчет платы за них, а обязанность граждан регулярно их оплачивать.
Учебная БД представляет собой очень упрощенный пример информационной модели расчетно-аналитической компоненты расчетно-платежного комплекса «Абонент+», которая используется для информационного обеспечения деятельности организаций по оказанию населению жилищно-коммунальных услуг и расчетам за них [3].
Учебная БД состоит из 8 таблиц: 5 таблиц-справочников и 3 информационных таблиц.
В учебной БД используются такие таблицы-справочники:
§ Street. Справочник улиц, в домах которых проживают абоненты. Поля таблицы:
StreetCD – уникальный код улицы (первичный ключ);
StreetNM – название улицы, расшифровывающее код улицы.
§ Abonent. Справочник абонентов. Поля таблицы:
AccountCD – номер лицевого счета абонента, уникальным образом идентифицирующий каждого из абонентов (первичный ключ);
StreetCD – код улицы, на которой проживает абонент (внешний ключ, ссылающийся на первичный ключ таблицы Street);
HouseNo – номер дома, в котором проживает абонент;
FlatNo – номер квартиры;
Fio – фамилия, имя и отчество абонента в формате «Фамилия И.О.»;
Phone – номер телефона.
§ Services. Справочник услуг, оказываемых абонентам жилищно-коммунальными организациями. Поля таблицы:
ServiceCD – код услуги (первичный ключ);
ServiceNM – наименование услуги.
§ Disrepair. Справочник типовых неисправностей газового оборудования абонентов. Поля таблицы:
FailureCD – код неисправности газового оборудования (первичный ключ);
FailureNM – наименование неисправности газового оборудования.
§ Executor. Справочник исполнителей заявок, поданных абонентами на ремонт газового оборудования. Исполнителями являются работники ремонтной службы газораспределительной организации, оказывающей соответствующие услуги. Поля таблицы:
ExecutorCD – уникальный код, идентифицирующий исполнителей ремонтных заявок (первичный ключ);
Fio – фамилия, имя и отчество исполнителя в формате «Фамилия И.О.».
В качестве информационных таблиц учебной БД выделены следующие таблицы:
§ NachislSumma. Таблица для хранения информации о размерах ежемесячных начислений плат абонентам за оказанные им услуги (которые расшифровываются в справочнике услуг Services). Оплата за ремонт газового оборудования производится по факту оказания услуги, и начисление за него не производится. Поля таблицы:
NachislFactCD – уникальный идентификатор факта начисления (первичный ключ);
AccountCD – номер лицевого счета абонента, которому было сделано начисление (внешний ключ, ссылающийся на первичный ключ таблицы Abonent);
ServiceCD – код услуги, за которую выполнено начисление (внешний ключ, ссылающийся на первичный ключ таблицы Services);
NachislSum – значение начисленной суммы;
NachislMonth – номер месяца, за который произведено начисление;
NachislYear – год, за месяц которого выполнено начисление.
§ PaySumma. Таблица для хранения значений оплат, внесенных абонентами за оказанные им услуги. Для каждого факта оплаты по какой-либо услуге указывается дата осуществления оплаты, оплачиваемые месяц и год. Поля таблицы:
PayFactCD – уникальный идентификатор факта оплаты по услуге Request (первичный ключ);
AccountCD – номер лицевого счета абонента, оплатившего оказанную ему услугу (внешний ключ, ссылающийся на первичный ключ таблицы Abonent);
ServiceCD – код оплаченной услуги (внешний ключ, ссылающийся на первичный ключ таблицы Services);
PaySum – значение оплаченной суммы;
PayDate – дата оплаты;
PayMonth – номер оплачиваемого месяца;
PayYear – оплачиваемый год.
Таким образом, при сопоставлении этой информации по конкретному
абоненту с данными, хранящимися в таблице NachislSumma, можно вы
числить размер долга (дебет) или переплаты (кредит) у данного абонента
на указанный месяц года.
§ Request. Таблица для хранения информации о заявках абонентов на ремонт газового оборудования. Каждая ремонтная заявка характеризуется номером лицевого счета абонента (расшифровка в справочнике Abonent), заявившего определенную неисправность газового оборудования (расшифровка в справочнике Disrepair) в его доме или квартире, исполнителем ремонтной работы (справочник Executor), датой регистрации заявки, датой выполнения ремонта и признаком погашения (1/0). Поля таблицы:
RequestCD – уникальный код ремонтной заявки (первичный ключ);
AccountCD – номер лицевого счета абонента, подавшего данную ремонтную заявку (внешний ключ, ссылающийся на первичный ключ таблицы Abonent);
FailureCD – код неисправности газового оборудования, заявленной абонентом в данной ремонтной заявке (внешний ключ, ссылающийся на первичный ключ таблицы Disrepair);
ExecutorCD – код исполнителя, ответственного за выполнение данной ремонтной заявки (внешний ключ, ссылающийся на первичный ключ таблицы Executor);
IncomingDate – дата поступления заявки;
ExecutionDate – дата выполнения заявки;
Executed – поле логического типа, признак того, погашена заявка или нет.
В общем случае зарегистрированная ремонтная заявка может быть:
- не назначена ни одному из исполнителей, а следовательно не выполнена и не погашена;
- назначена одному из исполнителей, но не выполнена им, а следовательно и не погашена;
- назначена одному из исполнителей, выполнена им, но не погашена;
- назначена одному из исполнителей, выполнена им и погашена.
ER-модели учебной БД на логическом и физическом уровнях представлены соответственно на рис. 1.5 и 1.6.
Для поддержания правил ссылочной целостности, реализующих запрет удаления записи в родительской таблице при наличии связанных записей в дочерних таблицах, в учебной базе данных определены следующие триггеры.
Td_Abonent. Триггер запускается после удаления строки в таблице Abonent. Если в таблицах NachislSumma или PaySumma имеются записи с внешним ключом AccountCD, ссылающимся на удаляемую строку таблицы Abonent, то триггер вызывает исключение Del_Restrict и операция удаления прерывается.
Td_Services. Триггер запускается после удаления строки в таблице Services. Если в таблицах PaySumma или NachislSumma имеются записи с внешним ключом ServiceCD, ссылающимся на удаляемую строку в таблице Services, то триггер вызывает пользовательское исключение Del_Restrict и прерывает выполнение операции.
Текст определения описанных триггеров приведен в скрипте по созданию учебной БД.
Ниже приводится содержимое всех таблиц учебной БД. Все запросы, приведенные в пособии, выполнены именно над этими данными, и их полное указание поможет проанализировать логику получения результатов.
Таблица А.1. Данные таблицы Street
| StreetCD | StreetNM |
| ЦИОЛКОВСКОГО УЛИЦА | |
| НОВАЯ УЛИЦА | |
| ВОЙКОВ ПЕРЕУЛОК | |
| ТАТАРСКАЯ УЛИЦА | |
| ГАГАРИНА УЛИЦА | |
| МОСКОВСКАЯ УЛИЦА | |
| КУТУЗОВА УЛИЦА | |
| МОСКОВСКОЕ ШОССЕ |
Таблица А.2. Данные таблицы Abonent
| AccountCD | StreetCD | HouseNo | FlatNo | Fio | Phone |
| Аксенов С.А. | |||||
| Конюхов В.С. | |||||
| Шубина Т.П. | |||||
| Тимошкина Н.Г. | |||||
| Лукашина Р.М. | |||||
| Мищенко Е.В. | |||||
| Маркова В.П. | |||||
| Свирина З.А. | NULL | ||||
| Шмаков С.В. | NULL | ||||
| Денисова Е.К. | |||||
| Стародубцев Е.В. | |||||
| Тулупова М.И. |
Таблица А.3. Данные таблицы Services
| ServiceCD | ServiceNM |
| Газоснабжение | |
| Электроснабжение | |
| Теплоснабжение | |
| Водоснабжение |
Таблица А.4. Данные таблицы Disrepair
| FailureCD | FailureNM |
| Засорилась водогрейная колонка | |
| Не горит АГВ | |
| Течет из водогрейной колонки | |
| Неисправна печная горелка | |
| Неисправен газовый счетчик | |
| Плохое поступление газа на горелку плиты | |
| Туго поворачивается пробка крана плиты | |
| При закрытии краника горелка плиты не гаснет | |
| Неизвестна |
Таблица А.5. Данные таблицы Executor
| ExecutorCD | Fio |
| Стародубцев Е.М. | |
| Булгаков Т.И. | |
| Шубин В.Г. | |
| Шлюков М.К. | |
| Школьников С.М. |
Таблица А.6. Данные таблицы NachislSumma
| NachislfactCD | AccountCD | ServiceCD | NachislSum | NachislMonth | NachislYear | ||||
| 18,3 | |||||||||
| 58,7 | |||||||||
| 58,7 | |||||||||
| 38,5 | |||||||||
| 58,7 | |||||||||
| 28,32 | |||||||||
| 19,56 | |||||||||
| 10,6 | |||||||||
| 38,28 | |||||||||
| 38,32 | |||||||||
| 37,15 | |||||||||
| 12,6 | |||||||||
| 25,32 | |||||||||
| 57,1 | |||||||||
| 8,3 | |||||||||
| 62,13 | |||||||||
| 37,8 | |||||||||
| 17,8 | |||||||||
| 22,56 | |||||||||
| 15,3 | |||||||||
| 32,56 | |||||||||
| 12,6 | |||||||||
| 37,15 | |||||||||
| 58,1 | |||||||||
| 28,32 | |||||||||
| 18,32 | |||||||||
| 21,67 | |||||||||
| 22,86 | |||||||||
| 60,1 | |||||||||
| 28,32 | |||||||||
| 22,2 | |||||||||
Окончание табл. А.6.
| NachislfactCD | AccountCD | ServiceCD | NachislSum | NachislMonth | NachislYear | ||||
| 25,3 | |||||||||
| 38,32 | |||||||||
| 8,3 | |||||||||
| 37,15 | |||||||||
| 18,3 | |||||||||
| 279,8 | |||||||||
| 266,7 | |||||||||
| 343,36 | |||||||||
| 271,6 | |||||||||
| 278,25 | |||||||||
| 254,4 | |||||||||
| 258,8 | |||||||||
| 239,33 | |||||||||
| 179,9 | |||||||||
| 180,13 | |||||||||
| 238,8 | |||||||||
| 237,38 | |||||||||
| 349,19 | |||||||||
| 346,18 | |||||||||
| 290,33 | |||||||||
| 580,1 | |||||||||
| 611,3 | |||||||||
| 444,34 | |||||||||
| 453,43 | |||||||||
| 454,6 | |||||||||
| 553,85 | |||||||||
| 435,5 | |||||||||
| 349,38 | |||||||||
| 418,88 | |||||||||
| 528,44 | |||||||||
| 466,69 | |||||||||
| 444,45 | |||||||||
| 480,88 | |||||||||
| 500,13 | |||||||||
Таблица А.7. Данные таблицы PaySumma
| PayFactCD | AccountCD | ServiceCD | PaySum | PayDate | PayMonth | PayYear |
| 58,7 | 08.01.2012 | |||||
| 06.01.2011 | ||||||
| 06.05.2013 | ||||||
| 10.02.2010 | ||||||
| 03.10.2011 |
Продолжение табл. А.7.
| PayFactCD | AccountCD | ServiceCD | PaySum | PayDate | PayMonth | PayYear |
| 13.06.2011 | ||||||
| 12.02.2013 | ||||||
| 22.06.2011 | ||||||
| 26.11.2012 | ||||||
| 21.11.2011 | ||||||
| 03.01.2012 | ||||||
| 58,5 | 19.07.2011 | |||||
| 06.10.2010 | ||||||
| 58,7 | 04.09.2011 | |||||
| 58,7 | 01.12.2011 | |||||
| 03.10.2011 | ||||||
| 38,5 | 13.09.2011 | |||||
| 05.02.2012 | ||||||
| 03.08.2012 | ||||||
| 19,56 | 02.04.2012 | |||||
| 03.10.2012 | ||||||
| 38,28 | 04.02.2013 | |||||
| 07.05.2013 | ||||||
| 37,15 | 04.11.2013 | |||||
| 20.09.2010 | ||||||
| 25,32 | 03.02.2013 | |||||
| 05.03.2012 | ||||||
| 8,3 | 10.09.2013 | |||||
| 03.05.2010 | ||||||
| 37,8 | 12.07.2011 | |||||
| 10.07.2012 | ||||||
| 22,56 | 25.06.2013 | |||||
| 15,3 | 08.09.2010 | |||||
| 32,56 | 18.10.2011 | |||||
| 12,6 | 22.05.2012 | |||||
| 37,15 | 23.12.2013 | |||||
| 58,1 | 07.01.2011 | |||||
| 28,32 | 08.02.2011 | |||||
| 18.03.2012 | ||||||
| 19,47 | 10.04.2013 | |||||
| 22,86 | 04.05.2010 |
Окончание табл. А.7.
| PayFactCD | AccountCD | ServiceCD | PaySum | PayDate | PayMonth | PayYear |
| 07.06.2011 | ||||||
| 28,32 | 05.03.2012 | |||||
| 22,2 | 10.08.2013 | |||||
| 25,3 | 10.09.2011 | |||||
| 38,32 | 09.10.2011 | |||||
| 8,3 | 14.11.2012 | |||||
| 37,15 | 10.08.2013 | |||||
| 07.01.2011 | ||||||
| 10.06.2012 | ||||||
| 11.03.2013 | ||||||
| 15.12.2013 | ||||||
| 271,6 | 12.03.2013 | |||||
| 06.12.2013 | ||||||
| 254,4 | 10.08.2011 | |||||
| 258,8 | 08.03.2013 | |||||
| 239,35 | 11.06.2013 | |||||
| 179,9 | 01.05.2012 | |||||
| 180,13 | 21.10.2013 | |||||
| 04.04.2010 | ||||||
| 06.04.2011 | ||||||
| 349,19 | 14.07.2012 | |||||
| 346,18 | 13.08.2012 | |||||
| 09.04.2013 | ||||||
| 580,1 | 08.08.2012 | |||||
| 611,3 | 03.11.2013 | |||||
| 444,5 | 18.04.2011 | |||||
| 14.07.2012 | ||||||
| 12.05.2013 | ||||||
| 553,85 | 02.02.2012 | |||||
| 435,5 | 12.07.2012 | |||||
| 349,38 | 18.05.2011 | |||||
| 09.07.2012 | ||||||
| 528,44 | 26.11.2013 | |||||
| 466,69 | 03.06.2012 | |||||
| 444,45 | 16.11.2013 | |||||
| 05.09.2011 |
Таблица А.8. Данные таблицы Request
| RequestCD | AccountCD | ExecutorCD | FailureCD | IncomingDate | ExecutionDate | Executed |
| 17.12.2011 | 20.12.2011 | |||||
| 07.08.2011 | 12.08.2011 | |||||
| 28.02.2012 | 08.03.2012 | |||||
| 31.12.2011 | NULL | |||||
| 16.06.2011 | 24.06.2011 | |||||
| 20.10.2012 | 24.10.2012 | |||||
| 06.11.2011 | 08.11.2011 | |||||
| 01.04.2011 | 03.04.2011 | |||||
| 12.01.2013 | 12.01.2013 | |||||
| 08.08.2011 | 10.08.2011 | |||||
| 04.09.2010 | 05.12.2010 | |||||
| 04.04.2013 | 13.04.2013 | |||||
| 20.09.2010 | 23.09.2010 | |||||
| NULL | 28.12.2011 | NULL | ||||
| 15.08.2011 | 06.09.2011 | |||||
| 28.12.2012 | 04.01.2013 | |||||
| 17.12.2011 | 27.12.2011 | |||||
| 11.10.2011 | 11.10.2011 | |||||
| 13.09.2011 | 14.09.2011 | |||||
| 18.05.2011 | 25.05.2011 | |||||
| 07.05.2011 | 08.05.2011 |