6. Last date for theSubmission of the assignment is June 24, 2009. DATABASE MANA

ID: 3612559 • Letter: 6

Question

6. Last date for theSubmission of the assignment is June 24,

2009.

DATABASE MANAGEMENT SYSTEM (CS403)

MODULE: 05

ASSIGNMENT

Consider thefollowing fewrelations 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 Viewsthat List Professors’ current Responsibilities &roles

information inUniversity.

1. (NameView: Views_One)

List columnsname as Professor Number, Professor Full Name, Hire

Date and JobDescription of those professors who are the part of

universityfaculty from last 6 months.

Hint:using aggregate functionslike DATEADD (datepart,number,

date )

2. (NameView: Views_Two) UseViews_One as Nesting View

List columnsname as Professor Full Name

, Project Name,Project

Assign Date andAssign Hours

Where ProjectAssign Date shouldn’t be older than 4 months and this

implies tothose professors who are the part of university facultyfrom

last 6months.

Hint:

Using (Viewnamed: Views_One) , PROFESSOR, ASSIGN AND PROJECT

relationshere.

Notes:

1. Professor Full Name shallbe from 1stViews 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.