ASSIGNMENT Consider the followingfew relations of Academicsystems. Here we have
ID: 3612535 • Letter: A
Question
ASSIGNMENT
Consider the followingfew relations of Academicsystems.
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 inUniversity.
1. (Name View: Views_One)
List columns name as Professor Number,Professor Full Name, Hire
Date and Job Description of thoseprofessors who are the part of
university faculty from last 6months.
Hint: usingaggregate functions like DATEADD (datepart, number,
date )
2. (Name View: Views_Two) Use Views_One as NestingView
List columns name as Professor FullName
1
, Project Name, Project
Assign Date and AssignHours
Where Project Assign Date shouldn’tbe older than 4 monthsand this
implies to those professors who are thepart of 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 Viewsnamed 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!!!!!!