(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?
(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.
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.