CS101b - Assignment 5

The Entity-Relationship Model

Please keep track of the total time you spend on this assignment, and make a note of that on what you turn in. This way we can tune the assignments for future classes.

This week's assignment involves visual representations of database schemas using the Entity-Relationship model. For questions that involve drawing diagrams, you can use a program such as Visio, Powerpoint, xfig, dia, etc. Or, you can turn in hand-drawn diagrams. However you must follow these guidelines:

• Regardless of how you create your diagrams, they must be legible! If your work is hard to read, you are more liable to lose points.
• If you use a program, submit your work as a PDF file or PS file. If you can't save in either of these formats, give me a printed copy instead.

Some of the problems include translating diagrams into SQL DDL commands; in those cases, feel free to type the SQL into a text file (or some other file), as a separate document.

Suggested Reading

This week's assignment draws from the majority of Chapter 6. The reading sections for Lectures 14 through 18 indicate the most relevant sections: 6 - 6.7.5, 6.8.1, and 6.9. Of course, reading the entire chapter is recommended.

Book Problems

Do these problems from the "Exercises" section of Chapter 6.

General Hints:

When you are designing these database schemas, you should think hard about what constraints should be specified. Always try to design schemas so that it is not possible to represent invalid states.

Also, think about what types to use for the various columns when translating relation schemas into SQL DDL:

• Remember the trade-offs between CHAR and VARCHAR data-types, and the different strengths and weaknesses of the various numeric and date/time types.
• Also, make sure that you specify sufficient sizes/precisions for parameterized types. Don't choose the largest size possible because this is wasteful, but don't specify unreasonably small size limits, either.
• Finally, always make sure to specify all NOT NULL and key constraints that are appropriate. (The one exception is CHECK constraints, which I think are probably best to be avoided in general, due to their lack of portability and their often excessive performance impact.)

You will lose points if your designs specify bad column types or size/precision limits. If you provide rationale for your choices in SQL comments then you will be less likely to lose points. Also, you will lose points for leaving out constraints; these are essential in any good database design.

• Problem 6.15

  After you have created this E-R diagram, convert it into the corresponding relational model schemas. If you apply any schema combinations or other optimizations, note this and explain why this is a good idea.

  Finally, translate the relational model schemas into SQL DDL commands that can successfully be loaded into MySQL. (You should actually try that, of course.) Make sure that your SQL DDL commands include all primary key, foreign key, and candidate key constraints indicated or implied by the E-R diagram!

  Scoring: The E-R diagram is one part, the relational model schema is another, and the SQL DDL is a third part. Each part is worth 6 points, for 18 in total.

• Problem 6.21, parts b and c. Although not obvious in the problem statement, make sure your updated diagram also allows warehouses to supply the new kinds of items as well.

  Once you have completed both parts b and c, take your final E-R diagram and convert it into the corresponding relational model schemas. If you apply any schema combinations or other optimizations, note this and explain why this is a good idea.

  Finally, translate therelational model schemas into SQL DDL commands that can successfully be loaded into MySQL. (You should actually try that, of course.) Make sure that your SQL DDL commands include all primary key, foreign key, and candidate key constraints indicated or implied by the E-R diagram!

  Scoring: The final E-R diagram is one part, the relational model schema is another, and the SQL DDL is a third part. Each part is worth 10 points, for 30 points in total.

• Problem 6.22. Make sure your discussion touches on at least these particular topics:

  • Similarities and differences between what kinds of relationships are actually able to be modeled with the various approaches.
  • Similarities and differences between the kinds of mapping cardinalities and participation constraints that can be enforced, what those constraints might mean, and whether they are actually useful.
  • Your impressions as to what kinds of scenarios each of these patterns might be useful for, if any.

  Scoring: 20 points total.

• Problem 6.23. Don't specify the foreign key constraints in English; rather, write SQL DDL commands to create every table in the schema, and specify all constraints for primary keys, foreign keys, and candidate keys. Make sure your SQL DDL actually loads into MySQL.

  Scoring: 20 points total.