Определим основные типы сущностей исходя из описания предметной области.
Отделение (BRANCH)
Каждое отделение имеет следующий набор атрибутов: номер отделения, адрес (почтовый индекс, город, улица, дом), номер телефона и номер факса.
Сотрудник (STAFF)
Каждый сотрудник характеризуется следующими атрибутами: номер сотрудника, фамилия, имя сотрудника, дата рождения, пол, адрес (город, улица, дом, квартира), номер домашнего телефона, дата зачисления в штат, должность и зарплата.
Объект недвижимости для продажи (PROPERTY_FOR_SALE)
Характеризуется такими атрибутами как: номер объекта недвижимости, дата регистрации, полный адрес (почтовый индекс, город, улица, дом и квартира), тип объекта (N-этажный панельный или кирпичный), этаж, количество комнат, площадь (общая, жилая, площадь кухни), балкон (балкон, лоджия, застеклён, их количество или отсутствие), наличие телефона (есть, нет) и отпускная цена.
Владелец (PRIVATE_OWNER)
Владелец имеет атрибуты: номер владельца, фамилия, имя, адрес и номер телефона.
Покупатель (BUYER)
Каждый покупатель характеризуется следующими атрибутами: код покупателя, фамилия и имя, адрес (город, улица, дом, квартира) номер телефона (рабочий и домашний), предпочтительное количество комнат, максимальная цена.
Определим типы связей, которые существуют между основными сущностями.
Между сущностями Отделение и Сотрудник существует связь 1:М, обязательная только с одной стороны. Связь обязательна только с одной стороны, т.к. каждое из отделений компании имеет несколько штатных сотрудников, но не все сотрудники компании работают в отделениях. В обратном направлении, каждый из сотрудников отделений работает только в одном из них. Ключевой атрибут сущности Отделение: Номер отделения (Branch_no). Ключевой атрибут сущности Сотрудник: Номер сотрудника (Staff_no).
Из описания предметной области известно, что каждый объект недвижимости, выставленный на продажу, закрепляется за конкретным отделением компании. В каждом отделении компании есть сотрудник, отвечающий за работу с выставленными на продажу объектами недвижимости. Для отражения этой ситуации необходимо провести связь между сущностями Объект недвижимости для продажи и Отделение(ключевым атрибутом сущности Объект недвижимости для продажи является Номер объекта недвижимости (Property_no)). Для того чтобы узнать, какой объект недвижимости обслуживается каким сотрудником и, с другой стороны, какой сотрудник отвечает за данный объект, вводится дополнительная связь между сущностями Объект недвижимости для продажи и Сотрудник. Между сущностями Отделение и Объект недвижимости для продажиустановлена связь 1:М, обязательная с 2-х сторон. Между сущностями Сотрудник и Объект недвижимости для продажи – связь 1:М, необязательная с 2-х сторон, так как из всех сотрудников компании только торговые агенты занимаются продажей недвижимости и отвечают за работу с ними. В обратном направлении, объект может быть не связан ни с одним из сотрудников. Например, когда объект впервые регистрируется в компании.
Теперь необходимо отразить связь между сущностями Владелец и Объект недвижимости для продажи. Если рассмотреть эту связь с одной стороны, то можно заметить, что один владелец может владеть несколькими объектами недвижимости. С другой стороны, каждый объект принадлежит только одному владельцу. Следовательно, связь между сущностями - 1:М. Поскольку каждый владелец владеет, по крайней мере, одним объектом недвижимости, а каждый объект должен иметь одного владельца, связь является обязательной с обеих сторон.
Из описания предметной области известно, что потенциальный покупатель обращается в одно из отделений компании, в котором ему могут предложить осмотреть разные объекты недвижимости. Клиент, как правило, желает осмотреть один или несколько, предлагаемых ему объектов недвижимости. Сведения о таком просмотре включают дату осмотра объекта и комментарии потенциального покупателя (согласен он или нет купить данную квартиру и др.). Образуется необязательная с двух сторон связь М:М между сущностями Покупатель и Объект недвижимости для продажи. Отдельный клиент может осмотреть или осматривает несколько выставленных на продажу объектов (1:М), а каждый объект может быть осмотрен несколькими клиентами (1:М). Связь необязательна со стороны клиента из-за возможного отсутствия объекта, отвечающего его требованиям. С другой стороны, сведения о некоторых объектах просто регистрируется в компании, а осмотр их клиентами не производится.
Если клиент согласен купить некоторый объект, то он заключает с компанией договор на покупку. Сотрудник компании должен оформить это соглашение. Каждый объект может быть продан единственному клиенту, и каждый клиент может купить один или более объектов в одно и то же время. Образуется необязательная с двух сторон связь 1:М между сущностями Покупатель и Объект недвижимости для продажи. Но так как, всякий раз, при покупке клиент заключает договор с компанией, мы определим две связи. Связь 1:М между сущностями Покупатель и Договор на покупку, а также связь 1:М между сущностями Объект недвижимости для продажи и Договор на покупку. Связи обязательны со стороны сущности Договор на покупку. Ключевым атрибутом для сущности Покупатель является Код покупателя (Buyer_no), а для сущности Договор на покупку – атрибут Номер договора (Sale_no). Кроме этого сущность Договор на покупку (CONTRACT_ON_SALE) имеет атрибуты: название нотариальной конторы, дата заключения договора, стоимость услуг. Инфологическая модель данных приведена на рисунке 7.
Переход к реляционной модели
Преобразование ER-модели в реляционную схемуосуществляется в соответствии со следующими правилами:
1. каждая простая сущность превращается в отношение. Имена отношений могут отличаться от имен сущностей, так как могут быть ограничены требованиями конкретной СУБД;
2. каждый атрибут становится возможным столбцом с тем же именем, для каждого атрибута задается допустимый тип данных и обязательность или необязательность этого атрибута;
3. компоненты уникального идентификатора сущности превращаются в первичный ключ отношения;
4. в каждое отношение, соответствующее подчиненной сущности, добавляется набор атрибутов основной сущности, являющейся первичным ключом основной сущности. В отношении, соответствующем подчиненной сущности этот набор атрибутов становится внешним ключом.
5. Для связи М:М используется специальный механизм преобразований, который позволяет отразить множественные связи, неспецифичные для реляционной модели. Это делается введением дополнительного связующего отношения, которое связано с каждым исходным связью 1:М, атрибутами этого связующего отношения являются первичные ключи связываемых отношений. При этом каждый из атрибутов нового отношения является внешним ключом, а вместе они образуют первичный ключ новой связующей сущности.
В реляционной модели связи явным образом не отображаются, однако между отношениями поддерживаются иерархические связи (в каждой связи одно отношение выступает как основное, а другое как подчиненное). Это значит, что один кортеж основного отношения может быть связан с несколькими кортежами подчиненного отношения. Для поддержки этих связей оба отношения должны содержать наборы атрибутов, по которым они связаны. В основном отношении это первичный ключ отношения. В подчиненном отношении для моделирования связи должен присутствовать набор атрибутов, соответствующий первичному ключу основного отношения. Данный набор атрибутов в подчиненном отношении принято называть внешним ключом. Согласно правилу 4 перехода к реляционной модели (в каждое отношение, соответствующее подчиненной сущности, добавляется набор атрибутов основной сущности, являющейся ее первичным ключом), введем в дополнительное отношение Договор на покупку ключи отношений Покупатель и Объект недвижимости для продажи.
Для связи М:М между сущностями Покупательи Объект недвижимости для продажи введем дополнительное связующее отношение, которое связано с каждым исходным связью 1:М.. Атрибутами этого связующего отношения, помимо даты осмотра и комментарии, будут первичные ключи связываемых отношений, т.е. Property_no и Buyer_no. Для нового отношения они являются внешними ключами, а вместе они образуют первичный ключ новой связующей сущности Осмотр(VIEWING).
