ITPEC FE Databases Questions with Answers — Page 2

Databases

77 questions · Fundamental Engineering · 51–77

Oct 2021 · Question 26

Tables Course and Section were created to record the course and section information of a university, respectively, as shown below; the primary keys are underlined.

Course (cid, title, credits)

Section (cid, secid, semester, year)

The current status of those tables are shown below.

Course

cid	title	credits
CSE101	Discrete Mathematics	3
CSE102	Computer Prog. I	3
CSE103	Computer Prog. II	3
EEE101	Electrical Circuits I	4
EEE102	Electrical Circuits II	4

Section

cid	secid	semester	year
CSE101	1	Spring	2018
CSE101	1	Spring	2019
CSE101	2	Fall	2019
CSE102	1	Fall	2018
CSE102	2	Fall	2018
CSE103	1	Spring	2019
CSE103	2	Fall	2019
EEE101	1	Spring	2019
EEE102	1	Spring	2019
EEE102	1	Fall	2019

When the SQL shown below is executed, which of the following tables is obtained as the output?

SELECT C.title

FROM Course C

WHERE 1 = (SELECT COUNT(cid)

FROM Section S

WHERE C.cid = S.cid AND S.year = 2019);

a)b)c)d)

Oct 2021 · Question 27

Which of the following is an appropriate explanation of an E-R diagram?

a)A relationship is expressed by describing the related entity name in the entity type.b)The relationship between entity types is expressed by an arrow from the referencing side in the direction of the referenced side.c)There are no attributes in the entity type, but the relationship type has attributes.d)There are several kinds of relationships between entity types such as one-to-many or many-to-many.

Oct 2021 · Question 28

Which of the following is the main purpose of transaction support in a database management system?

a)To ensure that either all the updates corresponding to a given transaction are made or none of them are madeb)To ensure that only authorized users can access the databasec)To help users update data by providing a graphical user interfaced)To provide an accessible catalog in which descriptions of data items are stored

Oct 2021 · Question 58

In the event of a disk failure, which of the following is a method for recovering a database by restoring a full backup data onto a disk from a tape, and then reflecting, from logs, post-update copies after the full backup was taken?

a)Checkpoint restartb)Rebootc)Roll forwardd)Rollback

Apr 2020 · Question 27

Which of the following is the database function that is automatically executed when a specific action such as update, delete, or insert occurs within a database?

a)Cursorb)Stored procedurec)Triggerd)Update query

Apr 2020 · Question 28

A sequence of two relational algebra expressions is shown below.

T₁ ← π_Y(R)

T ← T₁ − π_Y((S × T₁) − R)

Here, “π_Y”, “×”, and “−” represent projection, direct product, and difference, respectively. When the relational states of R and S are as follows, which of the following can be obtained as T?

X	Y
1	A
2	A
2	B

X
1
2

a)b)c)d)

Apr 2020 · Question 29

There are three tables, EMPLOYEE, PROJECT, and WORK_PROJ for recording employees, projects, and working information of employees on projects respectively. When the SQL statement shown below is executed for these tables, which of the following is generated as the output?

EMPLOYEE

EID	ENAME
1	Rahbar
2	Karthik
3	Abir

PROJECT

PID	PNAME
1	Construction
2	Land Purchase

WORK_PROJ

EID	PID	HOURS
1	1	20
1	2	10
2	1	40
3	1	20
3	2	10

[SQL Statement]

SELECT ENAME FROM EMPLOYEE

WHERE NOT EXISTS

((SELECT PID FROM PROJECT)

EXCEPT

(SELECT PID FROM WORK_PROJ

WHERE WORK_PROJ.EID = EMPLOYEE.EID))

a)b)c)d)

Apr 2020 · Question 30

In a database system, which of the following is the action to undo changes done by transactions executed after the last commit?

a)Commitb)Roll backc)Roll forwardd)View

Oct 2020 · Question 25

Which of the following is the key of the relation schema, R (A, B, C, P, Q, T), when R has the functional dependencies shown below?

A→B

A→C

CP→Q

CP→T

a)Ab)A, Bc)A, Pd)C, P

Oct 2020 · Question 26

The employee’s ID, name, salary, manager ID, and working department are recorded in the Employees table as follows:

Employees

Emp_ID	Emp_name	Salary	Manager_ID	DID
10	Amit	50000	18	3
11	Vikrom	75000	16	2
12	Nishi	40000	18	3
13	Niloy	60000	17	1
14	Pritom	80000	18	3
15	Mohitlal	45000	18	3
16	Rahman	90000	null	1
17	Roxy	55000	16	2
18	Santosh	65000	17	1

