1/2
Seat No.: Enrolment
GUJARAT TECHNOLOGICAL UNIVERSITY
DIPLOMA ENGINEERING SEMESTER -III• EXAMINATION SUMMER 2017
Subject Code: 330703 Date: 05 05 2017
Subject Name: Database Management System
Time: 02:30 PM TO 05:00 PM Total Marks: 70
Instructions:
1. Attempt all questions.
2. Make Suitable assumptions wherever necessary.
3. Figures to the right indicate full marks.
4. Use of programmable Communication aids are strictly prohibited.
5. Use of only simple calculator is permitted in Mathematics.
6. English version is authentic.
Q.1

Explain functions and responsibilities of data base administrator.
07





Draw a neat diagram of structure of a DBMS and explain it.
07

DBMS

Q.2

What is E-R diagram? Explain in brief with suitable example.
07


E-R diagram


Justify: DBMS is better than file oriented system.
07

DBMS

OR

Explain outer join operations of relational algebra with example.
07

Relational algebra

Q.3

Explain any two data models in brief.
07





Explain super key and candidate key of relational algebra.
07

Relational algebra

OR
Q.3

Explain project and union operation of relational algebra.
07


Relational algebra


List various character functions of SQL. Explain any two with suitable example.
07

SQL

Q.4

What is constraint? Explain primary key and foreign key constraint.
07


Constraint Primary key foreign key constraint


Explain physical data independence and logical data independence.
07

Physical data independence logical data independence

OR
Q.4

What is attribute? Explain different types of attributes with suitable example.
07




2/2

Write a short note on generalization.
07

Generalization

Q.5

Differentiate between disjoint sub-classes and overlapping sub-classes.
07


Disjoint sub-classes overlapping sub-classes


For Railway reservation database write the following queries in SQL:
Passengers pname, rate, age)
Train tr_name, zone, capacity)
Reserves tr_id, date, seat_no)
Find all passengers who have reserved seats in western railway.
Find the names of passengers who have reserved seats in train 'TR1001' is a train Id).
Find the names and age of youngest passenger.
Find the average age of passengers.
02
02
02
01

SQL
Passengers pname, rate, age)
Train tr_name, zone, capacity)
Reserves tr_id, date, seat_no)

train Id






OR
Q.5

Write a short note on specialization.
07


Specialization


Using given tables write SQL statements for the following queries
employee(personname, street city)
works(personname, companyname, salary)
Find the names and cities of residence of all employees who work for Infosys.
Find the maximum salary provided by each company.
(iii)Find the names and street addresses of employees who live in ahmedabad.
Find the names of all companies.
02
02
02
01

SQL queries
employee(personname, street city)
works(personname, companyname, salary)