Ask DBMS Expert


Home >> DBMS

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;


DBMS, Programming

  • Category:- DBMS
  • Reference No.:- M92800982
  • Price:- $75

Guranteed 36 Hours Delivery, In Price:- $75

Have any Question?


Related Questions in DBMS

Data mining assignment -in this assignment you are asked to

Data Mining Assignment - In this assignment you are asked to explore the use of neural networks for classification and numeric prediction. You are also asked to carry out a data mining investigation on a real-world data ...

Sql query assignment -for this assignment you are to write

SQL Query Assignment - For this assignment you are to write your answers in a word document. This assignment is in three parts: Part A (reporting queries), Part B (query performance), Part C (query design). For this assi ...

The groceries datasetimagine 10000 receipts sitting on your

The groceries Dataset Imagine 10000 receipts sitting on your table. Each receipt represents a transaction with items that were purchased. The receipt is a representation of stuff that went into a customer's basket. That ...

You are in a real estate business renting apartments to

You are in a real estate business renting apartments to customers. Your job is to define an appropriate schema using SQL DDL in MySQL. The relations are Property(Id, Address, NumberOfUnits), Unit(ApartmentNumber, Propert ...

Objectivethe objective of this lab is to be familiar with a

OBJECTIVE: The objective of this lab is to be familiar with a process in big data modeling. You're required to produce three big data models using the MS PowerPoint software. This tool is available on UMUC Virtual Deskto ...

The relation memberstudentid organizationid roleid stores

The relation Member(StudentId, OrganizationId, RoleId) stores the membership information of student joining organization. For example, ('S1', 'O2', 'R3') indicates that student with Id 'S1' joined the organization with i ...

Relational database exerciseyou have been assigned to a new

Relational Database Exercise: You have been assigned to a new development team. A client is requesting a relational database system to manage their present store with the anticipation of adding more stores in the future. ...

Relational database design a given the following business

Relational Database Design A) Given the following business rules, identify entity types, attributes (at least two attributes for each entity, including the primary key) and relationships, and then draw an Entity-Relation ...

We can represent a data set as a collection of object nodes

We can represent a data set as a collection of object nodes and a collection of attribute nodes, where there is a link between each object and each attribute, and where the weight of that link is the value of the object ...

Data model development and implementationpurpose of the

Data model development and implementation Purpose of the assessment (with ULO Mapping) The purpose of this assignment is to develop data models and map Database System into a standard development environment to gain unde ...

  • 4,153,160 Questions Asked
  • 13,132 Experts
  • 2,558,936 Questions Answered

Ask Experts for help!!

Looking for Assignment Help?

Start excelling in your Courses, Get help with Assignment

Write us your full requirement for evaluation and you will receive response within 20 minutes turnaround time.

Ask Now Help with Problems, Get a Best Answer

Why might a bank avoid the use of interest rate swaps even

Why might a bank avoid the use of interest rate swaps, even when the institution is exposed to significant interest rate

Describe the difference between zero coupon bonds and

Describe the difference between zero coupon bonds and coupon bonds. Under what conditions will a coupon bond sell at a p

Compute the present value of an annuity of 880 per year

Compute the present value of an annuity of $ 880 per year for 16 years, given a discount rate of 6 percent per annum. As

Compute the present value of an 1150 payment made in ten

Compute the present value of an $1,150 payment made in ten years when the discount rate is 12 percent. (Do not round int

Compute the present value of an annuity of 699 per year

Compute the present value of an annuity of $ 699 per year for 19 years, given a discount rate of 6 percent per annum. As