| 1. |
(a) |
Consider the
following RDBMSs |
| |
|
employee |
| |
|
| EMP_NO |
E_NAME |
Deptt_NO |
| E1 |
A.K |
D1 |
| E2 |
B.K |
D1 |
| E3 |
C.K |
D2 |
| E4 |
D.K |
D3 | |
| |
|
Tel-Ext
| EMP_NO |
OFFICE |
EXT |
| E1 |
OF1 |
111 |
| E1 |
OF2 |
222 |
| E2 |
OF3 |
333 |
| E3 |
OF4 |
444 |
| E5 |
OF5 |
444 | |
| |
|
Deptt
| Deptt_NO |
NAME |
Head |
| D1 |
Computer
Sc |
E1 |
| D2 |
Mgmt |
E2 |
| D3 |
Medicine |
E3 | |
| |
|
Identify the
relations defined by the following expressions in relational
algebra, when applied to the above database.
(i) Divide (PROJECT
EXT ON EMP_NO, OFFICE) BY ROOMS where ROOMS is a
follows |
| |
|
rooms
|
| |
|
(ii)
(PROJECT employee on EMP_NO) MINUS (PROJECT Deptt on HEAD)
(iii)
JOIN employee and EXIT WHERE EMP_NO = HEAD |
| |
(b) |
Design an E-R diagram for a
particular enterprise. You have several alternatives
from which to choose.
(i) What criteria should you consider
in deciding on the appropriate model ?
(ii) Design several
alternative E-R diagrams to represent a university registrar
system. The office maintains data about each class including
the instructor, the enrolment and the time and place of the class
meetings. For each student-class pair, a grade is
recorded.
List the merits of each and argue in favour of one of
the alternatives. |
| |
(c) |
Write SQL
statement for the followings :
(Refer to the diagram of
Q1(a))
(i) Retrieve the names of employees (E_NAME) who do not
share a telephone extension (EXT).
(ii) Delete details of
employee who is head of depth. |
| |
(d) |
Define 3NF. Explain
why the employee relation in the following database des not satisfy
3NF and decompose employee relation into relations which do satisfy
3NF. What advantages are gained by this decomposition
? |
|
|
employee
| EMP_NO |
NAME |
ADDRESS |
Deptt |
DE[_HEAD |
| E1 |
N1 |
A1 |
D1 |
E1 |
| E2 |
N2 |
A2 |
D1 |
E1 |
| E3 |
N3 |
A3 |
D1 |
E1 |
| E4 |
N4 |
A4 |
D2 |
E5 |
| E5 |
N5 |
A5 |
D2 |
E5 | |
| 2. |
(a) |
Define the concept
of aggregation. Give several examples of where this concept is
useful. |
|
(b) |
List four
responsibilities of database manager. For each responsibility,
explain those problems that would arise if the responsibilities were
not met. |
| 3. |
(a) |
Discuss key
features and advantages of B-tree indexes. What is the
difference between B-tree and its different versions ? |
|
(b) |
Create a B-tree
structure of the order 3 of the following relation. |
|
|
customer
| C_NO |
NAME |
LOCATION |
| C1 |
N1 |
L1 |
| C2 |
N2 |
L2 |
| C9 |
N3 |
L2 |
| C10 |
N4 |
L3 |
| C11 |
N5 |
L3 |
| C15 |
N6 |
L3 |
| C19 |
N7 |
L4 |
| C23 |
N8 |
L3 |
| C25 |
N9 |
L4 |
| C37 |
N10 |
L2 |
| C32 |
N11 |
L2 |
| C34 |
N12 |
L1 | |
|
(c) |
Comment on
disadvantages of B-tree. |
| 4. |
(a) |
Discuss the relative
advantages of centralized and distributed database. How might
a distributed database designed for a local area network differ from
one designed for a long haul network ? |
|
(b) |
A large university has a
number of colleges in its jurisdiction. Each college has
students and teachers. Teachers have certain qualifications
and may have taught in other colleges. Some teachers
have joint appointments and can teach in more than one
college. However a student can attend only one college.
Show how you would model the University college system using the
network model. |
| 5. |
(a) |
How does the
concept of an object in object oriented model differ from the
concept of an entity in the entity relationship model ? |
|
(b) |
Design an object oriented
database for a vehicle database that describes the type of vehicle
manufactured by a major company. Show the classes and
subclasses. For each class, list variables and methods.
Indicate the class from which they are inherited. |
|
(c) |
Discuss the
drawbacks of RDBMS in comparison to OODBMS (Object-oriented
DBMS). |
| 6. |
(a) |
Explain two
integrity rules with proper examples. Explain the anomalies
that may occur when the two integrity rules are
violated. |
|
(b) |
why do most
computer systems support several file organization techniques
? |
