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: Find the names and GPAs of all students whose major prog ID is 'P000'.

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)Student table is enough
As Major refs DegreeProgram, it is the foreign key in Student which refers to progid in DgreeProgram.
b) No, not required as major field is present in the Student and also names and GPAs.
c) Yes a select operator is needed for the query as names and GPAs are to be selected.
d) The columns required are names , Gpa
e)
SELECT Sname, Gpa FROM Student WHERE Majore = 'P000';

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.