Case Study 3 - InstantBuy

Case Study 3 - InstantBuy
A home shopping catalog company called InstantBuy specializes in the provision of clothing and household items for customers. InstantBuy has many offices throughout the UK and Eire to process customer orders and has decided to distribute its operations according to areas of the UK and Eire. There is also an additional requirement to distribute staff information according to area and furthermore to allow access to staff data either by personal information (by Personnel) or by-salary-related information (by Payroll).

InstantBuy offices are grouped into areas of the UK and Eire as follows:
Area 1: Scotland and Wales Area 3: North England
Area 2: Eire and Northern Ireland Area 4: South England
InstantBuy provides various types of items that fall into the following categories:
W: Womenswear
C: Childrenswear
M: Menswear
H: Household
InstantBuy only provides household items to customers in Scotland and Wales, and North England, and does not provide childrenswear items in Eire and Northern Ireland.
-(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.