Проектирование БД риэлтерского агентства.

Кодпроцедуры:delimiter $$CREATE PROCEDURE MakeContractPaid(contractId int unsigned)BEGINUPDATE ContractSET isPaid = true WHERE id = contractId;END$$delimiter ;Процедура добавления агентаСинтаксиспроцедурыAddAgent(agentName, agentContacts, agentInterest, out agentId)ВходныеданныеagentName- полное имя нового создаваемого агента, тип данныхvarchar(255); agentContacts - информация для связи с новым создаваемым агентом, тип данных varchar(255); agentInterest - базовое вознаграждение агента от сумм оплаченной сделки, тип данных decimal(3, 2).Выходные данныеagentId - IDнового созданного агента, тип данных intunsignedПример вызовапроцедурыПример выполнения показан на рис. 12.Описание алгоритмаПроцедура добавляет (соездает) в базу данных нового агента.Кодпроцедуры:delimiter $$CREATE PROCEDURE AddAgent(agentName varchar(255), agentContacts varchar(255),agentInterest decimal(3, 2),out agentId int unsigned)BEGININSERT INTO Agent (name, contacts, interest, lastPayDate) VALUES (agentName, agentContacts,agentInterest, null); SET agentId = last_insert_id();END$$delimiter ;Процедура удаления агента по имениСинтаксиспроцедурыRemoveAgentByName(agentName)Входные данныеagentName- полное имя агента для поиска в таблице Agent, которого необходимо удалить из базы данных, тип данных varchar(255).Выходные данныеотсутствуютПример вызовапроцедурыПример выполнения показан на рис. 13.Описание алгоритмаПроцедура ищет и удаляет агента из базы данных по его полному имени.Кодпроцедуры:delimiter $$CREATE PROCEDURE RemoveAgentByName(agentName varchar(255))BEGINDELETE FROM Agent WHERE name = agentName;END$$delimiter ;Процедура создания встречиСинтаксиспроцедурыCreateMeeting(timestamp, agentName, clientName,propertyAddressTemplate, out meetingId)Входные данныеtimestamp- дата и время, когда должна быть проведена встреча, тип данных datetime; agentName - полное имя агента для поиска в таблице Agent, который будет проводить встречу (осуществлять показ объекта недвижимости), тип данных varchar(255); clientName - полное имя клиента, которому объект недвижимости будет демонстрироваться, тип данных varchar(255); propertyAddressTemplate - шаблон адреса недвижимости, шаблон заполняется по стандарту SQL, тип данных varchar(255).Выходные данныеmeetingId - IDновой созданной встречи, тип данных intunsigned.Пример вызовапроцедурыПример выполнения показан на рис. 14.Описание алгоритмаПроцедура переводит контракт в состояние "оплачено", инициируя таким образом возможность выплаты агенту его вознаграждения.Кодпроцедуры:delimiter $$CREATE PROCEDURE CreateMeeting(timestamp datetime,agentName varchar(255),clientName varchar(255),propertyAddressTemplate varchar(255),out meetingId int unsigned)BEGININSERT INTO Meeting (timestamp,isCompleted,agentId,clientId,propertyId) VALUES (timestamp,false,(SELECT id FROM Agent a WHERE a.name = agentName),(SELECT id FROM Client c WHERE c.name = clientName),getFirstPropertyIdByAddressTemplate(propertyAddressTemplate));SET meetingId = last_insert_id();END$$delimiter ;7.3. ТриггерыТриггер на изменение статуса оплаты контрактаНазвание:after_contract_update и after_contract_insert – предназначен для того, чтобы не оставалось незавершенных встреч для оплаченных контрактов.Алгоритм: Без этих триггеров нарушается целостность бизнес-данных. То есть логически, встреча не может быть запланированной (то есть незавершенной), если деньги по договору на недвижимость уже поступили, то есть сделка свершилась. Эти два триггера обеспечивают такую целостность. В момент изменения таблицы Contract, и в момент вставки новых записей (если вставляется уже оплаченный контракт). Если контракт оплачен, то соответствующая ему встреча принудительно завершается.Код триггера:delimiter $$CREATE TRIGGER after_contract_updateAFTER UPDATEON contract FOR EACH ROWBEGINUPDATE MeetingSET isCompleted = trueWHERE id = new.meetingId;END$$delimiter ; delimiter $$CREATE TRIGGER after_contract_insertAFTER INSERTON contract FOR EACH ROWBEGINUPDATE MeetingSET isCompleted = trueWHERE id = new.meetingId;END$$delimiter ;Триггер на удаление агентаНазвание:before_agent_delete – предназначен для удаления связанных данных из базы данных при удалении записи агента (каскадное удаление).Алгоритм: Записи в таблицеMeeting (встреча) зависят от существования записей в таблице Agent (агент). Поэтому при необходимости удаления агента из базы данных надо предварительно удалить связанные записи встреч. Так же записи в Contract (договоры) зависят от записей в таблице Meeting - для Meeting-Contract этот триггер проводит аналогичную для пары таблиц Agent-Meeting операцию.Кодтриггера:delimiter $$CREATE TRIGGER before_agent_deleteBEFORE DELETEON agent FOR EACH ROWBEGINSET SQL_SAFE_UPDATES = 0;DELETE FROM ContractWHERE meetingId IN (SELECT id FROM MeetingWHERE agentId = old.id);SET SQL_SAFE_UPDATES = 1;DELETE FROM Meeting WHERE agentId = old.id; END$$delimiter ;ЗАКЛЮЧЕНИЕВ рамках данной курсовой работы в соответствии с заданием было проведено моделирование данных на трех уровнях - концептуальном, логическом и физическом. Как результат, создана функционирующая под управлением системы управления базами данных (СУБД) mySqlбаза данных (БД) риэлторского агентства. Был изготовлен код, наполняющий БД риэлторского агентства тестовыми данными. Были разработаны необходимые для работы БД риэлторского агентства запросы, функции и процедуры SQL. Так же, для обеспечения целостности БД, были изготовлены триггеры. База данных имеет частичную функциональность, так как нет понимания, каким именно требованиям в обозначенной предметной области, риэлторское агентство, эта БД должна удовлетворять. Без таких ограничений, кажется, что такую БД можно создать и из тысячи таблиц, и все равно она не удовлетворит каким-то требованиям каких-то экспертов.Для демонстрации было создано 5 таблиц, 5 функций, 6 процедур, 2 триггера.В ходе выполнения были закреплены навыки работы в mySQLWorkbench и mySQLModels, а также навыки разработки реляционных БД, так жебыли выполнены все пункты требований пореализации БД.СПИСОК ИСПОЛЬЗОВАННЫХ ИСТОЧНИКОВWhatIsaDatabase? : Что такое база данных? // ОRACLE : официальный сайт / Oracle Corporation. – URL: https://www.oracle.com/database/what-is-database (датаобращения: 25.12.2023)Whatisdatamodeling? : Что такое моделирование данных? // IBM : официальный сайт / IBMCorporation. – URL: https://www.ibm.com/topics/data-modeling (датаобращения: 25.12.2023)EmilDrkušić. ARealEstateAgencyDataModel : Модель данных риэлторсого агентства : блог архитектора баз данных // VertabeloSA: официальныйсайт. – URL: https://vertabelo.com/blog/managing-houses-and-properties-a-real-estate-agency-data-model (датаобращения: 26.12.2023)MatthewGroves. WhatisDataModeling? Conceptual, Physical, Logical : Что такое моделирование данных? Концептуальное, физическое, логическое : блог сотрудника Couchbase,автора нескольких книг по проектированию и разработке ПО // Couchbase, Inc: официальныйсайт. – URL: https://www.couchbase.com/blog/conceptual-physical-logical-data-models (датаобращения: 26.12.2023)FredCoulson. DatabaseNormalizationTutorial : Практикум по нормализации базы данных:личный сайт архитектора баз данных. – URL: https://www.phlonx.com/resources/nf3 (датаобращения: 26.12.2023)Стружкин, Н. П. Базы данных: проектирование. Практикум: учебноепособие для вузов / Н. П. Стружкин, В. В. Годин. – Москва: ИздательствоЮрайт, 2020. – 291 с. – Текст: электронный // ЭБС Юрайт : сайт. – URL:https://urait.ru/bcode/451246 (дата обращения: 20.12.2023).Ульман, Л. MySQL / Л. Ульман. – Москва: ДМК Пресс, 2008. – 352 с. – ISBN 5-94074-229-7. – Текст: электронный // Литрес : электронно-библиотечная система. – URL: https://www.litres.ru/book/larri-ulman/mysql-rukovodstvo-po-izucheniu-yazyka-434245 (дата обращения: 19.12.2023). — Режим доступа: по подписке.Мартишин, С. А. Проектирование и реализация баз данных в СУБД MySQL с использованием MySQL Workbench: учеб. пособие / С.А. Мартишин, В.Л. Симонов, М.В. Храпченко.– М.: ИД «ФОРУМ»: ИНФРА-М, 2023. – 160 с. – Текст: электронный. – URL: https://znanium.ru/catalog/document?id=424789 (дата обращения: 20.12.2023). – Режим доступа: по подписке.Мартишин, С. А. Базы данных. Практическое применение СУБД SQL- и NoSQL-типа для проектирования информационных систем: учебное пособие / С.А. Мартишин, В.Л. Симонов, М.В. Храпченко. – Москва:ФОРУМ: ИНФРА-М, 2024. – 368 с. – Текст: электронный. – URL: https://znanium.ru/catalog/document?id=434322 (дата обращения: 21.12.2023). – Режимдоступа: по подписке.MySQL 8.0 Reference Manual // MySQL : сайт. – URL: https://dev.mysql.com/doc/refman/8.0/en (датаобращения: 24.12.2023).Data Types. // MySQL : сайт. – URL: https://dev.mysql.com/doc/refman/8.0/en/data-types.html (датаобращения: 24.12.2023).Стружкин, Н. П. Базы данных: проектирование: учебник для вузов / Н. П. Стружкин, В. В. Годин. – Москва: Издательство Юрайт, 2020. – 477 с. – Текст: электронный // ЭБС Юрайт : сайт. – URL: https://urait.ru/book/bazy-dannyh-proektirovanie-511019 (дата обращения: 19.12.2023). – Режимдоступа: по подписке.Осипов, Д. Л. Технологии проектирования баз данных / Д. Л. Осипов. – Москва: ДМК Пресс, 2019. – 498 с. – ISBN 978-5-97060-737-4. – Текст: электронный // Литрес: электронно-библиотечная система. – URL: https://www.litres.ru/book/dmitriy-osipov/tehnologii-proektirovaniya-baz-dannyh-45707039 (дата обращения: 22.12.2023). – Режим доступа: по подписке.Руководство по проектированию реляционных баз данных // Хабр : сайт. – URL: https://habr.com/ru/post/194714 (дата обращения: 19.12.2023).MySQL 8 для больших данных / Ш. Чаллавала, Д. Лакхатария, Ч. Мехта, К. Патель ; перевод с английского А. В. Логунова. – Москва: ДМК Пресс, 2018. – 226 с. – ISBN 978-5-97060-653-7. – Текст: электронный // Литрес: электронно-библиотечная система. – URL: https://www.litres.ru/book/dzhadip-lakhatariya/mysql-8-dlya-bolshih-dannyh-48411119 (дата обращения: 21.12.2023). — Режим доступа: по подписке.