West Bengal Institute of Technology (WBIT) 2009-5th Sem B.Tech Computer Science and Engineering Computer Science - Database Management System - Question Paper
Roll No. :................................................................. vv
Invigilator's Signature:...............................................
CS/B.Tech(CSE)/SEM-5/CS-502/2009-10 2009
Time Allotted : 3 Hours Full Marks : 70
The figures in the margin indicate full marks.
Candidates are required to give their answers in their own words
as far as practicable.
GROUP - A (Multiple Choice Type Questions)
1. Choose the correct alternatives for any ten of the following :
10 x 1 = 10
i) Cardinality ratio means '
a) number of attributes associated with an entity
b) number of entitles related with other entities via a relationship
c) number of entities in an entity set
d) ratio of number of columns and rows in a table.
11) The DML provides following function access to the database :
a) retrieve data and/or records
b) add ( or insert) records
c) delete records from database files
d) all of these.
a) decomposing a set of relations
b) successive reduction of relation schema
c) deciding which attributes in a relation to be grouped together
d) all of these.
iv) Given a relation R = { A, B, C} and set of functional dependencies F = { A - B, B - C } , if R is decomposed into two different relations JR1 = { A, B},
R2 = { B, C } , then the decomposition is
a) lossless join decomposition
b) dependency preserving
c) both (a) and (b)
d) none of these.
v) Which of the following is correct ?
a) An SQL query automatically eliminates duplicates
b) An SQL query will not work if there is no indices on the relations
c) SQL permits attribute names to be repeated in the same relation
d) None of these.
vi) The ability to modify the internal schema without causing any change to external schema is
a) external data independence
b) logical data independence
c) physical data Independence
d) internal data independence.
vii) In order to permanently remove all the data from the STUDENT table without changing its structure, you need to execute which of the following queries ?
a) DROP TABLE STUDENT
b) DELETE ALL FROM STUDENT
c) DROP ALL FROM STUDENT
d) DELETE FROM STUDENT.
viii) In order to add a foreign key constraint on the dept-id attribute in EMP table referring to the ID attribute in the DEPT table, you will use
a) ALTER TABLE command with ADD clause on DEPT talbe
b) ALTER TABLE command with ADD clause on EMP talbe
c) ALTER TABLE command with MODIFY clause on DEPT talbe
d) ALTER TABLE command with MODIFY clause' orv EMP talbe.
a) ALTER
b) DROP
d) SELECT.
c) CREATE
x) Which of the following is the way to undo the effects of an aborted transaction ?
a) Compensation transaction
b) Rollback
c) Recoveiy
d) Error control.
xi) View is a
a) temporary table b) virtual table
c) dynamic table d) permanent table.
GROUP - B (Short Answer Type Questions)
Answer any three of the following. 3 x 5 = 15
2. What is the difference between logical data independence and physical data independence ? What is a view ? What is the usefulness of a view ? Write an SQL query to create view name bank with the following attributes :
(accno, acname, balance).
2 + 3
3. Define BCNF. How does it differ from 3NF ? Why is it
considered a stronger from 3NF ?
5
CS/B.Tech(CSE)/SEM-5/CS-502/2009-10
4. Write SQL statements on the following tables :
SALESPEOPLE ( snum, sname, city, commission) CUSTOMERS ( cnum, cname, city, rating, snum)
ORDERS (onum, amt, odate, cnum, snum)
a) Show the commissions of all the salespersons who receive at least one order of amount greater tfrgn Rs. 5,000.
b) Find all customers located in cities where salesperson
Amit has customers. 2 x 2i
2
5. a) Explain with examples the terms Super key, Candidate
key and Primary key.
b) Who are the different database users ? 2 + 3
6. Discuss five main advantages of database management system over file management system. 5
GROUP - C (Long Answer Type Questions )
Answer any three of the following. 3 x 15 = 45
7. a) Design a generalization - Specialization hierarchy for a
motor-vehicle sales company. The company sells motorcycles, passenger cars, vans and buses. Justify your placement of attributes at eadi level of hierarchy. Explain why they should not be placed at a higher or lower level.
CS/B.Tech(CSE)/SEM-5/CS-502/2009-10
b) Define the concept of aggregation with a suitable example.
c) Define a foreign key. Why is the concept needed ? How does it play a role in the Join operation ?
d) What is difference between JOIN and OUTJOIN operation ? 5 + 3 + 4 + 3
8. a) Construct an E-R diagram for the following problem :
A store has different counters managed by different employees. A counter has different items, but no two counters have common items. Customers buy from different counters but bills are prepared at bill counter only. Once in a month performance of persons managing counters is evaluated in terms of sales. Items are also reviewed and slow-moving items are identified.
b) What is weak entity set ? Explain with suitable
example.
c) Discuss vertical and horizontal fragmentation. 8 + 3 + 4
9. a) Explain two-phase locking protocol.
b) Consider the following two transactions :
Tj : read (A) ;
read { B ) ;
if A = 0, then B : = B + 1 ;
write (B)
T2 : read ( B ) ;
read (A) ;
if B = 0, then A : = A + 1 ; write (A)
add lock and unlock instructions to transactions T1 and T2, so that they observe the two-phase locking
protocol. Can the execution of these transactions result in a deadlock ?
c) Distinguish between locking and timestamp protocols for concurrency controls. Explain multiversion two-phase locking. 4 + 6 + 5
10. a) Discuss insertion anomalies with an example. Suggest a method to overcome from it.
b) Given a relational schema Supply ( sno, city, status, pno, qty) with FD set
F = { sno -> city, city -> status, { sno, pno } - qty}
Find the key of the schema.
Also reduce it into 3NF.
c) Define MVD with suitable example.
d) Explain partial dependency and transitive dependency with examples. 3 + 6 + 3 + 3
HOTEL ( hotelno. name, address )
ROOM ( roomno. hotelno. type, price_pn )
BOOKING ( hotelno. guestno. dateform, dateto, roomno )
GUEST ( guestno. name, address )
where the underlined column names are primary keys.
a) Write down expressions in relational algebra for the following queries :
i) list all the hotels which Eire situated in Kolkata.
ii) list all single rooms with a charge below Rs. 1000 per night.
HI) list the names of all guests who are going to stay at ITC Hotel from 25th December to 1st January.
iv) list the price per night and type of all rooms at Grand Hotel.
v) list all guests currently staying at Taj Hotel.
b) Write down the expressions in tuple relational calculus for the following queries :
list name and address of hotels.
c) Write short notes on integrity constraints. 9 + 3 + 3
55701 8
|
Attachment: |
| Earning: Approval pending. |
