Реализации базы данных в Microsoft SQL Server

 

insert into city values ('Москва');

insert into city values ('Самара');

insert into city values ('Ульяновск');

insert into city values ('Берлин');

insert into city values ('Брюссель');

 

insert into street values ('ул. Ленина');

insert into street values ('ул. Лейпцигская');

insert into street values ('бул. Ревизьон');

insert into street values ('ул. Ульяновская');

insert into street values ('ул. Полевая');

 

insert into address values ('1, оф.1', 1, 1, 1, 1);

insert into address values ('1, оф.2', 1, 1, 1, 1);

insert into address values ('3', 1, 1, 1, 1);

insert into address values ('1, оф.1', 1, 2, 2, 5);

insert into address values ('2', 1, 2, 2, 5);

insert into address values ('1, оф.1', 1, 3, 3, 4);

insert into address values ('10, оф.1', 1, 1, 1, 1);

insert into address values ('12, оф.1', 1, 1, 1, 1);

insert into address values ('15, оф.2', 1, 1, 1, 1);

insert into address values ('10, оф.2', 1, 2, 2, 5);

insert into address values ('5, оф.2', 3, null, 4, 2);

insert into address values ('4', 1, 1, 1, 1);

insert into address values ('4', 1, 2, 2, 5);

insert into address values ('4', 3, null, 4, 2);

 

insert into realty_type values ('магазин');

insert into realty_type values ('офис');

insert into realty_type values ('склад');

 

insert into realty values (200, 1, 'новый офис', 1, 2);

insert into realty values (300, 2, 'новый офис', 2, 2);

insert into realty values (2000, 1, 'склад товаров', 3, 3);

insert into realty values (100, 1, 'новый офис', 4, 2);

insert into realty values (200, 1, 'помещение под магазин', 5, 1);

insert into realty values (150, 2, 'новый офис', 6, 2);

 

insert into bank values ('Сбербанк', '+7(495)1234568', 12);

insert into bank values ('Уралсиб', '+7(846)4588566', 13);

insert into bank values ('Банк германии', '589-9854-444', 14);

 

insert into client values ('Иванов Иван Иванович', null, 1258544589, '+(495)4567895', 0, 7);

insert into client values ('Иванов Иван Петрович', 'Такси', 1258534489, '+(495)4567892', 1, 8);

insert into client values ('Семенов Семен  Семенович', 'Продукты питания', 1258367789, '+(495)4567833', 1, 9);

insert into client values ('Петров Петр  Петрович', 'Спортивные товары', 4558544589, '+(846)4567895', 1, 10);

insert into client values ('Людвиг Фишер', 'Грузоперевозки', 4565544589, '433-567-895', 1, 11);

 

insert into employee values ('Иванов Иван Иванович', '+(495)4556695');

insert into employee values ('Петров Петр Петрович', '+(495)4567777');

insert into employee values ('Семенов Михаил Иванович', '+(495)4567222');

 

insert into pay_type values ('Ежемесячно', 30);

insert into pay_type values ('Ежеквартально', 90);

 

insert into contract values ('2012-01-01', '2012-12-20', 30000, 1, 1, 1, 1, 1);

insert into contract values ('2012-02-01', '2012-12-20', 40000, 1, 2, 1, 2, 2);

insert into contract values ('2012-03-01', '2013-12-20', 40000, 2, 3, 1, 3, 1);

insert into contract values ('2012-04-01', '2013-12-20', 45000, 1, 4, 2, 4, 1);

insert into contract values ('2012-05-01', '2013-12-20', 35000, 1, 5, 3, 5, 2);

 

 

Рис. 10 – Ввод команд для заполнения таблиц

Создание  запросов

 

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

 

1. Просмотр всей  недвижимости и ее типа. Из адреса недвижимости указывается только город.

 

select realty.realty_id, realty.square, realty.floor, realty.description, realty_type.realty_type_name, city.city_name from realty left outer join realty_type on realty.realty_type_id=realty_type.realty_type_id left outer join address on address.address_id=realty.address_id left outer join city on city.city_id=address.city_id;

 

