Calculate the average number of block accesses

Assignment Help Database Management System

Reference no: EM131963325

QUESTION 1

A PARTS file with Part# as hash key includes records with the following Part# values: 2369, 3760, 4692, 4871, 5659, 1821, 1074, 7115, 1620, 2428, 3943, 4750, 6975, 4981, 9208. The file use eight buckets, number from 0 to 7. Each bucket is one disk block and holds two records. Load these records into the file in the given order, using the hashing function h(K) = K mod 8. Calculate the average number of block accesses for a random retrieval on Part#.

QUESTION 2
Assume one file has r = 106 records. Each record takes R = 100bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
What is the number of B-+tree internal nodes at the next highest level needed if blocks are approximately 69% full (round up for convenience)?

(Please note this internal level is just above the B-+tree lowest internal node level).

QUESTION 3
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.

How many index blocks bi are needed to build the single level primary index ?

QUESTION 4
Assume one file has r = 106 records. Each record takes R = 100bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
How many block accesses on the average are needed to fetch a record by doing linear search on the single level index part?

QUESTION 5
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.

What is the least number of block accesses to fetch a record by using single level primary index with binary search?

QUESTION 6
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
What is the fan-out value if multi-level index is used?

QUESTION 7
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.

How many levels are there so that top level index fits on a single block in multilevel index?

QUESTION 8

Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.

How many block accesses are needed to fetch a record by using multilevel index?

QUESTION 9

What is the key value of the 4th index record on the top level, assuming each index record points to the record with the highest key value in a block of the next level down?

QUESTION 10

Assume one file has r = 106 records. Each record takes R = 100bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.

What is the order P of the B+-tree for the internal node if B+-tree access structure on the keyis constructed?

QUESTION 11

What is the order P of the B+-tree for the leaf node if B+-tree access structure on the keyis constructed?

QUESTION 12

Assume one file has r = 106 records. Each record takes R = 100bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.

What is the number of leaf-level blocks needed in B-+tree if the tree nodes are approximately 69% full (round up for convenience)?

QUESTION 13
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
What is the number of B-+tree internal nodes at the lowest level needed if blocks are approximately 69% full (round up for convenience)?
(Please note this internal level is just above the B-+tree leaf node level).

QUESTION 14
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
What is the number of levels needed if internal nodes also 69% full (round up for convenience)?

QUESTION 15
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
What is the total number of blocks required by the B+-tree?

QUESTION 16
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
What is the number of block accesses needed to search for and retrieve a record from the file -- given its key value by using B+-tree?

QUESTION 17
Assume one file has r = 106 records. Each record takes R = 100bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000,
inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer)takes 10 bytes.
What is the blocking factor bfr for the file?

QUESTION 18
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
How many data blocks b are needed to hold records?

QUESTION 19
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
How many data blocks accesses on the average are needed to fetch a record by using linear search?

QUESTION 20
Assume one file has r = 106 records. Each record takes R = 100 bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
How many data blocks accesses are needed to fetch a record by using binary search?

QUESTION 21
Assume one file has r = 106 records. Each record takes R = 100bytes, of which 10 bytes are for the key of the record. Suppose the key values range from 1 through 1,000,000, inclusive. Assume the block size B is 1000 bytes for all files, and that an address (block pointer, tree node pointer, or data record pointer) takes 10 bytes.
What is the blocking factor bfr for the single level index ?

QUESTION 22
For a B+-tree of order 3 consisting of 3 levels for the index part, what is the maximum amount of records which may be stored assuming that a data block for data records can hold 100 of them?

a. 2700

b. 1800

c. 900

d. 2560

QUESTION 23
For a B-tree of order 4 consisting of 3 levels, what is the maximum amount of records which may be stored?

a. 1023

b. 255

c. 256

d. 768

QUESTION 24
For the given database schema:
R1(A,B,C,D)
R2(A,E)
R3(B,F,H)
R4(C,G)
R5(H,I)
Please list all the foreign keys in R

a. A

b. B

c. C

d. All of A, B, and C are foreign key in R1

QUESTION 25
Given universal schema R(A,B,C,D,E,F,G,H,I) and a set of functional dependencies as follows:
AB → CD
A → E
B → FH
C → G
D → B
G → C
H → I
when schema R(A,B,C,D,E,F,G,H,I) is normalized or decomposed into the following schema:
R1(A,B,C,D)
R2(A,E)
R3(B,F,H)
R4(C,G)
R5(H,I)

