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.

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: Return the names of those degree programs that are offered by the 'CS' department but not by the 'EE' department. Note that 'CS' and 'EE' are department IDs.

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 tables which are to be used are DegreeProgram and DDoffers.

DegreeProgram contains the name of the degree programs

DDoffers contains the Department id

The tables have common field ProgId which can be used to join

a) In plain English, write down the first query unit.

Answer: Get ProgName FROM DegreeProgram where it is offered by department 'CS'.

b) In plain English, write down the second query unit.

Answer: Get ProgName FROM DegreeProgram where it is offered by department 'EE'.

c) What should be the set operator to combine the first and second query units.

Answer: As we need the Program names that are only offered by CS Department not by EE department, then we need to delete the common ones from the Program names that are only offered by CS Department.

So to delete that the set operator used is set difference (-) for NOT.

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 ProgramName FROM DegreeProgram INNER JOIN DDoffers

ON DegreeProgram.ProgId = DDoffers.ProgId

WHERE DDoffers.DeptId = 'CS'

MINUS

SELECT ProgramName FROM DegreeProgram INNER JOIN DDoffers

ON DegreeProgram.ProgId = DDoffers.ProgId

WHERE DDoffers.DeptId = 'EE';

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.