И.Д. Кособудский, Е.В. Третьяченко, И.Е. Шпак

Приложение А. Описание учебной базы данных

Связи между таблицами

В реляционных базах данных между таблицами, как уже было отмечено, существуют связи (отношения). Если между некоторыми сущностями существует связь, то факты из одной сущности ссылаются или некоторым образом связаны с фактами из другой сущности. Связь работает путем сопоставления первичного ключа одной таблицы (родительской сущности) с элементом внешнего ключа другой таблицы (дочерней сущности) [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 – оплачиваемый год.

Таким образом, при сопоставлении этой информации по конкретному

абоненту с данными, хранящимися в таблице NachislSum­ma, можно вы

числить размер долга (дебет) или переплаты (кредит) у данного абонента

на указанный месяц года.

§ 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