Figure 2 ER Diagram Section 2 Populate the database. Please check sections 3 for

Question

Figure 2 ER Diagram

Section 2

Populate the database. Please check sections 3 for details.  

Section 3

Use SQL to specify the following queries.   When you populate the database, insert data such that at least one rowwill be display as the result of running each query.

Section 4

Specify the statements to drop all the tables. Pay attention to the order of the drop statements in order to drop everything successfully.  Note that this section is exactly the same as the one in Assignment 4. You can use your assignment 4 directly, or modify your assignment 4 (if needed).

Explanation / Answer

1) Retrieve information about the projects (title, status and whether archived or not) that have an ‘Optional’ reviewer.

select p.title,p.Project_status from project p,reviews r where r.Reviewer_Role='Optional' and p.Project_ID=r.Project_ID;

2) Retrieve the information about the investigators (first name, last name and organization) who have done more than one project.

select i.First_Name,i.Last_Name,i.organization from investigator i where ssn in (select ssn from WORKS_ON group by ssn having count(*)>2);

3) Retrieve the information of reviewers (first name and last name), investigators (first name and last name) for the project “Air Quality Research Project”.

select r.First_name,r.Last_name,i.First_name,i.Last_name from REVIEWER r, INVESTIGATOR i, PROJECT p where (p.Project_ID=r.Project_ID and i.SSN=r.SSN) and p.Title='Air Quality Research Project';

4) Get the contact information (contact number, contact person name, contact type and contact remainders) for the projects which have a contact history. Also get the Project_IDs for those projects.

select c.Contact_ID,c.CP_Name,c.Project_ID,c.Contact_Type,r.Contact_Reminders from CONTACT_HISTORY c,CONTACT_REMINDER r where c.Contact_ID=r.Contact_ID;

5) Get the project information (project id, title, status and whether archived or not) which has a ‘Co_PI’.

I cannot understood what is Co_PI

6) Retrieve all the projects (project id, title) in which “John Smith” serves as a principle investigator.

select p.Project_ID,p.Title from WORKS_ON w, INVESTIGATOR i, PROJECT p where (p.Project_ID=w.Project_ID and i.SSN=w.SSN) and i.First_Name='John Smith';

7) Retrieve the reviewers who reviewed more than 2 projects. Please list reviewer’s SSN and number of projects reviewed by the reviewer.

select ssn,count(*) from reviews group by ssn having count(*)>2;

8) Retrieve the investigators who serve as “PI” for at least 2 projects. Please list the SSN, last name, and number of projects served as “PI”.

select ssn,count(*) from reviews where reviewer_role='PI' group by ssn having count(*)>2;

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.