E D1071 Pages: 4

TRIP(Ssn, FromCity, ToCity, DepartureDate, ReturnDate, TripId)
EXPENSE(TripId, AccountNo, Amount)
a) A trip can be charged to one or more accounts. Specify the foreign keys for this (3)
schema, stating any assumptions you make.
b) Write relation algebra expression to get the details of salespersons who have travelled (3)
between Mumbai and Delhi and the travel expense is greater that Rs. 50000.
c) Write relation algebra expression to get the details of salesperson who had incurred (3)
the greatest travel expenses among all travels made.
PART C
Answer all questions, each carries 3 marks.
8 With the help of an example, illustrate the use of SQL TRIGGER. (3)
9 List the basic data types available for defining attributes in SQL? (3)
10 Consider a relation R={A,B,C,D,E,F} and a set of functional dependencies (3)
F={A>BC,C->BD,BF->E,F-D}. Find the closure of A. Is Aa candidate key?
Justify.
11 What are fully functional dependencies and partial functional dependencies? Give ೩೫ (3)
example to distinguish between these?
PART D
Answer any two full questions, each carries 9 marks.
12 a) Consider the following table MARKS. Why is the table not in INF? Reconstruct the (5)
table so that it is in INF.

10110. | No. | Name | Marks 81
| Name |
42 Maths
1001 Tom 34 Chemistry
37 Physics

21 | 14001
1057 | Sam | 25 | 0002

34. Physics
45 Maths
1001 Tom | 48 | Chemistry

| 44 [100

b) When does a relational scheme is said to be in 3NF? How is BCNF different from (4)
3NF?
13 a) List aggregate functions of SQL. (3)

Page 2 of 4