Consider the following fewrelations of Academic systems. Here we have data base

Question

Consider the following fewrelations of Academic systems.
Here we have data base design with little bit changes and islimited
to four relations.
PROJECT ( PROJ_NUM, PROJ_NAME, PROF_NUM)
PROFESSOR ( PROF_NUM, PROF_FNAME, PROF_LNAME,
PROF_HIREDATE, JOB_CODE).
JOB (JOB_CODE, JOB_DESCRIPTION, JOB_CHG_HOUR)
ASSIGN (ASSIGN_NUM, ASSIGN_DATE, PROJ_NUM, PROF_NUM,
ASSIGN_HOURS)
A. You are required to write the VIEWS for the followingsituations.
Create Views that List Professors’ current Responsibilities& roles
information in University.
1. (Name View: Views_One)
List columns name as Professor Number, Professor Full Name,Hire
Date and Job Description of those professors who are the partof
university faculty from last 6 months.
Hint: using aggregate functions like DATEADD (datepart, number,
date )
2. (Name View: Views_Two) Use Views_One as Nesting View
List columns name as Professor Full Name
1
, Project Name, Project
Assign Date and Assign Hours
Where Project Assign Date shouldn’t be older than 4 monthsand this
implies to those professors who are the part of university facultyfrom
last 6 months.
Hint:
Using (View named: Views_One) , PROFESSOR, ASSIGN AND PROJECT
relations here.

Explanation / Answer

Dear,

CREATE VIEW List Professors AS

SELECT PROF_FNAME, JOB_DESCRIPTION 

FROM PROFESSOR, JOB 

WHERE JOB.JOB_CODE=PROFESSOR.JOB_CODE;

 -------------------------------------------------------------------------------------------- 

CREATE VIEW Views_One AS

 SELECT PROF_NUM, PROF_FNAME, PROF_LNAME, PROF_HIREDATE,JOB_DESCRIPTION

FROM PROFESSOR, JOB

WHERE PROFESSOR.JOB_CODE=JOB.JOB_CODE

AND  DATEADD(MONTH,-6,GETDATE());    

 -------------------------------------------------------------------------------------------- 

CREATE VIEW Views_Two AS

 SELECT  PROF_FNAME, PROF_LNAME, PROJ_NAME, ASSIGN_DATE, ASSIGN_HOURS

FROM Views_One, PROJECT, ASSIGN

 WHERE PROFESSOR.PROF_NUM=PROJECT.PROF_NUM

 AND   PROFESSOR.PROF_NUM=ASSIGN.PROF_NUM

AND   PROJECT.PROF_NUM=ASSIGN.PROJ_NUM

 AND   ASSIGN_DATE > (GETDATE(), INTERVAL-4MONTHS);

I hope this will helps you!!!!!! 

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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