MCS-23 Introduction to Database Management Systems Assignment Questions

Question 1:

(i) What is DBMS? How is it different from RDBMS?

(ii) Is DBMS usage always advisable or some times we may depend on file base systems? Comment on the statement by describing the situation where DBMS is not a better option & file base systems is better.

(iii) Describe ANSI SPARC 3-level architecture of DBMS with details of languages associated at different levels plus the level of data independence.

(iv) How logical architecture of DBMS differs from physical architecture?

(v) Create an E R diagram and relational schema to hold information about the situation in many institutions affiliated to some University, many teachers of different disciplines are teaching to many students enrolled in many courses offered by the university to the students through the institutions. Use concept of keys, aggregation, generalisation, cardinality etc. in a proper way.

(vi) Say the schema of respective entities is:

Teacher( T#, Tname, Tqual, Tsubject, Tcourse, Prog)
Student(Roll#., Sname, Sage, Saddress, Scourse.Prog , Smarks)
Teaches(T#, Roll# , Scourse, Prog ,University)

Performa following queries in SQL using the given schema:

a) Find details of Teachers who taught DBMS.
b) Find details of students who did MCA from IGNOU.
c) Find courses taught by T# 5078.
d) Find address of students whose marks are less than 50.

Question 2:

(i) What is the utility of relational algebra & relational calculus? Name some software’s based on these concepts?

(ii) Comment on the statement “Set theory has contributed a lot to RDBMS” support it with the help of suitable examples.

(iii) “Redundancy of data is many times beneficial” Justify the statement, also describe the situation when redundancy will mess up the current data base status, at that instance of time what actions you will prefer to take.

(iv) In Oracle we are having variety of versions Oracle 8, Oracle 9, etc, what does the associated number mean. Again we are having Oracle 8i, Oracle 9i etc, what does this “i” mean.

(v) Describe the various file organization techniques? How a binary tree is different from B-tree and B+ tree? Under which situation we need to use B+ tree or B tree.

Question 3:

(i) Prove “Any relation which is in BCNF is in 3NF,but converse is not true”
Consider the schema and functional dependency set of Empdept given below:

Empdept (emp# , Dept#, Manager#, Dept_Name , Dept_Loc)
Emp# Dept# --> manager# manager# --> Dept #

Viewing the given functional dependency set prove that the relation is in 3NF but not in BCNF.

(ii) Which functional dependencies are to be removed to achieve respective normal form? Discuss all the normal forms up to 4NF?

(iii) What is the mathematical basis of SQL? The SQL statement: select * from student will perform like projection or selection? Give details in support of your answer.

(iv) Describe ‘ACID’ properties of transaction violation of which properly leads to lost up date problem & suitable example.

(v) How 2-phase locking differs from 2-phase commit?

Question 4:

(i) How serial schedule differs from serializable schedule? How can you defect that the schedule is serializable?”A positively interleaved system can not be serialized“, Comment on the statement and prove it with suitable example? In which type of scheduling the problem of deadlock is prominent and in which type the problem of starvation is prominent. Justify your answer?

(ii) Number of users in a concurrent system continuously grows and we find that respectively one strategy of concurrency management flops and other is invoked. Describe various strategies of concurrency management in this scenario of development and handling of a concurrent Data base environment.

(iii) How centralized DBMS differs from distributed DBMS? Can the network of any DBMS afford to have bridges?

(iv) What is fragmentation? What are the various types of fragmentation? How you implement respective fragmentation schemes.

(v) Compare MS-ACCESS and ORACLE (give at least 4 comparisons).