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 |