Question 1:
(a) What are the advantages of having three-level database architecture?
How are they related to Data Independence? Explain with the
help of an example.
(b) What are the different forms of integrity control in database
management system? Describe with the help of examples.
(c ) What is a Transaction? What are the problems of concurrent
transactions? Describe with the help of examples.
(d) What is locking? How does it solve the problem of concurrent
transaction execution? Describe with the help of examples.
(e) What is database security? How can a database be made more
secure?
(f) How can a database recover from failure when many transactions
are going on? Describe with the help of an example.
(g) What is the purpose of a Primary and Secondary indices?
Explain with the help of examples.
(h) Which of the two indices enhance performance more? Give
reason.
(i) What is a distributed database management system? How is
it different to that of client server database systems?
Question 2:
(a) How can a Database recover from the failure of media? Explain
this with the help of an example database of a Departmental
store, where online purchases and sales transactions are going
on. Also write the pseudo-code for the transactions such that
these transactions do not have any concurrency related problems.
Use Locks at appropriate places. Make suitable assumptions,
if any?
(b) Prepare an E-R diagram for your study center showing all
the entities, the associations like the aggregation, specialization
and generalization. Assumptions can be made wherever necessary.
Question 3:
Consider a “Library Management System” which keeps
the following tables:
Book (isbn-no, book-title, author, publisher, edition, year-of-copyright)
BookAccession (accession-no, isbn-no, date-of-purchase)
Members (m-id, m-name, m-address, m-phone).
Issue-return (accession-no, m-id, expected-date-of-return, actual-date-of-return)
Please note that a member can be issued a book for a period of 15 days. The actual-date-of-return is kept blank for the books that have not been returned. Write and run the following SQL queries on the tables:
(i) Find the m-id and m-name of the members who have got maximum
number of un-returned books.
(ii) List the book details along with the number of copies for
that book in the library (issued or not-issued both)
(iii) Find the names of all those students who have got all
the books issued to him of the author named “ABC”
.
(iv) Find the books that are expected to be returned in this
week.
(v) Find those members who have not got any book issued to him/her
during last six months.
Make suitable assumptions, if any.