Scribd
Upload
125 views

DBMS QB

The document contains a question bank for the Database Systems subject divided into 5 modules. Module 1 covers database concepts like the three schema architecture, data independence, ER modeling, and relational algebra. Module 2 focuses on relational databases including relational schema, keys, integrity constraints, and SQL queries. Module 3 discusses advanced SQL topics such as views, triggers, and JDBC. Module 4 is about relational database design techniques including normal forms up to 5NF. Module 5 covers transaction management, concurrency control, and recovery techniques in database systems.

Uploaded by

md shakil ahsan mazumder
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
125 views

DBMS QB

The document contains a question bank for the Database Systems subject divided into 5 modules. Module 1 covers database concepts like the three schema architecture, data independence, ER modeling, and relational algebra. Module 2 focuses on relational databases including relational schema, keys, integrity constraints, and SQL queries. Module 3 discusses advanced SQL topics such as views, triggers, and JDBC. Module 4 is about relational database design techniques including normal forms up to 5NF. Module 5 covers transaction management, concurrency control, and recovery techniques in database systems.

Uploaded by

md shakil ahsan mazumder
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

Subject: Database Systems

Subject Code : 18CIC53


QUESTION BANK

MODULE-1

1. Explain the characteristics of the Database Approach


2. Describe the three schema architecture. Why mapping is required among schema levels.
3. What are the advantages of DBMS over file systems?
4. Explain the difference between logical and physical data independence
5. Explain the different types of end users.
6. Define the following with an example:
Weak entity ii) Cardinality ratio iii) Degree of a relationship
iv)Recursive relation v) Role name vi) Strong Entity
7. Explain the typical components of a DBMS with a neat diagram.
8. Define and explain the following terms with an example each:
i) Snapshot ii) Intension iii) Extension iv) Schema construct
9. Which are the different DBMS Interfaces?
10. Design an ER - diagram for keeping track of information about hospital database, taking into account
at least 4 entities.
11. Define Composite attribute, multivalued attribute, derived attribute, complex attribute, key attribute,
Null Value.
12. With a neat diagram explain the main phases of database design.
13. Give a summary of notations used in ER Diagram.
14. Design an ER – diagram for keeping track of information about Company database, taking into
account at least 4 entities.

MODULE-2

1. Explain the characteristics of relations


2. Explain super key, candidate key and primary key with examples.
3. Discuss domain Constraint, Integrity constraint with examples.
4. Consider the following schema for a company database:
Employee (Name, SSN, Salary, DNo, SuperSSN)
Department (DName, DNos, MGRSSN) .
Project (Pname, Pnumber, DNum)
Works ON (ESSN, PNo, Hours)
Dependent (ESSN, Dependent-name, Sex)
Write Relational Algebraic Expressions for the following
1) List all pairs of Employees SSNs and Project names they work on

2) List all Employees and department names they work for

3) List all departments and names of their managers

4) List all Employees names and Department Names they work for
5) List all pairs of Employees names and Project names they work on

6)Write relational algebraiTo retrieve the social security numbers of all employees who either work in
department 5 or directly supervise an employee who works in department 5

7)

8)

6) Consider the following schema and write the queries using SQL
EMPLOYEE(Name, SSN, Address, Sex, Salary, SupervisorNum, Dno )
DEPARTMENT(Dname, Dnumber, MgrSSN, Mgr_Startdate)
DEPENDENT(ESSN,Dependent_name,Sex,Bdate,Relationship)
i) Find all the employees working for the department “Research”
SELECT NAME FROM EMPLOYEE, DEPARTMENT
WHERE DNo=DNos AND DName=’Research’;
ii) Write a query in SQL to display all the unique job in descending order.
SELECT DISTINCT job_name
FROM employees ORDER BY job_name DESC;
iii) List the names of employees who superwise themselves
SELECT NAME FROM EMPLOYEE
WHERE SuperSSN is NULL;
iv) List all employees who earn more than their managers
SELECT E1.Name FROM EMPLOYEE E1, EMPLOYEE E2
WHERE E1.SuperSSN=E2.SSN AND E1.Salary>E2.Salary;
v) Write a query in SQL to list the employees who are senior to their own MANAGERS
SELECT E1.Name FROM EMPLOYEE E1, EMPLOYEE E2
WHERE E1.SuperSSN=E2.SSN AND E1.hire_date>E2. hire_Date;
7) Write the SQL Queries for the given schema:
Student (Name, student-number. class, major)
Course (Course name, Course Number, Credit hours, Department)
Section (Section identifier. Course number. Semester, Year, Instructor)
Grade-report (Student Number, Section Identifier, Grade)
Pre-requisite (Course number, Pre requisite Number)
i. Change the class of student 'Uday' to 2.
ii. Delete the record for the student whose name is 'GEETA' and whose student
number is 17.
iii. For each Section taught by Prof. Jain retrieve the course number, Semester,
Year and number of students who took the section.
iv. Retrieve the names of all senior student majoring in 'CS'.
v. Insert a new course, < 'neural networks, 'CS4390', 3, 'CS')
8) Consider the following schema and write the queries using SQL (10)
STUDENT(Student_Id, Sname, Major, GPA)
FACULTY(Faculty_Id, Fname, Dept, Designation, Salary)
COURSE(Course_Id, Cname, Faculty_Id)
ENROLL(Course_Id, Student_Id, Grade)
i) List the names of all students enrolled for the course “CS54”
ii) List the names of students enrolled for the course “CS54” and have received ‘A’ Grade
iii) List all the departments having an average salary of Rs 20000/-
iv) Give a 15% raise to salary of all faculties.
v) List the names of all faculty members begining with “R” and ending with letter “U”

9) Explain the following specified SQL Clauses with example WHERE clause , ORDER BY, GROUP
BY, HAVING
10) Explain the different DDL commands with examples.
11) Explain the different DML commands with examples.
12) Explain the uses of ALTER command with examples.
13) Explain with an example self-referential integrity.
14) Explain the uses of the following SQL commands a) GRANT b)REVOKE c)ROLLBACK d)COMMIT

MODULE-3

1. What is view in SQL and how it is define? How views are implemented?
2. Explain correlated queries with an example.
3. Write a correlated query to retrieve the names and Ids of employees whose salary is greater than
the average salary of the department

4. Explain the different Aggregate functions available in SQL with examples.


5. Explain triggers and assertions in SQL with an example.
6. Demonstrate cursors with example.
7. What is SQLJ and what is it used for?
8. Explain the different types of JDBC drivers.
9. Mention the steps in JDBC database access.
10. Explain the three-type application architecture

MODULE-4

1. Explain the INFORMAL DESIGN GUIDELINES FOR RELATION SCHEMAS


2. What is Functional Dependency? Explain its different types.
3. Explain first normal form with an example
4. Explain second normal form with an example
5. Explain third normal form with an example
6. Explain BCNF with an example
7. Explain multivalued dependency? Explain with an example.
8.Explain fourth normal form with an example
9. Explain fifth normal form with an example
10. Explain Inference rules for functional dependency.
11. Explain the properties of relational decompositions.
12. prove

MODULE-5

1. Explain the desirable Properties of Transactions


2. Explain why co0ncurrencu control is required.
3. Explain why recovery is required
4. Explain Two-Phase Locking 2PL protocol
5. Write short notes on deadlock prevention and avoidance
6. Which are the different types of database failures?
7. Which are the different types of database update possible during database recovery?
8. Explain the Aries recovery algorithm

You might also like