B.Tech., IV Semester
Examination, June 2023
Grading System (GS)
Max Marks: 70 | Time: 3 Hours
Note:
i) Attempt any five questions.
ii) All questions carry equal marks.
a) Describe the three levels of schema architecture. Why do we need mappings between different schema levels? (Unit 1)
b) Explain how the data is organized in a network DBMS? How it is different from Hierarchical DBMS. (Unit 1)
a) What is the impact of weak entity set in database design? Discuss with an example. (Unit 2)
b) Develop an Entity Relationship diagram for Indian movies. It should include where it was shot, details of the actors, directors, producers, the movie language and so on. (Unit 1)
a) Consider the following two tables as following. (Unit 2)
R1
| A | B | C |
|---|---|---|
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
| 10 | 11 | 12 |
R2
| D | E | C |
|---|---|---|
| 1 | 2 | 3 |
| 7 | 8 | 9 |
| 25 | 26 | 27 |
| 79 | 80 | 81 |
Show the output for the following Relational Algebra Operators:
i) $\Pi_A(R1 * R2)$ where * denotes natural join
ii) $\Pi_B(R1 ⟕ R2)$ where ⟕ denotes left outer join
iii) $\Pi_C(R1 ⟕ R2)$ where ⟕ denotes left outer join
b) Consider the following two tables. (Unit 2)
Department
| dno | dname |
|---|---|
| 101 | CSE |
| 102 | DS |
| 103 | IT |
| 104 | CCE |
Employee
| Eno | Ename | dno |
|---|---|---|
| E1 | A | 101 |
| E2 | B | 101 |
| E3 | D | 102 |
| E4 | A | NULL |
| E5 | Y | 104 |
Discuss the foreign key and referential integrity constraints on above tables.
a) Give an example of two relations that would give a meaningful result of carrying out a full outer join. Formulate the full outer join using SQL. (Unit 3)
b) Assume the following employee and department table and write the SQL queries- (Unit 3)
Emp (EID, Ename, DOJ, Salary, Dname)
Dept(DID, Dname)
i) List the employees having salary between 10,000 and 20,000
ii) Update the salaries of all employees in marketing department and hike it by 10%
iii) Get the gross salaries of all employees
iv) Display the department that has no employee
v) Find the number of employees in the marketing department
vi) Find the name of employee having maximum salary
vii) Find the second maximum salary in the employee table
a) Identify the Highest Normal Form of the Relation R (A, B, C, D, E, F, G, H) and Functional Dependency set F= {AB→CD, D→EG, F→H, C→EF, H→A, G→B, A→B}. (Unit 4)
b) Consider the following employee table. Suppose that employees assigned to multiple projects and can have multiple jobs. (Unit 4)
| Eno | Pno | Skill |
|---|---|---|
| E1 | 101, 102 | Coding, Testing, Maintenance |
| E2 | 110, 111, 115 | Analysis, Design, Testing |
Normalize the table to the possible extent.
a) Identify the given schedule is conflict serializable or not. (Unit 5)
| T1 | T2 | T3 |
|---|---|---|
| R(A) | ||
| W(A) | ||
| W(A) | ||
| W(A) | ||
| R(B) | ||
| R(B) | ||
| W(B) |
b) Consider the following two transactions T1 and T2 as following (Unit 5)
T1: Transfer of Rs. 1,000 from Account A to Account B
T2: Transfer of 10% amount from Account A to Account B
The Initial value of Account A is 2000 and Account B is 3000. Create any one concurrent schedule with T1 and T2 and calculate the final values of A and B.
a) Discuss with an example when concurrent execution of two transactions lead to conflict? (Unit 5)
b) Illustrate the concept of two-phase locking protocol and its variants with an example. (Unit 5)
Write short note on (any two):
i) Oracle Application Express (APEX) (Unit 3)
ii) Database Anomalies (Unit 4)
iii) Distributed Databases (Unit 5)
iv) Views (Unit 3)
