We take furniture company of Try Star international .It has two branches one in Lahore and other in Karachi. Old system were centralized and we design a new system of database which is distributed i.e. the database of the Lahore campus is in Lahore and the database of karachi campus is in Karachi i.e a collection of multiple ,logically interrelated databases distributed over a computer network.

DDBMS:
A distributed database management system is defined as a software system that permits the management of the ddbs and makes the distribution transparent to the users.

Why we use distributed database?

In centralized system, all queries are handled by the main server and all loads are on the server but in distributed system, a separate server for each site exists. • In centralized system If centre goes down, everything is down. So you need to have offsite redundant servers. • In centralized system there is the single point of failure that can bring an entire company down in the event of a server crash but in distributed system if one server crashed down we get information from main server.

Distributed Database design:
The design of a distributed computer system involves making decisions on the placement of data and programs across the sites of the computer network .in the case of distributed DBMSs,the distribution of application invoves two things: • The distribution of the distributed DBMS softwares • the distribution of thr application of the programs that run on it
It has been suggested that the organization of the distributed systems can be investigated along three orthogonal dimensions: 1. Level of sharing 2. Behavior of access patterns 3. Level of knowledge on access pattern behavior

We find level of data sharing in our system and all the programs of our system are replicated at all the sites but data files are not. Accordingly user requests are handled at the sites where they originate and the necessary data files are moved around the network.

Behavior of access patterns: Along the second dimension of access patterns behavior it is possible to identify two alternatives. The access patterns of user request may be static so that they do not change over time and dynamic. It is obviously considerably easier to plan for and manage the static environments then would be the case for dynamic distributed systems.

Level of knowledge on access pattern behavior:The third dimension of classification is the level of knowledge about the access patterns behavior. The programmer have the complete information about the system where the access patterns can reasonably be predicted and do not deviate significantly.

[pic]

Design strategies:

The major strategies for designing distributed database are as follows
• Top down approach
• Bottom up approach

The strategy we adopt for designing our database is top down approach.

Top down design process:
A frame work for this process is shown in figure. This activity begins with a requirement analysis that defines the environment of that system and ends on the physical design which maps the local conceptual schemas to the physical storage devices available at the corresponding sites.and the result is in some form of feedback , which may result in backing up to one of the initial steps in the design.

[pic]

Relation in our databases:

In our database we have three relations namely Customer,Order,Product and Order-line

In customer relation customer id is the primary key and it s become a foreign key in Order relation. And product id which is the primary key of Product realation becomes a foreign key of the order-line relation So with foreign key the relationship creates between all the relations.

Customer(Customer-name, Customer-address, City, State, Postal-code)

|Customer-id |Customer-name |Customer-address |City |State |Postal-code |
|1 |Mehak |Johar town |Lahore |Pakistan |7890 |
|2 |Madiha |Iqbal town |Lahore |Pakistan |3250 |
|3 |Asma |Gulberg |Karachi |Pakistan |5436 |
|4 |Amna |Allard ave |Albany |USA |1567 |
|5 |Saira |Derby |London |UK |8745 |
|6 |Saima |Gulberg |Karachi |Pakistan |3278 |
|7 |Ayesha |Derby |London |UK |9876 |

Order(Order-id, Order-date, Customer-id)

|Order-id |Order-date |Customer-id |
|1001 |10/3/2006 |2 |
|1002 |3/6/2008 |4 |
|1003 |15/5/2009 |1 |
|1004 |10/12/2012 |6 |
|1005 |17/3/2012 |1 |
|1006 |18/9/2012 |5 |

Product(Product_id, Product_description, Product_finish, Price)

|Product_id |Product_description |Product_finish |Price |
|12 |End table |Cherry |175 |
|13 |Coffe table |Natural Ash |200 |
|16 |Writer desk |Cherry |670 |
|14 |Dining table |Natural Ash |790 |
|15 |Computer desk |Walnut |800 |

Orderline(Order-id ,Product_id,Ordered quantity)

|Order-id |Product_id |Ordered quantity |
|1001 |12 |4 |
|1002 |14 |3 |
|1002 |15 |2 |
|1003 |16 |7 |
|1004 |13 |4 |
|1005 |14 |5 |

Fragmentation:

Relations decomposed into smaller, disjunctive fragments. These fragments are distributed across nodes.

Goals
Exploiting processing power of multiple nodes by decomposing operations into sub-operations that can be processed in parallel.

