Advanced Database Management Systems-Practice Questions

MCS-043

 

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.

(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 multi valued 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)

Question 3:

How will you enforce referential integrity constraints in Oracle? Explain with the help of an example?

Question 4:

Draw a simple Use Case diagram for a library system and explain?

Question 5:

(i) What are triggers and cursors and their uses? Explain with the help of an example for each.

(ii) Write a trigger that restricts modifying an employee table beyond 2 hrs overtime per day.

Question 7:

What are the parameters for measuring cost for performing a query. Discuss the algorithm and the related cost of performing selection operation?

Question 8:

Explain SQL related security commands?

Question 9:

Explain clustering in data mining?

Question 10:

What the help of a suitable example, explain insertion and deletion anomalies.

Question 11:

How is the check pointing information used in the recovery operation in case of the system crash in DBMS.

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

(b) Find all the employees who are working as Deputy Registrar and living in the same cities.

Question 13:

How does PostgreSQL perform storage and indexing of tables? Also discuss the type of indexes in PostgreSQL?