Midterm2 Flashcards

1
Q

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

A

b) ASLAN ASYLZHAN

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

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

A

e) Committed

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

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

A

a) ALTER TABLE EMPLOYEE
DROP COLUMN Surname

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

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

A

c) Multivalued dependencies

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

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

A

a) HAVING

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

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.

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

d) Event(s), Condition, Action

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

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

A

d) Always up to date

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

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

A

a) Unrepeatable read problem

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

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

A

d) The temporary update (or dirty read) problem

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

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

A

e) REVOKE

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

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

A

b) Atomicity, Consistency, Isolation, Durability

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

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

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

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

A

c) Improve performance of database queries

17
Q

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

A

d) LIKE

18
Q

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.

A

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

18
Q

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

A

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

19
Q

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

A

e) Possible system performance problems

20
Q

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

A

c) Functional dependency

21
Q

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

A

e) To filter rows after grouping and applying aggregate functions

22
Q

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

A

d) No Transitive Dependency

23
Q

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

A

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