Midterm2

Question 1

There are five employees in table E = (First_name, Last_name). The names of the employees are ALIBEK, ALUA, ARNA, ASLAN and ASYLZHAN. If the following query is executed, which name(s) will you see as the result of it?
SELECT First_name
FROM E
WHERE First_name LIKE ‘ A%_L%’
a) ALIBEK, ALUA
b) ASLAN ASYLZHAN
c) NONE
d) ALUA
e) ALIBEK, ALUA, ASLAN, ASYLZHAN
f) ASLAN
g) ASYLZHAN
h) ALIBEK

Answer 1

b) ASLAN ASYLZHAN

Question 2

A transaction completes its execution is said to be in _____ state.
a) Temporary
b) Finished
c) Active
d) Ended
e) Committed

Answer 2

e) Committed

Question 3

Consider the table EMPLOYEE = (name, surname). Which of the following SQL commands can be used to delete column Surname from this table?
a) ALTER TABLE EMPLOYEE
DROP COLUMN Surname
b) MODIFY EMPLOYEE
DELETE Surname
c) DROP COLUMN Surname
FROM EMPLOYEE
d) DELETE COLUMN Surname
FROM EMPLOYEE
e) DROP Surname
FROM TABLE EMPLOYEE

Answer 3

a) ALTER TABLE EMPLOYEE
DROP COLUMN Surname

Question 4

4NF is specifically related to coping with?
a) Join dependencies
b) Inclusion dependencies
c) Multivalued dependencies
d) None
e) Functional dependencies

Answer 4

c) Multivalued dependencies

Question 5

Choose the correct order of the SQL commands below used in a query
a) SELECT, FROM, WHERE, HACING, GROUP BY, ORDER BY
b) SELECT, WHERE, FROM, HAVING, ORDER BY, GROUP BY
c) ORDER BY, SELECT, WHERE, FROM, HAVING, GROUP BY
d) SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY
e) SELECT, FROM, WHERE, HAVING, ORDER BY, GROUP BY

Answer 5

d) SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

Question 6

What is the SQL operator that applies a specific condition to rows grouped together based on identical values in certain columns?
a) HAVING
b) PARTITION BY
c) GROUP BY
d) DISTINCT
e) ORDER BY

Answer 6

a) HAVING

Question 7

Which of the followinf may not be correct about database triggers?
a) A trigger checks the condition specified in the trigger and it should run only if the condition is evaluated to be true
b) A database trigger is a procedure that starts automatically if specified changes take place or an event occurs
c) The action part in a trigger shows what will happen if the trigger runs
d) A common use of triggers is to maintain a database consistent and they are activated by a specific kind of statement (insert, delete, update)
e) Database triggers are introduced for querying databases more efficiently and they always increase the performance of the database.

Answer 7

e) Database triggers are introduced for querying databases more efficiently and they always increase the performance of the database.

Question 8

A typical trigger has the following three components?
a) Assertion, Index, Action
b) Condition, Index, Action
c) None
d) Event(s), Condition, Action
e) Event(s), Condition, Assertion

Answer 8

d) Event(s), Condition, Action

Question 9

Based on the discussion in our lectures, which one is NOT correct about materialized views?
a) If V is a materialized view, then V will be used as a table
b) Precomputed offline - faster at runtime
c) Used in data warehouses
d) Always up to date
e) All are correct

Answer 9

d) Always up to date

Question 10

Consider the following interleaved transactions to be executed as given. What kind of drastic probelm can occur, where R(A) stands for reading item A, W(A) stands for writing item A and C stands for Commit?
T1: R(A) R(A), W(A),C
T2: R(A), W(A), C
a) Unrepeatable read problem
b) None
c) Update problem
d) The temporary update (or dirty read) problem
e) Dirty write problem

Answer 10

a) Unrepeatable read problem

Question 11

Consider the following interleaved transactions to be executed as given. What kind of drastic probelm can occur, where R(A) stands for reading item A, W(A) stands for writing item A and C stands for Commit?
T1: R(A), W(A), R(A), W(A),Abort
T2: R(A), W(A), C
a) Unrepeatable read problem
b) None
c) Update problem
d) The temporary update (or dirty read) problem
e) Dirty write problem

Answer 11

d) The temporary update (or dirty read) problem

Question 12

Which of the following statements is incorrect about the inference rules for functional dependencies?
a) reflexitivity: if Y is-subset of X, then X -> Y
b) Decomposition: if XY -> Z, then X -> Z and Y -> Z
c) Union: if X -> Y and X -> Z, then X ->YZ
d) Augmentation: if X-> Y, then WX-> WY
e) Pseudo transitivity: If X -> Y and WY -> Z, then WX -> Z

