Q2: Assume that relation R(a,b,c,d,e) contains 5,000,000 records, where each data block of the relation holds 10 records. R is organized as a sorted file with secondary indexes. Assume that R.a is a candidate key for R, with values in the range 0 to 4,999,999, and that R is stored in R.a order from smaller to larger. For each of the following access methods indicate whether the method is applicable or not for each query listed below. In the case that the access method or algorithm is applicable estimate is cost in terms on number of block accesses. In the case where the algorithm in not applicable state the reason. Access method: (A) Access the sorted file for R directly (file scan). (B) Use a B+ tree index on attribute R.a. (C) Use a hash index on attribute R.a. Queries: 1. Oa<50,000 (R) 2. Oa=50,000(R) Present your answer in the form of a table. File scan oa<50,000(R) oa=50,000(R) B+-tree Linear hashing L L

Q2: Assume that relation R(a,b,c,d,e) contains 5,000,000 records, where each data block of the relation holds 10 records. R is organized as a sorted file with secondary indexes. Assume that R.a is a candidate key for R, with values in the range 0 to 4,999,999, and that R is stored in R.a order from smaller to larger. For each of the following access methods indicate whether the method is applicable or not for each query listed below. In the case that the access method or algorithm is applicable estimate is cost in terms on number of block accesses. In the case where the algorithm in not applicable state the reason. Access method: (A) Access the sorted file for R directly (file scan). (B) Use a B+ tree index on attribute R.a. (C) Use a hash index on attribute R.a. Queries: 1. Oa<50,000 (R) 2. Oa=50,000(R) Present your answer in the form of a table. File scan oa<50,000(R) oa=50,000(R) B+-tree Linear hashing L L

Database Systems: Design, Implementation, & Management

11th Edition

ISBN:9781285196145

Author:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Chapter10: Transaction Management And Concurrency Control

Section: Chapter Questions

Problem 1P

See similar textbooks

Similar questions

