MCS-043 Advanced Database Management Systems Solved Assignment 2018-2019

Course Code : MCS-043
Course Title : Advanced Database Management Systems
Assignment Number : MCA(IV)/043/Assignment/2018-19
Maximum Marks : 100
Weightage : 25%
Last Dates for Submission : 15th October, 2018 (For July session)
15th April, 2019 (For January session)

This assignment has thirteen questions, which carries 80 marks. Answer all the
questions. Rest 20 marks are for viva voce. You may use illustrations. Place go
through the guidelines regarding assignments given in the Programme Guide for
the format of presentation.

Question 1: Consider a small training institute in which students register for
skilled based program offered by the institute. A program can be
full or a part-time or both. Every student necessarily registers in at
least one program and at most four programmers. Faculty also
can be full time or part time or both. A faculty can conduct not
more than 3 courses per semester. (20 marks)
(a) Draw the EER (extended ER) diagram for the above organization
showing all entities, relationship, aggregation, generalization
/specialization and convert it into relational schemas
(b) Draw the appropriate tables and relationship among the tables for
the above diagram and normalize the tables up to 3NF
(c) Identify weak entity sets in the above diagram if any. How will
you convert a weak entity set to a strong entity set? Illustrate.
(d) Identify multivalued dependency in the above diagram.
Question 2: Create an XML schema for the list of the courses to be offered in
the second semester of MCA and their details (like, consumer
code, consumer name, number of credits, assignments makes TEE
marks) (5 marks)
Question 3: How will you enforce referential integrity constraints in Oracle?
Explain with the help of an example? (5 marks)
Question 4: Draw a simple Use Case diagram for a library system and
explain? (5 marks)
Question 5: (i) What are triggers and cursors and their uses? Explain with the
help of an example for each. (5 marks)
(ii) Write a trigger that restricts modifying an employee table beyond
2 hrs overtime per day. (5 marks)
8
Question 6: Create and explain an object oriented database for the following
UML diagram. Make assumption about attributes and functions.
(5 marks)
Question 7: What are the parameters for measuring cost for performing a query.
Discuss the algorithm and the related cost of performing selection
operation? (5 marks)
Question 8: Explain SQL related security commands? (4 marks)
Question 9: Explain clustering in data mining? (3 marks)
Question 10: What the help of a suitable example, explain insertion and deletion
anomalies. (4 marks)
Question 11: How is the check pointing information used in the recovery operation
in case of the system crash in DBMS. (4 marks)
Question 12: Consider the following database
Employees ( emp-name, streets, city, age)
Working( emp-name, department)
Designation ( emp-name, designation, salary).
Write the relational algebraic expressions and SQL statements for the
following queries:
(a) Find the name, street & cities of all employees working for department
D1 and D2 as Section Officers and earning salary more then 30000
(3 marks)
(b) Find all the employees who are working as Deputy Registrar and
living in the same cities. (3 marks)
Question 13: How does PostgreSQL perform storage and indexing of tables? Also
discuss the type of indexes in PostgreSQL?