a. The normalized schema:
R1(A,B,C,D)
R2(A,E)
R3(B,F,H)
R4(C,G)
R5(H,I)
possesses the lossless join decomposition property.

b. The normalized schema:
R1(A,B,C,D)
R2(A,E)
R3(B,F,H)
R4(C,G)
R5(H,I)
possesses the functional dependency preservation property.

c. Both a. and b.

d. neither a. nor b.

QUESTION 26
Given universal schema R(A,B,C,D,E,F,G,H,I) and a set of functional dependencies as follows:
AB → CD
A → E
B → FH
C → G
D → B
G → C
H → I
when schema R(A,B,C,D,E,F,G,H,I) is normalized or decomposed into the following schema:
R1(A,B,C,D)
R2(A,E)
R3(B,F,H)
R4(C,G)
R5(H,I)

a. The decomposition created is in the Boyce-Codd normal form.

b. The decomposition created is in the third normal form.

c. both a. and b.

d. neither a. nor b.

QUESTION 27
Given universal schema R(A,B,C,D,E,F,G,H,I} and a set of functional dependencies as follows:
AB → CD
A → E
B → FH
C → G
D → B
G → C
H → I
What is the key (candidate key) for the schema R(A,B,C,D,E,F,G,H,I).

a. AC

b. AH

c. AB

d. AG

QUESTION 28
Given universal schema R(A,B,C,D,E,F,G,H,I} and a set of functional dependencies as follows:
AB → CD
A → E
B → FH
C → G
D → B
G → C
H → I
What is the highest normal form of the universal schema R(A,B,C,D,E,F,G,H,I)?

a. The first normal form (1NF)

b. The Boyce-Codd normal form (BCNF)

c. The third normal form (3NF)

d. The second normal form (2NF)

QUESTION 29
Given universal schema R(A,B,C,D,E,F,G,H,I} and a set of functional dependencies as follows:
AB → CD
A → E
B → FH
C → G
D → B
G → C
H → I
What is the best normalized schema you would like to select in the following?

a. R1(A,B,C,D)

R2(A,E)

R3(B,F,H)

R4(C,G)

R5(H,I)

b. R1(A,B,C,D)
R2(A,E)
R3(B,F,H)
R4(C,G)
R5(H,I)
R6(G,C)

c. R1(A,B,C,D)
R2(A,E)
R3(B,F,H)
R4(C,G)
R5(H,I)
R6(G,C)
R7(D,B)

d. Any of of a, b, and c.

QUESTION 30
If a relational database table is in Boyce-Codd normal form, it is also in

a. the first normal form.

b. the second normal form.

c. the third normal form.

d. all of a., b., and c.

QUESTION 31
If a relational database table is in the first normal form, it is also in

a. the non-first normal form.

b. the second normal form.

c. the third normal form.

d. the Boyce-Codd normal form.

QUESTION 32
If a relational database table is in the second normal form, it is also in

a. the first normal form.

b. the third normal form.

c. the Boyce-Codd normal form

d. all of a., b., and c.

QUESTION 33
If a relational database table is in the third normal form, it is also in

a. the first normal form.

b. the second normal form.

c. the Boyce-Codd normal form.

d. both a. and b.

QUESTION 34
Given two relation schema with referential integrity constraints as follows,

Schema:

EMPLOYEE(SSN, NAME, AGE, SALARY, SUPERSSN, DNO)

DEPARTMENT(DNUMBER, DNAME, MGRSSN, MGRSTARTDATE)

Referential Integrity Constraints:

EMPLOYEE.SUPERSSN references to EMPLOYEE.SSN

EMPLOYEE.DNO references to DEPARTMENT.DNUMBER

DEPARTMENT.MGRSSN references to EMPLOYEE.SSN

Please select the correct set of SQL statements to create these two database tables and enforce these referential integrity constraints correctly.

