Assignment #2
Due Wednesday October 16

Reminder: Please be sure to make a copy of your solution to Assignment #1 before turning it in. Several problems in this assignment build on your answers to problems in Assignment #1.

1. Consider the Entity-Relationship diagram you designed for Problem (1) or Problem (2) from Assignment #1. Choose whichever problem you prefer. Please redraw the E/R diagram.

   (a)

   Use the method for translating an E/R diagram to relations to produce a set of relations for your database design. You may specify your relational schema using any notation used in class. Please be sure to underline key attributes.

   (b)

   Give a different relational schema from the one you got in part (a) that still captures the same information.

2. Consider the ODL schema you designed for Problem (1) or Problem (2) from Assignment #1. Choose whichever problem you did not choose for Problem (1) on this assignment. (In other words, after doing the first two problems on this assignment you should have considered the E/R diagram from one problem on Assignment #1 and the ODL schema from the other.) Please specify the schema again.

   (a)

   Use the method for translating an ODL schema to relations to produce a set of relations for your database design. You may specify your relational schema using any notation used in class. Please be sure to underline key attributes. In cases where we gave alternative mappings for ODL constructs (such as for sets and relationships), you may use whichever mapping you prefer.

   (b)

   Give a different relational schema from the one you got in part (a) that still captures the same information.

3. Consider a binary many-many relationship set R between an entity set E and itself. Let E have attributes A1,A2,...,An where A1 is the key. Let R have one attribute A. Assume that entity set E is translated to a relation in the usual straightforward way: .

   (a)

   Suppose there are no roles associated with relationship set R. Give a mapping from R into a relational schema.

   (b)

   Now suppose R has roles called r1 and r2. Give a mapping from R into a relational schema such that the information about roles is captured.

4. Consider a ternary relationship set R among three entity sets E1, E2, and E3. Let entity set E1 have one attribute A1 and let A1 be a key, let E2 have one attribute A2 and let A2 be a key, and let E3 have one attribute A3 and let A3 be a key. Relationship set R has no attributes.

   Draw the eight possible Entity-Relationship diagrams for relationship set R and entity sets E1, E2, and E3 when arities are specified. (There are eight possibilities because each entity set can either have or not have an arrow pointing to it.) For each E/R diagram, specify a relation for R and underline in the relation a minimal key. Recall from Problem (4) on Assignment #1 that a key is minimal if attributes that are not needed in the key are not included. The relations for the entity sets are trivial and need not be included.

5. Consider the following ODL schema. This design is probably similar to the design for the real-world database that is used to automatically match graduating medical students with internship positions.

     interface MedStudent {
       key ID;
       attribute integer ID;
       attribute string name;
       relationship List<Internships> positions
          inverse Internships::wants-position;
       relationship Set<Internships> wants-me
          inverse Internships::students; }
   
     interface Internships {
       key (hospital, city);
       attribute string hospital;
       attribute string city;
       attribute integer #positions;
       relationship List<MedStudent> students
          inverse MedStudent::wants-me;
       relationship Set<MedStudent> wants-position
          inverse MedStudent::positions; }

   In the schema, relationship positions in class MedStudent is an ordered list reflecting a medical student's choice of internships, while relationship students in class Internships is an ordered list reflecting a hospital's choice of interns. Relationships MedStudent::positions and Internships::students are not inverses of each other, since in general there is not an exact (or even close) match between the internship positions students desire and the students desired for a particular internship. Instead, we've specified separate inverse relationships: a relationship MedStudent::wants-me that is the inverse of Internships::students, and a relationship Internships::wants-position that is the inverse of MedStudent::positions. Since there is not necessarily an ordering among the related objects in these inverse relationships, they are specified as sets rather than lists.

   Finally, here's the problem: Putting together the techniques described in class and in the course notes for translating the various ODL constructs in the schema above to relations, produce a set of relations for this database design:

   (a)

   For your first relational schema, use the approach for translating sets/lists and relationship pairs that is similar to what's used in the E/R translation--a separate relation is created for each relationship pair.

   (b)

   For your second relational schema, instead use the approach for translating sets/lists and relationship pairs that is described in the course notes, where all relationship information is embedded in the relations created for the two related classes.

6. Personal Database Application (PDA)

   (a)

   Consider the ODL schema you designed for your PDA in Problem (6) of Assignment #1. Please specify the schema again. Use the method for translating an ODL schema to relations to produce a set of relations for your database design. As usual, you may specify your relational schema using any notation used in class, and please be sure to underline key attributes. In cases where there are alternative mappings for ODL constructs (such as for sets and relationships), you may use whichever mapping you prefer.

   (b)

   Consider the Entity-Relationship diagram you designed for your PDA in Problem (6) of Assignment #1. Please redraw the E/R diagram. Use the method for translating an E/R diagram to relations to produce a set of relations for your database design. As usual, you may specify your relational schema using any notation used in class, and please be sure to underline key attributes.

   (c)

   Which of the two relational schemas you obtained in parts (a) and (b) of this problem do you like better? Is there anything you still don't like about the schema (e.g., attribute names, relation structure, duplicated information, etc.)? If so, modify the relational schema to something you prefer. You will be working with this schema quite a bit, so it's worth spending some time to make sure you're happy with it.

   (d)

   Familiarize yourself with the Sybase relational DBMS by logging into Sybase, reading documentation, running various help functions, etc., as described in Handout #4: Getting Started with Sybase. You don't need to turn anything in for this problem.

   (e)

   Create relations for your PDA in the Sybase system according to the final relational schema you gave in part (c) of this problem. Turn in a script showing your commands for creating the relations, and showing a Sybase session in which the relations are successfully created.