When a query is formulated in SQL to retrieve the manager ID and the average salary of the employees under his/her direct report, and the output is obtained as below, which of the following is the appropriate combination to be inserted in blanks E and F in the SQL statement?

select E as "Manager_ID",

avg(a.Salary) as "Average_Salary"

from Employees a, Employees b

where E = F

group by E

order by E

Manager_ID	Average Salary
16	65000
17	62500
18	53750

a)a.DIDb.DIDb)a.Emp_IDb.Emp_IDc)a.Manager_IDb.Manager_IDd)b.Emp_IDa.Manager_ID

Oct 2020 · Question 28

When a failure occurs in a storage unit that stores a database, which of the following is an operation that can recover the database by using backup files and a log?

a)Archiveb)Checkpoint dumpc)Commitd)Rollforward

Oct 2020 · Question 29

Which of the following is an appropriate explanation of the granularity of locks?

a)When a large number of transactions update the same data, and the granularity is set as large, the number of transactions that can be simultaneously executed increases.b)When all data in a table are referenced, and the granularity is set as large, data referenced from other transactions can be undisturbed.c)When data are updated, and the granularity is set as large, the waiting times of other transactions increase, and the total throughput declines.d)When the granularity is set as large, the number of included data increases, and the number of locks that one transaction makes increases.

Oct 2019 · Question 29

Which of the following is an SQL statement that gives the same result as the SQL statement that is described below for the “Product” table and the “Inventory” table? Here, the underlined part indicates the primary key.

SELECT ProductNumber FROM Product

WHERE ProductNumber NOT IN (SELECT ProductNumber FROM Inventory)

Product

ProductNumber	ProductName	UnitPrice

Inventory

WarehouseNumber	ProductNumber	InventoryQuantity

a)SELECT ProductNumber FROM Product WHERE EXISTS (SELECT ProductNumber FROM Product)b)SELECT ProductNumber FROM Inventory WHERE NOT EXISTS (SELECT ProductNumber FROM Product)c)SELECT ProductNumber FROM Product WHERE EXISTS (SELECT ProductNumber FROM Inventory WHERE Product.ProductNumber = Inventory.ProductNumber)d)SELECT ProductNumber FROM Product WHERE NOT EXISTS (SELECT ProductNumber FROM Inventory WHERE Product.ProductNumber = Inventory.ProductNumber)

Oct 2019 · Question 31

Which of the following is an appropriate description of the lock operation that is used for the concurrency control of a transaction?

a)For a resource on which a shared lock is acquired, the acquisition of a new shared lock by another transaction is permitted.b)For a resource on which a shared lock is acquired, the acquisition of a new exclusive lock by another transaction is permitted.c)For a resource on which an exclusive lock is acquired, the acquisition of a new shared lock by another transaction is permitted.d)For a resource on which an exclusive lock is acquired, the acquisition of a new exclusive lock by another transaction is permitted.

Apr 2019 · Question 27

In an SQL statement, which of the following is a constraint that is specified with FOREIGN KEY and REFERENCES?

a)Assertionb)Check constraintc)Key constraintd)Referential constraint

Apr 2019 · Question 28

In a client/server system, which of the following is the mechanism that reduces the network load between the client and server by placing the frequently used commands on the DBMS on the server in advance?

a)Group commitmentb)Multithreading of server processesc)Stored procedured)Two-phase commitment

Apr 2019 · Question 29

Which of the following is a characteristic to guarantee that the result of an update transaction is either performed completely or canceled as if nothing happened?

a)Atomicityb)Consistencyc)Durabilityd)Isolation

Apr 2019 · Question 30

Which of the following is the process that is executed periodically to prevent reducing the access efficiency of the database?

a)Backupb)Database dumpc)Reorganizationd)Roll back

Oct 2018 · Question 27

“a → b” represents the fact that when the value of attribute a is determined, the value of attribute b is determined uniquely. For example, “Employee number → Employee name” represents that when the employee number is determined, the employee name is determined uniquely. Based on this notation, when the relations between attributes a through j are established as shown in the figure below, which of the following is an appropriate combination of three (3) tables that defines the relations in a relational database?

