Pay attention to the foreign key constraints and understand their meanings. You can find the schema used in this exercise below: Department (Deptid, Dept Name, DeptAddress, Chair); PK: DeptId; FK: Chair refs Faculty. Faculty (FacName, FacSSNO, Office Address, Worksfor); PK: FacSSNO; FK: Works for refs Department. Degree Program (Progid, ProgramName, ProgType, Coordinator, UnivReg, CollReq); PK: ProgId; FK: Coordinator refs Faculty Student (Sid, SSNo, Sname, CurAddress, Major, StuLevel, Gpa); PK: Sid; FK: Major refs Degree Program; UNIQUE: SSNO. DDoffers (DeptId, Progid, DeptReqrmnt), PK: (DeptId, ProgId); FK: DeptId refs Department, Progid refs Degree Program. 1. Constructing Single-Unit Queries A single-unit query only has a single "select ...from" unit (block). It can be constructed using the following steps: . Find in the database schema the table or tables that contain the information items needed for the query. . If more than one table is found, then join operations are needed to combine those tables into one big table. Be careful to use the appropriate foreign-key/primary-key pair to join the tables. . Read the query requirement to check if all the rows of the joined table are needed in the final query result. If not, use a select operator to filter the rows. Read the query requirement to check if all the columns of the joined table are needed in the final result. If not, use a project operator to filter the columns. Ouery. Find the records of .11 faults momhare uha (1) Query: Find the records of all faculty members who work for the department with ID 'CS'. 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: what columns should be specified for the project operator? Answer: c) Based on your answers above, provide the complete query statement. You can write it as a relational algebraic expression or SQL command. Answer: (2) 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: 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: .) 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: 3) Query: Return faculty names and the names of the departments that they work for. .) 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: the departments that offer them; you only need to find programs whose Prog Type is 'BS'. a) What tables and attributes are needed for the query? Answer: 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: Do we need a project operator for the query? If yes, what columns should be specified for the project operator? Answer: c) Based on your answers above, provide the complete query statement. You can write it as a relational algebraic expression or SQL command. Answer: 2. Divide-and-Conquer for Multi-Unit Queries Query Condition To decide whether a given query requirement is single- unit or multi-unit, we need to find its query condition first. Query condition is the part of the query requirement that specifies what rows should be kept in the query result. In English grammar, this is often the adjective clause of a sentence. For example, if the query requirement is to find the names of the programs that are offered by departments whose IDs are 'CS' and EE then the query condition is the underlined part of the sentence. Note that when a query requirement does not have a query condition, it is a single-unit query. (1) Query: Find all faculty members who work for the department with ID 'CS'. .) Does the query have a query condition? If yes, what is the query condition? Answer: 2) Query: Find names and GPAs of all students. a) Does the query have a query condition? If yes, what is the query condition? Answer: (3) Query: For students majoring in the program with ID 'P000', return their names and GPAs. a) Does the query have a query condition? If yes, what is the query condition? Answer: Keywords in Query Condition to Recognize Multi-unit Queries Once we find the query condition in the query requirement (assuming there is one), we will check for the existence of one or more keywords in the query keywords: AND, OR and NOT. If any of the keywords is found in the query condition, then the query is multi- unit. Otherwise, it is single unit. (1) Query: Return records of all faculty members who a) Is the query multi-unit? Why or why not? Answer: (2) Query: Return program IDs of those degree programs offered by both 'CS' and 'EE' departments. Note that 'CS' and 'EE' are department IDs. a) Is the query multi-unit? Why or why not? Answer: (3) Query: Return names and GPAs of all students. a) Is the query multi-unit? Why or why not? Answer: (4) Query: For students majoring in program ID 'P000' or 'P001', return their names. a) Is the query multi-unit? Why or why not? Answer: 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 (c) for AND, set union () for OR, and set difference (-) for NOT (1) Query: Return the names of the departments whose addresses are 'MCS' or 'HOH'. 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. .) Based on your answers above, provide the complete query statement. You can write it as a relational Answer: (2) 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: (3) 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