Разработка базы данных для менеджера по обслуживанию клиентов в агентстве недвижимости

Результат выполнения

Поиск по фамилии
Номер регистрации	Номер_паспорта	ФИО	Номер_телефона	Адрес	Тип варианта клиента	Номер_счета	Дата обращения
1	5847924	Бондарев А.С.	(884)-15-24-62	Белинская 7 кв 45	Физическое лицо	1233456	04.09.2012

 

16. Выборка сведений о видах недвижимости с указанием типа недвижимости и ее стоимости

Рисунок Д2 «Окно конструктора запроса выборка по группе недвижимости»

 

Результат выполнения

выборка по группе недвижимости
Вид недвижимости	Город	Район	Цена за 1 кв м	Стоимость
Частный дом	Пенза	Сосновка	6 000,00р.	1 200 000,00р.

 

17. Выборка клиентов, обратившихся с заданной недвижимостью в заданный интервал (за 2012 год)

Рисунок Д3 «Окно конструктора запроса Поиск по заданному интервалу»

Результат выполнения

поиск по заданному интервалу
ФИО	Дата обращения	Тип недвижимости
Бондарев А.С.	04.09.2012	Квартира
Бондарев С.Н.	04.09.2012	Квартира
Кочнев Е.А.	10.12.2012	Квартира

 

Расчетные запросы

18. Расчет стоимости разового обслуживания клиента (сумма заказа)

Рисунок Д4 «Окно конструктора запроса Расчет суммы заказа»

Рисунок Д5 «Окно построителя выражений для поля Стоимость»

Рисунок Д6 «Окно построителя выражений для поля Сумма заказа»

 Результат выполнения

Расчет суммы заказа
ФИО	Тип недвижимости	Стоимость	Тип услуги	Процент за услуги	Сумма заказа	Номер_сделки	Дата сделки
Бондарев А.С.	Квартира	160 000,00р.	Продажа	10	16000	101	28.09.2012
Бондарев С.Н.	Квартира	160 000,00р.	Продажа	10	16000	102	28.09.2012
Кочнев Е.А.	Квартира	160 000,00р.	Покупка	10	16000	103	28.09.2012

19. Расчет количества выполненных заявок клиентов за заданный период

Делается в 2 этапа.

1 этап.

Создается запрос на поиск совершенных сделок за заданный период

Рисунок Д7 «Окно конструктора запроса Выборка сделок по дате»

Результат выполнения

выборка сделок по дате
Номер_сделки	Дата сделки
101	28.09.2012
102	28.09.2012
103	28.09.2012

Этап 2.

20. Вычисление количества совершенных сделок. Источник данных предыдущий запрос

 

 

 

 

 

 

 

Рисунок Д8 «Окно и результат выполнения запроса Количество сделок»

 

21. Перекрестный запрос: сумма продаж по клиентам и периодам (месяц).

Выполняется в 2 этапа.

1 Этап

Источники: таблица Сделка и запрос Расчет суммы заказа

Рисунок Д9 «Окно конструктора запроса «Расчет суммы заказа»

2 Этап 

22. Создание запроса с помощью мастера

Результат выполнения

Расчет суммы заказа по периодам простой_перекрестный

ФИО

янв

фев

мар

апр

май

июн

июл

авг

сен

окт

ноя

дек

Бондарев А.С.

48000

32000

Бондарев С.Н.

48000

32000

Кочнев Е.А.

48000

32000

 

23. Запрос – диаграмма: изменение количества обслуженных клиентов по месяцам

Рисунок Д10 «Окно конструктора запроса Диаграмма по месяцам»

 

Результат выполнения

24.  Диаграмма: распределение  суммы продаж по клиентам за  заданный месяц

Рисунок Д11 «Окно конструктора запроса Распределение суммы продаж по месяцам»

 

Результат выполнения

 

 

Запросы манипулирования

25. Создание и пополнение архива клиентской базы

Рисунок Д12 «Окна создания таблицы и конструктора запроса на архивацию»

 

Результат выполнения

Клиенты_архив
Номер_ паспорта	ФИО	Номер_ телефона	Адрес	Тип варианта клиента	Номер_ счета	Дата обращения
5847924	Бондарев А.С.	(884)-15-24-62	Белинская 7 кв 45	Физическое лицо	1233456	04.09.2012
2564789	Бондарев С.Н.	(841)-56-87-92	Белинская 7 кв. 43	Физическое лицо	1234567	04.09.2012
564829	Борисов А.М.	(841)-57-95-23	Пенза ул Мира 27 кв.54	Физическое лицо	4578921	05.10.2012
2346789	Егоров А.А.	(841)-52-64-57	Каменка ул. Баумана 44 кв 56	Юридическое лицо	142467	16.10.2012
457823456	Веселова К.А.	(841)-15-64-21	г. Пенза ул. Ладожская 48 кв.89	Физическое лицо	465789	14.11.2012
5492312	Лапшин М.П.	(822)-54-62-15	г. Пенза ул. Проспект Победы 100 кв.69	Юридическое лицо	14589	16.11.2012
456213	Носков	(845)-25-46-12	г. Москва ул. Светлая 14 кв.56	Юридическое лицо	456782	16.11.2012
4523145	Скворцова И.В.	(841)-56-12-34	г. Москва ул. Энтузиастов 145 кв.41	Юридическое лицо	12456	04.12.2012
1245621	Плотников М.П.	(822)-12-46-12	г. Каменка ул. Дружбы 32, кв 78	Физическое лицо	12456	22.08.2012
456123	Плотникова Г.А.	(841)-25-45-61	г. Каменка, ул. Дружбы 45 кв. 68	Физическое лицо	456789	22.08.2012
25648621	Кочнев Е.А.	(841)-56-24-21	Г. Каменка, ул Дзержинского 53, кв 34	Юридическое лицо	12456	10.12.2012
12345626	Рысев А.М.	(841)-24-56-12	г. Пенза, ул Пушкина 36, кв.78	Физическое лицо	1245678	02.11.2012

 

