Файл: Агентство недвижимости.docx

Федеральное государственное образовательное бюджетное учреждение

высшего профессионального образования

«Санкт-Петербургский государственный университет телекоммуникаций

им. проф. М.А. Бонч-Бруевича»

Кафедра Безопасности информационных систем

КУРСОВОЙ ПРОЕКТ

По дисциплине «Управление данными»

на тему:

«Агентство недвижимости»
Выполнил: студент группы ИСТ-113 Иешин И.А.

«___» ___________ 2022 г. _________/ Иешин И.А./

Принял: к.с.-х.н., преподаватель кафедры БИС Медведев С.А.

«___» __________ 2022 г. _________/Медведев С.А./

Санкт-Петербург

2022

Содержание

Введение 3

Техническое задание 4

Таблица атомарных объектов 5

Графическая нотация Чена 6

Нормализация 7

Запросы 8

Запросы к базе данных и их результаты 10

Заключение 12

Введение

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

Разработка базы данных предусматривает несколько этапов, в процессе прохождения которых информационная система будет совершенствоваться, это: описание предметной области базы данных, составление таблицы атомарных объектов и функциональных зависимостей, создание графической интерпретации предметной области в терминах сущностей и связей с помощью графической нотации Питера Чена, выделение будущих таблиц с названиями их столбцов, доказательство нахождения этих таблиц в 1, 2, 3 и Бойса-Кодда нормальных формах, создание запросов в нотации реляционной алгебры и реализация базы данных с помощью СУБД.

Техническое задание

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

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

В качестве владельца может выступать человек либо же застройщик. Если владелец человек, то информация о нем состоит из ФИО, номера телефона, серии и номера паспорта. Если же в качестве владельца выступает застройщик, то информация о нем состоит из имени компании и номера телефона компании.

Для связи с конкретным клиентом необходимо иметь информацию о ФИО и контактах клиента. Для совершения сделки помимо ФИО и контактов необходимо иметь паспортные данные.

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

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

Таблица атомарных объектов

Адрес
Наименование субъекта	-
Населенный пункт	Наименование субъекта
Название улицы	Населенный пункт
Номер дома	Название улицы
Дом
Номер дома	-
Наличие мусоропровода	Номер дома
Наличие лифта	Номер дома
Наличие детской площадки	Номер дома
Наличие автомобильной стоянки	Номер дома
Количество этажей	Номер дома
Квартира
Номер квартиры	-
Количество комнат	Номер квартиры
Ремонт	Номер квартиры
Наличие балкона	Номер квартиры
Площадь квартиры	Номер квартиры
Стоимость квартиры	Номер квартиры
Владелец
Частное лицо
Паспортные данные	-
Номер телефона	Паспортные данные
ФИО владельца	Паспортные данные
Застройщик
Лицензия компании	-
Название компании застройщика	Лицензия компании
Номер телефона компании застройщика	Лицензия компании
Клиент
Паспортные данные	-
Номер телефона	Паспортные данные
ФИО клиента	Паспортные данные
Сделка
Номер сделки	-
Сумма сделки	Номер сделки
Дата сделки	Номер сделки
Статус сделки	Номер сделки
Сотрудник агентства
Паспортные данные	-
ФИО сотрудника	Паспортные данные
Должность сотрудника	Паспортные данные
Номер телефона	Паспортные данные
Количество совершенных сделок	Паспортные данные

Графическая нотация Чена

Рисунок 1 – нотация Чена

Субъект РФ (ID, название субъекта РФ)
Населенный пункт (ID, название населенного пункта, ID субъекта РФ)
Улица (ID, название улицы, ID населенного пункта)
Дом (ID, номер дома, ID улицы, стоянка, детская площадка, ID кол-во этажей)
Кол-во этажей (ID, число этажей, наличие лифта, наличие мусоропровода)
Квартира (ID, номер квартиры, ID дома, кол-во комнат, ремонт, балкон, площадь, стоимость, ID владельца)
Владелец (ID, тип владельца)
Частное лицо (паспортные данные, телефон, ФИО)
Компания застройщика (лицензия, телефон, название компании)
Клиент (паспортные данные, ФИО, телефон)
Сделка (номер сделки, статус, сумма, дата, паспорт клиента, ID владельца, паспорт сотрудника, ID квартиры)
Сотрудник агентства (Паспортные данные, ФИО, должность, номер телефона, кол-во сделок)

Нормализация

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

Для того чтобы таблицы были во 2-ой нормальной форме необходима принадлежность к 1-ой нормальной форме и полная функциональная зависимость всех не ключевых атрибутов от каждого столбца первичного ключа. Все таблицы, в которых первичный ключ простой автоматически принадлежат 2-ой нормальной форме. В таблице “Дом” ключ состоит из атрибутов Номер дома и ID улицы. Все не ключевые атрибуты находятся в полной функциональной зависимости от этого ключа, так как, к примеру наличие стоянки нельзя определить, зная только ID улицы. Также наличие стоянки невозможно определить только по Номеру дома, потому что дом обязан находится на какой-либо улице. Аналогично с таблицами “Субъект РФ”, “Населенный пункт”, “Улица”, “Квартира” и “Кол-во этажей”.

