Gujarat University
MCA Semester - III
Section I
Q.I . Study the following relational scheme:
Emp ( emp_cd, emp_nm,salary, manager_cd)
Project (proj_cd, proj_nm)
Assigned_to(ernp_cd,proj_cd)
Assume the following. Write your assumptions, if any.
• Employee may be assigned to more that one project.
• Manager is an employee.
Solve the following queries in
I. Relational algebra [3]
II. SQL [6]
Queries:
a) Retrieve names of all employees with the name of the project to which they are assigned.
b) Count the number of employees assigned to each project.
c) For a given emp_cd, find the name of the manager.
d) Retrieve the names of employees who are not assigned to any project.
e) Retrieve the names of employees who are assigned to all the projects.
f) List project name and average salary of employees assigned to that project where average salary of
employees in the project is higher than Rs-8500.
Q.2 A university is offering various courses. Student, submits an application form to get an admission in a
course. For each course: there are certain criteria which student has to satisfy: Each course has a fixed
number of seats. Admissions are given based on the merit achieved Wan 'open test. Students are called for
personal interview in. the order of .merit and offered admission provided seats are available. Once the
student pays fees, s/he is enrolled and registered.
List down the entities and relationships with necessary attributes. Develop complete Entity-Relation model and membership class of participating entities. [8]
OR
Q.2 a) Explain 'delete' and 'update' rules 'for the foreign key. Why is an 'insert' rule not necessary for the
foreign key? [3]
b) Explain ANSI/SPARC architecture of DBMS. [3]
c) Write down the role of DBA. [2]
Q.3 Following is a relational schema for a Library. [8]
Library (member_id, member_nm, member_addr, membership_type,
no_of_tickets_allowed, no_Of_tickets_given, book_id, isbn_no,
book_title, authorl, author2, publisher, language_of_book,
book_issue_no,book_issue_date,book_.return_date)
Explain anomalies (if any) regarding insert, delete and update when above scheme is implemented in a database.
Write your assumptions and convert the above scheme irito3NF.
OR
Q.3 Explain about FD, MVD and JD in detail with examples. [8]
Section II
Q.4 Write any THREE short notes. [9]
a)Serializability
b) Stages 'in query processing
c) Two-phase commit protocol
d) Discretionary access control
Q.5 a) Discuss about concurrency problems in detail. [4]
b) List DOBMS objectives and explain any two. [4]
OR
Q.5 a) Describe Any two methods to implement relation operator 'join': [4]
b) Explain ‘Divide and conquer strategy’ for query optimization in DDBMS with example. [4]
Q.6. Attempt any FOUR [8]
a) What is 3VL? Why should one allow the foreign key to be NULL?
b) Define View and write and its advantages.
c) Explain checkpoint in recovery
d) Describe intent locks in brief.
e) Differentiate between table and relation
f) write about database integrity rule.
