DBMS-Slip3

Slip3

QUESTION

Consider the following Entities and Relationships                                      

Project (pno, pname, start_date, budget, status)

Department (dno, dname, HOD)

Relation between Project and Department is Many to One

Constraint: Primary key.

Project Status Constraints: C – completed,Progressive, I-Incomplete

Solution

Create a Database in 3NF & write queries for following.

• List the project name and department details worked in projects that are ‘Complete’.

SQL>Select  p1name, ddname, hodd, locationd from department1, project1 where   department1. dno1= project1.dno1 and  project1.status='C';

• Display total budget of each department.

SQL>Select sum(budget), ddname from  department1, project1 where department1. dno1= project1.dno1  group by ddname;

• Display incomplete project of each department

SQL>select p1name,status ,count(department1. dno1) from department1, project1 where   department1. dno1= project1.dno1 and project1.status='I'  group by status,p1name    ;

• Find the names of departments that have budget greater than 50000

SQL>select ddname from  department1, project1 where  department1. dno1= project1.dno1 and budget >=50000  ;

• Displayall project working under 'Mr.Desai'.

SQL>Select  p1name  from department1, project1 where   department1. dno1= project1.dno1 and hodd= 'Mr.Desai';