a)Table 1 (a)Table 2 (b, c, d, e)Table 3 (f, g, h, i, j)b)Table 1 (a, b, c, d, e)Table 2 (b, f, g, h)Table 3 (e, i, j)c)Table 1 (a, b, f, g, h)Table 2 (c, d)Table 3 (e, i, j)d)Table 1 (a, c, d)Table 2 (b, f, g, h)Table 3 (e, i, j)

Oct 2018 · Question 28

The data model in the diagram below is implemented with three (3) tables. Which of the following is an appropriate combination of A and B in table “Transfer” that contains the record that indicates “500 dollars sales to Company X are posted to the cash account on April 4, 2017”? Here, the data model is described in UML.

Account

AccountCode	AccountTitle
208	Sales
510	Cash
511	Deposits
812	Travel expenses

Transfer

AccountCode	Debit/Credit	Amount	TransactionNumber
A	B	500	0122
208	Credit	500	0122
510	Credit	500	0124
812	Debit	500	0124

AccountingTransaction

TransactionNumber	DateOfPosting	Description
0122	2017-04-04	Company X
0124	2017-04-04	Company X

a)208Creditb)208Debitc)510Creditd)510Debit

Oct 2018 · Question 29

After relations X and Y are joined, which of the following is (are) the relational algebra operation(s) to obtain relation Z?

StudentNumber	Name	FacultyCode
1	Amy White	A
2	Bob Green	B
3	Cathy Black	A
4	David Grey	B
5	Edward Brown	A
6	Frank Blue	A

FacultyCode	FacultyName
A	Engineering
B	Information
C	Literature

FacultyName	StudentNumber	Name
Information	2	Bob Green
Information	4	David Grey

a)Projection and selectionb)Projection and unionc)Selectiond)Selection and union

Oct 2018 · Question 30

In a DBMS, when multiple transaction programs update the same database simultaneously, which of the following is a technology that is used to prevent logical contradictions?

a)Exclusive controlb)Integrity constraintc)Normalizationd)Reorganization

Apr 2018 · Question 26

Which of the following properties of database transactions refers to the ability of the system to recover a committed transaction when either the system or the storage media fails?

a)Atomicityb)Consistencyc)Durabilityd)Isolation

Apr 2018 · Question 27

There is a “Delivery” table that has six (6) records. Which of the following is the functional dependency that is satisfied by this table? Here, “X → Y” indicates that X functionally determines Y.

Delivery

Delivery_date	Department_ID	Department_name	Delivery_destination	Component_ID	Quantity
2016-08-21	300	Production department 2	Chicago office	1342	300
2016-08-21	300	Production department 2	Chicago office	1342	300
2016-08-25	400	Production department 1	Boston factory	2346	300
2016-08-25	400	Production department 1	Boston factory	2346	1,000
2016-08-30	500	Research and development department	Boston factory	2346	30
2016-08-30	500	Research and development department	New York office	1342	30

a)Delivery_date → Component_IDb)Delivery_destination → Component_IDc)Department_ID → Component_IDd)Department_name → Delivery_destination

Apr 2018 · Question 28

Which of the following methods to join two (2) tables in an RDBMS is a description of the sort-merge join method?

a)All rows in the tables are re-ordered according to the values in the column to be joined, and they are joined in order from the top.b)If a column to be joined in one of the tables is included in the index, the column to be joined in the other table and the index value are used to join the tables.c)The values in a column to be joined in one of the tables are extracted in order, and they are joined with the column to be joined from the other table.d)The values in a column to be joined in one of the tables are used to create a hash table, and this is joined with the column to be joined from the other table.

Apr 2018 · Question 29

When the SQL statement shown below is executed on the “Employee” table and the “Department” table, which of the following is the result?

SELECT COUNT(*) FROM Employee, Department

WHERE Employee.Department = Department.Department_name AND

Department.Floor = 2

Employee

Employee_number	Department
11001	Administration
11002	Accounting
11003	Sales
11004	Sales
11005	Information systems
11006	Sales
11007	Planning
12001	Sales
12002	Information systems

Department

Department_name	Floor
Planning	1
Administration	1
Information systems	2
Sales	3
Accounting	2
Legal affairs	2
Procurement	2

a)1b)2c)3d)4

Apr 2018 · Question 30

In a distributed database system, which of the following is a method for the finalization of update processing when an inquiry is made to multiple sites that perform a series of transaction processes as to whether finalization is possible or not and whether finalization is possible at all sites?

a)Exclusive controlb)Roll backc)Roll forwardd)Two-phase commitment