### Written Assignment #1 Due Wednesday April 12

#### Turn-in procedure and late policy reminder

• On-campus students: Your assignment must be turned in by 5:00 PM on Wednesday April 12. You may turn the assignment in during class on Wednesday, or you may turn it in at the course administrator's office: Marianne Siroker, Gates 436. Assignments less than 24 hours late will be penalized 10%. Assignments more than 24 but less than 48 hours late will be penalized 30%. No assignments will be accepted after 5:00 PM on Friday April 14.

• SITN students: Your assignment must be timestamped by the courier on Thursday April 13. Late assignments timestamped by the courier on Friday April 14 will be penalized 20%. Assignments timestamped later than April 14 will not be accepted. Assignments not sent by SITN courier are subject to the late policy for on-campus students specified above. No late assignments are permitted for remote SITN students.

• For emergencies: Since emergencies do arise, each student is allocated four "chits". Each chit may be used to turn in a single assignment up to 24 hours late with no penalty, or two chits for up to 48 hours late. For SITN students, two chits are required to turn in an assignment one courier-day late. As with penalized late work, no assignments will be accepted more than 48 hours (or one courier-day) late. We'll keep track of your chits automatically.

#### Problem 1

In the E/R model, consider a relationship set R between two entity sets E1 and E2. Does it ever make sense for the same pair of entities e1 (from E1) and e2 (from E2) to participate in the relationship set more than once? That is, might we ever want to have two (or more) instances of (e1, e2) in R? If not, explain why not. If so, give a real-world example: give details of two entity sets and a relationship set that model a real-world domain where we might have a pair of entities related more than once.

#### Problem 2

Hints: There is some interplay between Problem 1 and at least one part of this problem, and not every part of this problem necessarily has a single correct answer.
• Part (a) Consider a relationship set R between two entity sets E1 and E2. Let K1 and K2 be the key attributes for E1 and E2, respectively. Assume for now that R has no attributes. Thus, if we translate R to the relational model, the schema for R's relation will be (K1,K2). If R is a one-one relationship set, what is a key for the corresponding relation (K1,K2)?

• Part (b) Continuing, if R is a one-many relationship set from E1 to E2, what is a key for the corresponding relation (K1,K2)?

• Part (c) Continuing, if R is a many-many relationship set, what is a key for the corresponding relation (K1,K2)?

• Part (d) Now suppose R is a many-many relationship set and has an attribute A, so the schema for the corresponding relation is (K1,K2,A). What is a key for (K1,K2,A)?

• Part (e) Now suppose R is a multiway relationship set between three entity sets E1, E2, and E3. Let K1, K2, and K3 be the key attributes for E1, E2, and E3, respectively. Assume R has no attributes. Thus, if we translate R to the relational model, the schema for R's relation will be (K1,K2,K3). If in the E/R diagram there is one arrowhead pointing to entity set E1 and no other arrowheads, what is the key for (K1,K2,K3)?

• Part (f) Continuing, if in the E/R diagram there is one arrowhead pointing to entity set E1, one arrowhead pointing to E2, and no other arrowheads, what is the key for (K1,K2,K3)?

• Part (g) Continuing, if in the E/R diagram there is one arrowhead pointing to each entity set, what is the key for (K1,K2,K3)?

• Part (h) Continuing, if in the E/R diagram there are no arrowheads at all, what is the key for (K1,K2,K3)?

#### Problem 3

Find a site on the Web for which you're quite sure there is a database management system operating behind the scenes. If the site you select has a wide variety of data and a very powerful interface, try to isolate a smaller portion of the site (or choose a simpler site).
• Part (a) What is the URL for the site you selected?

• Part (b) Briefly describe, in English, the data managed by the site (or portion of the site) and the queries available to the user.

• Part (c) Specify a possible E/R design for the data managed by the site. Don't forget to underline key attributes for entity sets and include arrowheads indicating the multiplicity of relationship sets. If there are weak entity sets or "is-a" relationships, make sure to notate them appropriately.

• Part (d) Using the method for translating an E/R diagram to relations, produce a set of relations from your E/R design. Specify (underline) keys for all relations.

• Part (e) Give a different relational schema from the one you got in part (d) that still captures the same information. Specify (underline) keys for all relations.

#### Problem 4

• Part (a) Specify an E/R design for your database. Don't forget to underline key attributes for entity sets and include arrowheads indicating the multiplicity of relationship sets. If there are weak entity sets or "is-a" relationships, make sure to notate them appropriately.

• Part (b) Using the method for translating an E/R diagram to relations, produce a set of relations from your E/R design. Specify (underline) keys for all relations.

• Part (c) Give a different relational schema from the one you got in part (b) that still captures the same information. Specify (underline) keys for all relations.
Note: There is no single right answer to this question by any means, although some answers may be better than others.

#### Problem 5

Consider the four relations on pages 188-189 of the Ullman/Widom textbook. These relations contain a database for describing PC's, laptops, and printers.

• Part (a) Reverse-engineer a corresponding E/R diagram for this relational schema. That is, give an E/R diagram that would be mapped to this set of relations.

• Part (b) Give an alternate relational design for this database that uses only one relation.