Case Study 4 - Complete Pet Care

Essay

Case Study 4 - Complete Pet Care
A company called Complete Pet Care provides private health-care for domestic pets throughout the UK. Complete Pet Care, which currently has over one hundred surgeries and opens a new surgery almost every month, has decided to distribute its operations according to areas of the country (i.e. Scotland, North England, South East England, South West England, Wales and Northern Ireland). The company proposes to distribute staff details to the appropriate areas, however staff payroll details will be processed by the Head Office of Complete Pet Care, which is located in the Scotland.
All primary key fields are physically implemented as integers.
-(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.