Case Study 1 - Real Estate Agency

Essay

Case Study 1 - Real Estate Agency
A large real estate agency has decided to distribute its project management information at the regional level. A part of the current centralized relational schema is as follows:

Agencies are grouped regionally as follows:
Region 1: North; Region 2: South; Region 3: East; Region 4: West

Information is required by property type, which covers: Domestic, Industrial, and Letting. There are no Industrial properties in the South and all Letting properties are in the West of Scotland. Properties are handled by the local estate agents office. As well as distributing the data on a regional basis, there is an additional requirement to access the employee data either by personal information (by Personnel) or by salary-related information (by Payroll).
-(a) Draw an Entity-Relationship Diagram for the above case study.
(b) Using this diagram from (a) above, produce a distributed database design for the system that satisfies the correctness rules for fragmentation and include:
(i) a suitable fragmentation schema for the system;
(ii) in the case of primary horizontal fragmentation, give a minimal set of predicates;
(iii) the reconstruction of global relations from fragments.
Give a full explanation of the reasoning behind each step and state any assumptions necessary to support your design.