1.10. Создание хранимых процедур пользователя

Задание 1.10.1. Создание хранимой процедуры МуРгос, которая будет возвращать список авторов, проживающих в определенном штате, а также список написанных ими книг. Хранимая процедура будет иметь единственный параметр @state с типом данных char(2), с помощью которого будет указываться штат, об авторах которого требуется получить информацию. Присвоим этому параметру значение по умолчанию 'СА'. Таким образом, при вызове хранимой процедуры будет возвращаться информация об авторах, проживающих в штате Калифорния.

Текст процедуры следующий:

CREATE PROC MyProc

@state char(2) = 'СА'

AS

SELECT a.au_lname. a.au_fname, t.title.

FROM authors a, titleauthor ta, titles t

WHERE ta.au_id=a.au_id AND

t.title_id=ta.title_id AND

state = @state

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

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

• словесное описание предметной области;

• обобщенную схему базы данных в нотации IDEF1X;

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

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

• спецификаций запросов, представлений, хранимых процедур триггеров.

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

В приведенных ниже таблицах используются следующие обозначения:

• PK – первичный ключ, AK – альтернативный ключ, FK – внешний ключ;

• N – один из целых типов, R – один из вещественных типов, С – один из символьных типов, В – логический тип, D – тип дата.

Описание предметных областей заимствовано в [9], поэтому выражаем благодарность авторам книги и рекомендуем книгу студентам в качестве практического пособия по языку SQL.

2.1. Предметная область «Летопись острова Санта-Белинда»

Словесное описание предметной области. Где-то в великом океане находится воображаемый остров Санта-Белинда. Вот уже триста лет ведется подробная летопись острова. В эту летопись заносятся и данные обо всех людях, какое-то время проживавших на острове. Записываются их имена, пол, даты рождения и смерти. Хранятся там и имена их родителей, если известно, кто они. У некоторых отсутствуют сведения об отце, у некоторых – о матери, а часть людей, судя по записям, – круглые сироты. Из летописи можно узнать, когда был построен каждый дом, стоящий на острове (а если сейчас его уже нет, то когда он был снесен), точный адрес, кто и когда в нем жил.

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

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

Схема базы данных в нотации IDEF1X

Описание таблиц базы данных

№ п.п	Название	Идентификатор	Назначение	Тип связи	Атрибуты для связи
1.	Жители	People	Список жителей острова	1:М Жильцы	Ид. № жителя
				1:М Предприниматели	Ид. № жителя
2.	Дома	house	Список домов	1:М Жильцы	Ид. № дома
3.	Жильцы	Occupant	Список жильцов для каждого дома	М:1 Жители	Ид. № жителя
				М:1 Дома	Ид. № дома
4.	Ремесла	Crafts	Список ремесел	1:М Предприниматели	Ид. № ремесла
5.	Предприниматели	Business-mans	Список предпринимателей	М:1 Жители	Ид. № жителя
				М:1 Ремесла	Ид. № ремесла

Описание атрибутов таблицы Жители

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № жителя	ID_People	N	10	0÷9999999999	–	+	PK
2.	Фамилия	Surname	C	20	Только буквы	–	+	–
3.	Имя	Name	С	15	Только буквы	–	+	–
4.	Пол	Sex	В	1	0 или 1	1	+	–
5.	Дата рождения	Date_R	D	8	01.01.1700÷01.01.2015	–	+	–
6.	Дата смерти	Date_D	D	8	01.01.1700÷01.01.2015	–	–	–
7.	Отец	Father	С	20	Только буквы	–	–	–
8.	Мать	Mother	С	20	Только буквы	–	–	–

Описание атрибутов таблицы Дома

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № дома	ID_ house	N	8	0÷99999999	–	+	PK
2.	Адрес	Address	C	50		–	+	АК
3.	Дата постройки	Date_P	D	8	01.01.1700÷01.01.2015	–	+	–
4.	Дата сноса	Date_	D	8	01.01.1700÷01.01.2015	–	–	–
5.	Площадь	Area	R	7.2	0÷9999	–	+	–

Описание атрибутов таблицы Жильцы

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № жителя	ID_People	N	10	0÷9999999999	–	+	FK
2.	Ид. № дома	ID_ house	N	8	0÷99999999	–	+	FK
3.	Дата начала проживания	Date_N_Р	D	8	01.01.1700÷01.01.2015	–	+	–
4.	Дата окончания проживания	Date_К_Р	D	8	01.01.1700÷01.01.2015	–	–	–

Описание атрибутов таблицы Ремесла

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № ремесла	ID_craft	N	3	0÷999	–	+	PK
2.	Название ремесла	Name_craft	С	20	–	–	+	AK
3.	Описание ремесла	Description	С	200	–	–	–	–

Описание атрибутов таблицы Предприниматели

№ п/п	Название	Идентификатор	Тип	Размер	Ограничения	Знач. по умолчанию	Обязательное поле?	Признак ключа
1.	Ид. № жителя	ID_People	N	10	0÷9999999999	–	+	FK
2.	Ид. № ремесла	ID_craft	N	3	0÷999	–	+	FK
3.	Дата начала деятельности	Date_N_D	D	8	01.01.1700÷01.01.2015	–	+	–
4.	Дата окончания деятельности	Date_К_D	D	8	01.01.1700÷01.01.2015	–	–	–
5.	Причина окончания деятельности	Reason	С	80	–	–	–	–
6.	Количество наемных работников	Workmans	N	3	0÷999	0	+	–

Запросы на выборку данных

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

2. Выдать список жителей острова, проживающих по заданному адресу в заданный период времени.

3. Определить количество предпринимателей мужского пола, имеющих наемных работников, среднее, минимальное и максимальное количество наемных работников.

4. Выдать количество домов, их суммарную и среднюю площадь для всех жителей, родившихся в 20-м веке.

5. По названию ремесла выдать все адреса предпринимателей.

Представления

1. Создать представление, в котором для каждого жителя острова хранились его фамилия и имя, адрес проживания, дата начала и окончания проживания, площадь его дома.

2. Создать представление, в котором для каждого предпринимателя острова хранились его фамилия и имя, название ремесла и его краткая характеристика, количество наемных работников, причем поле «дата окончания деятельности» должно быть пусто.

Хранимые процедуры

1. Написать хранимую процедуру, создающую список всех жителей острова, не проживающих ни в одном доме острова.

2. Написать хранимую процедуру, осуществляющую прописку заданного жителя острова по указанному адресу. При прописке необходимо проверить, достаточно ли площади для прописки, предполагая, что на каждого проживающего должно приходиться не менее 20 м².

Триггеры

1. При удалении очередного предпринимателя проверять, остались ли еще предприниматели, занимающиеся этим же ремеслом.

2. При изменении площади дома проверять, не нарушены ли нормы проживания (не менее 20 м² на одного человека).

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