Department (DeptId, DeptName, DeptAddress, Chair); PK: DeptId; FK: Chair refs Fa
ID: 3755810 • Letter: D
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.
Divide-and-Conquer Query Construction
Once we have recognized a multi-unit query, we can use the divide-and-conquer method to construct it.
First, based on the keyword found in the query condition, we divide the original query requirement into multiple single-unit queries. We can then construct the single-unit queries separately using the method presented in Section 1 Constructing Single Unit Queries.
After the single-unit queries are constructed, they can be combined using the set operator corresponding to the keywords found in the query condition: set intersect (Ç) for AND, set union (È) for OR, and set difference (-) for NOT.
Query: For students majoring programs named 'Comp Sci' or 'Soft Engr', return their snames.
a) In plain English, write down the first query unit.
Answer:
b) In plain English, write down the second query unit.
Answer:
c) What should be the set operator to combine the first and second query units.
Answer:
d) 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
Here the requirement to get student names who are majoring in the programs 'Comp Sci' or 'Soft Engr'. To get the data for the query Student and DegreeProgram tables are required because Sname is present in the Student table and ProgramName is present in the DegreeProgram. So a join is need among the table using the ProgId in DegreeProgram table with the Major in the Student table.
a) In plain English, write down the first query unit.
Answer: Get Sname, ProgramName From Student and DegreeProgram tables
b) In plain English, write down the second query unit.
Answer: Join Condition on Student.Major = DegreeProgram.ProgId with select condition ProgramName = 'Comp Sci' or 'Soft Engr'
c) What should be the set operator to combine the first and second query units.
Answer: The set operator to combine the first and second query units is set intersect (Ç).
d) Based on your answers above, provide the complete query statement. You can write it as a relational algebraic expression or SQL command.
Answer: SELECT Sname, ProgramName FROM Student
INNER JOIN DegreeProgram ON Student.Major = DegreeProgram.DeptId
WHERE DegreeProgram.ProgramName = 'Comp Sci' OR DegreeProgram.ProgramName = 'Soft Engr'.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.