IMP Of DBMS (3130703)

---

Database Management System [IMP]

---

*Click On  Below Topic to Expand it.

Module - 1 : Database system architecture

1. Explain disadvantages of conventional file-based system compared to Database management system.
2. Explain three level architecture of DBMS.
3. Explain different types of outer join with example.
4. What are the main functions of a Database Administrator?
5. Explain tasks of DBA.
6. List and explain DML statements with suitable examples.
7. Explain advantage of database management system over a file oriented system.
8. Enlist and explain the advantages of DBMS over traditional file systems.

Module - 2 : Data models

1. Explain specialization and generalization concepts in ER diagram with suitable example.
2. Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents.
3. 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 each level of the hierarchy. Explain why they should not be placed at a higher or lower level.
4. Explain specialization and generalization concepts in ER diagrams with suitable examples.
5. Draw an E-R diagram for the bank management system. OR Define E-R diagram. Draw an E-R diagram for the Library Management System. Assume relevant entities and attributes for the given system.
6. Design a database for an airline. The database must keep track of customers and their reservations, flights and their status, seat assignments on individual flights, and the schedule and routing of future flights. Your design should include an E-R diagram, a set of relational schemas, and a list of constraints,including primary-key and foreign-key constraints.
7. Design a database for a hospital with a set of patients and a set of medical doctors. Associate witheach patient a log of the various tests and examinations conducted. Your design should include an E-Rdiagram, a set of relational schemas, and a list of constraints, including primary-key and foreign-key constraints.
8. What is constraint? Explain types of constraints.
9. The relational database schema is given below. employee (person-name, street, city) works (person-name, company-name, salary) company (company-name, city) manages (person-name manager-name) Write the relational algebra expressions for the given queries. 1.Find the names of all employees who work for First Bank Corporation. 2.Find the names and cities of residence of all employees who work for First Bank Corporation. 3.. Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000 per annum. 4. Find the names of all employees in this database who do not work for First Bank Corporation.
10. Construct an E-R diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents. Each insurance policy covers one or more cars, and has one or more premium payments associated with it. Each payment is for a particular period of time and has an associated due date and the date when the payment was received.
11. What do you mean by integrity constraints? Discuss various integrity constraints.

Module - 3 : Relational query languages

1. List the relational algebra operators. Discuss any two such algebra operator with suitable example.
2. What is Relational Algebra? Define Relational Algebra Operation Cross product with example.

Module - 4 : Relational database design

1. What is meant by normalization? Write its needs. List and discuss database anomaly during database design.
2. Explain various Normal forms up to 3NF.
3. Consider schema R = (A, B, C, G, H, I) and the set F of functional dependencies {A → B, A → C, CG → H, CG → I, B → H}.( Use F + ) Prove that AG → I Holds- Module No.4| (3M)(P4 - Appeared 1 Time).
4. Consider schema EMPLOYEE(E-ID,E-NAME,E-CITY,E-STATE) and FD = {E-ID->E-NAME, E-ID->E-CITY, E-ID->E-STATE, E-CITY->E-STATE} 1) Find attribute closure for: (E-ID)+ 2) Find(E-Name).
5. Elaborate Normalization. Explain any two normal forms with suitable example(s).
6. Why should normalization be performed on a table and what are its benefits. Explain 3NF and BCNF.
7. Normalize the following schema, with given constraints, to 4NF. books(accessionno, isbn, title, author,publisher) users(userid, name, deptid, deptname) List of constraints: accessionno → isbn isbn → title isbn → publisher isbn →→ author userid → name userid → deptid deptid → deptname.
8. What is redundant functional dependency? Explain trivial and non trivial functional dependency with example.
9. A college maintains details of its lecturers' subject area skills. These details comprise: Lecturer Number, Lecturer Name, Lecturer Grade, Department Code, Department Name, Subject Code, Subject Name, Subject Level Assume that each lecturer may teach many subjects but may not belong to more than one department. Subject Code, Subject Name and Subject Level are repeating fields. Normalize this data to Third Normal Form.
10. Explain Armstrong’s Axioms in detail.
11. A software contract and consultancy firm maintain details of all the various projects in which its employees are currently involved. These details comprise: Employee Number, Employee Name, Date of Birth, Department Code, Department Name, Project Code, Project Description, Project Supervisor Assume the following: Each employee number is unique. Each department has a single department code. Each project has a single code and supervisor. Each employee may work on one or more projects. Employee names need not necessarily be unique. Project Code, Project Description and Project Supervisor are repeating fields. Normalize this data to Third Normal Form.

