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

Федеральное агентство  по образованию 
министерство образования и науки российской федерации 
ГОУ ВПО “Пермский государственный университет”

Экономический факультет 
Кафедра информационных систем и математических методов 
в экономике

Курсовая работа

На тему:

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

«Отдел аренды»

 

                                                                        Выполнил:  

Проверил:

Пермь 2008 

Содержание

 

Содержание…………………………………………………………………………………..…..2

Описание предметной области……………………………………………………………….…3

Концептуальная схема…………………………………………………………………...………3

Логическое проектирование…………………………………………………………………….4

Структура базы данных………………………………………………………………………….3

Создание базы данных…………………………………………………………………………..4

Заполнение базы данных……………………………………………………………………...10

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

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

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

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

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

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

Создание отчетов  в MS Access…………………………………………………………….…..27

Экспорт базы данных из MS Access в MS SQL Server 2008………………………………....27

Заключение……………………………………………………………………………………...34

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Описание  предметной области

 

Отдел аренды занимается сдачей в аренду недвижимости. Недвижимость может находиться в разных городах  и быть разного типа (склад, магазин  и т.п.). Клиентом, арендующим недвижимость, может быть как физическое, так и юридическое лицо. Оплата аренды осуществляется через банк. Оплата может производиться за разные промежутки времени (ежемесячно, ежеквартально и т.п.). Отдел аренды заключает с клиентом договор, который содержит информацию о клиенте, типе арендуемой недвижимости, сроках аренды, банке, через который производится оплата. За каждым договором закреплен сотрудник, который заключил договор и курирует его.

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

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

 

Концептуальная  схема

 

При проектировании базы данных во многих случаях целесообразно  использовать метод «сущность-связь», затем от полученной ER-диаграммы  переходят к отношениям. Основными понятиями метода «сущность-связь» являются следующие:

  • Сущность
  • Атрибут сущности
  • Ключ сущности
  • Связь между сущностями
  • Степень связи

Сущность –  объект, информация о котором хранится в БД. Отдельные объекты, которые входят в данный тип, называют экземплярами объекта.

Атрибут является свойством сущности.

Ключ сущности – атрибут или набор атрибутов, используемых для идентификации  сущности.

Связь между  сущностями предлагает зависимость  между атрибутами этих сущностей. Свойства связей:

    1. Мощность связи – максимальное количество экземпляров одного объекта, связанных с одним экземпляром другого объекта.
    2. Показатель кардинальности – показатель, используемый для того. Чтобы показать количество возможных связей для каждого экземпляра.

Для бинарных связей существует три вида показателя кардинальности: 1:1 (один к одному), 1:M (один ко многим), M:M (многие ко многим).

    1. Степень участия: полная, если каждый экземпляр некоторого объекта обязательно должен участвовать в связи. Частичная, если для объекта допустимо неучастие некоторых экземпляров связи.

На основании  описания предметной области можно  выделить следующие объекты и  назначить им свойства (табл. 1).

Таблица 1. «Объекты и атрибуты»

Объект

Атрибут

Ключ

Страна

Код

название

Код страны

Регион

Код

название

Код региона

Город

Код

название

Код города

Улица

Код

название

Код улицы

Адрес

Код

Здание (номер)

Улица

Код страны

Код региона

Код города

Код адреса

Тип недвижимости

Код типа

название

Код типа недвижимости

Недвижимость

Код

Площадь

Этаж

Описание

Код адреса

Код типа недвижимости

Код недвижимости

Банк

Код

Название

Телефон

Код адреса

Код банка

Клиент

Код

ФИО

Название организации

ИНН

Телефон

Флаг что  физ. или юр. лицо

Код адреса

Код клиента

Сотрудник

Код ФИО

ФИО

телефон

Код сотрудника

Тип оплаты

Код

Название

Интервал оплаты

Код типа оплаты

Контракт

Код

Начальная дата

Конечная дата

Цена

Код типа оплаты

Код сотрудника

Код клиента

Код банка

Код типа недвижимости

Код контракта


 

Описание предметной области позволяет определить связи:

Таблица 2. «Связи»

Связь

Объекты

Показатель  кардинальности

Степень участия

Содержит

Страна

Адрес

1:М

Полная

Полная

Содержит

Регион

Адрес

1:М

Частичная

Полная

Содержит

Город

Адрес

1:М

Полная

Полная

Содержит

Улица

Адрес

1:М

Полная

Полная

Имеет

Адрес

Клиент

1:М

Полная

Полная

Имеет

Адрес

Банк

1:М

Полная

Полная

Имеет

Адрес

Недвижимость

1:М

Полная

Полная

Содержит

Тип недвижимости