26. Удаление данных о выполненных заказах за заданный период  (за 2012 год)

Рисунок Д13 «Окно конструктора запроса на удаление данных»

Результат выполнения

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

27. Групповое изменение цен за 1 кв метр в определенном городе для определенного типа недвижимости на заданный процент (на 5%)

Рисунок Д15 «Окна конструктора запроса и результата выполнения запроса на изменение»

 

1. Копирование результатов расчетных запросов в хранилище для анализаСоздадим новую БД Архив БД
2. Создадим запрос (Создание таблицы с указанием пути к файлу Архив БД и именем БД Старые заказы.

Рисунок Д16 «Окно создание архивной базы данных»

3. Возьмем ранее созданный запрос Расчет суммы заказа по периодам простой в качестве источника  данных

Рисунок Д17 «Окна запроса и результата выполнения запроса архивации»

Старые заказы
Дата сделки	ФИО	Сумма заказа
28.09.2012	Бондарев А.С.	16000
28.09.2012	Бондарев А.С.	16000
28.09.2012	Бондарев А.С.	16000
05.12.2012	Бондарев А.С.	16000
12.12.2012	Бондарев А.С.	16000
28.09.2012	Бондарев С.Н.	16000
28.09.2012	Бондарев С.Н.	16000
28.09.2012	Бондарев С.Н.	16000
05.12.2012	Бондарев С.Н.	16000
12.12.2012	Бондарев С.Н.	16000
28.09.2012	Кочнев Е.А.	16000
28.09.2012	Кочнев Е.А.	16000
28.09.2012	Кочнев Е.А.	16000
05.12.2012	Кочнев Е.А.	16000
12.12.2012	Кочнев Е.А.	16000

 

Приложение Е 
Тексты запросов на языке SQL

 

1. Поиск всех данных о клиенте по фамилии

SELECT Клиенты.[Номер регистрации], Клиенты.Номер_паспорта, Клиенты.ФИО, Клиенты.Номер_телефона, Клиенты.Адрес, Клиенты.[Тип варианта  клиента], Клиенты.Номер_счета, Клиенты.[Дата  обращения]

FROM Клиенты

WHERE (((Клиенты.ФИО)=[введите  фамилию]));

 

2. Выборка сведений о видах недвижимости с указанием типа недвижимости и ее стоимости

SELECT [Вид недвижимости].[Вид недвижимости], Недвижимость.Город, Недвижимость.Район, Недвижимость.[Цена за 1 кв м], [Недвижимость]![Цена  за 1 кв м]*[Недвижимость]![Количесво  кв м] AS Стоимость

FROM [Вид недвижимости] INNER JOIN Недвижимость ON [Вид недвижимости].[Вид недвижимости] = Недвижимость.[Тип недвижимости]

WHERE ((([Вид недвижимости].[Вид недвижимости])=[Введите  тип недвижимости]) AND ((Недвижимость.Город)=[Введите  город]) AND (([Недвижимость]![Цена за 1 кв м]*[Недвижимость]![Количесво кв  м]) Is Not Null));

 

3. Выборка клиентов, обратившихся с заданной недвижимостью в заданный интервал (за 2012 год)

SELECT Клиенты.ФИО, Клиенты.[Дата обращения], Недвижимость.[Тип  недвижимости]

FROM Клиенты INNER JOIN Недвижимость ON Клиенты.[Номер регистрации] = Недвижимость.[Номер регистрации]

WHERE (((Клиенты.[Дата обращения])>=#1/1/2012# And (Клиенты.[Дата обращения])<=#12/31/2012#) AND ((Недвижимость.[Тип недвижимости])=[Введите  тип недвижимости]));

 

4. Расчет стоимости разового обслуживания клиента (сумма заказа)

SELECT Клиенты.ФИО, Недвижимость.[Тип недвижимости], [Недвижимость]![Количесво  кв м]*[Недвижимость]![Цена за 1 кв  м] AS Стоимость, Услуги.[Тип услуги], Услуги.[Процент за услуги], [Цена  за 1 кв м]*[Количесво кв м]*[Услуги]![Процент  за услуги]/100 AS [Сумма заказа], Сделка.Номер_сделки, Сделка.[Дата сделки]

FROM Услуги INNER JOIN ((Клиенты INNER JOIN Недвижимость ON Клиенты.[Номер  регистрации] = Недвижимость.[Номер  регистрации]) INNER JOIN Сделка ON (Недвижимость.[Регистрационный  номер] = Сделка.[Регистрационный номер]) AND (Клиенты.[Номер регистрации] = Сделка.[Номер  регистрации])) ON Услуги.[Тип услуги] = Сделка.[Тип услиги]

WHERE (((Недвижимость.[Цена  за 1 кв м]) Is Not Null))

ORDER BY Клиенты.ФИО;

 

5. Поиск совершенных сделок за заданный период

SELECT Сделка.Номер_сделки, Сделка.[Дата сделки]

FROM Сделка

WHERE (((Сделка.[Дата сделки]) Between [введите  начальную дату] And [Введите конечную  дату]));