DBMS Model Questions 2022 Scheme

Module 1

1. Explain the characteristics of database approach.

2. Explain the advantages of using DBMS Approach.
3. Explain three schema Architecture.
4. Define data independence. Explain types of data independence.
5. Explain database languages and Interfaces.
6. Discuss the various component modules of a DBMS and their interaction with a neat
diagram.
7. Draw ER diagram of Company Database.
8. Draw ER diagram of Bank Database
10.Define an entity and an attribute. Explain the different types of attributes that occur in the
ER model, with an example.
11. Explain the Structural Constraints,
12.What is the cardinality ratio? Explain the possible cardinality ratios for binary relationship
types with an example

Module 2
1 Explain primary key , referential integrity and foreign key concepts with the specific
example
2 Explain union, Intersection and Minus Operations of Relational algebra with
examples

3 Describe Selection and Projector Operator in Relational Algebra and mention the
difference between them with examples.
4 Develop the following queries in Using Relational Algebra.
i) Find the names of all the employees whose salary is greater than 30000.
σsalary>30000(Employee)
ii) Retrieve the name and emp id of all employees.
πname, empid(Employee)
iii) Find the fname and lname of employees in department 4 that earn > 50000
πfname, lname(σsalary>30000(Employee))

5 Explain different types of Joins in SQL

6 Explain relational model constraints.
7 Explain different steps of ER to Relational Mapping algorithm.

8 Convert the following ER Diagram to Relational Model

Module 3
1. Explain the data types that are allowed for SQL attributes.
INTEGER, FLOAT, DECIMAL(i,j), CHAR(n), VARCHAR(n),DATE, TIME,
TIMESTAMP
2. Outline the different constraints in SQL while creating table

3. Explain the schema change statements of SQL

CREATE TABLE/VIEW-AS:

DROP TABLE:

ALTER TABLE/VIEW-AS:

UPDATE-SET-WHERE:
4. Write the SQL queries for the following:
a) Write the syntax to Create, Alter and Drop table. (REFER ABOVE Qs)
b) Write SQL Query to Create Employee table with the following attributes: eid, ename
and salary.
CREATE TABLE Employee (
eid INT PRIMARY KEY,
ename VARCHAR(100),
salary DECIMAL(10, 2)
);
c) Alter table employee by adding one more attribute called address.
ALTER TABLE Employee
ADD COLUMN address VARCHAR(255);
d) Give syntax to drop table employee and drop column salary.
ALTER TABLE Employee
DROP COLUMN salary;

DROP TABLE Employee;

5. Write the SQL queries for the following:
a) Retrieve the birth date and address of employee whose employee id is 10.
SELECT birth_date, address
FROM Employees
WHERE emp_id = 10;
b) Retrieve the name and address of all employees who work for ‘Research’ department.
SELECT e.emp_name, e.address
FROM Employees e, Department d
WHERE d.dept_name = 'Research' AND d.dept_name = e.dno;
c) Retrieve all employees in department 5 whose salary is between 30000 and 40000
SELECT *
FROM Employees
WHERE dept_id = 5
AND salary BETWEEN 30000 AND 40000;
d) Retrieve distinct salaries of employees
SELECT DISTINCT salary
FROM Employees;
6. Explain the ALTER TABLE command. Explain how the new constraint can be added and
also an existing constraint can be removed using suitable examples.(REFER Q3)
7. Explain INSERT, DELETE, UPDATE statements in SQL taking suitable examples.
INSERT:
INSERT INTO Employees (emp_id, emp_name, emp_salary)
VALUES (1, 'Alice', 50000);
DELETE:
DELETE FROM Employees
WHERE emp_id = 1;
UPDATE:
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
8. Explain the aggregate functions in SQL? Explain with examples.

9. Explain the command used for ordering the query results? Explain with the syntax and an
example

10.Write SQL queries for following:

