Pick the correct answer(s).

1. If R and S be the two relational schema, the result obtained from the operation R ∩ S is
equivalent to the operation
[ ] R-(R-S) [ ] RU(RUS) [ ] RU(R-S) [ ] R-(RUS)

2. For a union operation to be valid between two schema R and S one of the following
condition is not necessary.
[ ] The relations R and S must be of the same arity.
[ ] The relation R and S must always be the database relation not the temporary relation
that has been resulted from relational algebra expression.
[ ] The domains of the ith attribute of R and the ith attribute of the S must be the same,
for all i.
[ ] Both the schema must have the same number of attributes.

3. A CASE SQL statement is which of the following?

[ ] A way to establish an IF-THEN-ELSE in SQL.
[ ] A way to establish a loop in SQL.
[ ] A way to establish a data definition in SQL.
[ ] All of the above.

4. What SQL command can be used to delete columns from a table?

[ ] MODIFY TABLE TableName DROP COLUMN ColumnName
[ ] MODIFY TABLE TableName DROP ColumnName
[ ] ALTER TABLE TableName DROP COLUMN ColumnName
[ ] ALTER TABLE TableName DROP ColumnName

5. To update an SQL view, the DBMS must be able to associate the column(s) to be updated
with:
[ ] a particular column in a particular underlying table.
[ ] a particular column in a particular row.
[ ] a particular row in a particular underlying table.
[ ] a particular row in a particular column.

6. When an entity instance may be a member of multiple subtypes or it does not have to be a
member of a subtype, it is which of the following?
[ ] Disjoint with total specialization
[ ] Disjoint with partial specialization
[ ] Overlap with total specialization
[ ] Overlap with partial specialization
7. In which scenario would index be most useful?
[ ] The indexed column is declared as NOT NULL.
[ ] The indexed columns are used in the FROM clause.
[ ] The indexed columns are part of an expression.
[ ] The indexed column contains a wide range of values.

8. Relational calculus is a
[ ] Procedural language. [ ] Non- Procedural language.
[ ] Data definition language. [ ] High level language.

9. is an abstraction in which relationship sets (along with their associated entity

sets) are treated as higher-level entity sets, and can participate in relationships
[ ] aggregation [ ] specialization [ ] generalization [ ] recursion

10. Transaction processing was made possible by the development of

[ ] Direct access storage [ ] magnetic tape
[ ] cache register input [ ] minicomputers

11. A relational schema R is in 3rd normal form with respect to a set F of functional
dependencies if, for all functional dependencies in F+ of the form α→β, where α⊆β and
β⊆R, at least one of the following never holds.
[ ] α→β is a trival functional dependency
[ ] α is a superkey for R
[ ] Each attribute A in β- α is functionally dependent on α
[ ] Each attribute A in β- α is contained in a candidate key for R

12. Either all operations of the transaction are reflected properly in the database or none
means
[ ] Atomicity [ ] Consistency [ ] Isolation [ ] Durability

13. In a index, the index must store a list of pointers to all records with the same
search-keyvalue.
[ ] dense clustering [ ] dense non-clustering
[ ] sparse clustering [ ] sparse non-clustering

14. A failed state transactions can enter to the aborted state and can restart the transaction iff
[ ] the transaction was aborted as a result of some hardware or software error that was
not created through the internal logic.
[ ] the transaction was aborted as a result of some internal logical error that can be
corrected only be rewriting the application program.
[ ] the transaction was aborted as a result of recurrent error
[ ] the transaction was aborted as a result of some complex problem which has to be
revised on timely.

15. Embedded SQL is which of the following?

[ ] Hard-coded SQL statements in a program language such as Java.
[ ] Process of making an application capable of generating specific SQL code on the fly.
[ ] Hard-coded SQL statements in a procedure.
[ ] Hard-coded SQL statements in a trigger.
16. Which of the following is correct?
[ ] A SQL query automatically eliminates duplicates.
[ ] SQL permits attribute names to be repeated in the same relation.
[ ] A SQL query will not work if there are no indexes on the relations
[ ] None of these

17. What does the TRUNCATE statement do?

[ ] Removes the table [ ] Removes all rows from a table
[ ] Shortens the table to 10 rows [ ] Removes all columns from a table

18. An entity in A is associated with at most one entity in B. An entity in B, however, can be
associated with any number (zero or more) of entities in A is defined by relation.
[ ] One to One [ ] One to Many [ ] Many to One [ ] Many to Many

19. How do you define the cardinality for the following relation?

Entity A Relates Entity B

[ ] One to One [ ] One to Many [ ] Many to One [ ] Many to Many

20. In a SELECT statement that includes a HAVING clause, where is the GROUP BY clause
placed in the SELECT statement?
[ ] Before the WHERE clause [ ] Before the HAVING clause
[ ] After the HAVING clause [ ] Before the ORDER BY clause
 [2Q  8 = 16 marks]
Answer ANY TWO questions.

1. Explain the different design issues in the ER design of any system.

2. Explain with examples how ACID properties in transaction management system make
the transaction robust in RDBMS.

3. Normalize the following schema up to BCNF form.

R(Project_code,Project_title,Project_Manager,Project_budget,Emp_no,Emp_name,
Dept_no, Dept_name,Hourly_rate).

Dependencies:
Project_code,Project_Title→Project_budget,
Project_code,Emp_no→Hourly_rate
Emp_no→Ename,Dept_no
Dept_no→Dept_name
Emp_no,Project_code→Project_manager
Project_code→Project_title

SECTION “C”
[6Q  4 = 24 marks]

Attempt ANY SIX questions.

4. What do you mean by database schema? How do you differentiate it with database
instances? Explain physical independence in database system.

5. What type of role does the key implementation plays in Relational Database system?
Explain with examples.

6. Explain the term functional dependency and Transitive closure.

7. What do you understand by serializability? Explain how Two-phase locking protocol

ensureserializability.

8. What type of problems may arise in distributed database system? Explain with examples.
9. Why do database system support concurrent execution of transactions, in spite of the
extra programming effort needed to ensure that concurrent execution does not cause any
problems?

10. With the help of the schema describe below express the query(a ,b) in either relational
algebraic form or in SQL.

Instructor(ID,name.dept_name,salary)
Section(course_id,section_id,semester,year,building,room_num,time_s
lot_in)

a. Give a 5 percent salary raise to the instructors whose salary is less than average
salary.
b. Find all the courses taught in the fall 2012 semester but not in spring 2013 semester.