Page 36 - IP

(b) In a Bank’s database, there are two tables ‘Customer’ and ‘Transaction’ as shown
below.
Customer

Acc_No Cust_Name Cust_City Cust_Phone Open_Bal
2101001 Sunita Ambala 9710557614 10000
2201002 Sandhya Patna 8223545233 15000
2301003 Vivek New Delhi 9972136576 13000
2401004 Meena New Delhi 9321305453 10000

Transaction

Trans_Id Acc_No Transaction_Type Amount
Tr001 2301003 Credit 15000
Tr002 2201002 Credit 20000
Tr003 2101001 Debit 3500
Tr004 2301003 Credit 26000
Tr005 2301003 Credit 24000

Consider these tables while attempting the questions given below:

(i) Identify the candidate keys of Customer table. 1
OR
(i) Briefly explain the concept of Candidate keys.

(ii) Which column can be considered as foreign key column in Transaction table? 1
OR
(ii) Identify Primary Key column of Transaction table.

(c) With reference to the above given tables, attempt the questions given below: 9

(i) Write a query to display customer’s name who has withdrawn the money.
OR
(i) Write a query to display customer’s name along with their transaction details.

(ii) Write a query to display customer’s name who have not done any transaction yet.
OR
(ii) How many rows and column will be there in the Cartesian product of the above
given tables. Also mention the degree and cardinality of the Cartesian product of
the above given tables.

(iii) Select Acc_No, sum(Amount) from Customer c, Transaction t where
c.Acc_No=t.Acc_No group by c.Acc_No having Transaction_Type="Credit";
OR
(iii) Discuss the significance of having clause with group by statement with suitable
example

7. (a) It’s an era of online financial transactions. Help Julie in identifying any two challenges 2
which a common man may face for the same.

7