1. (a) Draw ER diagram for the situation given below. In a Department many employees are working on many projects which are under the control of the manager of the department. The manager of the department also holds the responsibility of the welfare of the employees. Make suitable choice of the attributes for the concerned entitles and transform your ER diagram into a relational database. (8)
(b) Differentiate between the following : 4x5=20
(i) Physical data independence and Logical data independence
(ii) Serial schedule and Serializable schedule
(iii) Object based data models and Record based logical data models
(iv) File base system and DBMS
(c) R1 and R2 are two given relations
R1:
| A |
B |
| A1 |
B1 |
| A2 |
B2 |
| A3 |
B3 |
| A4 |
B4 |
R2:
| X |
Y |
| A1 |
B1 |
| A7 |
B7 |
| A2 |
B2 |
| A4 |
B4 |
Find Union, Intersection and Set Difference.
(d) Draw suitable graph for following locking requests, and find whether the transactions are deadlocked or not.
| T1 : S_lockA |
— |
— |
| — |
T2 : X_lockB |
— |
| — |
T2 : S_lockC |
— |
| — |
— |
T3 : X_lockC |
| — |
T2 : S_lockA |
— |
| TI : S_lockB |
— |
— |
| TI : S_lockA |
— |
— |
| — |
— |
T3 : S_lockA |
| All the locking requests start from here |
||
2. (a) Consider the following relational database schema
Employee (empcode. ename, eaddress, esalary)
Department (deptcode, dname. diocation)
Project (projcode . projname, projduration)
Worksfor (empcode, deptcode, projcode, duration)
Perform following queries using SQL and relational algebra : (2 1/2
x4=10)
(i) Find the name of the employees whose salary is more than 5 lacs
per annum.
(ii) Find details of departments located in Delhi.
(lii) Find details of employees working on project 'P123'
(iv) Find the name of department in which project 'P123' is executed.
(b) What are integrhy constraints ? Discuss difFerent types of integrity constraints which can be imposed on databases.
(c) Why is normalization of database done ? Discuss synthesis and decomposition approach of normalization with an example.
Q.3. (a) What is the importance of File organisation in databases ? What are the different types of file organisations available ? Discuss any one of them in detail.
(b) What are Clustering Indexes ? Discuss with example.
(c) With the help of an example prove the statement "Every relation which is in 3NF is not in BCNF but the converse is true"
Q.4 . (a) What Is the need for distributed database systems ? Discuss the structure of distributed database.
(b) What is Write Ahead Log protocol ? What is the purpose of this protocol?
(c) Discuss the relationship between security and integrity. What are different levels of security measures that may be considered to protect the database?