Database Set 4 (30 mcqs)

1. 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

2. 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

3. The completeness constraint may be one of the following: Total generalization or specialization , Partial generalization or specialization . Which is the default ?
a) Total
b) Partial
c) Should be specified
d) Cannot be determined

4. ___________ 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

5. Which utilities can we used to export data from sql server to a text file?
a) DTS export wizard
b) BCP
c) ISQL
d) a and b

6. 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)

7. Problems occurs if we don’t implement proper locking strategy
a) Dirty reads
b) Phantom reads
c) Lost updates
d) Unrepeatable reads

8. 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

9. By default sql server has ___________ isolation level
a) READ COMMITTED
b) READ UNCOMMITTED
c) SERIALIZABLE
d) REPEATABLE READ

10. 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+

Consider the following relational schemas and answer the questions below
The section relation
Course_id    Sec_id    Semester    Year    Building
BIO-101    1    Spring    2010    Painter
CS-102    4    Summer    2009    Packyard
EE-201    3    Fall    2010    Watson
FIN-301    1    Spring    2011    Richard

The teaches relation
Id    Course_id    Sec_id    Semester    Year
1001    CS-101    1    Fall    2009
1002    EE-201    2    Spring    2010
1003    FIN-301    3    Fall    2009
1004    BIO-101    1    Summer    2011

11. Which one of the following can be treated as a primary key in teaches relation ?
a) Id
b) Semester
c) Sec_id
d) Year

12. The primary key in the section relation is
a) Course_id
b) Sec_id
c) Both a and b
d) All the attributes

13. Select * from teaches where Sec_id = ‘CS-101′ ;
Which of the following Id is selected for the following query ?
a) 1003
b) 1001
c) None
d) Error message appears

14. Select Id,Course_id,Building from section s and teaches t where t.year=2009 ;
Which of the following Id are displayed ?
a) 1003
b) 1001
c) Both a and b
d) Error message appears

15. The query which selects the Course_id ‘CS-101′ from the section relation is
a) Select Course_id from section where Building = ‘Richard’;
b) Select Course_id from section where Year = ’2009′;
c) Select Course_id from teaches where Building = ‘Packyard’;
d) Select Course_id from section where Sec_id = ’3′;

16. Create table section
(Course_id varchar (8),
Sec_id varchar (8),
Semester varchar (6),
Year numeric (4,0),
Building numeric (15),
primary key (course id, sec id, semester, year),
foreign key (course id) references course);
Which of the following has an error in the above create table for the relation section
a) Primary key (course id, sec id, semester, year)
b) Foreign key (course id) references course
c) Year numeric (4,0)
d) Building numeric (15)

17. The relation with primary key can be created using
a) Create table instructor ( Id , Name)
b) Create table instructor ( Id , Name , primary key(name))
c) Create table instructor ( Id , Name , primary key (Id))
d) Create table instructor ( Id unique, Name )

18. How can the values in the relation teaches be deleted ?
a) Drop table teaches;
b) Delete from teaches;
c) Purge table teaches;
d) Delete from teaches where Id =’Null’;

19. In the above teaches relation ” Select * from teaches where Year = ’2010′” displays how many rows ?
a) 2
b) 4
c) 5
d) 1

20. The relation changes can be got back using ________ command
a) Flashback
b) Purge
c) Delete
d) Getback

21. A system is in a ______ state if there exists a set of transactions such that every transaction in the set is waiting for another transaction in the set.
a) Idle
b) Waiting
c) Deadlock
d) Ready

22. The deadlock state can be changed back to stable state by using _____________ statement.
a) Commit
b) Rollback
c) Savepoint
d) Deadlock

23. What are the ways of dealing with deadlock ?
a) Deadlock prevention
b) Deadlock recovery
c) Deadlock detection
d) All of the mentioned

24. When transaction Ti requests a data item currently held by Tj , Ti is allowed to wait only if it has atimestamp smaller than that of Tj (that is, Ti is older than Tj ). Otherwise, Ti is rolled back (dies). This is
a) Wait-die
b) Wait-wound
c) Wound-wait
d) Wait

25. When transaction Ti requests a data item currently held by Tj , Ti is allowed to wait only if it has atimestamp larger than that of Tj (that is, Ti is younger than Tj ). Otherwise, Tj is rolled back (Tj is wounded by Ti ). This is
a) Wait-die
b) Wait-wound
c) Wound-wait
d) Wait

26. The situation where the lock waits only for a specified amount of time for another lock to be released is
a) Lock timeout
b) Wait-wound
c) Timeout
d) Wait

27. The deadlock in a set of transaction can be determined by
a) Read-only graph
b) Wait graph
c) Wait-for graph
d) All of the mentioned

28. A deadlock exists in the system if and only if the wait-for graph contains a ___________.
a) Cycle
b) Direction
c) Bi-direction
d) Rotation

29. Consider the employee work-team example, and assume that certain employees participate in more than one work team . A given employee may therefore appear in more than one of the team entity sets that are lower level entity sets of employee. Thus, the generalization is _____________.
a) Overlapping
b) Disjointness
c) Uniqueness
d) Relational

30. __________ rollback requires the system to maintain additional information about the state of all the running transactions.
a) Total
b) Partial
c) Time
d) Commit

Answers
1-c 2-d 3-b 4-d 5-d
6-b 7-b 8-a 9-a 10-c
11-a 12-c 13-d 14-c 15-b
16-d 17-c 18-b 19-a 20-a
21-c 22-b 23-d 24-a 25-c
26-a 27-a 28-a 29-a 30-b

Sharing is caring!

Leave a Comment

Your email address will not be published. Required fields are marked *

shares