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.

Question

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.

Quizplus · Accepted Answer

The Answer is (a)

(a) (b) Possible solution Don't fragment PropertyType/Region - replicate relations at all sites - only contain a small number of records. Agency Use primary horizontal fragmentation for Agency with minterm predicates: {regionNo = 1 ('North') and propertyTypeNo = 1 ('Domestic'), regionNo = 1 ('North') and propertyTypeNo = 2 ('Industrial'), regionNo = 2 ('South') and propertyTypeNo = 1 ('Domestic'), regionNo = 3 ('East') and propertyTypeNo = 1 ('Domestic'), regionNo = 3 ('East') and propertyTypeNo = 2 ('Industrial'), regionNo = 4 ('West') and propertyTypeNo = 3 ('Letting')}

(b)
Possible solution
Don't fragment PropertyType/Region - replicate relations at all sites - only contain a small number of records.
Agency
Use primary horizontal fragmentation for Agency with minterm predicates:
{regionNo = 1 ('North') and propertyTypeNo = 1 ('Domestic'),
regionNo = 1 ('North') and propertyTypeNo = 2 ('Industrial'),
regionNo = 2 ('South') and propertyTypeNo = 1 ('Domestic'),
regionNo = 3 ('East') and propertyTypeNo = 1 ('Domestic'),
regionNo = 3 ('East') and propertyTypeNo = 2 ('Industrial'),
regionNo = 4 ('West') and propertyTypeNo = 3 ('Letting')}