ASSIGNMENT Consider the following few relations of Academic systems. Here we hav
ID: 3612591 • Letter: A
Question
ASSIGNMENTConsider 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’ 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.
Notes:
1. Professor Full Name shall be from 1st Views named Views_Onee
Explanation / Answer
Dear,