Database Management Systems (DBMS) University Questions 4
Database Management Systems
Time :
Three Hours Max.Marks:100
PART – A
(8 X 5 = 40 Marks)
Answer ALL
Questions
1.
Explain the different types of entity constraints with examples.
2.
What is Three Schema Architecture of DBMS? Illustrate the same with a neat
diagram.
3.
Briefly explain Query Optimization Process in centralized database systems with
a neat diagram.
4.
What are the different kinds of Storage media available? Augment your answers
with information on capacity, cost and storage volatility.
5.
Consider the following transactions that transfer funds from one account to
another: T1 transfers Rs.50 from account A to account B. T2 transfers 10% of
the balance from account A to account B.
T1: Read (A); T2:
Read (A);
A = A - 50; Temp = A *
0.1;
Write (A); A = A –
Temp;
Read (B); Write (A);
B = B + 50; Read (B);
Write (B); B = B +
Temp;
Write
(B);
Suppose
the current values of A and B are Rs.1000 and Rs.2000, respectively, which among
the following sequences will preserve the A+B consistency? What will be the DB
state values for A and B after executing each sequence?
Seqence I
|
Sequence II
|
Read (A);
A = A - 50;
Write (A);
Read (A);
Temp = A * 0.1;
A = A – Temp;
Write (A);
Read (B);
B = B + 50;
Write (B);
Read
(B);
B = B +
Temp;
Write
(B);
|
Read (A);
A = A - 50;
Read (A);
Temp = A * 0.1;
A = A – Temp;
Write (A);
Read (B);
Write (A);
Read (B);
B = B + 50;
Write (B);
B = B +
Temp;
Write
(B);
|
6.
What is the difference between Hierarchical Model and Relational Model? Explain
with example.
7.
Highlight the importance of ‘isolation’ in executing a transaction. What are
the effects caused when proper isolation is not maintained?
8.
What are the advantages of distributed databases? Are they different from
shared memory and shared processor architectures? Which architecture best suits
for distributed databases? Illustrate with the same with a neat diagram.
PART – B
(6 X 10 = 60 Marks)
Answer any
SIX Questions
9.
As a software engineer you were asked to recommend a DBMS system. What are the characteristics
that you will consider before recommending a particular DBMS to your Project
Manager?
10.
a) Consider the universal relation R={A,B,C,D,E,F,G,H,I,J} and the set of
functional dependencies F={ AB→C, A→DE, B→ F, F→GH, D→IJ }. Assume no multivalues
are present. [5]
i) What is the key for R?
ii) Decompose R into 2 NF and then into 3 NF relations.
b)
Prove or disprove the following inference rules for functional dependencies. Specify
the name of the rule you use to derive the result.
i) { X→Y and Y is a subset of Z} |= {X→Z} [2]
ii) {X → Y, X→W, WY→Z} |= {X→Z} [2]
iii) {X→Y, Z→W} |= {XZ→YW} [1]
11.
Consider the following database:
Employee
(FirstName, MidName, LastName, SSNNumber, Birthday, Address, Sex, Salary,
Supervisor SSN, DepartmentNumber)
Department
(DepartmentName, DepartmentNumber, ManagerSSN, ManageStartDate)
Project
(ProjectName, ProjectNumber, ProjectLocation, DepartmentNumber)
a) Create table department. Set
Department number as primary key, department name as not null, manager ssn
refers to supervisor ssn of employee table and when this manager ssn is deleted
then it need to be set as null and when it is updated the change should be in
effect in employee table too. [3]
b)
For every project located in 'Stafford', list the project number, the
controlling department number, and the department manager's last name, address,
and birth date. [2]
c)
For each employee, retrieve the employee's name, and the name of his or her immediate
supervisor. [2.5]
d)
Make a list of all project numbers for projects that involve an employee whose last
name is 'Smith' as a manager of the department that controls the project. [2.5]
12.
Describe the Query Decomposition process. Include short notes on each phase and
present examples to illustrate the same.
13.
Present an ER schema for a company database and explain about the notations
used in ER diagrams. Make necessary assumptions.
ER
Diagram should include:
i. Clear statement of all the assumptions made.
ii. Entity types.
iii. Attributes (Single, composite & Multivalued).
iv. Relationship Types.
v. Cardinality of relationships.
vi. Participation constraint.
14.
Based on the given schema, formalize the following transaction: Identify the Read
Set (RS), Write Set (WS), Base Set (BS), Domain (Σ) and the partial ordering of
operations in Σ based on irreflexivity.
Teacher
(tid, t_name, cid, max_stu_strength, allotted, semester)
Student
(stu_name, stuid, program, cgpa)
Course
(cid, c_name)
Registration
(stuid, tid, cid, semester)
Begin_transaction Register
begin
input (stu_id, teacher_id, course_id,
semester);
EXEC SQL SELECT MAX_STU_STRENGTH,
ALLOTTED INTO temp1, temp2 FROM TEACHER
WHERE t_id = teacher_id AND c_id = course_id AND sem = semester;
If temp1 = temp2 then
output ("cannot register - no
free seat");
abort
else
EXEC SQL UPDATE TEACHER
SET ALLOTED = ALLOTTED + 1
WHERE t_id = teacher_id AND c_id =
course_id;
EXEC SQL INSERT INTO REGISTRATION
(stu_id, t_id, c_id, sem)
VALUES (stu_id, teacher_id,
course_id, semester);
commit
output ("registered
successfully")
endif
end
15.
Why is XML considered as one of the best ways of electronic document
processing? Include notes on XML document schema and DTD.
16.
Design a relational database corresponding to the following E-R diagram.
**************
No comments:
Post a Comment