Question 1) Write a detailed note on Referential Integrity.
Question 2) What do you understand by Decomposition of Relation Scheme?
Question 3) Write down the functions of DBA.
Question 4) Distinguish between Weak Entity and Strong Entity with appropriate example.
Question 5) Consider the following database relations:
Country(Con-code, Name, Capital)
Population (Pop-code, Population)
Country and Population are related with one to one relationship.
Create relational database for the above and convert it in 3NF and solve the given queries in SQL :
(i) List highest population country.
(ii) Give name and population of country whose capital is ‘Delhi’.
(iii) Print Countrywise Population.
Question 6) Consider the given relation:
Teacher (tno, tname, collegename, dept.)
Etest (eno, testname)
Teacher and Etest are related with many to many relationship.
Create a relational database for the above and convert it in 3NF and solve the following queries in SQL:
(i) Count no. of teachers who passed ‘Set Exam’ of Computer Science.
(ii) Delete all teacher details of ‘Maths’ Dept.
(iii) Print total no. of teachers passing respective exam.
Question 7) Consider the following relation:
Employee (empno, empname, salary, comm., desg.)
Department (deptno, deptname, location)
Employee and Department are related with many to one relationship.
Create relational database for the above and convert it in 3NF and solve the following queries in SQL:
(i) Determine employees who are working at Ahmednagar city location.
(ii) Find maximum, minimum and average salary for every designation.
(iii) Update commission for every employee by 5% who belongs to ‘Computer Department’.
Question 8) Consider the given relation:
Item (I_no, I_name, I_qty)
Po (p_no, p_date, cost, qty)
Supplier (s_no, s_name, s_addr.)
Item and Po are related with one to many relationship and supplier and Po are related with one to many relationship.
Create a relational database in 3NF and solve the following queries in SQL :
(i) Find out po_no, po_date and supplier_name of Po which is maximum amount.
(ii) List name of supplier to whom Po is given for ‘Mouse’.
(iii) List name of supplier who is going to supply ‘Monitor’ with minimum cost.