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