Результат запроса:

 

 

2. Просмотр всех  сотрудников отдела аренды.

 

select * from employee;

 

Результат запроса:

 

 

3. Сколько договоров заключил каждый сотрудник.

 

select e.employee_id, e.employee_fio, e.employee_phone, count(contract.contract_id) as contract_count from employee e left outer join contract on contract.employee_id=e.employee_id group by e.employee_id, e.employee_fio, e.employee_phone;

 

Результат запроса:

 

4. Запрос выводит название города и количество договоров аренды на недвижимость в нем. Результат сортируется по убыванию числа договоров.

 

select c.city_name, count(con.contract_id) as contract_count from city c join address a on c.city_id=a.city_id join realty r on r.address_id=a.address_id join contract con on con.realty_id=r.realty_id group by c.city_name order by contract_count desc;

 

Результат запроса:

 

 

5. Запрос находит  все открытые договора с юридическими  лицами из Москвы. Москва имеет  city_id = 1. У клиентов являющихся физическим лицом поле individual = 1.

 

select c.contract_id, c.start_date, c.end_date, c.price, c.pay_type_id, client.company, client.client_phone from contract c join client on client.client_id=c.client_id and client.individual=1 join address on address.address_id=client.address_id and address.city_id=1 where c.end_date>GETDATE();

 

Результат запроса:

 

 

Создание  процедур и триггеров

 

1. Процедура,  возвращающая список договоров  заключенных в указанный промежуток  времени. Входные параметры: date1 – начальная дата заключения договора, date2 – конечная дата заключения договора.

create procedure get_contract;1

@date1 date=NULL,

@date2 date=NULL

as

select * from contract where start_date > @date1 and start_date < @date2

 

Вызов процедуры:

 

declare @date1 date, @date2 date

set @date1='2012-01-01'

set @date2='2012-05-01'

exec get_contract;1 @date1,@date2

 

Результат:

 

 

2. Процедура рассчитывает прибыль по договору. На основе интервала типа оплаты (ежемесячно или ежеквартально) и цены за указанный интервал рассчитывается стоимость одного дня аренды. Затем стоимость дня аренды умножается на число прошедших дней аренды. Полученная сумма возвращается как результат функции. Входной параметр – id договора.

 

create procedure get_profit;1

@code int = null,

@result decimal(38,2) = 0 output,

@days int = 0,

@day_cost decimal(38,2) = 0,

@start_date date = null,

@last_date date = null,

@interval int = 0

as

set @last_date = (select end_date from contract where contract_id = @code)

set @start_date = (select start_date from contract where contract_id = @code)

if @last_date > GETDATE()

begin

set @days = DATEDIFF(DAY, @start_date, GETDATE())

end

else

begin

set @days = DATEDIFF(DAY, @start_date, @last_date)

end

set @interval = (select p.interval from pay_type p where p.pay_type_id = (select pay_type_id from contract where contract_id = @code))

set @day_cost = (select price from contract where contract_id = @code) / @interval

set @result = @day_cost * @days

Вызоа процедуры:

 

declare @code int, @result decimal(38,2)

set @code = 1

exec get_profit;1 @code,@result output

select @result

 

Результат процедуры:

 

 

3. Триггер, который  не позволяет удалять не завершенные  контракты.

 

create trigger delete_contract

on contract for delete

as

declare @end_date date

set @end_date = (select end_date from deleted)

if @end_date > getdate()

begin

raiserror('Нельзя удалить открытый контракт',1,11)

rollback tran

end

return

 

4. Триггер, запрещающий  создание договора с физическими  лицами не из России. Физическим  лицом является клиент, у которого  значение поля individual = 0. Проверка на это значение происходит в триггере.

 

create trigger insert_contract

on contract for insert

as

declare @ind int, @country_id int

set @ind = (select c.individual from inserted join client c on c.client_id = inserted.client_id)

