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. List two major
problems associated with updates performed using views.
b. Design a relational database for a university registrar’s
office. The office maintains data about each class, including the instructor,
the number of students enrolled, and the time and place of the class meetings.
For each student–class pair, a grade is recorded.
c. Let R = (A, B, C), and let r1
and r2 both be relations on schema R. Give an expression in SQL
that is equivalent to each of the following queries: (a) r1 r2 (b) ΠAB(r1) x
ΠBC(r2).
d. Explain
condition-defined, user-defined constraints, disjoint and overlapping constraints
with respect to generalization in an E-R diagram.
e. Differentiate
between super key, candidate key, primary key and foreign key.
f.
Differentiate between OLTP and OLAP
systems.
g. Explain
what is meant by redundant information and loss of information.
Explain why each of these properties may
indicate a bad relational database design.
(7 4)
Q.2 a. List
the major characteristics of database approach. Also mention some disadvantages
of it.
b. Describe the three level
schema architecture. Why do we need mappings between schema levels? How do different schema
definition languages support this architecture?
c. A university
registrar’s office maintains data about the following entities: (a) courses, including
number, title, credits, syllabus, and prerequisites; (b) course offerings, including
course number, year, semester, section number, instructor(s),timings, and
classroom; (c) students, including student-id, name, and program; and (d)
instructors, including identification number, name, department, and title.
Further, the enrollment of students in courses and grades awarded to students
in each course they are enrolled for must be appropriately modeled. Construct
an E-R diagram for the registrar’s office. Mention all assumptions that you
make while drawing the E-R diagram. (6+6+6)
Q.3 a. Consider the following four relations :
employee (person-name,
street, city)
works (person-name,
company-name, salary)
company (company-name,
city)
manages (person-name,
manager-name)
Answer the following queries in SQL:
1.
Find all the manager’s name along with their company names.
2.
Find names of employees whose salary is greater than the average
salary of employees in their company.
3.
Find the names of all employees who live in the same city and the
same street as their managers.
4.
Find the names of all employees who earn more than every employee
of Small Bank Corporation.
b. Discuss the
entity integrity and referential integrity constraints. What is their significance?
c. When and why do
you provide a null value to an attribute? (12+4+2)
Q.4 a. Suppose that we decompose the schema R =
(A, B, C, D, E) into
R1 (A, B, C)
R2 (A, D, E)
Is the
decomposition of R into R1 and R2 dependency preserving for the following set F
of functional dependencies? If not, decompose it into dependency preserving
relations: Justify your answers?
A →
BC
CD → E
B → D
E → A
b. Prove or
disprove the following inference rules for functional dependencies. A
proof can be made either by a proof
argument or by using inference rules IR1 through IR3. A disproof should be done
by demonstrating a relation instance that satisfies the conditions and
functional dependencies in the left hand side of the inference rule but do not
satisfy the conditions or dependencies in the right hand side.
1.
{A →C, B →D} |= {AB →C }
2.
{ B →C, B →A, AC →D} |= {B →D}
3.
{B →D, C →D} |= {B →C}
where the symbol |= stands for “logically infers”
c. Compute
the closure of the following set F of functional dependencies for
relation schema
R = (P, Q, R, S, T).
P →QR
RS →T
Q→
S
T→
P
List the candidate
keys for R. (6+6+6)
Q.5 a. Discuss the lost update, dirty read and
incorrect summary problems associated with transactions.
b. What is a
timestamp? How does the system generate timestamps? Discuss the timestamp
ordering protocol for concurrency control.
c. Stable storage cannot be implemented.
(i) Explain why it cannot be.
(ii) Explain how
database systems deal with this problem.
d. Assume that immediate modification is used in a system. Show, by an
example, how an inconsistent database state could result if log records for a
transaction are not output to stable storage prior to data updated by the transaction
being written to disk. (6+6+3+3)
Q.6 a. Define a data warehouse. Explain the
characteristics or features of a data warehouse.
b. Explain
knowledge discovery in databases. Discuss the role of data mining in it.
c. Define distributed database. Differentiate
between distributed and parallel databases. Also mention some advantages of
distributed databases. (6+6+6)
Q.7
Write Short notes on any THREE of the following:-
(i)
Lossless join decomposition and dependency preservation.
(ii)
Multivalued dependency and join dependency.
(iii)
ACID properties of Transactions
(iv) Strict and conservative two phase locking
protocol.
(v) Database security. (6+6+6)