lOMoARcPSD|54900139

Dbms questionbank

Database Management System (APJ Abdul Kalam Technological University)

Scan to open on Studocu

Studocu is not sponsored or endorsed by any college or university

Downloaded by Abi ([email protected])
lOMoARcPSD|54900139

QUESTION BANK

Subject: CST 204 Database Management System

Module – I

[Short Answer Type Questions]

1. Define Database? Discuss about applications of Database Systems?
2. Differentiate between Database Management System and file based system.
3. Define internal architecture of DBMS.
4. Explain the role of DBA.
5. Explain the advantages of DBMS.
6. What is Data Abstraction? Explain about different views of data?
7. Define Instance and Schema? List different data models and explain?
8. Draw the Architecture of Database?
9. Discuss about Database users and Administrators
10. Draw ER diagram for Ternary Relationship set with suitable example?
11. What do you understand by generalization and specialization attributes in DBMS?
12. Define Entity, Attributes, Entity set, relationship with appropriate notations?
13. What is a weak entity? Explain with example?
14. Differentiate between DBMS and RDBMS.
15. Explain the term cardinality ratio with an example.

[Long Answer Type Questions]

16. Explain three-tier architecture of DBMS?
17. Explain about Database languages with examples?
18. Write about logical database design (ER to Relational) with suitable examples?
19. A company needs to store information about employees (identified by ssn, with
salary and phone as attributes), departments (identified by dno, with dname and
budget as attributes), and children of employees (with name and age as attributes).
Employees work in departments, each department is managed by an employee; a
child must be identified uniquely by name when the parent (who is an employee;
assume that only one parent works for the company) is known. We are not interested
in information about a child once the parent leaves the company.
Design an E-R diagram indicating all entities with generalization and specialization,
attributes with key and cardinality ratio?

20. A large Bank named “XYZ” is an international bank having 28 branches overseas and
country and different cities. Each branch offer services banking and trading. Customer
can select saving/current account with single or join operation in the banking. Each
branch maintains the account detail of customers keep record of each transaction in
each service by the customer to his/her account.

Downloaded by Abi ([email protected])

lOMoARcPSD|54900139

Design an E-R diagram indicating all entities with generalization and specialization,
attributes with key and cardinality ratio?

21. Construct an E-R diagram for a university Director”s office. The office maintains
data about each class, including the instructor, the enrolment, and the time and place
of the class meetings. For each student-class pair, a grade is recorded. Document all
assumptions that you make about the mapping constraints.
22. What is physical and logical data independence? Also explain the difference
between physical and logical data independence.
23. What are the different types of data model?
24. Explain hierarchical model of DBMS and compare it with Network model.
25. Explain the characteristics of relational database management system (RDBMS).

Module – II

[Short Answer Type Questions]

1. What do you understand by data types in DBMS?
2. What do you understand by primary key? Explain its features with an example.
3. Define joins in relations.
4. Explain the term selection and projection in relation algebra.
5. What is a database trigger? Which are the different kinds of triggers?
6. How can DBA give to all privileges to user? Show with SQL expression.
7. How to list and update row in a table? Explain with syntax and examples
8. Explain DDL, DML queries with an example.
9. Define the concept of aggregation. Give two examples of where this concept is useful.
10. Explain keys, foreign keys and constraints on attributes and tuples.
11. Define a NULL value? How do you retrieve null values from the database?
12. Discuss about outer joins with examples.
13. When can we use group by clause, explain.
14. When can we use having clause, explain.

[Long Answer Type Questions]

15. Explain about integrity constraints over relations (key management of DBMS)?
16. What is a join ? Explain about conditional join and natural join with syntax
and example
17. Consider the following relation
Employee (person_name, street, city)
Works (person_name, company_name, salary)
Company (Company_name, manager_name)
Manages (person_name, manager_name)

Consider primary key according to relation.

Give an expression in the relational algebra to express each of the following:-

a. Find the names of all employees who work for corporation bank.
b. Find the names and cities of residence of all employees who work in
corporation bank.
Downloaded by Abi ([email protected])
lOMoARcPSD|54900139

c. Find the names of all employees in this database who live in the same city
as the company for which they work.

d. Find the names of all employees who live in the same city and on the same street
as do their managers.

18. Consider the following relation