set @country_id = (select c.country_id from country c where upper(c.country_name) = 'РОССИЯ'

if (select a.country_id from inserted join client c on inserted.client_id = c.client_id join address a on a.address_id = c.address_id) = @country_id and @ind = 1

begin

raiserror('Нельзя создать контракт',1,11)

rollback tran

end

return

 

5. Триггер запрещает  изменять ИНН клиента.

 

create trigger update_client

on client for update

as

if update (inn)

begin

raiserror('Нельзя изменить ИНН',1,11)

rollback tran

end

return

 

Обеспечение безопастности

 

Важное место  при создании базы данных занимает обеспечение ее безопасности.  Необходимо обеспечить доступ к имеющейся базе данных определенных пользователей. Для обеспечения доступа используем команду exec sp_grantdbaccess:

 

exec sp_grantdbaccess @loginame = 'LAPTOP\User,@name_in_db='rental_user'

 

Гарантируем ему  доступ к таблице contract и разрешим ему изменять, вносить и удалять данные в этой таблице:

 

grant insert on contract to rental_user

grant delete on contract to rental_user

grant update on contract to rental_user

 

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

 

exec sp_revokedbaccess @name_in_db = 'rental_user'.

 

Создание  базы данных в MS Access

 

Создадим в MS Access базу данных аналогичную рассмотренной выше. Для этого запустим MS Access из главного меню. Выполним команду Файл→Создать→Новая База Данных. В файловом окне укажем имя файла «rental_department».

В режиме таблицы выберем создание таблицы в режиме конструктор (рисунок 11).

 

 

Рис. 11 – Выбор  режима конструктора

 

На экране откроется окно конструктора таблиц. На рисунке 12 показано для таблицы городов. По аналогии создадим другие таблицы.

 

 

Рис. 12 – Режим  конструктора создания таблиц

 

После создания таблиц установим связи между  ними. Для этого щелкнув правой кнопкой мыши в области базы данных, выбираем пункт «схема данных» (рисунок 13).

 

 

Рис. 13 – Открытие схемы данных

 

Добавляем все  таблицы в схему данных. Устанавливаем  связи в соответствии со структурой базы данных на рисунке 2. Для создания связи щелкаем мышкой по таблице. Появляется окно «изменение связей» (рисунок 14). Нажимаем «новое». Выбираем таблицы, которые участвуют в связи. Выбираем поля, по которым связываются таблицы (рисунок 15). Ставим галочку «Обеспечение целостности данных». Нажимаем «Ок». Созданная связь появится на схеме данных с указанием типа связи. В нашем случае тип связи один ко многим. По аналогии создадим остальные связи. На рисунке 16 показана схема данных со всеми связями.

 

 

Рис. 14 – Окно «изменение связей»

 

 

Рис. 15 – создание связи

 

 

Рис. 16 – Схема  данных со связями

 

 

Заполнение  таблицы городов показано на рисунке 17.

 

 

Рис. 17 – Заполнение таблицы город

 

По аналогии заполним остальные таблицы.

 

Создание  запросов в MS Access

 

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

Далее на рисунках приведено  создание запросов в базе данных и результаты выполнения запросов:

 

 

Рис. 18 – Создание запроса «просмотр всей недвижимости»

 

Рис. 19 – Создание запроса «город и количество договоров»

 

 

Рис. 20 – Создание запроса «просмотр сотрудников»

 

 

 

 

 

 

Рис. 21 – Создание запроса «число договоров сотрудника»

 

 

Рис. 22  - Результат  запроса «просмотр всей недвижимости»

 

 

Рис. 23  - Результат  запроса «город и количество договоров»

 

Рис. 24  - Результат  запроса «просмотр сотрудников»

 

 

Рис. 25  - Результат  запроса «число договоров сотрудников»

 

Создание  форм в MS Access

 

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

Далее показаны созданные в приложении формы:

 

 

Рис. 26 – Главное  меню

 

 

Рис. 27 – Просмотр банков

 

Рис. 28 – Просмотр клиентов