Aryabhatta Knowledge University
Sem. iv 2013 Time 3hr Full marks: 70
DATABASE SYSTEMS
Attempt any five questions in which question no 1 is
compulsory
1.
Choose the correct option (any seven) :
(a)
In SQL, SELECT is a
(i)
DDL command
(ii)
DML command
(iii)
DCL command
(iv)
TCL command
(b)
Primary key never takes
(i)
Date
(ii)
NULL
(iii)
Constant value
(iv)
NOT NULL
(c)
What is the maximum number of functional
dependencies (trival and non-trivial) of a relation R of degree n?
(i)
2n
(ii)
22n
(iii)
n!
(iv)
2n
(d)
In SQL, UPDATE clause changes
(i)
The value of attributes
(ii)
The name of columns
(iii)
The name of table
(iv)
None of the above
(e)
Second normal form never supports
(i)
Transitivity relation
(ii)
Multivalued attribute
(iii)
Partial dependencies
(iv)
None of the above
(f)
From a relation ‘π’operator selects
(i)
Row
(ii)
Attribute
(iii)
Domain of attribute
(iv)
Name of relation
(g)
What are the components of an ER model?
(i)
Entities
(ii)
Attributes
(iii) Relationships
(iv) All
of the above
(h)
What is the RDBMS terminology for column or
field ?
(i)
Tuple
(ii)
Relation
(iii) Attribute
(iv) Domain
What is the name of the attribute or attribute
combination of one relation whose values are required to match those values are required to match
those of the primary key of same other relation?
(i)
Primary key
(ii)
Candidate key
(iii) Attribute
(iv) Domain
(i)
When all the columns in a table fully
functionally depend upon the primary key, the table is said to satisfy the –
normal form.
(i)
First
(ii)
Second
(iii)
Third
(iv)
Fourth
2.
(a) what
are database system ? what are the
characteristic that distinguish a DBMS
from traditional file processing system?
(b) Explain the elements of a database with the help of a neat diagram.
3.
A university registrar office maintains data
about the following entities:
·
Courses
including number ,titles ,credits,syllabusand prerequisites
·
Courses
_offering including course _no ,year ,semester , section_no ,instructor ,
timings and classroom
·
Students
including student _id ,name and program
·
Instructor
including identification_no ,name, department, and title
Further the enrolment of students in courses and grades awarded to
students in each courses and grades awarded to students in each courses they
are enrolled for must be modelled .construct an ER diagram for the diagram for
the registrar ‘s office and document all assumptions that you make about the
mapping constraints.
4.
Consider the databse below and express the
queries in SQL:
Employee
(person-name,street ,city)
Works (person-name
,company-name ,salary)
Company(company-name,city)
Manages(person-name
,manager-name)
(a)
Find the names of all employees in this database who live in the
same city as the company for which they work.
(b)
Find the names of all employees who live in the
same city and on the same street as do their managers.
(c)
Find the names of all employees who earn more
than every employee of ‘Aije Bank Ltd.’
(d)
Find all companies located in every city in
which ‘Rural Bank (India)’is located.
(e)
Give the
managers a 10 percent salary raise, whose salary is greater than 50,000 and for
others, give only a 3 percent raise.
5.
(a) what is query tree ? when is it required ?
construct a query and generate a query tree for that.
(b) suppose a relation R with
attributes A,B,C and D. For the set of FDs,C→D,C→A, B→C.Identify the best normal form thatR satisfies
6.
(a) show with example that ‘not exits’ keyword
can be used to implement division operaor in SQL.
(b) write a short note on integrity constraints.
7.
(a) discuss the ‘insertion anomalies’, ‘updation
anomalies’ and ‘deletion anomalieswith
respect to normal forms with suitable example and suggest a method to overcome
them.
(b) discuss the problem of spurious tuples and state how we may prevent
it.
8.
(a)what do you mean by ACID properties of a
transaction? Explain the usefulness of each with example.
(b) explain the distinction between serial schedule and serializable
schedule.
(c) given a schedule S. Test for conflict and with serializability:
T1
T2 T3
Read(Q)
Write (Q) read(Q)
Write
(Q)
Write (Q)
9.
Write short notes on:
(a)
Specialization and generalization
(b)
Ternary relationship and Aggregation
(c)
Mapping cardinalities
(d)
Derived attribute