Третья нормальная форма определяется следующим образом: таблица во 2-ой нормальной форме и отсутствуют транзитивные зависимости не ключевых атрибутов. Так, например, в таблице “Дом” наличие лифта и мусоропровода зависит от количества этажей (никто не станет делать лифт или мусоропровод в одноэтажном доме), поэтому они вынесены в отдельную таблицу.

Для принадлежности таблиц к нормальной форме Бойса-Кодда необходимо: принадлежность к 3-ей форме и ключевые атрибуты составного ключа не должны зависеть от не ключевых атрибутов. Это означает что все таблицы, в которых ключ простой автоматически принадлежат нормальной форме Бойса-Кодда. Все остальные таблицы также принадлежат данной нормальной форме. К примеру, в таблице “Квартира” Номер квартиры или ID дома никак не зависят от кол-ва комнат, ремонта и т.д.

Запросы

Какой адрес дома?

π номер дома, название улицы, название населенного пункта, название субъекта РФ (σ ID дома = 1 (Дом ⋈ Улица ⋈ Населенный пункт ⋈ Субъект РФ))

Есть ли лифт в доме?

π наличие лифта (σ номер дома = 78 и ID улицы = 4 (Дом ⋈ Количество Этажей))

Узнать ФИО сотрудника, который проводил сделку

π ФИО сотрудника (σ Номер сделки = 1 (Сотрудник ⋈ Сделка))

Сколько комнат в квартире?

π Кол-во комнат (σ ID квартиры = 4 (Квартира))

Сколько у владельца квартир?

π Кол-во квартир (σ ID владельца = 3 (Квартира))

Узнать информацию о компании застройщика.

π Лицензия, название, телефон (σ ID владельца = 2 (Компания застройщика))

Узнать информацию о клиенте и дату совершения сделки по номеру сделки.

π Паспорт, ФИО, Телефон, Дата сделки (σ Номер сделки = 1 (Сделка ⋈ Клиент))

Скрипт создания базы данных и таблиц для неё:

DROP DATABASE IF exists agency;

CREATE DATABASE agency;
USE agency;
CREATE TABLE `Subject_rf` (

`ID` INT PRIMARY KEY,

`name` VARCHAR(50)

);
CREATE TABLE `Locality` (

`ID` INT PRIMARY KEY,

`name` VARCHAR(50),

`ID_subject_rf` INT,

FOREIGN KEY (`ID_subject_rf`)

REFERENCES Subject_rf (`ID`)

);

CREATE TABLE `Street` (

`ID` INT PRIMARY KEY,

`name` VARCHAR(50),

`ID_locality` INT,

FOREIGN KEY (`ID_locality`)

REFERENCES Locality (`ID`)

);
CREATE TABLE `Count_floors` (

`ID` INT PRIMARY KEY,

`count_floors` INT,

`lift` BOOL,

`garbage_chute` BOOL

);
CREATE TABLE `House` (

ID INT PRIMARY KEY,

`num_house` INT,

`ID_street` INT,

`parking` BOOL,

`playground` BOOL,

`ID_count_floors` INT,

FOREIGN KEY (`ID_street`)

REFERENCES Street (`ID`),

FOREIGN KEY (`ID_count_floors`)

REFERENCES Count_floors (`ID`)

);
CREATE TABLE `Owner` (

`ID` INT PRIMARY KEY,

`type_owner` ENUM('pp', 'dc')

);
CREATE TABLE `Private_person` (

`ID` INT,

`passport` VARCHAR(20) PRIMARY KEY,

`fio` VARCHAR(100),

`phone` VARCHAR(20),

FOREIGN KEY(`ID`)

REFERENCES Owner(`ID`)

);
CREATE TABLE `Developer_company` (

`ID` INT,

`license` VARCHAR(50) PRIMARY KEY,

`name` VARCHAR(30),

`phone` VARCHAR(20),

FOREIGN KEY(`ID`)

REFERENCES Owner(`ID`)

);

CREATE TABLE `Apartament` (

`ID` INT PRIMARY KEY AUTO_INCREMENT,

`num_apartament` INT,

`ID_house` INT,

`count_rooms` INT,

`repair` BOOL,

`balcony` BOOL,

`square` FLOAT,

`price` INT,

`ID_owner` INT,

FOREIGN KEY (`ID_house`)

REFERENCES House (`ID`),

FOREIGN KEY (`ID_owner`)

REFERENCES Owner (`ID`)

);
CREATE TABLE `Client` (

`passport` VARCHAR(20) PRIMARY KEY,

`fio` VARCHAR(100),

`phone` VARCHAR(20)

);
CREATE TABLE `Employee` (

`passport` VARCHAR(20) PRIMARY KEY,

`fio` VARCHAR(100),

`phone` VARCHAR(20),

`count_deal` INT,

`job_title` VARCHAR(50)

);
CREATE TABLE `Deal` (

`number_deal` INT PRIMARY KEY auto_increment,

`status` ENUM('completed', 'abort'),

`ID_apartament` INT,

`price` INT,

`data` DATE,

`passport_client` VARCHAR(20),

`ID_owner` INT,

`passport_employee` VARCHAR(20),

FOREIGN KEY (`ID_owner`)

REFERENCES Apartament (`ID_owner`),

FOREIGN KEY (`passport_client`)

REFERENCES Client (`passport`),

FOREIGN KEY (`passport_employee`)

REFERENCES Employee (`passport`),

FOREIGN KEY (`ID_apartament`)

REFERENCES Apartament(`ID`)

);