Requirements
• All partitions should have equal sizes
• Partitions should support processing of operations

Two fundamental strategies:

• Horizontal fragmentation Horizontal fragmentation partition the relations along its tuples. tHus each fragment has the subset of the tuples of the relation, there are two versions of horizontal partition

• Primary • Derived
Primary horizontal fragmentation:
Primary horizontal fragmentation of a relation is performed using predicates that are defined on that relation.
Primary Horizontal fragmentation of relation Customer is given below
Customer1=σ State =”Pakistan”(CUSTOMER)
Customer2=σ State =”UK”(CUSTOMER)
Customer3=σ State =”USA”(CUSTOMER)

Customer1

|Customer-id |Customer-name |Customer-address |City |State |Postal-code |
|1 |Mehak |Johar town |Lahore |Pakistan |7890 |
|2 |Madiha |Iqbal town |Lahore |Pakistan |3250 |
|3 |Asma |Gulberg |Karachi |Pakistan |5436 |
|6 |Saima |Gulberg |Karachi |Pakistan |3278 |

CUSTOMER2

|Customer-id |Customer-name |Customer-address |City |State |Postal-code |
|5 |Saira |Derby |London |UK |8745 |
|7 |Ayesha |Derby |London |UK |9876 |

CUSTOMER3

|Customer-id |Customer-name |Customer-address |City |State |Postal-code |
|4 |Amna |Allard ave |Albany |USA |1567 |

The fragmentation of relation CUSTOMER .If the only application that access CUSTOMER want to access the tuples according to the location, the set is complete since each tuple of each fragment CUSTOMER,has the same probability of being accessed. If however there is asecond application which access only those CUSTOMER tuples where the country is England then Pr is not complete.So

Pr = { State=”UK”, State=”USA”, State=”PAKISTAN”}
The reason completeness is the desirable property is because fragments obtained according to a complete set of predicates are logically uniform since they all satisfy the minterm predicate.

The primary horizontal fragmentation of Product relation is given below:
PRODUCT1=σ PRICE=500(PRODUCT)

PRODUCT1

|Product_id |Product_description |Product_finish |Price |
|12 |End table |Cherry |175 |
|13 |Coffe table |Natural Ash |200 |

PRODUCT2

|Product_id |Product_description |Product_finish |Price |
|16 |Writer desk |Cherry |670 |
|14 |Dining table |Natural Ash |790 |
|15 |Computer desk |Walnut |800 |

Derived horizontal fragmentation:
Derived fragmentation is the portioning of a relation that results from predicates being defined on another relation. A derived horizontal fragmentation is defined on a member relation of a link according to a selection operation specified on its owner . It is important to remember two points. First, the link between the owner and the member relations is defined as an equi-join. Second, an equi-join can be implemented by means of semi-joins. This second point is especially important for our purposes, since we want to partition relation according to the fragmentation of its owner, but we also want the resulting fragment to be defined only on the attributes of the member relation.
Accordingly, given a link L where owner (L)=S and member(L)=R, the derived horizontal fragments of R defined as

[pic]

Where w is the maximum number of fragments that will be defined on R, and [pic] ,

Where Fi is the formula according to which the primary horizontal fragmentation is defined.

To carry out the derived horizontal fragmentation , three inputs are needed: the set of partiotions of the owner relation( PRODUCT ) , the member relation, and the set of semi-join predicates between the owner and the member( PRODUCT.PRODUCT-ID =ORDER.ORDER-ID ).

In this case, there is more than one possible derived horizontal fragmentations of R. The decision as to which candidate fragmentation to choose is based on two criteria:

• The fragmentation with better join characteristics

• The fragmentation used in more applications

OREDR_LINE1 = OREDR_LINE PRODUCT1
OREDR_LINE2 = OREDR_LINE PRODUCT2

Where

PRODUCT1 = σ PRICE ≤ 500

PRODUCT2 = σ PRICE > 500

PRODUCT1

|Order-id |Product_id |Ordered quantity |
|1001 |12 |4 |
|1004 |13 |4 |

PRODUCT2

|Order-id |Product_id |Ordered quantity |
|1002 |14 |3 |
|1002 |15 |2 |
|1003 |16 |7 |
|1005 |14 |5 |

Another example ofderived horizontal fragmentation is :

OREDR1 = OREDR CUSTOMER 1
OREDR2 = OREDR CUSTOMER 2
OREDR3 = OREDR CUSTOMER 3

