Ques 1.

What are the advantages of a database management system over a file-based system? Explain the three-level architecture of a DBMS.

Ques 2.

Explain the following terms in the context of a relational model with the help of one example of each– Cartesian Product, Candidate Key, Primary Key constraint, Set difference operation and Referential Integrity.

Ques 3.

Explain the concept of clustering index with the help of an example

Ques 4.

A Bank uses a database system for maintaining information about its customers. This database is
used for the following requirements:
 To find the customer id, name, account number and balance of various customers.
 To find the deposits and withdrawals made by a customer for a specific period.
 To calculate the interest on the customer deposit.
Perform the following tasks for the Bank:
i. Draw an ER diagram for the Bank. Specify key attributes and constraints on each entity type
and on each relationship type. Note any unspecified requirements and make appropriate
assumptions to make the specification complete.
ii. Design normalized tables in 3NF from the ER diagram drawn in part (i), with the required
integrity constraints. 

Ques 5.

What is MVD? Explain with the help of an example. Explain the 4th Normal Form.

Ques 6.

Consider the Relation R={A, B, C, D, E, F, G} and the set of functional dependencies.
A→ DE B→CG AB→ EF
What is the key to the relation R? Decompose R into 2NF and 3NF relations.

Ques 7.

Consider the following relational database:
Student (stid, stName, stProg, Phone)
Programme (progId, prName, prDuration, prFee)
Course (courseCode, courseName, courseCredit)
CourseProgramme(progID, courseCode)
The underlined attributes form the primary key of the relations. In relation Student, the stProg is
the programme code of the student and should reference progId of the Programme table. Please
note that a course may be part of several programmes. Please note that the CourseProgramme relation has two foreign keys. Write and run the following SQL queries on the tables:
(i) Create the tables with the primary and foreign key constraints.
(ii) Insert at least 5 records in the first 3 tables and 20 records in the 4th table.
(iii) List all the Programmes of the University in the order of programme name
(iv) Find the list of all the courses of programme whose ID is “PGDCA”.
(v) Find the list of those courses that are part of more than one programme.
(vi) Find the list of courses, which are to be studied by a student whose ID is “S001”
(vii) Find the total credits of each programme.
(viii) Find the list of students in each Programme.
(ix) List the pair of students who share the same phone number.
(x) List all the students whose name starts with the alphabet ‘A’.

Ques 8.

Explain the problems of concurrent transactions with the help of examples. What is a serializable schedule in this context? What is the two-phase locking protocol? Does it solve the problems of concurrent transactions? Justify your answer. Can deadlock occur when you use two-phase locking? Justify your answer.

Ques 9.

Explain the process of recovery using a log file. How is a checkpoint useful in recovery? Explain. Explain the process of access control in the context of database system security.

Ques 10.

Explain any one method of the query cost computation for SELECT and JOIN operations. Make the query tree for the following query (assume the database of problem 2(c).
SELECT s.name, p.pogID, c.courseName
FROM Student s, Programme p, Course c, Course_Programme cp
WHERE s.stProg = p.progID AND p.progID = cp.progID AND
c.courseCode=cp.courseCode AND progID=”MCA”;

Ques 11.

Explain the following with the help of an example of each: 
a) Features of Object-oriented database systems.
b) The multi-dimensional data model of a data warehouse.
c) Multimedia database
d) NoSQL databases