Consider the followingfew relations ofAcademic systems. Here we have data basede
ID: 3612561 • Letter: C
Question
Consider the followingfew relations ofAcademic systems.
Here we have data basedesign with little bit changes and is limited
to fourrelations.
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 towrite the VIEWSfor the following situations.
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 professorswho are the part of
university faculty from last 6months.
Hint: using aggregatefunctions like DATEADD(datepart, number,
date )
2. (Name View: Views_Two) Use Views_Oneas Nesting View
List columns name as Professor FullName
1
, Project Name, Project
Assign Date and Assign Hours
Where Project Assign Date shouldn’t beolder than 4 months andthis
implies to those professors who are the partof university faculty from
last 6 months.
Hint:
Using (View named: Views_One) , PROFESSOR,ASSIGN AND PROJECT
relations here.
Notes:
1. Professor Full Name shall be from1st Views namedViews_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!!!!!!