CS5481 Data Engineering Assignment 3
1. Transactions A.
(a)
(i)
(ii)
𝑇𝑇𝑇𝑇 12
(iii)
𝑇𝑇𝑇𝑇 12
Assume that the initial balance in two accounts are a and b, respectively. (𝑇𝑇 , 𝑇𝑇 ): 𝐴𝐴: 𝑎𝑎 ∗ 0.9; 𝐵𝐵: 𝑏𝑏 + 𝑎𝑎 ∗ 0.1; 𝑠𝑠𝑠𝑠𝑠𝑠 = 𝑎𝑎 + 𝑏𝑏;
12
(𝑇𝑇2, 𝑇𝑇1): 𝑠𝑠𝑠𝑠𝑠𝑠 = 𝑎𝑎 + 𝑏𝑏; 𝐴𝐴: 𝑎𝑎 ∗ 0.9; 𝐵𝐵: 𝑏𝑏 + 𝑎𝑎 ∗ 0.1;
𝑟𝑟𝑟𝑟𝑎𝑎𝑟𝑟(𝐴𝐴)
𝑡𝑡𝑟𝑟𝑠𝑠𝑡𝑡: = 𝐴𝐴 ∗ 0.1 𝐴𝐴: = 𝐴𝐴 − 𝑡𝑡𝑟𝑟𝑠𝑠𝑡𝑡 𝑤𝑤𝑟𝑟𝑤𝑤𝑡𝑡𝑟𝑟(𝐴𝐴)
𝑟𝑟𝑟𝑟𝑎𝑎𝑟𝑟(𝐵𝐵)
𝐵𝐵: = 𝐵𝐵 + 𝑡𝑡𝑟𝑟𝑠𝑠𝑡𝑡 𝑤𝑤𝑟𝑟𝑤𝑤𝑡𝑡𝑟𝑟(𝐵𝐵)
𝑟𝑟𝑟𝑟𝑎𝑎𝑟𝑟(𝐴𝐴) 𝑟𝑟𝑟𝑟𝑎𝑎𝑟𝑟(𝐵𝐵)
𝑤𝑤𝑟𝑟𝑤𝑤𝑡𝑡𝑟𝑟(𝑠𝑠𝑠𝑠𝑠𝑠)
Assume that the initial balance in two accounts are a and b respectively final value: 𝐴𝐴:𝑎𝑎 ∗ 0.9;𝐵𝐵:𝑏𝑏 + 𝑎𝑎 ∗ 0.1;𝑠𝑠𝑠𝑠𝑠𝑠 = 𝑎𝑎 + 𝑏𝑏;
𝑟𝑟𝑟𝑟𝑎𝑎𝑟𝑟(𝐴𝐴)
𝑟𝑟𝑟𝑟𝑎𝑎𝑟𝑟(𝐴𝐴) 𝑟𝑟𝑟𝑟𝑎𝑎𝑟𝑟(𝐵𝐵)
𝑡𝑡𝑟𝑟𝑠𝑠𝑡𝑡: = 𝐴𝐴 ∗ 0.1 𝐴𝐴: = 𝐴𝐴 − 𝑡𝑡𝑟𝑟𝑠𝑠𝑡𝑡 𝑤𝑤𝑟𝑟𝑤𝑤𝑡𝑡𝑟𝑟(𝐴𝐴)
𝑟𝑟𝑟𝑟𝑎𝑎𝑟𝑟(𝐵𝐵)
𝐵𝐵: = 𝐵𝐵 + 𝑡𝑡𝑟𝑟𝑠𝑠𝑡𝑡 𝑤𝑤𝑟𝑟𝑤𝑤𝑡𝑡𝑟𝑟(𝐵𝐵)
𝑤𝑤𝑟𝑟𝑤𝑤𝑡𝑡𝑟𝑟(𝑠𝑠𝑠𝑠𝑠𝑠)
Assume that the initial balance in two accounts are a and b respectively final value: 𝐴𝐴: 𝑎𝑎 ∗ 0.9; 𝐵𝐵: 𝑏𝑏 + 𝑎𝑎 ∗ 0.1; 𝑠𝑠𝑠𝑠𝑠𝑠 = 𝑎𝑎 ∗ 0.9 + 𝑏𝑏;
(b)
(i) One possible schedule:
(c)
𝑟𝑟 (𝐴𝐴);𝑤𝑤 (𝐴𝐴);𝑟𝑟 (𝐵𝐵);𝑟𝑟 (𝐴𝐴);𝑤𝑤 (𝐵𝐵);𝑟𝑟 (𝐵𝐵);𝑤𝑤 (𝑠𝑠𝑠𝑠𝑠𝑠); 1112122
𝑟𝑟 (𝐴𝐴);𝑟𝑟 (𝐴𝐴);𝑤𝑤 (𝐴𝐴);𝑟𝑟 (𝐵𝐵);𝑟𝑟 (𝐵𝐵);𝑤𝑤 (𝐵𝐵);𝑤𝑤 (𝑠𝑠𝑠𝑠𝑠𝑠); 1211212
(ii) One possible schedule:
(ii) No, 𝑟𝑟 (𝐴𝐴);𝑟𝑟 (𝐵𝐵);𝑟𝑟 (𝐴𝐴);𝑤𝑤 (𝐴𝐴);𝑟𝑟 (𝐵𝐵);𝑤𝑤 (𝐵𝐵);𝑤𝑤 (𝑠𝑠𝑠𝑠𝑠𝑠); (i) Yes2 2 1 1 1 1 2
CS5481 Data Engineering
Assignment 3
B.
𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐵𝐵);𝑟𝑟 (𝐵𝐵);𝑤𝑤 (𝐶𝐶);𝑐𝑐 ;𝑟𝑟 (𝐶𝐶);𝑐𝑐 ; 21122211
(a) Yes, one example schedule:
𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐵𝐵);𝑟𝑟 (𝐵𝐵);𝑟𝑟 (𝐶𝐶);𝑤𝑤 (𝐶𝐶);𝑐𝑐 ;𝑐𝑐 ; 12121221
(b) Yes, one example schedule:
(c) no
(d) no
(e) Yes, one example schedule:
𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐴𝐴);𝑟𝑟 (𝐵𝐵);𝑤𝑤 (𝐶𝐶);𝑤𝑤 (𝐵𝐵);𝑟𝑟 (𝐶𝐶);𝑐𝑐 ;𝑐𝑐 ; 12221121
𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐵𝐵);𝑤𝑤 (𝐴𝐴);𝑟𝑟 (𝐶𝐶);𝑟𝑟 (𝐵𝐵);𝑐𝑐 ;𝑤𝑤 (𝐶𝐶);𝑐𝑐 ; 11212122
(f) Yes, one example schedule:
𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐴𝐴);𝑟𝑟 (𝐵𝐵);𝑤𝑤 (𝐶𝐶);𝑤𝑤 (𝐵𝐵);𝑐𝑐 ;𝑟𝑟 (𝐶𝐶);𝑐𝑐 ; 12221211
(g) Yes, one example schedule:
𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐴𝐴);𝑤𝑤 (𝐵𝐵);𝑟𝑟 (𝐶𝐶);𝑐𝑐 ;𝑟𝑟 (𝐵𝐵);𝑤𝑤 (𝐶𝐶);𝑐𝑐 ; 12111222
(h) Yes, one example schedule:
CS5481 Data Engineering Assignment 3
2. Distributed Query Processing
The sample answers here may NOT be the ones with the lowest data transmission cost. (a)
(ii)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡1 to site 1. Cost = 8 * (20,000*0.4)=64,000 bytes
Site 2: 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡1 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆(𝑉𝑉𝑉𝑉𝑉𝑉)
Site 1: 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡2 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆,𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆,𝐷𝐷𝑆𝑆𝐷𝐷(𝑆𝑆𝑇𝑇𝑆𝑆 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡1)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡2 to site 2. Cost = (8+30+30) * (20,000*0.4)=544,000 bytes Site 3: 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡3 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆,𝐷𝐷𝑆𝑆𝐷𝐷(𝐴𝐴𝐶𝐶𝑇𝑇)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡3 to site 2. Cost = (6+30) * 1,200=43,200 bytes Site 2: 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡4 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆,𝐷𝐷𝐷𝐷𝐷𝐷𝐷𝐷,𝑆𝑆𝑆𝑆𝑆𝑆(𝑉𝑉𝑉𝑉𝑉𝑉 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡2 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡3)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡4 to site 3. Cost = (30+40+6)*6,000=456,000 bytes
Site 3: 𝑅𝑅𝑟𝑟𝑠𝑠𝑠𝑠𝑅𝑅𝑡𝑡 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆,𝐷𝐷𝐷𝐷𝐷𝐷𝐷𝐷,𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆(𝐴𝐴𝐶𝐶𝑇𝑇 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡4)
Total data transmission cost: 1,107,200 bytes (b)
(ii)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡1 to site 1. Cost = 8 * (20,000*0.4)=64,000 bytes
Site 2: Site 1: Site 3: Site 2: Site 1:
𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡1 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆(𝑉𝑉𝑉𝑉𝑉𝑉)
𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡2 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆,𝐷𝐷𝑆𝑆𝐷𝐷(𝑆𝑆𝑇𝑇𝑆𝑆 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡1)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡2 to site 2. Cost = (8+30) * (20,000*0.4)=304,000 bytes 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡3 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆,𝐷𝐷𝑆𝑆𝐷𝐷(𝐴𝐴𝐶𝐶𝑇𝑇)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡3 to site 2. Cost = (6+30) * 1,200=43,200 bytes 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡4 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆,𝑆𝑆𝑆𝑆𝑆𝑆,𝐷𝐷𝐷𝐷𝐷𝐷𝐷𝐷(𝑉𝑉𝑉𝑉𝑉𝑉 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡2 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡3)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡4 to site 3. Cost = (8+6+40) * 6,000=324,000 bytes 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡5 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆,𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆(𝑆𝑆𝑇𝑇𝑆𝑆 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡1)
Ship 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡4 to site 3. Cost = (8+30) * (20,000*0.4)=304,000 bytes
Site 3: 𝑅𝑅𝑟𝑟𝑠𝑠𝑠𝑠𝑅𝑅𝑡𝑡 ← ∏𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆,𝐷𝐷𝐷𝐷𝐷𝐷𝐷𝐷,𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆𝑆(𝐴𝐴𝐶𝐶𝑇𝑇 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡4 ⋈ 𝑇𝑇𝑟𝑟𝑠𝑠𝑡𝑡5) Total data transmission cost: 1,039,200 bytes