PART IV
Pet Horizontally derived from Surgery:
Owner: Horizontally derived from Pet:
Staff: Horizontally derived from Surgery + vertical fragmentation
Reconstructions:
Surgery: S1 S2 S3 S4 S5 S6
PART IV
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.
Client (clientNo, cName, cAddress, cTelNo, cFaxNo, officeNo)
Unit (unitRegNo, unitDescription, maxPayload, officeNo)
Trailer (trailerNo, trailerDescription, trailerLength, maxCarryingWt, officeNo)
where
Client contains the details of clients and the client number (clientNo) is the key. Clients are
registered with an office in their country and nearest to the location of their company.
Unit contains the details of the unit that pulls one or two trailers and the registration
The offices of Rapid Roads are grouped into countries as follows:
Country 1 (C1): UK Country 4 (C4): Switzerland
Country 2 (C2): France Country 5 (C5): Spain
Country 3 (C3): Germany Country 6 (C6): Italy
24.5 (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;
Possible solution
Country
Client/Unit/Trailer
HasUnit
TransportReq
Unit
Contains
ClientOrder
Orders
Client
1..1
Handles
1..1
PART IV
ClientOrder COi = ClientOrder clientNo CIE I = 1..6
(ii) Reconstruction:
Office: O1 O2 O3 O4 O5 O6
PART IV
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.
Office (officeNo, oAddress, oTelNo, oFaxNo, mgrNIN, countryNo)
Staff (NIN, fName, lName, sAddress, sTelNo, sex, DOB, position, taxCode, salary,
officeNo)
Office contains details of each office and the office number (officeNo) is the key. Each
(mgrNIN).
PurchaseOrder contains details of the purchase orders for each job and the combination of job number
and a purchase order number (jobNo, poNo) form the key.
Item contains details of all materials that can be used in printing jobs and the item number
As well as accessing printing jobs based on the publisher, jobs can also be accessed on the job type
The offices of Perilous Printing are grouped into countries as follows:
24.6 (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;
Possible solution
Country
Publisher
Pi: officeNo=I(Publisher) I
minterm predicates: {officeNo=1, officeNo officeNo=50}
POItem
Staff
1..1 1..*
Office
Contains
Item
PurchaseOrder
Orders
Publisher
BookJob
Orders
PART IV
Staff
S1: NIN, sName, sAddress, sTelNo, officeNo(Staff)
Bookjob
Bi: Bookjob pubNo Pi I
PurchaseOrder
Reconstructions
Publisher: P1 P2 P50
22.7 Discuss the advantages and disadvantages of fragmentation.
Advantages
Locality of Reference. Data stored close to where it is used, if possible. If a fragment is used at a
number of sites, it may be advantageous to store copies of the fragment at these sites.
Disadvantages
Design. May be difficult to design and allocate fragments efficiently.
24.8 (a) A DDBMS may be classified as homogeneous or heterogeneous. Compare and contrast
these two types of distributed systems.
In a homogeneous system, all sites use the same DBMS package. In a heterogeneous system, sites
may run different DBMS packages. Not only may the packages be different, but the packages
(b) Discuss the extended capabilities or services that a DDBMS must provide over a
centralized DBMS.
extended communication services to provide access to remote sites and allow the transfer of
24.9 Consider the following simplified relational schema for InstantBuy:
OrderDetail(orderNo, itemType) 10,000 records stored in London
can use the SQL query:
PART IV
For simplicity, assume that each tuple in each relation is 10 characters long, there are 100
Strategy Description
1
Move the
Client
relation to London and process query there.
2
Move the
ClientOrder
and
OrderDetail
relations to Glasgow and process query there.
involving that
.
simplicity, assume that the projected result is still 10 characters long.
State any assumptions necessary to support your calculations.
Strategy 1: Time = 1 + (1,000 * 10 / 10,000) = 2 seconds
24.10 Consider the following two relations:
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
-May- Staff relation. Under what conditions can this constraint
be checked locally?
If a record has previously been inserted that satisfies the constraint, then that can be used as a basis
PART IV
Student Project
Assignment Distributed Database Analysis and Design
Objective
database design based on those requirements.
Approach
It is essential to appreciate the importance of the analysis and design phase of a project and to complete this
before any implementation is carried out. The lectures are intended to familiarize you with the theory of
distributed database analysis and design, including fragmentation and allocation of fragments to sites,
You can then put theory into practice by investigating the needs of an existing company. You are asked to
interview relevant staff to discover the way in which the present system operates (which may be manual or
a centralized database system) and to determine the data and application requirements. You are free to
identify possible improvements and solutions to problems with the current system.
Finally, you must produce a comprehensive report for the analysis and design of your proposed distributed
system and include:
Assessment
The assessment will be based on a written report and an oral justification of the design. Assessment will be
carried out on the following components of the work:
Analysis of company data and application requirements (20)