USN 1 S I N2MC01
Siddaganga Institute of Technology, Tumakuru-572 103
(An Autonomous Institution affiliated to VTU, Belagavi, Approved by AICTE, New Delhi)
Second Semester Master of Computer Applications Examinations Oct-Nov 2024
Database Management Systems
Time: 3 Hours Max. Marks: 100
Not : 1. Revealing of Identity in any form in the answer book will be treated as malpractice.
e
2. Answer any five questions choosing one full question from each unit.
C
Unit - I M BL
O
PO PSO
01 a) Present your views on the advantages of database approach over the traditional file
system. 7 2 1 1
b) What do you mean by Data Model? Explain the 3-Schema architecture with a neat
sketch. 6 2 1 1
c)What do you mean by tier of architecture? Present the different types of database
architecture. 7 2 1 1
OR
02 a) Illustrate the various types of database utilities. 6 2 1 1
b) Discuss the different types of database interfaces. 6 2 1 1
c) Discuss the roles of different types of actors who works on the screen and off the
screen with the database environment. 8 2 1 1
Unit - II
03 a) Define i) Strong entity, ii) Weak entity, iii) Degree of relation,
iv) Cardinality of relation, v) Relation, vi) Tuple 6 1 2 1
b) Illustrate the various features of Generalization of Specialization with proper
examples. 7 2 2 2
c)What is cardinality ratio? Substantiate the facts of cardinality ration with respect to
Ternary relationship with proper examples. 7 2 2 2
OR
04 a) What are constraints? Discuss the major types of constraints with proper
examples. 6 2 2 1
b) Define: i) Schema ii) Instance iii) Data model iv) Aggregation 4 1 2 1
c) Please go thru the following case study of Banking Scenario:
A Bank is identified by name, Id number, location of the main office and an
address,
A Bank has many branches,
Each branch is identified by branchID, branch name and an address,
A branch has many customers who hold the account,
A customer is identified by customerId, customer_name and an address,
Account is held by Customer and a customer may have single account and many
accounts (SB account, Joint Account, Business Account),
Each Account is identified by account-No, account_type, balance_amount,
A bank offers Loans to customer through branches,
Loans are identified by loanID, loanType (House, Car, Business, Personal),
loan_amount.
Design and develop an ER diagram to represent this application 10 3 2 3
-1- Please Turn Over
-2- N2MC01
Unit - III
05 a) What are SQL-functions? Discuss the types of SQL-functions based on their
return types. 6 2 3 1
b) Consider the following tables with their attributes:
Employee (EmpNo, EmpName, Job, Hiredate, Salary, DeptNo)
Dept (DeptNo, DeptName, Location)
Develop the SQL-Queries for the following requirements.
i) Display the Employee details whose Salary between 15000 and 30000.
ii) Display the Employee Name in which first letter should be ‘A’ and last
letter should be ‘S’
iii) Display job type and number of employees in each of the category.
iv) Display the Employee details who is getting highest salary for a particular
department.
v) Display the Employee details who are working in a specific location.
vi) Display the employee details whose salary in greater than the average salary
in their respect department. 10 3 3 3
c) For the above tables, develop the queries in relational algebraic notations for the
following requirement.
i) Display the employee details with the designation ‘CLERK’.
ii) Display the employees details whose salary is greater than 20000.
iii) Display the employees who are working for the department ‘Accounts’
iv) Group the employees based on job and display the number of employees. 4 3 3 3
OR
06 a) What is inner Join? Discuss the various types of Inner JOINS with proper
examples. 7 3 3 2
b) Write the syntaxes for the following SQL commands along with one example.
i) CREATE, ii) ALTER, iii) DROP, iv) INSERT, v) UPDATE, vi) DELETE 6 1 3 1
c) What is Sub Query? Discuss the various types of sub-queries based on the
returning values. 7 2 3 2
Unit - IV
07 a) Write a structure of an anonymous PL/SQL block and explain each block. 5 1 4 1
b) Write a note on Procedure, Function and Trigger. 5 1 4 1
c) What do you mean by Normalizaation? Explain 2NF and 3NF. 6 3 4 3
d) Bring the following relation to third normal form:
Emp_Dept_Project (Emp#, Dept#, Project#, EmpName, DeptName, ProjectName,
DateofBirth, DeptHead, ProjectDuration, NumberofHours, Remuneration) 4 3 4 3
OR
08 a) What are exceptions? Explain exception handling section in PLSQL block. 5 2 4 1
b) What are Packages? Discuss the structure of Package specification and its body. 5 2 4 1
c) What are the problems caused when we use an un-normalized tables? Explain in
brief. 6 3 4 3
d) What is De-normalization? Explain with an example. 4 3 4 3
Please Turn Over
-3- N2MC01
Unit - V
09 a) Present the prime features of documents and collections with respect to MongoDB 5 2 5 2
b) Illustrate the various FIND operations performed in accessing the data in
MongoDB 5 3 5 3
c) Consider the following collections,
Book (ISBN, Title, Author, Publisher),
Student (USN, StudentName, Class)
Borrow (ISBN, USN, Borrowed Date)
Develop the queries in NOSQL (MongoDB) for the following requirements.
a) List the Book titles in which there is a particular keyword pattern
“Computing”.
b) Obtain the Names of students who have borrowed DBMS books.
c) List the Author names who have authored more than 1 Book.
d) List the Publisher names who have published more than 2 Books,
e) List the student names who have borrowed more than 1 Book. 10 3 5 3
OR
10 a) Elaborate on
i) Inserting one document into a collection,
ii) Inserting multiple documents into a collection 5 3 5 3
b) i) Updating the documents in a collection
ii) Removing documents from a collection 5 3 5 3
c) Consider the following collections,
Employee (SSN, Name, Job, Salary)
Project( ProjectNo, ProjectName, Duration)
Assigned_To(SSN, ProjectNo)
Develop the queries in NOSQL (MongoDB) for the following requirements.
a) List the employees who are working with a particular designation.
b) List the employees who are drawing the salary greater than 35000
c) List the employees who are working as Analyst and drawing the salary
greater than 35000
d) List the employees who are working for a particular project.
e) Update the employee salary with a new value for particular employee. 10 3 5 3
________