Relations c, u and t are instances of relations. Their schemas are Customer(Account, Cname, Province, Cbalance,...

 

Transcribed Image Text:

Relations c, u and t are instances of relations. Their schemas are Customer(Account, Cname, Province, Cbalance, Crlimit) Vendor (Vno, Vname, City, Vbalance) Transaction(Tno, Vno, Account, T.Date, Amount) where e has a primary index on Account, v has a primary index on Vno, t has a secondary index on Vno. t has a secondary index on Account. The following are statistical data of the relations, which may be used in the following questions. n = 40,000, n = 5,000,000, n = 1,000,000 b = 10,000, b = 500,000, b = 200,000 dist (City) = 20 The height of the index of c on Account is 5, the height of the index of t on Vno is 6. the height of the index of t on Account is 7. One index node is stored in a block in an index file on disk. Answer the following questions: 1. The following is a user query select Tno, Amount, Province from c, t, v where t.Vno v.Vno and t.Account = c.Account and City = 'Guelph'; Using the heuristic rules, which of the following strategy should we choose as the more efficient? Which rules should we use in this case? IITno, Amount, Province (ct City='Guelph'()) (1) IITno, Amount, Province (City='Guelph' (u) tc) (2) 2. Estimate the number of disk reads required for executing strategy (1) using indexed nested loop join when possible. Also estimate the number of tuples in the result of the strategy. It is assumed that the RAM space available for running the strategy can contain all the intermediate results. Please give intermediate and final results. 3. Estimate the number of disk reads required for executing strategy (2) using indexed nested loop join when possible. Also estimate the number of tuples in the result of the strategy. It is assumed that the RAM space available for running the strategy can contain all the intermediate results. Please give intermediate and final results. 4. Compare the numbers of disk reads of the two strategies, and very briefly explain the main reason. Relations c, u and t are instances of relations. Their schemas are Customer(Account, Cname, Province, Cbalance, Crlimit) Vendor (Vno, Vname, City, Vbalance) Transaction(Tno, Vno, Account, T.Date, Amount) where e has a primary index on Account, v has a primary index on Vno, t has a secondary index on Vno. t has a secondary index on Account. The following are statistical data of the relations, which may be used in the following questions. n = 40,000, n = 5,000,000, n = 1,000,000 b = 10,000, b = 500,000, b = 200,000 dist (City) = 20 The height of the index of c on Account is 5, the height of the index of t on Vno is 6. the height of the index of t on Account is 7. One index node is stored in a block in an index file on disk. Answer the following questions: 1. The following is a user query select Tno, Amount, Province from c, t, v where t.Vno v.Vno and t.Account = c.Account and City = 'Guelph'; Using the heuristic rules, which of the following strategy should we choose as the more efficient? Which rules should we use in this case? IITno, Amount, Province (ct City='Guelph'()) (1) IITno, Amount, Province (City='Guelph' (u) tc) (2) 2. Estimate the number of disk reads required for executing strategy (1) using indexed nested loop join when possible. Also estimate the number of tuples in the result of the strategy. It is assumed that the RAM space available for running the strategy can contain all the intermediate results. Please give intermediate and final results. 3. Estimate the number of disk reads required for executing strategy (2) using indexed nested loop join when possible. Also estimate the number of tuples in the result of the strategy. It is assumed that the RAM space available for running the strategy can contain all the intermediate results. Please give intermediate and final results. 4. Compare the numbers of disk reads of the two strategies, and very briefly explain the main reason.