Module - 5 : Query processing and optimization

1. Discuss various steps of query processing with diagram.
2. Explain query processing step with diagram. OR Explain steps of query processing with the help of a neat diagram.
3. Explain different types of outer join with example.
4. Consider the relational database given below. Give an expression in the relational algebra to express each of the following queries: Employee (person-name, street, city) , Works (person-name, company-name, salary) Company (company-name, city) , Manages (person-name, manager-name) 1)Find the names of all employees in this database who live in the same city as the company for whichthey work. 2) Find the names, street address, and cities of residence of all employees who work for HCLand earn more than $10,000 per annum.
5. Explain query evaluation process.
6. Explain following relational algebraic operation (i) Division (ii) inner join (iii) intersection (iv) full outer join (v) projection (vi) Cartesian product.
7. Explain the purpose of sorting with example with reference to query optimization.
8. Explain the measures of finding out the cost of a query in query processing.

Module - 6 : Storage strategies

1. Construct a Btree for the following set of key values: (2,3,5,7,11,17,19,23,29,31) Assume that the tree is initially empty and values are added in ascending order. Consider the number of pointers in each node as four.
2. Explain Dense and Sparse indices in detail.

Module - 7 : Transaction processing

1. What is a transaction? List and explain ACID property of transaction.
2. Describe log-based recovery in brief.
3. Write a note on two phase locking protocol.
4. Explain conflict serializability with the help of suitable examples.
5. Define Primary key, Candidate key and Super key.
6. Write a note on two phase locking protocol.
7. Write differences between shared lock and exclusive lock.
8. Define transaction. Explain various states of transaction with suitable diagrams.
9. Explain stored procedure with proper example.
10. What is log based recovery ? Explain Immediate database modification technique for database recovery.
11. What is the deadlock of transactions? Explain wound wait technique for prevention of deadlock.
12. Explain various types of LOCKs used in Lock base protocol for concurrency control.
13. Define transaction. Explain various states of transaction with suitable diagrams.
14. What is log based recovery? Explain Immediate database modification technique for database recovery.
15. Explain system recovery procedure with Checkpoint record concept.
16. What is a recoverable schedule? Why is recoverability of schedules desirable? Are there any circumstances under which it would be desirable to allow non-recoverable schedules? Explain your answer.
17. Consider schema R = (A, B, C, G, H, I) and the set F of functional dependencies {A → B, A → C, CG → H, CG → I, B → H}. Prove that AG → I Holds.

Module - 8 : Database Security

1. Explain Aggregate Functions of SQL with suitable examples.
2. Explain types of database users.
3. Explain Authorization and access control in brief.
4. Explain various mapping cardinalities.

Module - 9 : SQL Concepts

