CSIT-405 – Database Management System (DBMS)

Describe the three levels of schema architecture. Why do we need mappings between different schema levels? [7]

Explain how the data is organized in a network DBMS? How it is different from hierarchical DBMS. [7]

What is the impact of weak entity set in database design? Discuss with an example. [7]

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. [7]

Consider the following two tables.

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. [7]

Consider the following two tables as following.

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 \triangleright\!\!\!\triangleleft R2)$ where $\triangleright\!\!\!\triangleleft$ denotes left outer join

iii) $\Pi_C (R1 \triangleleft\!\!\!\triangleright R2)$ where $\triangleleft\!\!\!\triangleright$ denotes left outer join [7]

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. [7]

Assume the following employee and department table and write the SQL queries- 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 [7]

Identify the Highest Normal Form of the Relation R (A, B, C, D, E, F, G, H) and Functional Dependency set F = {AB $\to$ CD, D $\to$ EG, F $\to$ H, C$\to$ EF, H $\to$ A, G $\to$ B, A $\to$ B}. [7]

Consider the following employee table. Suppose that employees assigned to multiple projects and can have multiple jobs.

Eno | Pno | Skill
----|-----|--------------------------
E1  | 101 | Coding, Testing, Maintenance
E2  | 110 | Analysis, Design, Testing
E2  | 111 | Analysis, Design, Testing

Normalize the table to the possible extent. [7]

Identify the given schedule is conflict serializable or not.

T1 | T2 | T3
---|----|----
R(A)|    |
   | W(A)|
   |    | W(A)
   | R(B)|
R(B)|    |
   | W(B)|

[7]

Discuss with an example when concurrent execution of two transactions lead to conflict? [7]

Illustrate the concept of two-phase locking protocol and its variants with an example. [7]

Oracle Application Express (APEX) [7 Marks]

Database Anomalies [7 Marks]

Distributed Databases [7 Marks]

Views [7 Marks]