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

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 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

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 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.