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