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

Question

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

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.