1. Describe GRANT and REVOKE commands.
2. Define 1) view. Write syntax to create a view .Give an example of view. 2) Primary key, Foreign Key and NOT NULL constraint 3) primary key, NOT NULL key and Unique key 4) DDL, DML and DCL. 5)Primary key, Candidate key and Super key.
3. List and explain aggregation functions with suitable examples.
4. Explain difference between strong entity set and weak entity set.
5. Define View. Explain with appropriate examples.
6. Consider following relations and write SQL queries for given statements. Assume suitable constraints. Instructor(ID, Name, Dept_name , Salary) Teaches(ID, Course_id, Sec_id, Semester(even/odd),Year) 1) Write SQL query to create Instructor table. 2) Find the average salary of the instructors in the computer department. 3) Find the number of instructors in each department who teach a course in the semester of 2016. 4) Find the names of instructors with salary amounts between 30000 and 50000.
7. List and explain aggregation functions with suitable example.
8. Compare rollback with commit SQL commands.
9. Consider following the schema and write SQL for given statements. Client_master(clientno, name, address, city, pincode, state, baldue) Product_master(productno, name, profitpercent, unitmeasure, sellprice, costprice) Salesman_master(Salesmanno,name,address,city,pincode,state,salary,tgtotget, remarks) 07 1) Find out the names of all clients. 2) List all the clients who are located in Mumbai. 3) Delete all salesmen from salesman_master whose salaries are equal to Rs.3500. 4) Destroy the table client_master along with data. 5) List the name of all clients having ‘a’ as the second letter in their names. 6) Count the number of products having a cost price less than or equal to 500. 7) Calculate the average, minimum and maximum sell price of a product.
10. Consider following the schema and write SQL for given statements. employee (employee-name, street, city) works (employee-name, company-name, salary) company (company-name, city) manages (employee-name, manager_name) 1. Find the names of all employees who work for First Bank Corporation. 2. Give all employees of First Bank Corporation a 10-percent raise. 3. Find the names and cities of residence of all employees who work for First Bank Corporation. 4. Find the names, street addresses, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000. 5. Find all employees in the database who live in the same cities as the companies for which they work. 6. Find all employees in the database who do not work for First Bank Corporation.
11. Consider the following relations: EMP(empno, ename, jobtitle, managerno, hiredate, sal, commission, deptno) DEPT(deptno, dname, location) Answer the following queries in SQL and Give an expression in the relational algebra to express each of the following queries. a. Find the Employees working in the department number10, 20, 30 only. b. Find Employees whose names start with letter A or letter a. c. Find Employees along with their department name. d. Find the Employees who are working in Smith's department e. Find the Employees who get salary more than Allen’s salary. f. Display employees who are getting maximum salary in each department. g. Find list of employees whose hire date is on or before 1-April-18.
12. Describe the following SQL functions: (i) Add_months(x,y) (ii) Rtrim() (iii) To_date.
13. Explain commit, rollback and savepoint command.
14. Write a PL/SQL block to print the given number is prime or not.
15. Consider following the schema and write SQL for given statements. Student (RollNo, Name, DeptCode, City) Department (DeptCode, DeptName) Result (RollNo, Semester, SPI) 1. Display the name of students with RollNo whose name ends with ‘sh’. 2. Display department wise total students whose total students are greater than 500. 3. List out the RollNo, Name along with CPI of Student. 07 3 4. Create a RollNo field as the primary key for the existing Student table. 5. Display the student name who got the highest SPI in semester 1. 6. Display the list of students whose DeptCode is 5, 6,7,10. 7. Create table Student_New from student table without data.
16. Consider the tables given below. Write the SQL queries for the questions given below: T1 ( Empno, Ename , Salary, Designation,) T2 (Empno, Deptno.) (1) Display all the details of the employee whose salary is lesser than 10000. (2) Display the Deptno in which Employees with names starting with letter ‘S’ are working. (3) Add a new column Deptname in table T2. (4) Change the designation of Geeta from ‘Manager’ to ‘Senior Manager’. (5) Find the total salary of all the employees department wise. (6) Add Empno as primary key in existing table T1. (7) Display the Deptno having the highest number of employees.

Module - 10 : PL/SQL Concepts

1. Write a note on trigger.
2. What are triggers? Explain the advantages and the needs of triggers.
3. Write a PL/SQL program for inserting even numbers in EVEN table and odd number in ODD table from number 1 to 50.
4. Explain Cursor in PL/SQL.
5. Explain deadlock with suitable examples.
6. Explain stored procedures with proper examples.
7. Write a PL/SQL block to print the sum of Numbers from 1 to 100.
8. What is PL/SQL? Explain the difference between SQL and PL/SQL.