Where

CUSTOMER 1=σ STATE=”PAKISTAN”( CUSTOMER)
CUSTOMER 2=σ STATE=”UK”( CUSTOMER)
CUSTOMER 3=σ STATE=”USA”( CUSTOMER)

ORDER 1

|Order-id |Order-date |Customer-id |
|1001 |10/3/2006 |2 |
|1003 |15/5/2009 |1 |
|1004 |10/12/2012 |6 |
|1005 |17/3/2012 |1 |

ORDER2

|Order-id |Order-date |Customer-id |
|1006 |18/9/2012 |5 |

ORDER3

|Order-id |Order-date |Customer-id |
|1002 |3/6/2008 |4 |

Correctness rules for fragmentation:

we mention a number of rules to ensure the consistency of database. it is importent to note the similarities between the fragmentation of data for distribution and the normalization of relations . we will enforce the following three rules during fragmentation ,which ,togather ensure that the data base dose not undergo semantic change during fragmentation.

1. completeness 2. reconstruction 3. disjointness

For correct fragmentation , we should consider the above mentioned three correctness rules of fragmentation

Completeness: In our DBMS system ,we know about the tables and their attributes so we should be very carefull about completeness while fragmenting.there must exist all the records in the splited tables and no record should be lost in the process of fragmentation.

Reconstruction: Continue with the example, we can justify this property of fragmentation that when we join the splitted tables we can get a complete table in its actual form.in this we sould ckeck that no record is missing and by joining them we can get the all records.

Disjointness: It is an important property of fragmentation, for this we should be very careful while splitting table that no record is repeated or copied twice, such as if one table contain record of Pakistan second contains records of UK and third contains records of USA then in this case the tables are overlap and disjointness property fulfill.

Vertical fragmentation:

A vertical fragmentation of a relation R produces fragments each of which contain a subset

of relation’s attributes as wel as the primary key of the relation the objective of vertical fragmentation is to partition a relation into a set of smaller relations so that many of the user application will run on only one fragment.

There are two types exist for the vertical fragmentation of the global relations

• Grouping

• Splitting

Grouping:

Starts by assigning each attribute to one fragment and at each step,joins some of the fragments until some criteria is satisfied.

Splitting:

Starts with a relation and decides on beneficial portioning based on access behavior of applications to the attributes.

We select those attributes from our database:

Q1: find all customers which are from Lahore

Select customer_name

From customer

Where city=”Lahore”;

Q2: find id and name of all customers

Select customer_id,customer name

From customer;

A1 customer_id

A2 Customer_name

A3 city

A1 A2 A3

Q1 0 1 1

Q2 1 1 0

Sites on which query access,

Acc1(q1)=10,acc2(q1)=8

Acc1(q2)=0,acc2(q2)=20

Now ,find affinity matrix:

Aff(a1,a2)=acc1(q2)+acc2(q2)

=0+20

=20

Aff(a1,a3)=0

Aff(a2,a3)= acc1(q1)+acc2(q1)

=10+8

=18

Now, attribute affinity matrix:

A1 A2 A3

A1 0 20 0

A2 20 0 18

A3 0 18 0

Clustering:

The fundamental task in designing a vertical fragmentation algorithm is to find some means of grouping the attributes of a relation based on the attribute affinity values.

Cont(Ai,Aj,Ak)=2bond(Ai,Aj)+2bond(Aj,Ak)-2bond(Ai,Ak)

Here we find,

Cont(A1,A2,A3)= 2bond(A1,A2)+2bond(A2,A3)-2bond(A1,A3)

=2(20)+2(18)-2(0)

=40+36-0

=76

Cont(A1,A3,A2)= 2bond(A1,A3)+2bond(A3,A2)-2bond(A1,A2)

=2(0)+2(18)-2(20)

=0+36-40

=-4

Cont(A2,A1,A3)= 2bond(A2,A1)+2bond(A1,A3)-2bond(A2,A3)

=2(20)+2(0)-2(18)

=40+0-36

=4

Cont(A2,A3,A1)= 2bond(A2,A3)+2bond(A3,A1)-2bond(A2,A1)

=2(18)+2(0)-2(20)

=36+0-40

=-4

Now, clustering matrix is:

A1 A2 A3 A4

A1 0 20 0 20

A2 20 0 18 20

A3 0 18 0 0

A4 20 20 0 0