Computer Engineering - Data Bases 2

Full exam

Databases 2 - exam S. Comai, P. Fraternali, D. Martinenghi January 14th, 2022 Multichancestudents do not need to answer questions A.2, B.5, and C.2. A. Ranking (7 + bonus) The admission committee for a prestigious high school needs to select the best candidate students, shown in the table, and has decided to rank them according to a combination of their previous marks, weighting English 80% and Math 20%. All marks range from 1 to 10.EnglishMath Molly: 10.0Timmy: 10.0 Dolly: 8.0Dolly: 9.0 Billy: 8.0Polly: 8.0 Polly: 7.0Billy: 8.0 Jimmy: 7.0Jimmy: 6.0 Timmy: 6.0Molly: 5.0 1 )Run FA to nd the top student according to the given criterion. Specify the accesses that were made. 2)Do as above, but with TA. 3)Bonus.Is there a way to nd the same result with fewer accesses, while still guaranteeing correctness? Solution. 1.FA proceeds by sorted access until it reaches depth 2, nding Dolly in both rankings (4 sorted accesses).Then, it completes the scores for Molly (onMath) and Timmy (onEnglish) with 2 random accesses. Molly has the top score, computed as 10
0:8 + 5
0:2 = 9, while Dolly has 8:2 and Timmy 6:8. 2.TA nds Molly onEnglishby sorted access and completes her score with a random access onMath (overall 9); similarly, it nds Timmy by s.a. and makes a r.a. onEnglish(overall 6.8). The threshold is 10, so another round is needed. It then nds Dolly onEnglishby s.a. and completes her score by r.a., and then nds Dolly again by s.a. onMath, so no more r.a.'s are needed. The threshold is now 8.2 (same as Dolly's score), and TA can stop. TA makes in total 4 s.a.'s and 3 r.a.'s (note that a slightly dierent implementation of TA might rst read a row entirely, and then make the r.a.'s for the missing pieces, and in this case this would mean saving one r.a. for Dolly, thereby equalling the same cost as FA). 3.A possible strategy is as follows: make one s.a. toEnglish(Molly) and one r.a. toMathfor Molly, thereby nding her score (9). Make one more s.a. toEnglish(Dolly); at this point, the threshold is 8
0:8 + 10
0:2 = 8:4, which is less than Molly's score, so we can already stop (2 s.a.'s and 1 r.a.). We do not even need to make any s.a. toMath, because we know that the upper bound for marks is 10. The guarantees are the same as TA, because we are using a threshold, although we are descending asymmetrically in the rankings. B. Concurrency (9 points) Establish whether scheduleS, below, belongs to1)VSR,2)CSR,3)2PL-strict,4)2PL,5)TS-mono. S=r 2( X)r 4( W)w 2( X)w 1( Z)r 1( Z)r 3( X)w 1( Y)w 2( W)r 1( Z)w 3( Y): Justify your answers with succinct but clear explanations. Solution. ScheduleSis in CSR because its resource graph is acyclic. Therefore,Sis also in VSR. The schedule is not in 2PL-strict, as shown by the following unsatisable set of conditions on resourceX: 8 < :8 < X UX 2due to strictness, unlock request X UX 2must follow commit time for T2 X UX 2< S LX 3X-lock release must precede S-lock acquisition for Xas ofw[3] 2( X) andr[6] 3( X) S LX 3< 6lock acquisition must precede time of operationr[6] 3( X)However, it is in 2PL, since Transaction 2 can anticipate the lock request on Wbefore time 6 and then start the releasing phase. The schedule is not TS-mono, since there are killed transactions:OperationRTM( W)WTM( W)RTM( X)WTM( X)RTM( Y)WTM( Y)RTM( Z)WTM( Z)Killed transactions r 2( X)00200000 r 4( W)4 w 2( X)2 w 1( Z)1 r 1( Z)1 r 3( X)3 w 1( Y)1 w 2( W)2 as 2 new.upTo THENUPDATE covered SET upTo = possibleRightEnd; END IF; Although the triggering graph is cyclic, sinceUpdateCoveredcan recursively activate itself, the conditions RightEnd > new.upToandpossibleRightEnd > new.upToensure that a new activation will start only for higher values ofupToand that the cycle will then stop at the rightmost interval. 2 D. JPA (6 points) An application manages the tickets of a trouble ticketing service. The data are organized according to the following conceptual model.The application permits the operator to select a user, open his/her list of tickets ordered by submission date descending, select a ticket and check the details of the appliances (one or more) the ticket refers to. Also the operator can select an appliance and check the tickets referring to it, open one such ticket and check its details and the details of the user who submitted it. Tickets are in the order of millions, appliances are in the order of thousands and users are in the order of hundreds of thousands. Tickets are distributed uniformly across appliances and users. Show the JPA entities (with all their attributes) that map the domain ob jects of the conceptual model, taking into account the above mentioned access paths of the application and data cardinalities. When designing the annotations for the relationships, specify the owner side of the relationship, the mapped-by attribute, the fetch policy and the cascading policies you consider more appropriate to support the access required by the web application. Comment on the design choices you have made. Solution. See separate le with the solution. 3