Subject -DATABASE

10 poi 2. (a) Draw the Top-down design for the system (ques. no. 1). Suppose you have three sites (Mirpur, Dhamrai and Motijheel) that you have to distribute and replicate your data. (b) Consider relation EMP in Figure 2b. Suppose there are two applications that access EMP. The first is issued at five sites and attempts to find the salary of different employees given a range. Assume that Elec. Eng., analyst, Programmer are located at three different sites (Title- wise). The second application is issued at two sites where the employees with salary less than 30000 are managed at one site, whereas those with higher salary are managed at a second site. Derive the primary horizontal fragmentation of EMP using the foregoing information. EMP ENO ENAME TITLE SAL PNO RESP DUR E1 E2 E2 E3 E3 E4 E5 E6 E7 J. Doe Elect. Eng. 40000 M. Smith Analyst 34000 M Smith Analyst 34000 A. Lee Mech. Eng. 27000 A. Lee Mech. Eng. 27000 J. Miller Programmer 24000 B. Casey Syst. Anal. 34000 L. Chu Elect. Eng. 40000 R. Davis Mech. Eng. 27000 J. Jones Syst. Anal. 34000 P1 Manager P1 Analyst P2 Analyst P3 Consultant P4 Engineer P2 Programmer P2 Manager P4 Manager P3 Engineer P3 Manager 12 24 6 10 48 18 24 48 36 40 Figure-26: EMP relation 10 poi 2. (a) Draw the Top-down design for the system (ques. no. 1). Suppose you have three sites (Mirpur, Dhamrai and Motijheel) that you have to distribute and replicate your data. (b) Consider relation EMP in Figure 2b. Suppose there are two applications that access EMP. The first is issued at five sites and attempts to find the salary of different employees given a range. Assume that Elec. Eng., analyst, Programmer are located at three different sites (Title- wise). The second application is issued at two sites where the employees with salary less than 30000 are managed at one site, whereas those with higher salary are managed at a second site. Derive the primary horizontal fragmentation of EMP using the foregoing information. EMP ENO ENAME TITLE SAL PNO RESP DUR E1 E2 E2 E3 E3 E4 E5 E6 E7 J. Doe Elect. Eng. 40000 M. Smith Analyst 34000 M Smith Analyst 34000 A. Lee Mech. Eng. 27000 A. Lee Mech. Eng. 27000 J. Miller Programmer 24000 B. Casey Syst. Anal. 34000 L. Chu Elect. Eng. 40000 R. Davis Mech. Eng. 27000 J. Jones Syst. Anal. 34000 P1 Manager P1 Analyst P2 Analyst P3 Consultant P4 Engineer P2 Programmer P2 Manager P4 Manager P3 Engineer P3 Manager 12 24 6 10 48 18 24 48 36 40 Figure-26: EMP relation