You may discuss problems with other students in the class. However, each student must write up his or her own solution to each problem independently. That is, while you may formulate the solutions to problems in collaboration with classmates, you must be able to articulate the solutions on your own.
Chapter, exercise and page numbers refer to the course text Database Management Systems, 3rd edition, by Ramakrishnan and Gehrke
1. a. In class we discussed a relationship "written by" between an entity set "books" and an entity set "authors". We noted that some books have a different author for each chapter and an editor plays the role of author for the purposes of card catalogues and other listings of the book. We discussed a second relationship "edited by" relating "books" to another entity set "editors", but we did not draw the ER diagram. Draw the ER diagram. How, if at all, do you represent the constraint that a book must be authored or edited? If you cannot represent this constraint in your ER diagram, argue why not.
b. Consider Part a again, but now authors and editors are together in an entity set "producers". Show how you would represent with an ER diagram an new relationship "by" which relates "producers" to the entity set "books". How do you record whether the relationship is authorship or editorship? Now a book must be authored or edited; represent this.
c. Chapter 2, exercise 2.2, pg. 52, part 3. Assume that each course is represented once (as in our Princeton Undergraduate Announcement) and that every professor who can teach a given course is recorded. So more accurately, "each professor must be able to teach some course".
d. Chapter 2, exercise 2.2, pg. 52, part 6. The description in the textbook is somewhat ambiguous. My interpretation is that some professors can only teach a course as part of a team and so there must be a representation of team-teaching for some courses. I also allow the situation that there are other professors who can teach the courses in question individually. This must also be representable.
2. Chapter 2, exercise 2.6, pg. 53-54.
3. Chapter 3, exercise 3.10, pg.96.
4.For each of the parts of Problem 1 above, give the set of relations and constraints defining the relational database schema for the same situation. You may use SQL or the specification style used in lecture.
5. For your ER diagram solving Problem 2 above, give the set of relations and constraints defining the relational database schema for the same situation. Explain which constraints you can capture and how they are captured, and which constraints you cannot capture in the the relational model. Again, you may use SQL or the specification style used in lecture.