a.	1. Create table EMPLOYEE: create table EMPLOYEE (name varchar2(19) not null, ssn char (9), bdate date, sex char(3), salary number(8,2), superssn char(9), dno varchar(8), constraint empPK primary key (ssn), constraint empsuperFRK foreign key (superssn) references employee (ssn) disable) 2. Create table DEPARTMENT: create table DEPARTMENT (dname varchar2(15) not null, dnumber varchar(8), mgrssn char(9), mgrstartdate date, constraint departPK primary key (dnumber), constraint departUK unique (dname), constraint departFRK foreign key (mgrssn) references employee (ssn) disable) 3. Add constraint to EMPLOYEE table alter table employee add (constraint empdnoFRK foreign key (dno) references department(dnumber)) 4. Enable the constraints: alter table EMPLOYEE enable constraint empsuperFRK; alter table DEPARTMENT enable constraint departFRK;
b.	1. Create table DEPARTMENT: create table DEPARTMENT (dname varchar2(15) not null, dnumber varchar(8), mgrssn char(9), mgrstartdate date, constraint departPK primary key (dnumber), constraint departUK unique (dname)) 2. Create table EMPLOYEE: create table EMPLOYEE (name varchar2(19) not null, ssn char (9), bdate date, sex char(3), salary number(8,2), superssn char(9), dno varchar(8), constraint empPK primary key (ssn), constraint empsuperFRK foreign key (superssn) references employee (ssn), constraint empdnoFRK foreign key (dno) references department(dnumber) disable) 3. Add constraint to DEPARTMENT table alter table DEPARTMENT add (constraint departFRK foreign key (mgrssn) references employee(ssn)) 4. Enable the constraints: alter table EMPLOYEE enable constraint empsuperFRK; alter table EMPLOYEE enable constraint empdnoFRK;
c.	1. Create table DEPARTMENT: create table DEPARTMENT (dname varchar2(15) not null, dnumber varchar(8), mgrssn char(9), mgrstartdate date, constraint departPK primary key (dnumber), constraint departUK unique (dname) disable) 2. Create table EMPLOYEE: create table EMPLOYEE (name varchar2(19) not null, ssn char (9), bdate date, sex char(3), salary number(8,2), superssn char(9), dno varchar(8), constraint empPK primary key (ssn), constraint empsuperFRK foreign key (superssn) references employee (ssn)); 3. Add constraint to DEPARTMENT table alter table DEPARTMENT add (constraint departFRK foreign key (mgrssn) references employee(ssn)) 4. Add constraint to EMPLOYEE table alter table EMPLOYEE add (constraint empdnoFRK foreign key (dno) references department(dnumber))
d.	1. Create table DEPARTMENT: create table DEPARTMENT (dname varchar2(15) not null, dnumber varchar(8), mgrssn char(9), mgrstartdate date, constraint departPK primary key (dnumber), constraint departUK unique (dname), constraint departFRK foreign key (mgrssn) references employee (ssn) disable) 2. Create table EMPLOYEE: create table EMPLOYEE (name varchar2(19) not null, ssn char (9), bdate date, sex char(3), salary number(8,2), superssn char(9), dno varchar(8), constraint empPK primary key (ssn), constraint empsuperFRK foreign key (superssn) references employee (ssn), constraint empdnoFRK foreign key (dnodisable) 3. Add constraint to EMPLOYEE table alter table employee add (constraint empdnoFRK foreign key (dno) references department(dnumber)) 4. Enable the constraints: alter table EMPLOYEE enable constraint empsuperFRK; alter table DEPARTMENT enable constraint departFRK;

Reference no: EM131963325

Questions Cloud

Which got the greatest percent return on his investment : Which investor made the greatest profit? Which got the greatest percent return on his investment?

Explain how the suspense is resolved by the end of the play : For each example, explain how the suspense is resolved by the end of the play. Write your response in a paragraph of at least five sentences.

Particularly unique about the country of china : What cultural traits have you identified as particularly unique about the country of China?

How will foster resiliency of a community-intervention plan : How will you foster resiliency as part of a community-intervention plan? Include issues of gender, diversity, and ethics in your plan.

Calculate the average number of block accesses : CISC 660 - Calculate the average number of block accesses for a random retrieval and What is the number of B-+tree internal nodes at the next highest level need

Provide bad news from the company to the recipient : Assume your recipient has previously requested a review of the situation via email, letter, or personal meeting with management.

Explain what intrigues you about the given character : Explain what intrigues you about this character 3. Why does he or she stand out to you from the whole cast? Support your explanation with evidence from play.

What are other types of price discrimination : What are other types of price discrimination, their conditions and their relevance in a digital economy?

Should a project manager welcome or discourage an audit : Should a Project Manager welcome or discourage an audit of the project that they are running?

User Account

All Pages