Distributed database management systems
This presentation is the property of its rightful owner.
Sponsored Links
1 / 61

Distributed Database Management Systems PowerPoint PPT Presentation


  • 74 Views
  • Uploaded on
  • Presentation posted in: General

Distributed Database Management Systems . Lecture 25. Distributed Database Management Systems . Virtual University of Pakistan. In Previous Lectures: time 1:40-230. Reasons for Fragmentation Maximizes local access Reduces table size, etc. PHF using the SQL Server on same machine.

Download Presentation

Distributed Database Management Systems

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Distributed database management systems

Distributed Database Management Systems

Lecture 25


Distributed database management systems1

Distributed Database Management Systems

Virtual University of Pakistan


In previous lectures time 1 40 230

In Previous Lectures: time 1:40-230

  • Reasons for Fragmentation

    • Maximizes local access

    • Reduces table size, etc.

  • PHF using the SQL Server on same machine


In previous lectures 2 00

In Previous Lectures--2:00

  • DDBS design is a team effort

  • Involves

    • Domain Experts

    • DDBS Experts

    • Network Experts


In previous lectures

In Previous Lectures

  • Implemented PHF in a Banking Environment (around 3: 40)

  • DDBS layer is superimposed on the client sites (4:00)

  • Actual Data resides with the local sites (4:35)


Today s lecture

Today’s Lecture

Derived Horizontal Fragmentation


Derived horizontal fragmentation

Derived Horizontal Fragmentation

  • Fragmenting/ Partitioning a table based on the constraints defined on another table.

  • Both tables are linked with each other through Owner-Member relation


Distributed database management systems

Owner

a, b, c, d

TABLE1

Link

TABLE2

p, q, r, s, a

Member

Time: 5:36


Scenario around 7 25

Scenario (around 7:25)

Owner

titleId, titleName, sal

TITLE

Link

EMP

empId, empName, empAdres, titleId

Member


Why dhf here 8 50

Why DHF Here (8: 50)

  • Employee and salary record is split in two tables due to Normalization

  • Storing all data in EMP table introduces Transitive Dependency

  • That causes Anomalies


Phf of title table around 11 00

PHF of TITLE table, around 11:00

  • Predicates defined on the sal attribute of TITLE table

  • p1 = sal > 10000 and sal <= 20000

  • p2 = sal > 20000 and sal <= 50000

  • p3 = sal > 50000


Conditions for the title table 11 58

Conditions for the TITLE Table (11:58)

  • TITLE1 = (sal > 10000 and SAL ≤30000) (SAL)

  • TITLE2 = (sal > 20000 and SAL ≤50000) (SAL)

  • TITLE3 = (sal > 50000) (SAL)


Tables created with constraints 12 20

Tables created with constraints: 12:20

  • create table TITLE1 (titleID char(3) primary key, titleName char (15), sal int check (SAL between 10000 and 20000))

  • create table TITLE2 (titleID char(3) primary key, titleName char (15), sal int check (SAL between 20001 and 50000))

  • create table TITLE3 (titleID char(3) primary key, titleName char (15), sal int check (SAL > 50000))


Distributed database management systems

TITLE

TITLE3

TITLE1

TITLE2


Emp table at local sites 14 45

EMP table at local sites (14:45)

  • create table EMP1 (empId char(5) primary key, empName char(25), empAdres char (30), titleId char(3) foreign key references TITLE1(titleID))


Time 15 40

Time 15:40

PHF on

Owner

titleId, titleName, sal

TITLE

Link

empId, empName, empAdres, titleId

EMP

Member

Natural Join with Owner Fragments


Referential integrity constraint 18 30

Referential Integrity Constraint (18:30)

  • Null value in the EMP1.titleId is allowed

  • This violates the correctness requirement of the Fragmentation, i.e., it will violating the completeness critetion


Tighten up the constraint around 20 00

Tighten Up the Constraint Around 20:00

  • Further we need to impose the “NOT NULL” constraint on the EMP1.titleID

  • Now the records in EMP1 will strictly adhere to the DHF


