Case Study 2 - Quack Consulting

Case Study 2 - Quack Consulting
Quack Consulting is a computer consulting firm that specializes in developing and installing PC-based hardware/software systems. Quack Consulting has decided to distribute its project management information at the regional level. A part of the current centralized relational schema is as follows:
Consultants are grouped regionally as follows:
Region 1: North; Region 2: South; Region 3: East; Region 4: West

In addition, clients are grouped into the same regions; projects are managed by the office closest to the client. 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.