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

ID: 3612568 • Letter: C

Question

Consider the following few relations 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’ currentResponsibilities & 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

, 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 universityfaculty from

last 6 months.

Hint:

Using (View named: Views_One) , PROFESSOR, ASSIGN ANDPROJECT

relations here.

Notes:

1. Professor Full Name shall be from 1st Views namedViews_Onee

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!!!!!!

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