Недвижимость

1:М

Полная

Полная

Содержит

Тип оплаты

Контракт

1:М

Полная

Полная

Заключает

Клиент

Контракт

1:М

Полная

Полная

Участвует в  оплате

Банк

Контракт

1:М

Полная

Полная

Арендуется

Недвижимость

Контракт

1:М

Полная

Полная

Курирует

Сотрудник

Контракт

1:М

Полная

Полная


 

С целью повышения наглядности  и удобства проектирования для представления  сущностей и связей между ними построим концептуальную схему. Концептуальная схема предметной области приведена на рисунке 1.

 

 

Рис. 1 – Концептуальная схема

 

Логическое  проектирование

 

 

 

Структура базы данных

 

Структура базы данных для предметной области представлена на рисунке 2. На рисунке отображены таблицы базы данных с атрибутами и связи между ними.

 

 

Рис. 2 – Структура  базы данных

 

Создание  базы данных

 

Для начала работы необходимо запустить SQL Server Management Studio Express из главного меню (рисунок 3).

 

Рис. 3 - запуск SQL Server Management Studio

 

Входим в приложение, выбрав windows authentication.

 

 

Рис. 4 – Вход в SQL Server Management Studio

 

Рис. 5 – Создание базы данных

 

Для создания базы данных используется команда create database rental_department. Для ее выполнения нажимаем New Query и пишем команду создания базы в области запроса. Нажимаем Execute (рисунок 5). После выполнения команды в списке баз данных появилась созданная база данных (рисунок 6).

 

 

Рис. 6 – Созданная  база данных

 

Для работы с  созданной базой необходимо выбрать ее из списка (рисунок 7).

Рис. 7 – Выбор базы данных

 

После создания и выбора базы данных следует создать таблицы с помощью create table. SQL запросы создания таблиц этой базы данных выглядят следующим образом:

 

create table country(

country_id int identity(1,1) primary key,

country_name nvarchar(200) not null

);

 

create table region(

region_id int identity(1,1) primary key,

region_name nvarchar(200) not null

);

 

create table city(

city_id int identity(1,1) primary key,

city_name nvarchar(200) not null

);

 

create table street(

street_id int identity(1,1) primary key,

street_name nvarchar(200) not null

);

 

create table address(

address_id int identity(1,1) primary key,

building nvarchar(200) not null,

country_id int not null foreign key references country(country_id),

region_id int foreign key references region(region_id),

city_id int not null foreign key references city(city_id),

street int not null foreign key references street(street_id)

);

 

create table realty_type(

realty_type_id int identity(1,1) primary key,

realty_type_name nvarchar(200) not null

);

 

create table realty(

realty_id int identity(1,1) primary key,

square int not null,

floor int not null,

description nvarchar(2000) null,

address_id int not null foreign key references address(address_id),

realty_type_id int not null foreign key references realty_type(realty_type_id)

);

 

create table bank(

bank_id int identity(1,1) primary key,

bank_name nvarchar(200) not null,

bank_phone nvarchar(200) not null,

address_id int not null foreign key references address(address_id)

);

 

create table client(

client_id int identity(1,1) primary key,

client_fio nvarchar(200) null,

company nvarchar(200) null,

inn bigint not null,

client_phone nvarchar(200) not null,

individual int not null,

address_id int not null foreign key references address(address_id)

);

 

create table employee(

employee_id int identity(1,1) primary key,

employee_fio nvarchar(200) not null,

employee_phone nvarchar(200) not null

);

 

create table pay_type(

pay_type_id int identity(1,1) primary key,

pay_type_name nvarchar(200) not null,

interval int not null

);

 

create table contract(

contract_id int identity(1,1) primary key,

start_date date not null,

end_date date null,

price decimal(38,2) not null,

pay_type_id int not null foreign key references pay_type(pay_type_id),

client_id int not null foreign key references client(client_id),

bank_id int not null foreign key references bank(bank_id),

realty_id int not null foreign key references realty(realty_id),

employee_id int not null foreign key references employee(employee_id)

);

Ввод команд для создания таблиц показан на рисунке 8. После ввода команд нажимаем Execute.

 

 

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

 

После выполнения команд получаем следующие таблицы:

 

 

Рис. 9 – Созданные  таблицы

 

Заполнение  таблиц

 

Заполнение таблиц производим с помощью команды insert:

 

insert into country values ('Россия');

insert into country values ('Украина');

insert into country values ('Германия');

insert into country values ('Дания');

insert into country values ('Бельгия');

 

insert into region values ('Московская область');

insert into region values ('Самарская область');

insert into region values ('Ульяновская область');

 

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 – Просмотр клиентов

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