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: