ALCCS
NOTE:
· Question 1 is compulsory and
carries 28 marks. Answer any FOUR questions from the rest. Marks are indicated against each question.
· Parts of a question should be
answered at the same place.
Q.1 a. Discuss the difference between database systems and file
based systems.
b. Explain the difference between
a weak and a strong entity set.
c. Define foreign key. Explain its significance?
d. Explain the working of GROUP BY clause. What
is the difference between the WHERE and HAVING clause in SQL?
e. Discuss the role of a Database Administrator.
f. Define data warehousing and data mining.
g.
List all the functional dependencies that
holds true for the following relation
A |
B |
C |
|
|
|
|
|
|
|
|
|
|
|
|
(7 4)
Q.2 a. Construct an E-R diagram for the following
problem definition:
Each company operates four departments,
and each department belongs to one company. Each department employs one or more
employees, and each employee works for one department. Each of the employees
may or may not have one or more dependants, and each dependant belongs to one
employee.
b. What
are the different types of constraints imposed on Generalization in an E-R diagram?
c. How would you map the following ER constructs
into relations. Give suitable example?
(i)
Aggregation (ii) Ternary
relationship (iii) Inheritance
(6 3)
Q.3 a. What is an outer join. Discuss the different types of outer
joins with the help of examples.
b. Consider the
following relations for a database that keeps track of business trips of
salespersons in a sales office:
SALESPERSON (SSN, Name, Start_Year, Dept_No)
TRIP (SSN, From_City,
To_City, Departure_Date, Return_Date, Trip_ID)
EXPENSE (Trip_ID,
Account#, Amount)
Specify the following queries in relational
algebra:
(i) Find the details (all attributes of TRIP
relation) for trips whose expenses exceeds $2000.
(ii) Find the SSN of salesman who took trips
to ‘Honolulu’.
(iii) Find the total trip expenses incurred by the
salesman with SSN = ‘234-56-7890’.
c. Define a view? How is it different from a table? Write the SQL syntax for creating a view. (5+9+4)
Q.4 a. Use Armstrong’s axioms to prove the soundness of the union,
decomposition and pseudotransitivity rule with respect to a set of functional
dependencies.
b. Consider the following relation for published
books:
BOOK
(Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher)
Author_affil
referes to the affiliation of the
author. Suppose the following dependencies exist:
Book_title
-> Publisher, Book_type
Book_type
-> Listprice
Author_name
-> Author_affil
(i) What normal form is the above relation is
in? Justify?
(ii) Normalize the above relation to its highest normal form.
c. Consider the relation schema EMP_DEPT and the
following set G of functional dependencies on EMP_DEPT:
G
= {SSN -> {ENAME, BDATE,ADDRESS, DNUMBER} ,
DNUMBER
->{DNAME, DMGRSSN} }. Calculate the closures {SSN} + and {DNUMBER} + with
respect to G. (6 3)
Q.5 a. Discuss the different possible states of a transaction
with the help of a diagram.
b. Consider the precedence graph of a schedule given
below. Is the schedule conflict serializable? Justify?
c. Explain
the difference between volatile, non-volatile and stable storage in terms of
I/O cost.
d. Compare
the shadow-paging recovery scheme with the log-based recovery schemes in respect
to ease of implementation and overhead cost. (6+5+3+4)
Q.6 a. What is OLAP, ROLAP,
MOLAP and HOLAP.
b. Discuss the
key control measures that are used to provide security to data in databases.
c. What is a fragment of a relation? What are the
main types of fragments? Why is fragmentation used in distributed database
design?
d. Mention any three data mining techniques? (6+4+4+4)
Q.7 Write short notes on any THREE of the following:-
(i)
Lossless Join decomposition and Dependency
Preservation.
(ii) Granularity
of data items.
(iii) Canonical Cover.
(iv) 3NF
and BCNF. (6 3)