Master in Data Science (SMS, TU) Database Management Systems Question Paper 2080 Nepal

How wait for graph is used to determine deadlock?

Why group by and having clauses are used in SQL? Support your answer with suitable examples.

How division operation is done in relational algebra? Illustrate with example.

When a relation is said to be in BCNF?

Define database schema and instance with example.

How security in database can be ensured using discretionary and mandatory access control mechanisms. [6]

OR

How database is created using XML? Create a XML file to store information about student. Use your own assumptions if required. [2+4]

What is distributed database? Differentiate vertical fragmentation from horizontal fragmentation using suitable example. [2+4]

OR

What is indexing? How sparse indexing is different from dense indexing? How B+ tree is used in indexing? [1+2+3]

What is query optimization? How materlization is different from pipelining? Prepare the query tree and optimized query tree for following SQL statement;

SELECT FNAME, ADDRESS FROM EMPLOYEE INNER JOIN DEPARTMENT ON DNO = DNUMBER WHERE DEPARTMENT.DNAME='Research';

[1+2+3]

Describe weak entity with an example. Convert following ER diagram into the equivalent relational schema. [2+4]

The ER diagram contains: Entity Actor (attributes: Actorid [key], Name, Phone [multivalued]) related via Plays (1:M, attribute Quantity) to entity Movie (attributes: Title [key], ReDate). Movie is related via Producedby (M:N) to entity Producer (attributes: Prodid [key], Name, Budget).

What is serializability? How can you determine whether a schedule is conflict serializable? Given following schedule, determine whether it is conflict serializable or not and justify your answer. [1+2+3]