Important Questions for GTU Exam
1.
What
is database and DBMS? What are the advantages of DBMS?
2.
What
are the different types of DBMS?
3.
What
is file system management? What are the advantages and disadvantages of file
system management?
4.
Explain
different components of database environment with a neat diagram?
5.
Explain
all DBMS functions in detail.
6.
Explain
costs and risks of database approach.
7.
Explain
in detail range of applications.
8.
With
neat sketch explain the structure of DBMS?
9.
Explain
the characteristics of people who deal with databases?
10.
What
are business rules? How do you discover them and translate them into data model
components?
11.
What
are the different types of data models? Explain them in detail.
12.
Explain
different data models based on degree of abstraction.
13.
What
is E-R model? Explain.
14.
Explain
different keys and integrity constraints available in DBMS.
15.
What
are relational set operators? Explain them in detail.
16.
What
is a relationship? Explain different types of relationships in detail.
17.
Explain
different Codd’s relational database rules.
18.
What
is an entity? What are strong and weak entities and associative entities?
19.
What
is an attribute? Explain different types of attributes.
20.
What
is a relationship? Explain weak and strong relationships with examples.
21.
What
is a degree of relationship? Explain various degrees of relationships in
detail.
22.
Explain
step wise procedure of how do you develop an ER diagram using an example of
tiny college.
23.
What
are various database design challenges while developing an ER diagram.
24.
Explain
key decisions in physical database design.
25.
Explain
the steps involved in the database design?
26.
Define
the following terms: 1.) Entity 2.)Relationship 3.) KEY
27.
Descriptive
attributes 5.) Primary key
28.
Auniversity
database contains information about teachers and courses. Teachers teach
courses , each of the following situations concerns the teachers relationship
set. For each situation Draw an ER
diagram that describes it.
1). Every teacher must
teach some course.
2). Teacher can teach the
same course in several semesters, and each offers must be recorded.
3). Every Professor must
teach some course.
4). Explain
about weak entries, class hierarch and aggregation.
29.
What
is normalization? Explain 1st, 2nd and 3rd normal forms
with examples.
30.
Explain
Boyce-Codd normal form, 4th normal form with examples.
31.
Explain
denormalization.
32.
What
are the problems that will be caused by Redund any.
33.
Discuss
the problems related to de composition.
34.
Define
FD and Attribute closure.
35.
Define
different Normal forms with examples.
36.
What
are super types and sub types? Explain them in detail.
37.
Explain
specialization hierarchy and inheritance.
38.
Explain
subtype discriminator, disjoint and overlapping constraints and completeness
constraints.
39.
Explain
entity clustering.
40.
Explain
when to use primary keys, composite primary keys and surrogate primary keys.
41.
Explain
at least 2 design cases in flexible database design.
42.
Explain
attribute inheritance with example.
43.
Explain
different DDL, DML AND DCL commands in detail with examples.
44.
Explain
different data types in SQL? Explain the syntax of creating a table.
45.
Explain
different SQL constraints and indexes creation with examples.
46.
Explain
the complete syntax of select statement? What are the different comparision and
logical operators used in it.
47.
Explain
some special operators used in SQL.
48.
How
do you add and drop columns for a table using advanced data definition
commands.
49.
What
are order by, group by and having clauses? Explain them with suitable examples.
50.
What
are aggregate functions? Explain them in detail with examples.
51.
What
are views or virtual tables? Explain their creation.
52.
Explain
different types of joins with suitable examples.
53.
Define
relation scheme and a relation instance.
54.
Write
the syntax for the following commands:
1) Creating
a table
2) Inserting
values into it
3) Selecting
values from a table
4) Deleting
row from a table
55.
Define
a View?
56.
Answer each of the following
questions briefly:
The questions are based on the
following relational scheme
Emp (eid: integer, enami:
String, age: integer, salary:real
Works (eid: integer, did: integer,
Pet-time: integer )
Dept ( did: integer,
dnaname : string, budget : real, managerid: integer).
1). Write
the SQL Statements required to create the above relations
including appropriate versions of all
primary key and foreign key constraint.
2). Write
an SQL Statement to give every employee a 15%
raise.
3). Write
the conceptual Evolution strategy for select statement?
57.
Explain
the following operators in relational Algebra.
1). Selection 2)
Projection 3) Set operators 4) join
58.
Explain
about title Relational calculus.
59.
Explain
about Domain Relational calculus.
60.
Consider
the following schema
Suppliers ( Sid : integer, sham : string address:
string )
Parts (pid : integer: pname: string, color:
string)
Catalog ( Sid: integer, Pid : integer, cost:
real)
Write the following queries in relational algebra, TRC, DRC
1). Find the names of suppliers who supply some red part.
2). Find the sids of supplers who supply some red or green
part.
3). Find the sids of supplers who supply every part.
61.
What
are relational set operators? Explain with examples.
62.
Explain
join operators with using and on clauses.
63.
What
are queries? Explain them with where, in, having, any, all and from clauses.
64.
What
are correlated sub queries? Explain them with suitable examples.
65.
Explain
different SQL functions of various categories with suitable examples.
66.
What
are oracle sequences? How are they created and used?
67.
What
are updatable views? Explain how are they updated?
68.
What
are triggers? Explain with suitable examples
69.
Explain
stored procedures with suitable examples.
70.
Explain
PL/SQL processing with cursors with suitable examples.
71.
What
are PL/SQL stored functions? How are they created?
72.
What
is a transaction? Explain transaction properties and transaction log.
73.
Explain
different problems of concurrency control.
74.
Explain
concurrency control with locking methods.
75.
Explain
different types of locks and two phase locking system for serializability.
76.
What
are dead locks? What are basic techniques to control deadlocks.
77.
Explain
concurrency control with time stamping methods.
78.
Explain
concurrency control with optimistic methods.
79.
What
is database recovery? Explain various techniques used to recover the databases.
80.
Explain
important properties of a transaction.
81.
Explain
different Anomalies Associated with Inter leaved Execution.
82.
Discuss
Lock based concurrency control algorithm.
83.
Explain
about view serializability
84.
Explain
about lock management
85.
Explain
about Dead locks
86.
Explain
about Concurrency control without locks
Questions from GTU Papers
87.
What
is data independence ? Explain the difference between physical and logical
data independence with example.
88.
What
are the responsibilities of a DBA ?
89.
Explain
following Term with suitable example
(1) Primary Key (2) Candidate Key
(3) Super Key (4) On delete cascade
90.
Write
down the query for the following table where primary keys are underlined.
Person(ss#, name, address)
Car(license, year, model)
Accident(date, driver,
damage-amount)
Owns(ss#, license)
Log(license, date, driver)
(1) find the total number of people
whose cars were involved in accidents in 2009.
(2) Find the number of accidents in
which the cars belonging to “S.Sudarshan”
(3) Add a new customer to the
database.
(4) Add a new accident recorded for
the Santro belonging to “KORTH”
91.
Consider
the employee data. Give an expression in SQL for the following query :
Employee(employee-name, street,city)
Works(employee-name,
company-name,salary)
Company(company-name, city)
Manages(employee-name, manager-name)
(1) find the name of all employees
who work for State Bank.
(2) Find the names and cities of
residence of all employees who work for State Bank.
(3) Find all employee in the
database who do not work for State Bank.
(4) Find all employee in the
database who earn more than every employee of UCO Bank.
92.
Draw
E – R Diagram for the School Management System.
93.
Explain
1NF, 2NF, 3NF and BCNF.
94.
Draw
E – R Diagram for Library Management System
95.
Consider
a relation R with five attribute A,B,C,D,E having following dependencies
: A _B, BC_E and ED_A. List all Keys
for R In which normal form table is, justify your answer.
96.
What
is join? Explain various type of joins with example
97.
Write
Note on Cursor and its types
98.
What
is deadlock? When it is occurs and how to avoid it?
99.
Explain
strict two phase locking with its advantage and disadvantages.
100.
Explain
DDL,DML,DCL
101.
Define
Discretionary access control
102.
Explain
linear search and binary search algorithm for selection operation.
103.
Write
with example various built in string functions.
104.
Describe
Data Encryption.
105.
Consider
following schema and write SQL for given statements.
Student(Rollno, Name, Age,Sex,City)
Student_marks(Rollno,Sub1,Sub2,Sub3,Total,Average)
Write query to
(i) Calculate and store total and
average marks from Sub1, Sub2 & Sub3.
(ii) Display name of students who
got more than 60 marks in subject Sub1.
(iii) Display name of students with
their total and average marks.
(iv) display name of students who got
equal marks in subject Sub2
106.
Explain
database system architecture with diagram in detail.
107.
List
the benefits of database approach.
108.
What
is constraint in database? Explain types of constraints with suitable example.
109.
Draw
symbols for following in E-R diagram:
110.
Weak
Entity set, Derived attribute
111.
List
relational algebra operators and explain any two with example.
112.
List
the major functions performed by DBA.
113.
Draw
E-R diagram for supplier who supplies different parts.
The parts
are used in different projects. Explain the mapping cardinality used. Assume
suitable attributes.
114.
Explain
generalization and specialization in ER diagram with suitable example.
115.
Explain
method of query optimization
116.
Explain
BCNF with example.
117.
What problems can occur due to wrong
database design? How they can be solved?
118.
Define functional dependency. Explain
trivial and non-trivial FD with example.
119.
Explain various steps of query evaluation.
120.
Given relation R with attributes A,B,
C,D,E,F and set of FDs as
A-> BC, E-> CF,
B->E and CD-> EF. Find out closure {A,B} + of the set of attributes.
121.
What is concurrency? What are the three
problems due to concurrency? How the problems
can be avoided, explain for one of the three problems.
122.
Explain Two-Phase Locking protocol.
123.
What is the difference between security
and integrity?
124.
Explain system recovery procedure with
check point record concept.
125.
Explain Two-Phase commit protocol.
126.
State and explain Heath’s Theorem.
127.
Explain briefly the meaning of
serializability of transactions.
128.
Explain the difference between
Discretionary access control and mandatory access
control.
129.
What is deadlock? Explain Wait-For-Graph.
130.
What are transaction control commands?
Explain any two commands.
131.
Write short note on database triggers in
PL/SQL.
132.
What is security of data? Explain data
encryption.
133.
Explain deadlock detection mechanism.
134.
Explain any two string functions in SQL.
135.
Write short note on cursors in PL/SQL.
136.
Explain the purpose of the database
system.
137.
Explain different database users.
138.
Explain three level architecture of database
system
139.
Explain candidate key, primary key and
foreign key
140.
Explain following relational algebra
operation
141.
Natural join operation
142.
Selection and projection operation
143.
Explain specialization and generalization
feature of ER diagram with example.
144.
Construct E-R diagram for a hospital with
a set of patients and medical doctors. Associate
with each patient a log of various tests and examinations conducted.
145.
Explain aggregation operation of ER
diagram.
146.
Construct E-R diagram of the bank. It
provides different kinds of bank accounts. And
loans. It operates number of branches.
147.
What are anomalies in database design? How
can we solve it?
148.
Explain BCNF with example.
149.
Explain how to find closure of a set of
attributes?
150.
Explain query optimization process.
151.
What is normalization? What is the need
for normalization?
152.
Explain 3NF with example
153.
What is non-loss decomposition in
database? How it is useful in database?
154.
Explain evaluation of expression process
in query optimization.
155.
Explain Two phase commit protocol
156.
Explain shadow paging
157.
Explain mandatory access control of
database security.
158.
Explain two phase locking.
159.
Explain deadlock detection mechanism
160.
Explain Data encryption in brief.
161.
we have following relations:
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#,quantity)
Answer the following queries in SQL.
(i) Find name of supplier for city = ‘Delhi’.
(ii) Find suppliers whose
name start with ‘AB’
(iii) Find all suppliers
whose status is 10, 20 or 30.
(iv) Find total number of
city of all suppliers.
(v) Find s# of supplier
who supplies ‘red’ part.
(vi) Count number of
supplier who supplies ‘red’ part.
(vii) Sort the supplier
table by sname.
And
From The Same Do The Followings
(i) Delete records in supplier table whose
status is 40.
(ii) Add one field in
supplier table.
(iii) Explain commit
command
(iv) Explain Curser in
PL/SQL.
And
From The Same Do The Followings
(i) Find name of parts
whose color is ‘red’
(ii) Find parts name
whose weight less than 10 kg.
(iii) Find all parts
whose weight from 10 to 20 kg.
(iv)Find average weight
of all parts.
(v) Find S# of supplier
who supply part ‘p2’
(vi) Find name of
supplier who supply maximum parts.
(vii) Sort the parts
table by pname.
And
From The Same Do The Followings
(i) Delete records in parts table whose color
is ‘blue’. 01
(ii) Drop one field in
parts table. 01
(iii) Explain rollback
command. 02
(iv) Explain stored procedure in PL/SQL. 03
162.
What is data independence? Explain different
types of data independence with
suitable example.
163.
Solve following queries with following
table, where underlined attribute is
primary
key.
Person(ss#, name, address), Car(license, year, model)
Accident(date, driver, damage-amount), Owns(ss#, license)
Log(license,
date, driver)
1.
Find the name of a person whose license number is ‘12345’.
2.
Display name of driver with number of accidents done by that driver.
3.
Add a new accident by ‘Ravi’ for ‘BMW’ car on 01/01/2013 for damage
Amount of 1.5 lakh rupees.
164.
Explain following terms with suitable
example.
(1) Primary Key
(2) Candidate Key (3) Foreign Key (4) On Delete Cascade
165. Construct
E-R diagram for a hospital with a set of patients and medical doctors.
Associate with
each patient a log of various tests suggested by doctors and
Examinations
conducted. Use Specialization and Generalization in your
Diagram.
166. Construct
E-R diagram for a bank which has many branches and it supports
Different types of
accounts. It also provides loans to customers. Use
Specialization and
Generalization in your diagram.
167. What
is an anomaly in database design? How it can be solved? Explain BCNF with
suitable example.
168.
Explain evaluation of expression process
in query optimization.
169.
Explain various types of joins with
example.
170.
What is deadlock? What are its prevention
and avoidance methods?
171. Explain
with suitable example, the process of normalization covering from 1NF to 3NF.
172.
What is trigger? Explain its type with
their syntax. What are the applications of trigger?
173.
What is concurrency? If not controlled
where it can lead to? What are the methods to control concurrency?
174. For
Supplier – Parts database
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#,quantity)
Answer the following queries in SQL.
1. Display the
name of supplier who lives in ‘Ahmedabad’.
2. Display the
part name which is not supplied yet.
3. Count how many
times supplier ‘S1’ has supplied part ‘P1’.
4. Find all
suppliers whose status is either 20 or 30.
175. For
Supplier – Parts database
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#
,quantity) Answer the following queries in SQL.
1.
Find the name of part having ‘Red’ colour.
2.
Delete parts whose weight is more than 100 gram.
3.
Count how many times each supplier has supplied part ‘P2’.
4.
How much time shipment is for more than 100 quantities?
176. Explain
following SQL*Plus commands with suitable example
1. Commit 2. Grant
3. Rollback 4.
Revoke
177. Consider
following schema and write SQL for given statements.
• student( rollno, name, branch)
• exam(rollno, subject_code,
obtained_marks , paper_code)
• papers(paper_code, paper_satter_name, university)
( i ) Display name of student who got
first class in subject ‘130703’.
(ii) Display name of all student with
their total mark.
(iii) Display list number of student in
each university.
(iv) Display list of student who has not
given any exam.
178. Explain
ACID properties of Transaction with suitable example.
179.
Draw E-R diagram for Hospital management
system and covert into set of table
schema.
180. Describe
various disadvantages of file system compare to Data base management system.
181. With
example explain various mapping cardinalities and total participation.
182. Consider
following schema and represent given statements in relation algebra form.
* Branch(branch_name,branch_city)
* Account(branch_name, acc_no, balance)
*Depositor(Customer_name, acc_no)
( i ) Find out list of customer who have
account at ‘abc’ branch.
(
ii ) Find out all customer who have account in ‘Ahmedabad’ city and balance is
greater than 10,000.
(iii) find out list of all branch name
with their maximum balance.
183.
What is canonical cover? Consider
following set F of functional dependencies on schema R(A,B,C) and compute
canonical cover for F.
A -> BC B -> C A
-> B AB -> C
184.
Consider wholesaler of booh schema.
Book(Book_id,
book_title,publisher,book_price)
( i ) Implement procedure which print
details of books whose price is more than
average price.(Use cursor).
(ii) Write a trigger such that if record
is deleted from book table, insert old record
in book_backup table.
185.
Explain various steps involved in Query
evaluation.
186.
Explain conflict serializability with
example.
187.
Explain transformation of relational
expression into equivalent relational
expression.
188.
Explain view serializability with example.
189.
Consider schedule S with transaction T1
and T2. T1 transfer Rs. 150 from
Account
A to C and T2 adds Rs. 50 into account A. Prepare concurrent schedule with two phase locking protocol.
190.
Explain various deadlock prevention
methods.
191.
Explain immediate database modification
log based recovery method. Also
192.
Explain role of check point in log base.
193.
Explain working of 2-phase commit protocol.
194.
Discuss disadvantages of file processing
systems and advantages of
Database Management Systems
195.
Differentiate the following: DA and DBA
196.
Explain three level architecture of
database system
197.
What is Relational Algebra? Define
Relational Algebra Operation
Cross product with example.
198.
What does ER model mean? Specify all its
notations. 02
199.
Construct an E-R Diagram for an insurance
company with a set of customers, each of whom owns number of cars, also
each can have number of recorded accident
associated with it.
200.
Explain Mapping Cardinalities.
201.
Obtain E-R diagram for the Admission
procedure in a university. An advertisement
is issued giving essential qualifications for the course, the last date for
recipient of application, and the fee to be enclosed with the application. A
clerk in the registrar's office checks the received applications to see if mark
sheet and the fee are enclosed and sends valid application to the concerned
academic department. The department checks the application in detail and
decides the applicant to be admitted, those to be put in the waiting list, and
those to be rejected. Appropriate letters are sent to the registrar's office
which intimates the applicant.
202.
Explain irreducible sets of Functional
dependency with example.
203.
Why do we need normalization? Explain 4NF
& 5NF.
204.
What is Functional Dependency? Explain
non-loss decomposition.
205.
Explain log based recovery and mention all
its types
206.
Explain the purpose of sorting with
example with reference to query optimization
207.
What is NULL? Explain
208.
Explain the advantages of PL/SQL
209.
What do you mean by security? What the
objective while designing secure database?
210.
we have following relations:
EMP(empno, ename, jobtitle, managerno,
hiredate, sal, comm, deptno)
DEPT(deptno, dname, loc) Answer the
following queries in SQL.
i) Find the Employees working in the
department 10, 20, 30 only.
ii) Find Employees whose names start with
letter A or letter a.
iii) Find Employees along with their
department name.
iv) Find Employees whose manager is KING.
v) Find the Employees who are working in
Smith's department
vi) Find the Employees who get salary more
than Allen’s salary.
vii)Display
employees who are getting maximum salary in each department
211.
Explain Stored Procedure in PL/SQL
212.
Explain Transaction Control Commands.
213.
Explain the purpose and application of
DBMS.
214.
What is
normalization? What is redundancy? Compare 1NF and 2NF with example.
215.
Give Symbol used
in E-R Diagram and Draw the E-R diagram of University exam System.
216.
Give Symbol used
in E-R Diagram and Draw the E-R diagram of Library Management System.
217.
Explain natural
join operation with example.
218.
Explain gernarization
feature of E-R Diagram.
219.
Explain
selection and projection operation with example.
220.
Explain commit
and rollback command.
221.
Explain DDL ,
DML and DCL with example
222.
Explain aggregation
opearation of E-R Diagram.
223.
Implement
following relation using SQL query.
Student(stud_no,stud_name,sub1,sub2,totalmark,percentage)
a.
Create the
table, add 5 records and display the data
b.
Calculate total
mark and percentage and also arrange the students on
ascending order of
total mark and also make a view of it.
c. Update the mark of
sub1 of student_no=111 with 50 and also
d.
Calculate totalmark and percentage accordingly.
224.
Implement
following relation using SQL query.
Employee(emp_no,emp_name,department,city,salary)
(1) Find all the employee whose emp_no is lessthan 100 and salary
more than 25000 and department is “Account”
(2) count the no of employee and Sum the salary of all employee
(3) Delete the employee having minimum salary.
225.
Explain Data
encryption
226.
Explain deadlock
detection.
227.
Consider
following schema and write SQL for given statements.
Student(RollNo, Name, Age, Sex, City)
Student_marks(RollNo, Sub1, Sub2,Sub3,Total,Average)
Write query to
(i) Display name and city of students whose total marks are
greater than 225.
(ii) Display name of students who got more than 60 marks in
each subject.
(iii) Display name of city from where more than 10 students
come from.
(iv) Display a unique pair of male and female students.
228.
Explain database
system 3 tier architecture with clear diagram in detail. 06
229.
Explain any two
aggregate functions of SQL.
230.
What is
constraint in database? Explain types of constraint with suitable example.
231.
Draw symbols for
following in ER diagram Relationship
Set, and Primary key attribute
232.
Explain
specialization and generalization concepts in ER diagram with suitable example.
233.
What is ON
DELETE CASCADE in SQL? Explain clearly with example.
234.
Consider table
R(A,B,C,D,E) with FDs as A->B, BC->E and ED-> A. The table is in which normal form? Justify your
answer.
235.
Explain various
types of join with suitable example.
236.
Explain Trigger
and its types. Explain its applications and also the syntax to create it.
237.
Explain Two
Phase Locking protocol. What are its advantages and disadvantages?
238.
Explain system
recovery procedure with check point record.
239.
Explain in
detail Discretionary access control and mandatory access control.
240.
What is cursor?
Explain the types of cursors.
241.
Explain the
measures of finding out the cost of a query in query
processing. Explain any two
string functions in SQL.
242.
Define: (1) Data
(2) Entity (3) Meta Data (4) Super key (5) Not Null (6) Data Integrity (7) Dual
243.
Differentiate
Between: (1) update and insert (2) primary key and foreign key (3) grant and revoke (4) row and column
244.
Define E-R
Diagram. Draw E-R diagram with Customer, Loan and Payment sets.
245.
Write query for
the following:
(1) To create a table from a table.
(2)
To eliminate duplicate rows.
(3) To add a new column in the table
(4) To sort data in a table
246.
What is a view?
Explain how to create, its types and significance in DBMS.
247.
Explain all
types of Joins with commands and examples.
248.
What is
deadlock? Explain necessary conditions for deadlock and methods for handling it.
249.
Define Failure?
Explain Log based Recovery.
No comments:
Post a Comment