Student( Enrno, name, courseId, emailId, cellno)
Course(courseId, course_nm, duration)
a) Add a column city in student table.
 ALTER TABLE Student
ADD COLUMN city VARCHAR(100);
b) Find out list of students who have enrolled in “computer” course.
SELECT name
FROM Student
WHERE courseId IN
(SELECT courseId FROM Course WHERE course_nm = 'computer');
c) List name of all courses with their duration.
SELECT course_nm, duration
FROM Course;
d) List name of all students start with „a‟.
SELECT name
FROM Student
WHERE name LIKE 'a%';
e) List email Id and cell no of all mechanical engineering students
SELECT emailId, cellno
FROM Student
WHERE courseId IN (SELECT courseId FROM Course WHERE course_nm =
'mechanical');
11. Explain Group by and having clause in SQL with an example.
12. Explain Views in SQL. Give the syntax to create and drop views.

13. Consider the below table:

Orders(ord_no , purch_amt, ord_date , customer_id, salesman_id)
a) write a SQL query to calculate total purchase amount of all orders.
SELECT SUM(purch_amt) AS total_purchase_amount
FROM Orders;
b) write a SQL query to calculate the average purchase amount of all orders.
SELECT AVG(purch_amt) AS average_purchase_amount
FROM Orders;
c) write a SQL query that counts the number of unique salespeople.
SELECT COUNT(DISTINCT salesman_id) AS num_unique_salespeople
FROM Orders;
d) write a SQL query to find the maximum and minimum purchase amount.
SELECT MAX(purch_amt) AS max_purchase_amount,
MIN(purch_amt) AS min_purchase_amount
FROM Orders;
 Module 4
1. Construct Functional Dependency. Give Inference rules for Functional Dependencies.
2. Explain 1NF with example.
3. Explain 2NF with example.
4. Explain 3NF and BCNF with example.
5. Consider the below Relation
R{City, Street, HouseNumber, HouseColor, CityPopulation}
Assume key as {City, Street, HouseNumber}
The Dependencies are:
{City, Street, HouseNumber}  {HouseColor}
{City}  {CityPopulation}
Check whether the given R is in 2NF? If not convert into 2NF
6. Consider the relation
Emp-Proj ={SSN, Pnumber,Hours, Ename,Pname,Plocation}
Assume {SSN,Pnumber } as Primary key
The dependencies are:
{SSN,Pnumber}->Hours
SSN->Ename
Pnumber->{Pname,Plocation}
Normalize the above relation to 3NF
7. Consider the following relation
R {Title, PubId, AuId, Price, AuAddress}
Assume primary key as {Title, PubId, AuId}
The Dependencies are
{Title, PubId, AuID}  {Price}
{AuID}  {AuAddress}
Check whether the given R is in 2NF? If not convert into 2NF

8. Consider the following relation R {Studio, StudioCity, CityTemp}

Assum e Primary Key as {Studio}
The Dependencies are:
{Studio}  {StudioCity}
{StudioCity}  {CityTemp}
Check whether the given R is in 3NF? If not convert into 3NF

9. Give the minimal cover Algorithm. Find the minimal cover using the minimal cover
algorithm for the following functional dependency.
F = {B->A, D->A,AB->D}

10. Give the minimal cover Algorithm. Find the minimal cover using the minimal cover
algorithm for the following functional dependency.
 F = {B->A, D->A,AB->D}

Module 5

1. Explain the desirable properties of Transactions.

2. Explain Different states of Transactions with Diagram.
3. Explain Why concurrency is needed?
4. Develop the steps involved in read and write operations of transactions
5. Explain the operations of transactions.
6. Explain the reasons for failure of transactions
7. Explain Deadlock and Starvation.
8. Explain the characteristics of NoSQL Databases.
9. Explain Types of databases of NoSQL
10. Define the Graph database. List the advantages and Disadvantages of Graph databases
11. Compare NoSql and RDBMS
12. Explain the need of Schemaless database