The data item written by a committed transaction should be immediately stored on the disk. O True False The first step of the normalization process is to identify all foreign keys of a relation. True False The sorting operation can eliminate the duplicate tuples, but the hashing operation cannot. O True False
Task 2: Secondary Index & Aggregation Query Processing Assume the relation CITIZEN(ID, Tax-Code, Salary, Age) storing information about UK citizens’ tax codes and annual salaries. There are r = 60,000,000 records. Each attribute has the same size: 128 bytes. The relation is stored in a file sorted by the salary attribute. The block size B = 1024 bytes and any pointer in the system has size = 128 bytes. The salary attribute is assumed to be uniformly distributed across tuples. There are 6,000 distinct salary values and there are 10,000 different tax-code values. The tax-code and age are assumed to be statistically independent of salary. A data scientist, who has built a Secondary Index over the Tax Code (non- ordering, non-key attribute), is interested in the specific Tax Code: ‘1234L’. The data scientist did analyse the distribution of the Tax Code attribute and noted the following: • Let X be the number of citizens with Tax Code 1234L per data block. That is, given a random…
Indexed sequential file. Consider the relation named "student" which is mapped to a file as shown below, which is physically ordered by street address. (Note when there is a mix of numerical numbers and character strings, numerical numbers come first, followed by character strings, which are ordered alphabetically.) Suppose we have an indexed sequential file with a sparse index containing only three index items. Show a possible scenario of index items, along with pointers pointing to appropriate records in the data file. [Put your answer in the space below, by providing index items and pointers.] Index File name: studentss Index item Name Street Mike 30th street Hilary Donald 72nd street 90th street 168th street George A street Dodge street Farnam street Pacific street Joe Barack Al Kamala Explanation (optional):
PLZ HELP WITH THE FOLLOWING: We will assume that the Computer Science Department at a university has an agreement with the publishers to allow faculty and students to have free exclusive access to the e-books online through their website. They need you to design a database for them using the following business rules: The faculty and students (we refer to them in our system as clients) can electronically check out any book at any time. A book must have at least one author but can have more authors. An author must have written at least one book or more to be included in the system. A book may have never been checked out, but can be checked out many times by the same client or different clients over time. A client may have never checked out a book or they may have checked out many books over time. To simplify determining which client currently has a given book checked out, a M:N relationship between BOOK and CLIENT should be maintained. The information recorded for each book is book…
Exercise 8.3 Consider a relation stored as a randomly ordered file for which the only index is an unclustered index on a field called sal. If you want to retrieve all records with sal > 20, is using the index always the best alternative? Explain.
Assume the relation CITIZEN(ID, Tax-Code, Salary, Age) storing information about UK citizens’ tax codes and annual salaries. There are r = 60,000,000 records. Each attribute has the same size: 128 bytes. The relation is stored in a file sorted by the salary attribute. The block size B = 1024 bytes and any pointer in the system has size = 128 bytes. The salary attribute is assumed to be uniformly distributed across tuples. There are 6,000 distinct salary values and there are 10,000 different tax-code values. The tax-code and age are assumed to be statistically independent of salary. A data scientist, who has built a Secondary Index over the Tax Code (non- ordering, non-key attribute), is interested in the specific Tax Code: ‘1234L’. The data scientist did analyse the distribution of the Tax Code attribute and noted the following: • Let X be the number of citizens with Tax Code 1234L per data block. That is, given a random block, there are X citizens with Tax Code 1234L. Objective:…
7. A “natural join" in a relational database takes two tables of information and combines entries from the tables that share a particular field. You can read more about this at Wikipedia. Interpret the tables below as sets of sets? For the Course Enrollment Table define A = {{A38182818, Spring2021, Math230, 01}, ..., {A56363631, Spring2021, Math454, 01}}, and for the Student Table define B = {{Al, A81723653}, {Jo, A38182818}, {Su, A56363631}}. Course Enrollment Table Student Table A38182818 | Spring2021 | Math230 | 01 A81723653 | Spring2021 | Math350 01 Al A81723653 A81723653 Fall2020 Math152 04 Jo A56363631 A38182818 Fall2020 Math332 02 Su A38182818 A56363631 | Spring2021 Math454 | 01 (a) Construct the set C = {xUy : x € A, y E B, x N y # 0}. (b) Represent the resulting set C from part (a) as a table, similar to what you see above. Make sure to write down the table for C in a reasonably sorted order. (c) Why would you want to query the database for the set {x € C : Al e x}? Be…
Given the following relation R and its functional dependencies, what is the key of R? R(A, B, C, D, E, F) F = A --> B, B --> E, E --> C}
There are three tables in this database (see image). Write MYSQL code for: (1) Create a trigger “insert_inventory” on table “Inventory”. The trigger is fired after a row is inserted in table “Inventory”. After a row is inserted in table “inventory”, the “itemid”, the insertion time, and the action is inserted in table “Inventory_history”. The action is set to ‘add an item’. The oldprice is set to null. Test your trigger by inserting a row into Inventory and displaying the contents of Inventory_history. (2) Create a trigger “change_quantity” on table “Transaction”. The trigger is fired after a row is inserted in table “Transaction”. After a row is inserted in table “Transaction”, update the “quantity” in table “Inventory”. For example, if 3 iWatch are sold, then the quantity of iWatch in table “Inventory” is decreased by 3. Test your trigger by inserting a row into Transaction and displaying the contents of the relevant row in Inventory. (3) Create a trigger “change_price” on table…
Which of these statements is true about relation partitioning? It places different rows from the same table on different machines, allowing for the computing of different query predicates in parallel on different machines. It places different columns from the same table on different machines, which allows for computing different query predicates in parallel on different machines. It places certain tables on one machine and others on a different machine, allowing different query subexpressions in the same plan to be computed in parallel on different machines. It replicates and indexes data, which generally improves query performance.
Suppose that user U creates a relation R(A,B,C,D) and wants to grant the following privileges to us W, X, and Y. Write SQL statements to achieve the above authorizations: (a) User W should be able to read all attributes/records in the relation, and be able to grant that privilege to other users. (b) User X should be able to update attributes A a D, but cannot grant that privilege to others. (c) User Y can read only attributes A, B, and only those tuples where C = 'c1.
Assume that a student table in a university database had index on StudentID (the primary key) and indexes on Major, Age, MaritalStatus, and HomeZipCode (all secondary keys). Further, assume that the university wanted a list of students majoring in MIS or computer science, over age 25, and married OR students majoring in computer engineering, single, and from the 45462 zip code. How could indexes be used so that only records that satisfy this qualification are accessed?

SEE MORE QUESTIONS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag…

Computer Science

ISBN:

9781285196145

Author:

Steven, Steven Morris, Carlos Coronel, Carlos, Coronel, Carlos; Morris, Carlos Coronel and Steven Morris, Carlos Coronel; Steven Morris, Steven Morris; Carlos Coronel

Publisher:

Cengage Learning