Revised emp1 definition 21 00

Revised EMP1 Definition(21::00)

  • create table EMP1 (empId char(5) primary key, empName char(25), empAdres char (30), titleId char(3) foreign key references TITLE1(titleID) not NULL)


Defining all three emp tables 21 20

Defining all three EMP tables:21:20

  • create table EMP1 (empId char(5) primary key, empName char(25), empAdres char (30), titleId char(3) foreign key references TITLE1(titleID) not NULL)

  • create table EMP2 (empId char(5) primary key, empName char(25), empAdres char (30), titleId char(3) foreign key references TITLE2(titleID) not NULL)

  • create table EMP3 (empId char(5) primary key, empName char(25), empAdres char (30), titleId char(3) foreign key references TITLE3(titleID) not NULL)


Distributed database management systems

Implementing same scenario as PHF 22:10


Phf of emp at different sites 22 30

PHF of EMP at different sites (22:30)

  • create table EMP1 (empId char(5) primary key check (empId in ('Programmer')), empName char(25), empAdres char (30), titleId char(3))


Distributed database management systems

  • create table EMP2 (empId char(5) primary key check (empId in (‘Elect. Engr’,’Mech. Engr’)), empName char(25), empAdres char (30), titleId char(3))

  • create table EMP3 (empId char(5) primary key check (empId in (' Sys Analyst ')), empName char(25), empAdres char (30), titleId char(3))


Distributed database management systems

Adding a new record in TITLE

(23:10)

TITLE

TITLE

New Record


Time 24 00

Time 24:00

  • All three predicates of PHF defined in the two slide a couple of slides ago


May be 24 40

May be: 24:40

  • create table EMP1 (empId char(5) primary key check (empId in ('Programmer‘, ‘Assist Supr’)), empName char(25), empAdres char (30), titleId char(3))


Distributed database management systems

Original EMP Table

EMP


Dhfs of emp table

DHFs of EMP Table


Distributed database management systems

EMP1

EMP3

EMP2


Transactional replication 30 30

Transactional Replication (30:30)


Distributed database management systems

  • Data replicated as the transaction executes

  • Preferred in higher bandwidth and lower latency

  • Transaction is replicated as it is executed


Distributed database management systems

  • Begins with a snapshot, changes sent at subscribers as they occur or at timed intervals

  • A special Type allows changes at subscribers


Distributed database management systems

  • Lets do it

  • From the Enterprise Manager, select Replication, after a couple of nexts, we get this screen


Distributed database management systems

  • Publication has been created that can be viewed from Replication Monitor or from Replication, like this


Distributed database management systems

  • It has also created snapshot and log reader agents, which won’t work until we create a subscription.

  • For this, we select the publication from replication monitor, right click it, then select Push new subscription


Distributed database management systems

  • You select the particular database where you want to subscribe, we have created a new one


Distributed database management systems

  • On next slide is asks the schedule for Distributor


Distributed database management systems

A couple of more nexts and then


Distributed database management systems

  • After this we run the snapshot agent, that creates a snapshot, you can verify this from snapshot agent history, or you can go to subscriber database and have a look, like this


Distributed database management systems

  • We delete a record from our publication and we see that it is expressed in


Distributed database management systems

  • This will be automatically be transferred to Subscription.

  • If this activity could not be performed on subscriber, then the replication monitor will generate an error. You have to trap it and tune your application


Merge replication

Merge Replication


Distributed database management systems

  • From replication, start a new publication, a few nexts, and once again our old familiar screen


Distributed database management systems

  • After a few nexts, the merge publication will be created


Distributed database management systems

  • Now you execute the snapshot agent of this replication

  • It will create the snapshot, then you subscribe a database

  • We find the original data in the subscriber

  • After this if we make any change on either side, it will be reflected on the other side


Distributed database management systems

  • In case of merge replication, we have to be careful about the constraints, like Primary Key, or other constraints


  • Login