Engineering Questions with Answers - Multiple Choice Questions
Database MCQ – Transformation of Relational Expressions
1 - Question
Consider the following relational schemes for a library database:
Book (Title, Author, Catalog_no, Publisher, YEAR, Price)
Collection (Title, Author, Catalog_no)
WITH the following functional dependencies:
I. Title Author -> Catalog_no
II. Catalog_no -> Title Author Publisher YEAR
III. Publisher Title YEAR -> Price
Assume {Author, Title} is the key for both schemes. Which of the following statements is true?
a) Both Book and Collection are in BCNF
b) Both Book and Collection are in 3NF only
c) Book is in 2NF and Collection is in 3NF
d) Both Book and Collection are in 2NF only
View Answer
Answer: c
Explanation: The relation Collection is in BCNF: Its given that {Author, Title} is the key and there is only one functional dependency (FD) applicable to the relation Collection {i.e. Title Author –> Catalog_no}.
2 - Question
Let R(A,B,C,D,E,P,G) be a relational schema in which the following FDs are known to hold:
AB->CD
DE->P
C->E
P->C
B->G
The relation schema R is
a) in BCNF
b) in 3NF, but not in BCNF
c) in 2NF, but not in 3NF
d) not in 2NF
View Answer
Answer: d
Explanation: From the closure set of attributes we can see that the key for the relation is AB. The FD B->G is a partial dependency, hence it is not in 2NF.
3 - Question
Which of the following is/are false for RAW mode of FOR XML?
a) XMLSCHEMA option does not returns an in-line XSD schema
b) BINARY BASE32 returns the binary data in base32-encoded format
c) Each row in the query result is transformed into an XML element
d) None of the mentioned
View Answer
Answer: b
Explanation: XML was designed to transport and store data.
4 - Question
___________ refers to the ability of the system to recover committed transaction updates if either the system or the storage media fails.
a) Isolation
b) Atomicity
c) Consistency
d) Durability
View Answer
Answer: d
Explanation: In database systems, durability is the ACID property which guarantees that transactions that have committed will survive permanently.
5 - Question
Which utilities can we use to export data from sql server to a text file?
a) DTS export wizard
b) BCP
c) ISQL
d) DTS export wizard and BCP
View Answer
Answer: d
Explanation: The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.
6 - Question
You have a column that will only contain values from 0 to 256. What is the most economical data type to use for the column?
a) TINYINT
b) SMALLINT
c) INT
d) DECIMAL(1)
View Answer
Answer: b
Explanation: The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format.
7 - Question
Problems occurs if we don’t implement a proper locking strategy
a) Dirty reads
b) Phantom reads
c) Lost updates
d) Unrepeatable reads
View Answer
Answer: b
Explanation: Phantom reads occur when an insert or delete action is performed against a row that belongs to a range of rows being read by a transaction.
8 - Question
Which of the following fixed database roles can add or remove user IDs?
a) db_accessadmin
b) db_securityadmin
c) db_setupadmin
d) db_sysadmin
View Answer
Answer: a
Explanation: The db_accessadmin role manages security, but handles access to the database, as the name implies.
9 - Question
By default sql server has ___________ isolation level
a) READ COMMITTED
b) READ UNCOMMITTED
c) SERIALIZABLE
d) REPEATABLE READ
View Answer
Answer: a
Explanation: READ UNCOMMITTED is the most optimistic concurrency isolation option available in SQL Server.
10 - Question
Which of the following pair of regular expression are not equivalent?
a) 1(01)* and (10)*1
b) x(xx)* and (xx)*x
c) (ab)* and a*b*
d) x+ and x*x+
View Answer
Answer: c
Explanation: (ab)*=(a*b*)*.