DBMS Lab2 [MySQl]

Exercise: 2

Professors have a PROFID, a name, an age, a rank, and a research specialty. Projects have a project number, a sponsor name (e.g. UGC/AICTE/...), a starting date, an ending date, and a budget.  Graduate  students  have  an  USN,  a  name,  an  age,  and  a  degree  program  (e.g.  MCA/MPhil/BE/ME  ..).  Each  project  is  managed  exactly  by one  professor  (known  as  the  project'sprincipal  investigator).  Each  project  is  worked  on  by  one  or  more  professors  (known  as  the project's  co-investigators).  Professors  can  manage/work  on  multiple  projects.  Each  project  is worked  on  by  one  or  more  graduate  students  (known  as  the  project's  research  assistants).Graduate students can work on multiple projects. Each professor can supervise many students. A student who is working on a project can be supervised by only one professor.

ER Diagram:

Relational schema:

 

Tables

mysql> create table professor(pid int primary key, name varchar(20),age int, rank int, speciality varchar(10));

mysql> select * from professor;

mysql> create table project(pno int primary key, sponsor varchar(20),sdate date,edate date,budjet int,investigator  int;

mysql> select * from project;

mysql> create table student(usn varchar(10) primary key, name varchar(20), age int, degree varchar(10),supervisor int,foreign key(supervisor) references professor(pid));

mysql> select * from student;

mysql> create table co_investigator(pno int, pid int,foreign key(pno) references project(pno),

foreign key(pid) references professor(pid));

mysql> select * from co_investigator;

mysql> show tables;

Queries

a)      Retrieve  the  names  of  all  professors  who  do  not  have  an  ongoing  project  of  more  than 1 lakh.

mysql> select p.name from professor p,project pr where pr.budjet>100000 and pr.investigator=p.pid;

b)      Retrieve  the  names  of  all  graduate  students  along  with  their  professors  under  whom  they work  and project sponsor.

mysql> select s.name,p.name,pr.sponsor from student s,professor p,project pr where p.pid=s.supervisor and p.pid=pr.investigator;

c)      List the professors and sum of the budget of their projects started after 2005 but ended in 2010.

mysql> select p.name,sum(distinct (budjet)) from professor p,project where sdate>="2005-01-01" and edate<="2010-04-10" and p.pid=investigator group by p.pid;

d)     List the names of professors who has a total worth of project greater than the average budget of projects sanctioned

mysql> select distinct p.name from professor p,project pr where pr.budjet > (select avg(budjet) from project) and p.pid=pr.investigator;

e)      List the professors who work on all the projects.

mysql> select distinct p.name from professor p, project pr where p.pid=pr.investigator;

Posted in: II_DBMS_lab