Deck 9: Distributed DBMSs Concepts and Design

Question 1

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.

Answer 1

(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')}

Question 2

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.

Answer 2

(a)
(b)
Possible solution
Don't fragment Speciality/Region - replicate relations at all sites - only contain a small number of records.

Question 3

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.

Answer 3

(a)
(b)
Possible solution
Area /ItemType/Item Relations
No fragmentation.
Assumption - The Area/ItemType/Item relations will be used for reference purposes and will not be subjected to frequent updates. Although some of the areas require access to only part of the Items table, there are future plans to offer all of the item types in all of the areas.
Office/Client/ClientOrder/OrderDetail Relations

Question 4

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.

Question 5

Case Study 5 - Rapid Roads
A haulage company called Rapid Roads specializes in the transportation of loads throughout the UK and Europe. Rapid Roads has many offices throughout the UK and Europe to process customer orders and has decided to distribute its operations according to these countries. The company also proposes to distribute staff details to the appropriate countries, however staff payroll details will be processed by the Head Office of Rapid Roads, which is located in the UK.
The offices of Rapid Roads are grouped into countries as follows:
Country 1 (C1): UK $\quad$ Country 4 (C4): Switzerland
Country 2 (C2): France $\quad$ Country 5 (C5): Spain
Country 3 (C3): Germany $\quad$ Country 6 (C6): Italy
-(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 6

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.

Question 7

Discuss the advantages and disadvantages of fragmentation.

Question 8

(a) A DDBMS may be classified as homogeneous or heterogeneous. Compare and contrast these two types of distributed systems.(b) Discuss the extended capabilities or services that a DDBMS must provide over a centralized DBMS.

Question 9

Consider the following simplified relational schema for InstantBuy:
OrderDetail(orderNo, itemType) $\quad$ 10,000 records stored in London
Client(clientNo, cCity) $\quad$ $\quad$ $\quad$ $\quad$ 1,000 records stored in Glasgow
ClientOrder(clientNo, orderNo) $\quad$ 100,000 records stored in London
To list the clients in Edinburgh who have ordered items of type 'TV3190', we can use the SQL query:
SELECT C.clientNo
FROM Client C, OrderDetail OD, ClientOrder CO
WHERE C.clientNo = CO.clientNo AND CO.orderNo = OD.orderNo AND
cCity = 'Edinburgh' AND itemType = 'TV3190';
For simplicity, assume that each tuple in each relation is 10 characters long, there are 100 clients who have ordered item 'TV3190', there are 10 clients in Edinburgh and computation time is negligible compared to communication time. The communication system has a data transmission rate of 10,000 characters per second and a 1-second access delay to send a message from one site to another. For the following five possible strategies for this query, calculate the communication times, using the following algorithm:
Communication Time = $C_{0}$ +(no_of_bits_in_message/transmission_rate_per_bit)
where $C_{0}$ is the access delay.

State any assumptions necessary to support your calculations.

Question 10

Consider the following two relations:
Staff (staffNo, name, DOB, salary, deptNo)
Department (deptNo, deptName, managerStaffNo)
which are horizontally fragmented on the department number, deptNo. Assume there is an integrity constraint that requires that every member of staff earns less than every manager in the same department. Further assume that we wish to insert the tuple ('S9100', 'John Smith', '1-May-1960', 30000, 'D1') into the Staff relation. Under what conditions can this constraint be checked locally?