Project (proj#, proj_name, chief_architect)
Employee (emp#, emp_name)
AS Signed (proj#, emp_name)
Use relational algebra to express following queries.
a. Get details of employee working on project?
b. Get the employee number of employees who work on all projects.
c. Get details of project on which employee with name „AAA‟ is working.

19. Consider the following relation

Department (dept_name, building, budget)
Course (course_id, title, dept_name)
Instructor ( id, name, dept_name, salary)
Teaches (id, course_id, section_id, semester, year)

The key fields are underline as primary key. Give the SQL express of the following:-

a. Select the department name of instructor whose name neither „A‟ nor „B‟.
b. Find the number of instructors of each department who teach course „DBMS‟
c. Find the department that have the highest average salary.
d. Give a 5% salary raise to instructor whose salary is less than average.
20. Consider the following relational schema
Employee (empno, name, office, age)
Books (isbn, title,authors,publisher)
Loan (empno, isbn, date)

Write the following queries in relational algebra.

a. Find the names of employees who have borrowed a book Published by Navathe.
b. Find the names of employees who have borrowed all books Published by Navathe.
c. For each publisher, find the names of employees who have borrowed more than
five books of that publisher.
21. .Consider the following tables:
Employee (Emp_no, Name, Emp_city)
Company (Emp_no, Company_name, Salary)
a. Write a SQL query to display Employee name and company name.
b. Write a SQL query to display employee name, employee city ,company
name and salary of all the employees whose salary >10000
c. Write a query to display all the employees working in “XYZ‟ company

Downloaded by Abi ([email protected])

lOMoARcPSD|54900139

Module – III

[Short Answer Type Questions]

1. (a) Discuss about file organizations and indexing?

(b) Explain about Index structures?
2. (a) What is clustered index organization? Illustrate with example?
(b) Explain about Composite Search Keys? Illustrate with example?
3. (a) Illustrate Tree indexes ?
(b) Explain about ISAM?
4. (a) Discuss about static hashing?
(b) Explain about Extendible hashing?
5. (a) Explain about linear hashing?
(b) Compare Extendible vsLinear hashing?
6. (a) Give one Example for Extendible hashing?
(b) Give one example for Linear hashing?
(c) Draw the structure of B+ tree?
(d) Design example for Clustered indexes?
(e) Design example for Composite Keys?

[Long Answer Type Questions]

1. Compare file organizations?

2. Explain about B+ Trees Dynamic Indexing?
3. Explain about Search and Insert in Tree Structured Indexing?
4. Explain about Delete and Duplicated in Tree Structured Indexing ?

Module – IV

[Short Answer Type Questions]

5. What do you understand by Functional dependency?
6. Explain the term candidate key using functional dependency.
7. Explain the term atomicity in DBMS.
8. Explain the anomalies of DBMS.
9. What is redundancy? What are the problems caused by the redundancy?
10. If R={ A,B,C,D,E } and FD‟s F={ A→ C, AC→ D, E→ AD, E→H} List all the
candidate keys.
11. Compute canonical cover Fc for the R= {A, B, C, D} and FD‟s= { A→BC,
B→C, A→B , AB→C, AC→D}.
12. Explain database decomposition? Why it is necessary.
Downloaded by Abi ([email protected])
lOMoARcPSD|54900139

13. Explain BCNF in detail.

14. Explain lossy decomposition in detail.

[Long Answer Type Questions]

15. Explain codd‟s rules for relational database management system in detail.
16. Explain Normalization of DBMS.
17. Explain 1st Normal Form, 2nd Normal form and 3rd Normal Form with suitable
example.
18. Consider the universal relation R={ A,B,C,D,E,F,G,H,I} and the set of functional
dependencies F={(A,B)→{C],{A}→{D,E},{B}-→{F},{F}→{G,H},{D}→[I,J}.what
is the key for Decompose R into 2NF,the 3NF relations.
19. Normalize the given relation up
to 3 NF: R = {A, B, C, D}
Fd‟s = {AB→D, AC →BD, B→C}
20. Define Boyce-Codd normal form. How does it differ from 3NF? Why is it
considered a stronger form of 3NF?
21. Explain lossless join decomposition in
22. How do you find lossy and lossless decomposition?
23. Suppose you are given a relation R = {A, B, C, D, E} with the following functional
dependencies
F= {CE→D, D → B, C → A}
a. Find all candidate keys.
b. Identify the best normal form that R satisfies (1NF, 2NF, 3NF, or BCNF).
c. If the relation is not in B CN F, decompose it until it becomes BC N F.

24. Suppose you are given a relation R {A, B, C, D, E} with the following
functional dependencies

F= {AB→C, DC → AE, E → F}
a. What are the keys of this relation?
b. Is this relation in BCNF? If not, explain why by showing one violation
c. Is this decomposition (A, B, C, D) (B, C, D, E, F) a dependency
preserving decomposition? If not, explain briefly.

Downloaded by Abi ([email protected])

lOMoARcPSD|54900139

Module – V

[Short Answer Type Questions]

1. What do you understand by transaction in DBMS.
2. Define transaction management.
3. Explain Armstrong Axiom in functional dependency.
4. What do you understand by schedule in transaction?
5. Explain all types of schedule.
6. What do you mean by serializability.
7. Explain the term recoverability.
8. Explain concurrency control in brief.
9. What do you understand by dirty read in concurrency control.
10. Differentiate between shared lock and exclusive lock in concurrency control system
11. Define Timestamp in brief.
12. How can you secure your database?
13. How can you avoid deadlock in database.
14. Differentiate between growing and shrinking phase in 2PL(Two-phase locking)

[Long Answer Type Questions]

15. Explain transaction states with example?
16. Explain ACID properties of transaction management
17. What do you mean by serial and serializable schedules? Explain
conflict serializability.
18. What is deadlock? Discuss various protocols for deadlock prevention.
19. What is two-phase locking? How does it guarantee serializability
20. Explain the key features of Recoverability of schedule. Also explain log
based recovery.
21. Explain all types of problems arising in concurrency control
22. Explain concurrency protocol in detail.
23. Explain Two-phase locking protocol with an example.
24. Explain the working of Timestamp Ordering Protocol
25. What is meant by the concurrent execution of database transaction in a
multi-user system? Explain why concurrency control is needed with a suitable
example.

Downloaded by Abi ([email protected])