Case Study 6 - Perilous Printing
Perilous Printing Is a Large

Case Study 6 - Perilous Printing
Perilous Printing is a large printing company that does work for book publishers throughout Europe. The company currently has over 50 offices, most of which operate autonomously, apart from salaries, which are paid by the head office in each country. To improve the sharing and communication of data, the company has decided to implement a Distributed DBMS. Perilous Printing jobs consist of printing books or part of books. A printing job requires the use of materials, such as paper and ink, which are assigned to a job via purchase orders. Each printing job may have several purchase orders assigned to it. Likewise, each purchase order may contain several purchase order items.
As well as accessing printing jobs based on the publisher, jobs can also be accessed on the job type (jobType), which can be: 1 - Normal; 2 - Rush.
The offices of Perilous Printing are grouped into countries as follows:
Country 1: UK $\quad$ Country 2: France $\quad$ Country 3: Germany
Country 4: Italy $\quad$ Country 5: Spain
-(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.