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 the program names and the names of the departments that offer them; you only need to find programs whose ProgType is 'BS'.

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: