Database Storage & Design Chapter 24 Homework Tasks And Time Use Derived Fragmentation For

PART IV

Part 6 Distributed DBMSs and Replication

Chapter 24 Distributed DBMSs Concepts and Design

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:

Employee (NIN, fName, lName, address, DOB, sex, salary, taxCode, agencyNo)

Agency (agencyNo, agencyAddress, managerNIN, propertyTypeNo,

regionNo)

Property (propertyNo, propertyTypeNo, propertyAddress, ownerNo,

where Employee contains employee details and the national insurance number NIN is the key.

Agency contains agency details and agencyNo is the key. managerNIN identifies

the employee who is the manager of the agency. There is only one manager

for each agency; an agency only handles one type of property.

Property contains details of the properties the company is dealing with and the key is

propertyNo. The agency that deals with the property is given by

agencyNo, and the contact in the estate agents by contactNIN; owner is

Agencies are grouped regionally as follows:

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).

24.1 (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.

Possible solution

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 propertyTypeNo

regionNo propertyTypeNo

TypeFor

1..*

1..1

Employee

NIN

Handles

1..1

1..*

1..1

PART IV

A1: regionNo = 1 and propertyTypeNo = 1 (Agency)

A2: regionNo = 1 and propertyTypeNo = 2 (Agency)

Employee

Use vertical fragmentation for Employee:

Property and Owner

Use derived fragmentation for Property and Owner:

PART IV

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:

Client (clientNo, fName, lName, address, regionNo, telNo)

Project (projectNo, clientNo, projectStart, projectEnd, managerNIN)

Speciality (skillID, skillDescription, costRate)

where Client contains client details and the client number clientNo is the key.

Project contains project details and the project number projectNo is the key.

Speciality

Programming, SSADM) and the skill identifier skillID is the key.

Consultant contains consultant details and the national insurance number NIN is the key.

Consultants are grouped regionally as follows:

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

24.2 (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.

Possible solution

Consultant

Use vertical fragmentation for Consultant first:

Time

1..1

PART IV

CA1 regionNo = 1 (CA) (North)

Client

Use primary horizontal fragmentation for Client with minterm predicates:

{regionNo = 1, regionNo = 2, regionNo = 3, regionNo = 4}

Cl1 regionNo = 1 (Client) (North)

Tasks and Time

Use derived fragmentation for Task and Time:

Ti: Task consultantNIN=NIN CAi 1 i 4

Project

Use derived fragmentation for Project:

PART IV

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).

Client (clientNo, cName, cAddress, cTelNo, cFaxNo, Sex, DOB, officeNo)

Item (itemNo, itemDescription, itemSize, itemColor, itemPrice, itemTypeNo)

where:

Client contains the details of clients and the client number (clientNo) is the key. Clients are

registered with the office nearest to their home.

Item contains the details of items and the item number (itemNo) is the key.

ItemType contains the description of types of items and item type (itemType) is the key.

InstantBuy offices are grouped into areas of the UK and Eire as follows:

InstantBuy provides various types of items that fall into the following categories:

24.3 (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.

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

Predicates

{areaNo = 1, areaNo = 2, areaNo = 3, areaNo =4}

OrderDetail

Contains

ClientOrder

0..1

Orders

Client

1..*

1..1

PART IV

Staff Mixed

S1: staffNo, sName, sAddress, sTelNo, sex, officeNo(Staff)

Reconstruction

Office = O1 O2 O3 O4

PART IV

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.

Owner (ownerNo, oName, oAddress, oTelNo)

Pet (petNo, petName, petDescription, petSex, petDOB, dateRegistered,

where

Owner contains details of owners and the owner number (ownerNo) is the key. Owners are

registered with the nearest surgery in their area. Owners may own several pets.

Pet contains details of pets and the pet number (petNo) is the key. The owner is given by

the owner number (ownerNo) and the surgery by the surgery number (surgeryNo).

24.4 (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;

Possible solution

Area

Owns

Staff

Pet

petNo

1..1 1..*

Owner

ownerNo

Prescribes

Prescription

UsedIn

Medication

Manages

Has