25. What does the following query compute? 16- Following ER diagram is given to you. There is a 1:1 relationship between Professor and Department entities. Which of the following options are correct for mapping of 1:1 Relation Types 1) 1:1 relation RUNS is mapped by choosing the participating entity type DEPARTMENT and include a foreign key in DEPARTMENT the primary key of PROFESSOR. Because its participation in the RUNS relationship type is total. SELECT S.sname FROM Sailors s WHERE S.sid NOT IN (SELECT R.sid FROM Reserves R WHERE R.bid=102) 24. Given below is a small DB schema covering student enrollments in university courses, with primary keys underlined. What is the corresponding Domain Relational Calculus query in conversational English? Q:{il () (Dept (ghi) AND (3de) (Course (def) AND g=d AND d="MATH AND e=243) Sailors(sid: integer, sname: string, rating: integer, age: real) Boats (bid: integer, brame: string, color: String) Reserves (sid: integer, bid: integer, day, date) Student ID Name Major Major is a FK to Dept in Course d Course Dept Num Title Dept is a FK to Abbrev in Dept DEPARTMENT(DNO, Dname, Office, SSN) // SSN foreign key II) Set up a third relation R for the purpose of cross-referencing the primary keys of the two relations DEPARTMENT and PROFESSOR representing the entity types. III) No need to do something Dept Abbrey Name Office a) Find names of sailors who have not reserved any boat with bid=102 b) Find names of sailors who have reserved all boats except the one with bid=102 Enroll SID Dept Num Date SID is a FK to ID in Student; Dept-Num is a FK to Dept-Num in Course Date 1 N Professor Project Find names of sailors who have reserved at least a boat with bid=102 d) Find names of sailors who do not live in any boat with bid=102 e) None of them M a) Which office should we go to ask about MATH 243? b) Which department opens the MATH 243 course? Retrieve all the department information (Abbrev, Name, Office) of MATH 243 d) Retrieve all the course information (Dept, Num, Title) of MATH 243 N c) 1 M M 23. Following ER diagram is given to you. We want to map SERVICE entity to relational form. Give the names of the key attributes of SERVICE relation. 21. Employee and Dependent tables are given to you. What is the name of the operation that produced the following result table. 1 N Salary Model Capacity Weight WORKS ON EMPLOYEE N Department Grad Student Frame Lname Dependent name John Smith Alice lohn Smith Clizabeth PLANE TYPE Dame Oti MAINTAIN Pour Le num N PLOT John Smith Michael FUES OF TYPE Due World N Date/workode SERVICE Hours Theodore Roge Franklin Wong Alice Franklin Wong Hoy Franklin Wong | oyce Cnglish NULL Ramesh Narayan NULL ames Borg NULL AIRPLANE a) Only 11 b) I and II Only III d) II and III e) Only 1 PUANE SERVICE Poate id name STORED IN OWNS OWNER lennifer Wallace Abner Ahmad Jabbar NULL Alicia Zelaya NULL HANGAR Number Location Capacity EMPLOYEE Frame Minit Lame Address Sex Salary Super en John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M30000 333445555 Franklin Wong 333445556 1955-12-08 638 Voss, Houston, TX M40000 888665555 Alicia Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Jennifer s Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666BB4444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 James Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL Reg# Date, Workcode Date/workcode Reg#, Date/workcode d) Date, Workcode b) F DEPENDENT Essn 333445555 333445555 333445556 987654321 123456789 123456789 123456780 Dependent name Alice Theodore Joy M F M M Bdate Relationship 1988-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-20 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse Michael Alice Elizabeth F Cross Product b) Left Outer Join 25. What does the following query compute? 16- Following ER diagram is given to you. There is a 1:1 relationship between Professor and Department entities. Which of the following options are correct for mapping of 1:1 Relation Types 1) 1:1 relation RUNS is mapped by choosing the participating entity type DEPARTMENT and include a foreign key in DEPARTMENT the primary key of PROFESSOR. Because its participation in the RUNS relationship type is total. SELECT S.sname FROM Sailors s WHERE S.sid NOT IN (SELECT R.sid FROM Reserves R WHERE R.bid=102) 24. Given below is a small DB schema covering student enrollments in university courses, with primary keys underlined. What is the corresponding Domain Relational Calculus query in conversational English? Q:{il () (Dept (ghi) AND (3de) (Course (def) AND g=d AND d="MATH AND e=243) Sailors(sid: integer, sname: string, rating: integer, age: real) Boats (bid: integer, brame: string, color: String) Reserves (sid: integer, bid: integer, day, date) Student ID Name Major Major is a FK to Dept in Course d Course Dept Num Title Dept is a FK to Abbrev in Dept DEPARTMENT(DNO, Dname, Office, SSN) // SSN foreign key II) Set up a third relation R for the purpose of cross-referencing the primary keys of the two relations DEPARTMENT and PROFESSOR representing the entity types. III) No need to do something Dept Abbrey Name Office a) Find names of sailors who have not reserved any boat with bid=102 b) Find names of sailors who have reserved all boats except the one with bid=102 Enroll SID Dept Num Date SID is a FK to ID in Student; Dept-Num is a FK to Dept-Num in Course Date 1 N Professor Project Find names of sailors who have reserved at least a boat with bid=102 d) Find names of sailors who do not live in any boat with bid=102 e) None of them M a) Which office should we go to ask about MATH 243? b) Which department opens the MATH 243 course? Retrieve all the department information (Abbrev, Name, Office) of MATH 243 d) Retrieve all the course information (Dept, Num, Title) of MATH 243 N c) 1 M M 23. Following ER diagram is given to you. We want to map SERVICE entity to relational form. Give the names of the key attributes of SERVICE relation. 21. Employee and Dependent tables are given to you. What is the name of the operation that produced the following result table. 1 N Salary Model Capacity Weight WORKS ON EMPLOYEE N Department Grad Student Frame Lname Dependent name John Smith Alice lohn Smith Clizabeth PLANE TYPE Dame Oti MAINTAIN Pour Le num N PLOT John Smith Michael FUES OF TYPE Due World N Date/workode SERVICE Hours Theodore Roge Franklin Wong Alice Franklin Wong Hoy Franklin Wong | oyce Cnglish NULL Ramesh Narayan NULL ames Borg NULL AIRPLANE a) Only 11 b) I and II Only III d) II and III e) Only 1 PUANE SERVICE Poate id name STORED IN OWNS OWNER lennifer Wallace Abner Ahmad Jabbar NULL Alicia Zelaya NULL HANGAR Number Location Capacity EMPLOYEE Frame Minit Lame Address Sex Salary Super en John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M30000 333445555 Franklin Wong 333445556 1955-12-08 638 Voss, Houston, TX M40000 888665555 Alicia Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 Jennifer s Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 Ramesh K Narayan 666BB4444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 James Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL Reg# Date, Workcode Date/workcode Reg#, Date/workcode d) Date, Workcode b) F DEPENDENT Essn 333445555 333445555 333445556 987654321 123456789 123456789 123456780 Dependent name Alice Theodore Joy M F M M Bdate Relationship 1988-04-05 Daughter 1983-10-25 Son 1958-05-03 Spouse 1942-02-20 Spouse 1988-01-04 Son 1988-12-30 Daughter 1967-05-05 Spouse Michael Alice Elizabeth F Cross Product b) Left Outer Join