Tutorial 1: Distributed Database Design

1
Tutorial 1 Distributed Database Design

• INFS3200 Advanced Database SystemsSemester 2,
  2003

2
Tutor

• Name Chuan Li (Michael)
• E-mail s4014457_at_student.uq.edu.au
• Mobile [phone]
• Consultation
• Tuesdays 4pm-5pm in room 78-631 or at other times
  by appointment

3
Fragmentation

• Fragmentation vs. Replication
• Why fragmentation
• Types of fragmentation
• Horizontal fragmentation
• Row
• Same Schema
• Vertical fragmentation
• Column
• Each fragment must contain the primary key
• Hybrid fragmentation
• Can be specified by relation algebra or sql

4
Correctness rules

• Completeness
• Any data item must be covered by at least one
  fragment
• Reconstruction
• There exist relation operators (select, project,
  join) to reconstruct the original table from
  fragments
• Horizontal fragmentation - Union
• Vertical fragmentation - Join
• Hybrid fragmentation - combination of relational
  operators
• Disjointness
• Remove duplicate data
• Horizontal fragmentation - a row cannot appear in
  more than one fragment.
• Vertical fragmentation non-key attributes
  cannot appear in more than one fragment.

5
Question 1- Horizontal Fragmentation
Given the following relation and the predicates
p1 SAL gt 30000, p2 SAL lt 30000

• Perform a horizontal fragmentation of the table
  based on the given predicates.
• Is this a correct fragmentation?
• If the answer to (b) is no, explain why, and give
  the predicates that would correctly
• fragment the table

6
Question 1- Horizontal Fragmentation
Solution
Fragment 1 SALgt30000
Fragment 2 SALlt30000
Violate the completeness rule
p1 SAL ? 30000, p2 SAL lt 30000 or P1 SAL
gt30000, p2SAL ? 30000
Discussion How about p1 SAL ? 30000, p2 SAL lt
29999 ?
Integer? 29999.5? completeness
7
Question 2 - Vertical Fragmentation
A Student table is given as follows
Are any of the following vertical fragments of
the student table incorrectly constructed? If so,
what is the problem?
Fragment 1
Fragment 2
Fragment 3
Horizontal fragment
No primary keyCannot reconstruct
8
Question 3 - Integrity Constraint
The following global schema is given
R1 ABCD, R2 D E FG, R3 FG H I J

• Design a correct vertical data fragmentation of
  the above schema suitable for distributed
  databases with two sites.
• Identify all referential integrity constraints
  that must be maintained on each site of the
  distributed system and between sites of the
  system.
• Assume the following is the database population
• D r1, r2, r3
• r1(A B C D) r2(D E F G) r3(F G H I J)
• 1 2 1 1 1 2 3 1 3
  1 0 2 0
• 3 4 2 2 2 4 3 2 3
  2 6 1 0
• 3 2 3 2 3 1 3 2 2
  2 1 1 1

Computer the value of the following express
? EFH (r2 r3) in the centralized database
and then in your designed distributed version.
(Show complete algebraic expression required for
distributed computation).
9
Question 3 - Integrity Constraint
a)R1 ABCD r11 ABC, r12 ABD R2
D E FG r21 DG, r22 DEF R3 FG
H I J r31 FGI, r32 FGHJ rx1 in site
1 and rx2 in site 2
Be careful about Foreign key constraint !!!
C)
? EFH (r2 r3)
Centralized System r2(D E F G) r3(F G H I
J) ? D E F G H I J ? E F H 1 2 3 1
3 1 0 2 0 1 2 3 1 0 2 0
2 3 0 2 4 3 2 3 2 6 1 0
2 4 3 2 6 1 0 4 3 6 3 1 3 2
2 2 1 1 1 3 1 3 2 6 1 0
1 3 6
1 2 3 3 1 0 0 1 2 3 1
0 0 2 3 0 2 4 3 3 2 6
0 1 2 3 2 6 0 2 3 6 3 1 3
2 2 1 1 2 4 3 1 0 0
4 3 0
2 4 3 2 6 0 4 3 6
3 1 3 1 0 0
1 3 0
3 1 3 2 6 0 1 3 6
10
Question 4 - Hybrid Fragmentation
a) The following table r(A B C D) is given. Let
R1, R2, R3, R4, R5 be a fragmentation. Check its
correctness. Note that A is primary key.
R1
Strategy Visualize each fragment Suppose that
values of each attribute are ordered.
R2
R3
R4
R5
b) Is there any data replication in such design?
11
Question 4 - Hybrid Fragmentation
Incorrect violate the completeness rule
b) Is there any data replication in such design?
Yes
12
Thank you !!!