Answer 12

b) Decomposition: if XY -> Z, then X -> Z and Y -> Z

Question 13

In SQL, which of the following keywords is used in SQL in the database autorization process?
a) ACCESS RIGHT
b) AUTHORIZE
c) PERMIT
d) SELECT
e) REVOKE

Answer 13

e) REVOKE

Question 14

What are ACID properties of Transactions?
a) Atomicity, Completeness. Inconsistenct, Data Independence
b) Atomicity, Consistency, Isolation, Durability
c) Atomicity, Completeness, Isolation, Data Independence
d) Atomicity, Completeness, Isolation, Durability
e) Atomicity, Concurrency, Isolation, Data Independence

Answer 14

b) Atomicity, Consistency, Isolation, Durability

Question 15

Please choose the correct option for three-valued logic
a) None
b) FALSE AND UNKNOWN = UNKNOWN
FALSE OR UNKNOWN = UNKNOWN
c) FALSE AND UNKNOWN = FALSE
FALSE OR UNKNOWN = FALSE
d) FALSE AND UNKNOWN = UNKNOWN
FALSE OR UNKNOWN = FALSE
e) FALSE AND UNKNOWN = FALSE
FALSE OR UNKNOWN = UNKNOWN

Answer 15

e) FALSE AND UNKNOWN = FALSE
FALSE OR UNKNOWN = UNKNOWN

Question 16

Based on the discussion in our lectures, which one is NOT among the reasons to use virual views?
a) All are correct
b) Make some queries easier / more natural
c) Improve performance of database queries
d) Hide some data frim some users (security)
e) Modularity of database access

Answer 16

c) Improve performance of database queries

Question 17

Which operator below can be used to perform pattern matching?
a) MATCH
b) BETWEEN
c) PATTERN
d) LIKE
e) SIMILAR

Answer 17

d) LIKE

Question 18

Which of the following statements about primary and secondary indexes in SQL is correct?
a) Primary indexes are automatically created for each column in the table.
b) Secondary indexes are created on foerign key columns only
c) A primary key is an example of a secondary index
d) A primary index is created by default on the table’s primary key
e) Primary indexes have no impact on query performance.

Answer 18

d) A primary index is created by default on the table’s primary key

Question 18

Let STUDENT = (Sid, Sname, Major) be a table. Which SQL commands can be used to motify the current Major of the student with Sid = 10 to ‘CS’?
a) UPDATE STUDENT
SET Major = ‘CS’
WHERE Sid = 10
b) None
c) UPDATE STUDENT
WHERE Sid = 10
SET Major = ‘CS’
d) MODIFY STUDENT
SET Major = ‘CS’
WHERE Sid = 10
e) SET STUDENT.Major = ‘CS’
WHERE Sid = 10

Answer 18

d) MODIFY STUDENT
SET Major = ‘CS’
WHERE Sid = 10

Question 19

In addition to reducing data redundancy and avoiding undesirable characteristics, normatlization does not aim to eliminate _____?
a) Deletion anomalies
b) Spurious tuples
c) Update anomalies
d) Insertion anomalies
e) Possible system performance problems

Answer 19

e) Possible system performance problems

Question 20

BCNF is directly related to _______
a) Temporal dependency
b) Join dependency
c) Functional dependency
d) Multivalued dependency
e) Inclusion dependency

Answer 20

c) Functional dependency

Question 21

What is the primary purpoes of the HAVING clause in SQL?
a) To filter rows before grouping
b) To sort the result set in ascending or descending order
c) To specify conditions for joining tables
d) To filter rows based on conditions in a subquery
e) To filter rows after grouping and applying aggregate functions

Answer 21

e) To filter rows after grouping and applying aggregate functions

Question 22

When a relation is in 2NF and there is ______, it also satisfies to be in 3NF.
a) No Partial Dependency
b) None
c) No Temporal Dependency
d) No Transitive Dependency
e) No Relational Dependency

Answer 22

d) No Transitive Dependency

Question 23

What is not TRUE about BCNF?
a) In some cases when we normalize a relation to be in 3NF, it also satisfies BCNF
b) BCNF is stricter than 3NF
c) For any functional dependency of X-> Y in the table, X is superkey
d) Database in BCNF may end up with a loss of some funtional dependencies
e) Database in BCNF results in more efficient database querying than that in 3NF

Answer 23

e) Database in BCNF results in more efficient database querying than that in 3NF (not 100% sure)