INDERPRASTHA ENGINEERING COLLEGE
DBMS (BCS-501)
Remedial Assignment
1. Explain Relational Algebra with a suitable example.
2. Explain the architecture of DBMS in terms of its components with a proper diagram.
3. Differentiate between full and partial functional dependency.
4. What are the different types of Data Models in DBMS? Explain in details.
5. Distinguish between Procedural and non non-procedural languages.
6. For a given relation R(A,B,C,D,E,G,H) with set of Functional dependencies
F = {CH→G, A→BC, B→CFH, E→A, F→EG}. Find out Candidate keys(s)
7. Consider the relational schema:
employees(emp_id, first_name, last_name, salary, department_id)
products(prod_id, prod_name, category_id, price)
Write sub query for given relations using aggregate functions in SQL:
1. Details of all employees who earn more than the average salary of their department.
2. To display the department IDs where the total salary of employees is greater than the
average total salary across all departments.
3. To display Employees who earn the maximum salary in their department.
4. To display employees who work in departments having a higher than average number of
employees.
8. Consider the relational schema: K2
Physician (Reg_No, Name, City, Mobile,State, Fees)
Patient (PtName, PtCity)
Visits (Reg_No,PtName, DateOfVisit )
Answer the following queries all in RA or all in TRC:
i) Get the Physician details who lives in Delhi .
ii) Get Patient details who has been examined by ‘Dr. Ashok’ on 13-Sep-2023.
iii) Get the Name of Physician and Patient who lives in same city
iv) Get the Physician names and State of Physician who live in Mumbai with Fees greater
than 1000.
9. Explain Views and Indexes, Triggers, and Stored Procedures with examples.
10. Explain Normalisation in detail with an example.
11. Given the following set of FDs on schema R(V,W,X,Y,Z)
{Z → V, W→ Y, XY→Z, V→WX} State whether the following decomposition are loss-less-
join decompositions or not.
I. R1 = ( V,W,X),R2 = (V,Y,Z)
II. R1 = (V,W,X), R2 = (X,Y,Z)
12. Describe concurrency Control.
13. Discuss the procedure of Deadlock detection and recovery based in transactions.
14. Discuss the 2-phase commit protocol and the time stamp-based protocol with suitable
example.
15. Describe the Armstrong’s axioms in detail. What is the role of these rules in DBMS?