Question 1)a) Describe Advantages and disadvantages of Hierarchical Data Model and Network Data Model.
b) Explain the levels of Data Abstraction in DBMS.
Question 2)a) Explain Query Processor and Storage manager components of DBMS.
b) Describe advantages and disadvantages of DBMS approach.
Question 3)a) Consider University database which keeps track of students, their majors, their transcripts and their registrations, as well as of the university's course offerings. The database also keeps track of the sponsored research projects of faculty and graduate students. Draw the E-R Diagram which must be consist of Entity sets, attributes, relationships, mapping cardinality and keys.
b) Describe strong entity set and weak entity set in detail.
Question 4)a) Draw ER Diagram for Banking enterprises which must be consist of Entity sets, attributes, relationships, mapping cardinality and keys, aggregation, specialization, Role and Generalization.
b) Describe the constrains on Generalization in EER.
Question 5)a) What do extension and Intentions of relations mean.
b) Describe SQL Sequence and View Object.
c) Describe codd's comprehensive sub-language rule and data independence rule.
Question 6)a) Describe different keys used in relational model and Domain Constraints with suitable example.
b) Describe database languages with suitable example
Question 7)a) Consider the following Relations. It defines the schema of the database application for a library.
BOOK (Book_ISBN[pk], Title, Publisher_Name[fk])
BOOK_AUTHORS (Book_ISBN [pk,fk], Author_Name[pk])
PUBLISHER (Name [pk], Address, Phone)
BOOK_COPIES (Book_ ISBN [pk,fk], Branch_ID [pk,fk],Num_ Copies)
BOOK_LOANS (Book_ISBN[pk,fk], Branch_ID [pk,fk], Card_Num[pk,fk], Date_Out, Date_Due)
LIBRARY_BRANCH (Branch_ID [pk], Branch_Name, Address)
BORROWER (Card_Num[pk], Name, Address, Phone)
Answer the following queries in each of the SQL query languages that you know:
i) List all the ISBN and title of all books written by "John Smith".
ii) List all the ISBN and title of all books written by "John Smith" as the only author.
iii) List all the Card number and name of all borrowers who checked out two or more books on 10/16/2003.
iv) List all the branch ID and name of all library branches that have at least one copy of all the books.
v) List all the card number and name of borrowers who check out books from the LPL03 branch.
vi) List all the names of authors who use only one publisher.
vii) List all the names of authors and count of the number of books they have published. The list will be in descending order by the number of books.
viii) List all the ISBN, title and publisher of all books that have the word "Louisiana" in the title.
ix) List all the card number and name of the borrower who has checked out the most books.
x) For each branch list the branch ID and the number of books they have. This is the total number of books including multiple copies.
b) Explain sub query in SQL.
Question 8)a) The HRD manager has decided to raise the salary for all the employees in department no. 20 by 0.05. Whenever any such raise is given to the employees a record for the same is maintained in the emp_raise table.
It includes the employee no, the date when the raise was given and the actual raise. Write a PL/SQL block to update the salary of each employee and insert a record in the emp_raise table, create employee and department table.
b) Write brief notes on Assertion and Database Triggers.