An organization needs to provide Medical facilities to its employees and their dependents. Organization is having a list of Doctors, Hospitals and Test centres for the employees facility. An employee may get Medical facility from the list of Doctors, Hospitals and Test centres provided by the organization to them. Employee does not need to pay anything for the facilities availed. The Doctors, Hospitals and Test centres directly raise their bill to the organization.

Identify the entities, relationships, constraints and cardinality and construct an ER diagram for the above mentioned specifications. List your assumptions and clearly indicate the cardinality mappings as well as any role indicators in your ER diagram.

Explain the need of Distributed DBMS over Centralized DBMS. Also give the structure of Distributed DBMS.

What do you mean by fragmentation of a database? What is the need of fragmentation in DDBMS environment? Explain different types of fragmentation with an example of each.

List and explain the 4 basic properties of a Transaction with the help of appropriate examples.

For what reasons is 2-phase locking protocol required? Explain. Discuss the disadvantages of basic 2-phase locking protocol. List the ways and means that can be used to overcome the disadvantages.

Discuss the importance of file organisation in databases. Mention the different types of file organisations available. Discuss any one of the mentioned file organisations in detail.

Give the limitations of file based system. How can they be overcome using DBMS?

Compute the closure of the following set F of functional dependencies for relation schema

R = (A, B, C, D, E).

List the candidate keys for R.

Explain the Log-based recovery scheme with the help of an example.

Consider the relation R (A, B, C, D, E) and the set of functional dependencies :-

F(A→D, {A,B} → C, D → E)

Assume that the decomposition of R into {R1 (A, B, C) and R2 (A, D, E)}. Is this decomposition lossless? Justify?

How are database security and database integrity related? Briefly discuss the different levels of security measures which may be considered to protect the database.

What are integrity constraints? Discuss the various types of integrity constraints that can be imposed on database.

Write SQL commands for each of the following. Also illustrate the usage of each command through suitable example.

(i) Creation of views

(ii) Creation of sequences

(iii) Outer join

(iv) To give access permission to any use

With the help of a suitable example, discuss the insertion, deletion and updation anomalies that can occur in a database. Briefly discuss the mechanism to remove such anomalies.

Write a query in SQL for the following:

(i) Find the name of authors whose books are published by "ABC Press".

(ii) Find the name of the author and price of the book, whose Book_ID is '100'.

(iii) Find the title of the books which are published by Publisher_ID '20' and are published in year 2011.

(iv) Find the address of the publisher who has published Book_ID "500".

What is SQL? Explain its important features.

b) Consider the following schemas:

BOOK (Book_ ID, Title, Publisher_ID, Year_of Pub, Price)

AUTHOR (Author_ID, Book_ID, Author Name)

PUBLISHER (Publisher_ID, Book ID, Address, Name_of Pub, No. of Copies)

Probability Distribution

Correlation and Regression

Z-test and t-test

Linear and circular systematic sampling