1 / 61

Distributed Database Management Systems

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.

meghan
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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Distributed Database Management Systems Lecture 25

  2. Distributed Database Management Systems Virtual University of Pakistan

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

  4. In Previous Lectures--2:00 • DDBS design is a team effort • Involves • Domain Experts • DDBS Experts • Network Experts

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

  6. Today’s Lecture Derived Horizontal Fragmentation

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

  8. Owner a, b, c, d TABLE1 Link TABLE2 p, q, r, s, a Member Time: 5:36

  9. Scenario (around 7:25) Owner titleId, titleName, sal TITLE Link EMP empId, empName, empAdres, titleId Member

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

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

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

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

  14. TITLE TITLE3 TITLE1 TITLE2

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

  16. Time 15:40 PHF on Owner titleId, titleName, sal TITLE Link empId, empName, empAdres, titleId EMP Member Natural Join with Owner Fragments

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

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

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

  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)

  21. Implementing same scenario as PHF 22:10

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

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

  24. Adding a new record in TITLE (23:10) TITLE TITLE New Record

  25. Time 24:00 • All three predicates of PHF defined in the two slide a couple of slides ago

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

  27. Original EMP Table EMP

  28. DHFs of EMP Table

  29. EMP1 EMP3 EMP2

  30. Transactional Replication (30:30)

  31. Data replicated as the transaction executes • Preferred in higher bandwidth and lower latency • Transaction is replicated as it is executed

  32. Begins with a snapshot, changes sent at subscribers as they occur or at timed intervals • A special Type allows changes at subscribers

  33. Lets do it • From the Enterprise Manager, select Replication, after a couple of nexts, we get this screen

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

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

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

  37. On next slide is asks the schedule for Distributor

  38. A couple of more nexts and then

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

More Related