Q.1. (a) Consider the following requirements of a university database system :
The university keeps track of its students. It stores the student
name, enrolment number, date of birth, phone number, gender and address.
The university also keeps track of the programmes offered by it. The
information that is to be kept about the programme is : programme
code, programme name, fee, minimum eligibility and date of start of
programme.
The university has many departments. A programme is associated with
only one department. Each department has a location address and name.
A student can register only for one programme at a time
Draw an E- R diagram for the university. Make suitable assumptions,
if any.
(b) Consider the following relational scheme:
- Books (book_id, b_name, author, purchase_date, cost)
- Members (member_id, m_name, address, phone, birth_date)
- Issue_return (book_id, member_id, issue_date, return_date)
Formulate SQL queries to the following :
(i) Find the names of all those books that have not been issued.
(ii) Display the member_id and the number of book issued to that member.
(Assume that it a book in issue_return relation does not have a return_date,
then it is issued.)
(iii) Find the book that has been issued the maximum number of times.
(iv) Display the names and authors of books that have been issued
at any time to a member whose member_id is "ab".
(v) List the book-id of those books that have been issued to any member
whose date of birth is less than '01 01-1985', but have not been issued
to any member having the birth date equal to or greater than '01-01-1985'.
(c) What is data independence in the context of a DBMS ? Explain with the help of an example.
(d) List at least four problems of concurrent transactions. Explain each with the help of an example.
(e) What is a primary index ? How is it different from secondary index ? Explain them with the help of an example each. Why is secondary index more helpful in increasing the efficiency ? Explain what the help of an example.
Q.2. (a) Explain the six limitations of file based systems. How can the problem of data dependence be solved by Database systems ? Explain this with the help of an example.
(b) A bank has many branches. A customer can open his/her account
in any branch and can operate his/her account from any branch. The
information that is stored about the account includes account number,
branch, name of the customer, address of customer, guarantors of the
customer, account balance
(i) Suggest a suitable fragmentation scheme for the bank. Give reasons
in support of your scheme.
(ii) Suggest a suitable replicaiion scheme. Justify your suggestion.
Make suitable assumptions, if any.
(c) Write at least two advantages and two disadvantages of data replication.
Q.3. Consider the following relation :
UNIVERSITY (student_id, s-name, programme, subject, subject name,
credits, number_of_hrs, date_of_registration_to_programme, maximum-duration-of_programme)
Please note the following points about the database above :
A student can enrol for many programmes at a time.
Number_of_hrs per credit is 30.
A programme may consist of many subjects. One subject belongs to only
one programme.
(i) Explain at least three anomalies that exist in the relation above.
(ii) What are the functional dependencies in the relation above ?
What is the primary key of the relation ?
(iii) Normalise th e relation to first 2NF and then to 3NF.
(iv) Write the SQL commands for creation of tables.
Make suitable assumption, if any.
Q.4. (a) What is a log ? What are its contents ? How can log be used for database recovery ? Explain this with the help of an example. How is a checkpoint useful for log based recovery ? Explain with the help of an example.
(b) Why are multiple access paths needed for database files ? Explain with the help of an example. Explain the multilist file organisation with the help of an example.