1
Seat No.: Enrolment
GUJARAT TECHNOLOGICAL UNIVERSITY
MCA SEMESTER- II EXAMINATION WINTER 2018
Subject Code: 2620003 Date: 08-01-2019
Subject Name: Database Management System
Time: 02.30 pm to 5.00 pm Total Marks: 70
Instructions:
1. Attempt all questions.
2. Make suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.
Q.1

1.
2.
3.
4.
5.
6.
7.
Explain the following terms:
Tuples
Domain
Degree of Relation
Metadata
Candidate Key
Authentication
Deadlock
07

1.
2.
Answer the Following Questions:
What is an Attribute? Give examples of different types of attributes attribute of 1 type) for a STUDENT entity. Provide justification for the values of those attributes.
Write a Relational Algebra expression for the given query:
Display Name and Salary of all the employees who works for department.
Consider two relations Employee and Department as below.
EMP(Id, Name, DoB, JoiningDt, Dept_No, Salary)
DEPT(No, Name, Location)
03
04
Q.2

Decompose the following Relation upto 3NF. Also provide the explanation/ rules to convert from one normal form to another.
ORDER(order#, customer#, name, address, orderdate(product#, description, quantity, unitprice))
07

Draw an ER Diagram for the BUS Booking System.
A passenger book a ticket for a bus. Bus has its unique number, seating capacity, arrival time and departure time at a particular station. Bus travel by different routes. Route has its number and name. Bus has a conductor who checks the ticket of a customer. Bus has stops at different station. Stations are identified by their name and city.
07
OR

Draw an ER Diagram using the following statements.
In a hospital there are three different types of doctors (trainee, visitor and permanent) are there to checkup the patients admitted in the hospital. There are rooms in the hospital where patients are admitted and looked after by the nurse. Recipient maintains the records for each patient. Patient has to pay the bill against the medicines prescribed by the doctor and room changes.
07
Q.3

Differentiate Database and Database Management System. What are the advantages of database systems?
07

Explain functional dependency and compare 3NF with BCNF
Give an example of Multivalued dependency and explain how to handle it in 4NF?
03
04
2
OR
Q.3

Differentiate the process of generalization and Specialization. Take appropriate example.
07

What is data abstraction? How it is achieved in database system?
Explain role of different types of users of database.
03
04
Q.4

Define Relational Algebra and explain relational algebra operations: Select, Project, Union, Difference, Rename and EquiJoin.
07

What is lossless decomposition? How the dependency preservation is tested?
07
OR
Q.4

What is serializability? Explain conflict and view serializibility with appropriate examples.
07

Suppose that we decompose the schema into and
Show that the this decomposition is lossless join decomposition if the following set of Functional dependencies holds A BC, CD
07
Q.5

What is a Transaction? What are its Properties? Draw and explain Transaction state diagram.
07

Explain two-phase locking protocol with example.
07
OR
Q.5

How Deadlock is detected? Discuss the actions need to be taken for Recovery from Deadlock.
07

Explain Log-Based Recovery system with two types of database modification techniques.
07