Department (DeptId, DeptName, DeptAddress, Chair); PK: DeptId; FK: Chair refs Fa

Question

Department (DeptId, DeptName, DeptAddress, Chair); PK: DeptId; FK: Chair refs Faculty.

Faculty (FacName, FacSSNo, OfficeAddress, Worksfor); PK: FacSSNo; FK: Worksfor refs Department.

DegreeProgram (ProgId, ProgramName, ProgType, Coordinator, UnivReq, CollReq); PK: ProgId; FK: Coordinator refs Faculty.

Student (Sid, SSNo, Sname, CurAddress, Major, StuLevel, Gpa); PK: Sid; FK: Major refs DegreeProgram; UNIQUE: SSNo.

DDoffers (DeptId, ProgId, DeptReqrmnt), PK: (DeptId, ProgId); FK: DeptId refs Department, ProgId refs DegreeProgram.

Query: Return faculty names and the names of the departments that they work for.

a) What tables and attributes are needed for the query?

Answer:

b) Do we need join operations for the query? If yes, what are the join conditions?

Answer:

c) Do we need a select operator for the query? If yes, what is the select condition?

Answer:

d) Do we need a project operator for the query? If yes, what columns should be specified for the project operator?

Answer:

e) Based on your answers above, provide the complete query statement. You can write it as a relational algebraic expression or SQL command.

Answer:

Explanation / Answer

a) Department and Faculty tables
b) Yes, join between Faculty and Department table
Department.DeptId = Faculty.Worksfor
c) No, not required as there is no condition mentioned.
d) FacName, DeptName
e)
SELECT FacName, DeptName FROM Faculty INNER JOIN
Department ON Department.DeptId = Faculty.Worksfor

Please check the code. If you have any doubts comment below and i am happy to help :)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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