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

Explanation / Answer

a) Tables are DegreeProgram, Department, DDoffers
b) Yes, two joins are required
One from DegreeProgram and DDoffers
DegreeProgram.ProgID = DDoffers.ProgID
Another DDoffers and Department
DDoffers.DeptId = Department.DeptId
c) Yes, select condition is required
ProgType = 'BS'
d) ProgramName, DeptName
e) SELECT ProgramName, DeptName FROM DegreeProgram
INNER JOIN DDoffers ON DegreeProgram.ProgID = DDoffers.ProgID
INNER JOIN Department ON Department.DeptId = DDoffers.DeptId
WHERE ProgType = 'BS';

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.