Seat No.: Enrolment No.

GUJARAT TECHNOLOGICAL UNIVERSITY

Diploma Engineering – SEMESTER – 3 (NEW) – EXAMINATION – Winter-2023

Subject Code: 4331603 Date: 18-01-2024

Subject Name: Database Management
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 non-programmable scientific calculator is permitted.
6. English version is authentic.

Marks
Q.1 (a) Define the following terms: a). Data items b). Data dictionary c).Meta data 03
નીચેના શબ્દો વ્યાખ્યાયયત કરો: a). Data items b). Data dictionary c).Meta data
(b) Explain disadvantages of File oriented system. 04
ફાઇલ ઓયરએન્ટે ડ યિસ્ટમના ગેરફાયદા િમજાવો.
(c) Describe the responsibilities of DBA in detail. 07
DBA ની જવાબદારીઓનું યવગતવાર વર્ણન કરો.
OR
(c) Define data abstraction? Explain Three level Architecture of DBMS. 07
Data abstraction ની વ્યાખ્યા આપો? DBMS નું ત્રર્ સ્તરનું આયકણ ટેક્ચર િમજાવો.

Q.2 (a) Define the Following Terms :a).Relationship set b).Participation c).Candidate key 03
નીચેના શબ્દો વ્યાખ્યાયયત કરો: a).Relationship set b).Participation c).Candidate
key
(b) Explain Generalization with example. 04
Generalization ઉદાહરર્ િાથે િમજાવો.
(c) Define E-R diagram? Explain different symbols used in E-R diagram with example. 07
E-R Diagram ની વ્યાખ્યા આપો? E-R ડાયાગ્રામમાું વપરાતા યવયવધ Symbolsને
ઉદાહરર્ િાથે િમજાવો.
OR
Q.2 (a) Define Relational Algebra? List out various operations in relational algebra? 03
Relational Algebra ની વ્યાખ્યા આપો? Relational Algebra માું યવયવધ કામગીરીની
યાદી આપો?
(b) Explain Specialization with example. 04
Specialization ઉદાહરર્ િાથે િમજાવો.
(c) Define attribute? Explain different types of attributes with example. 07
Attribute ની વ્યાખ્યા આપો? યવયવધ પ્રકારના Attributeને ઉદાહરર્ િાથે િમજાવો.

Q.3 (a) Explain the GRANT and REVOKE statement in SQL. 03

SQL માું GRANT અને REVOKE સ્ટે ટમેન્ટ િમજાવો.
(b) Explain following Character functions. 1) INSTR 2) LENGTH 04
નીચેના Character functionિમજાવો .1) INSTR 2) LENGTH
(c) Write SQL statements for following table: 07
Student(Enno,name,branch,sem,clgname,bdate)
1. Create a table Student
2. Add a column mobno in Student table.
3. Insert one record in student table.
4. Find out list of students who have enrolled in “IT” branch.
5. Retrieve all information about student where name begin with ‘a’.
6. Count the number of rows in student table.
1
 7. Delete all record of student table.

નીચેના Table માટે SQL સ્ટે ટમેન્ટ લખો:

Student(Enno,name,branch,sem,clgname,bdate)
1. Create a table Student
2. Add a column mobno in Student table.
3. Insert one record in student table.
4. Find out list of students who have enrolled in “IT” branch.
5. Retrieve all information about student where name begin with ‘a’.
6. Count the number of rows in student table.
7. Delete all record of student table.
OR
Q.3 (a) Explain equi join with example in SQL. 03
SQL માું equi join ઉદાહરર્િાથેિમજાવો.
(b) Explain following Aggregate functions. 1) MAX 2) SUM 04
નીચેના Aggregate function િમજાવો .1) MAX 2) SUM
(c) Write SQL statements for the following table: 07
Employee(EmpID,Ename,DOB,Dept,Salary)
1. Create a table Employee
2. Find sum of salaries of all employee.
3. Insert one record in Employee table.
4. Find the names of employees who salary between 25000/- and 48000/-.
5. Display detail of all employees in descending order of their DOB.
6. List name of all employees whose name ends with ‘a’.
7. Find highest and least salaries of all employees.

નીચેના Table માટે SQL સ્ટે ટમેન્ટ લખો:

Employee(EmpID,Ename,DOB,Dept,Salary)
1. Create a table Employee
2. Find sum of salaries of all employee.
3. Insert one record in Employee table.
4. Find the names of employees who salary between 25000/- and 48000/-.
5. Display detail of all employees in descending order of their DOB.
6. List name of all employees whose name ends with ‘a’.
7. Find highest and least salaries of all employees.
Q.4 (a) Consider a following relational schema & give Relational Algebra Expressions 03
for the following queries.
Student (Enrollment_No,Name,DOB,SPI)
i. List out all students whose SPI is greater than 7.0.
ii. List name of student whose enrollment number is 007.
નીચે દશાણવેલ યરલેશનલ સ્કીુંમાું ને ધ્યાન માું લઇ દરેક ક્વેરી માટે યરલેશનલ એલજીબ્રા
એક્િપ્રેિન લખો.
Student (Enrollment_No,Name,DOB,SPI)
i. List out all students whose SPI is greater than 7.0.
ii. List name of student whose enrollment number is 007.
(b) Write a short note on partial functional dependency. 04
Partial functional dependencyની ટું કી નોુંધ લખો.
(c) Explain need of Normalization? Discuss about 2NF with example. 07
Normalization ની જરૂયરયાત િમજાવો? ઉદાહરર્ િાથે 2NF યવશે ચચાણ કરો.
OR
Q.4 (a) Consider a following relational schema & give Relational Algebra Expressions 03
for the following queries.
Student(Enno,name,age,address)
i. Find the name of students who live in Surat.
ii.List the name of students who are older than 30.
નીચે દશાણવેલ યરલેશનલ સ્કીુંમાું ને ધ્યાન માું લઇ દરેક ક્વેરી માટે યરલેશનલ એલજીબ્રા
એક્િપ્રેિન લખો.
Student(Enno,name,age,address)
i. Find the name of students who live in Surat.
ii.List the name of students who are older than 30.
(b) Define 1 NF? Explain 1NF with suitable example. 04
2
 1NF ની વ્યાખ્યા આપો? યોગ્ય ઉદાહરર્ િાથે 1NF િમજાવો.
(c) Define Transitive Dependency? Explain 3NF with suitable example. 07
Transitive Dependency ની વ્યાખ્યા આપો? યોગ્ય ઉદાહરર્ િાથે 3NF િમજાવો.
Q.5 (a) Define Serializability? Explain rules of serializability? 03
Serializability ની વ્યાખ્યા આપો? Serializabilityના યનયમો િમજાવો?
(b) Explain Attributes of Implicit Cursors. 04
Implicit Cursorsના Attribute િમજાવો.
(c) Explain two phase locking protocol with suitable example. 07
Two phase locking protocol ને યોગ્ય ઉદાહરર્ િાથે િમજાવો.
OR
Q.5 (a) Explain ACID properties of transaction. 03
ટર ાન્ઝેકશનની ACID પ્રોપટી િિમજાવો.
(b) Define Triggers? Explain advantages of triggers. 04
Triggers ની વ્યાખ્યા આપો? યટર ગિણના ફાયદા િમજાવો.
(c) List down problems of concurrency control. Explain any two with suitable example. 07
Problems of concurrency controlની યાદી બનાવો .કોઈપર્ બેના યોગ્ય ઉદાહરર્ િાથે
િમજાવો.