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 by Ramakrishnan and Gehrke
1. a. In class we discussed a relationship "written by" between an entity set "books" and an entity set "authors" and decided that "books" fully participated in the relationship, i.e. every book is written by one or more person. However, 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. Show how you would represent with an ER diagram an new relationship "by" which related an entity set containing authors and editors 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.
b. Consider Part a again, but now "authors" and "editors" are separate entity sets. What is 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.
c. Chapter 2, exercise 2.2, pg. 46, 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. 46, 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. 48.
3. Chapter 3, exercise 3.10, pg. 85
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.