6- Remember the Informal Design Guidelines for Relation Schemas. 11 - What does the following query find? In our example we have a university database. Instructors' information is kept in the Instructor table. Instructor(SSN, Name, Bdate, Dnumber) In the university only 10 percent of instructors have individual offices. (SELECT DISTINCT r.sid FROM boats b, reserves WHERE b.bid=r.bid AND b.color= 'red') EXCEPT (SELECT DISTINCT r.sid FROM boats b, reserves WHERE b.bid=r.bid AND b.color='green'); We want to store office number information and we have 2 criteria. 1- use the space efficiently 2- avoid placing attributes whose values may frequently be NULL. Our database designer proposed us two different designs. According to our criterias which design should we choose ? Sailors(sid: integer, sname: string, rating: integer, age: real) Boats( bid: integer, bname: string, color: string) Reserves (sid: integer, bid: integer, day: date) 1. Design / Instructor(SSN, Name, Bdate, Dnumber, Office_number) 11. Design // Instructor(SSN, Ename, Bdate, Dnumber) Instructor_Offices(SSN, Office number) a) Find the sailor IDs of at most one sailor who have reserved red boats but not green boats b) Find the sailor IDs of at least one sailor who have reserved red boats but not green boats c) Find the sailor IDs of all sailors who have reserved red boats but not green boats d) O None of these a) o Design 11 b) Design | c) O Design I and Design II d) None of them 19- Match the following definitions with the appropriate normal forms (first normal form, second normal form or third normal form) 1) Relation should not have a nonkey attribute functionally determined by another nonkey attribute (or by a set of nonkey attributes). II) Relation should have no multivalued attributes or nested relations. III) For relations where primary key contains multiple attributes, no nonkey attribute should be functionally dependent on a part of the primary key. a) O I) 1NF, II) 3NF, III) 2NF b) o I) 2NF, II) 1 NF, III) 3NE c) O I) 3NF, II) 2NF, III) 1NF d) I) 2NF, 11) 3NF, III) 1NE el 1) INE II) 2NE WII UNE