CPS 430/542 Lecture notes: E/R Design Principles


Coverage: [FCDB] §2.2 (pp. 39-47) and §2.5 (pp. 59-60)

Rules and guidelines

  • remain faithful to the requirements
  • avoid redundancy: less is more; say things at most once
    • e.g., do not add an attribute which is already captured in a relationship, or vice versa
    • not only inefficient use of storage, but
    • we desire a single point of modification
  • KISS
  • sometimes unnecessary to encode every possible relationship
  • entity set/relationship combo vs. attribute
  • multiway relationship vs. all binary relationships
  • there are many tradeoffs

Remain faithful to the requirements


    many-many, many-one, or one-one? aim to reflect reality

Sometimes unnecessary to encode every possible relationship

  • several relationships could represent same information;


    <works for> relationship or <offers> relationship necessary? it depends on requirements

  • some relationships can be deduced from others


    under what conditions would a <works for> relationship between [Instructors] and [Departments] be redundant?

Entity set/relationship combo vs. attribute?

  • an attribute is simpler to implement
  • again, a question of redundancy
  • department (name) and (address) as attributes of courses?



    or

  • see guidelines on p. 43 for replacing an entity set E with an attribute A
    • all arrows must enter E
    • E's attributes must collectively identify an entity, i.e., must be a key; department name and address do not collectively identify a department; name is sufficient to identify a department
    • no relationship involves E more than once
  • if met,
    • remove the relationship R and make attributes of E be attributes of other entity set; if R multiway (like above), attach attributes of E to R
    • may need to rename some attributes to prevent clashes with existing attributes
  • in instructors, courses, departments example above, guidelines indicate that first diagram is recommended; however if department (name) was the only attribute of [Departments], then the following diagram would be appropriate



    • note that repeating the same (dept name) for each course in a particular department is not true redundancy;
    • however repeating the address for each would be redundant

Multiway relationships vs. all binary

  • depends on requirements
  • know the differences

Keep in mind

  • no syntax to specify referential integrity on a many-many relationship; write a note
  • [FCDB] presents the classical notation for E/R modeling
  • there are several extensions to the classical syntax which involve, e.g., the representation of multivalued attributes
  • the syntax can only constrain the semantics in the multiplicity of relationships; all else is common sense, e.g., a married person should not be placed in the single entity set even though there is no constraint implicit in the syntax of the diagram forbidding one from doing so
  • data modeling is more of an art than a science

References

    [FCDB] J.D. Ullman and J. Widom. A First Course in Database Systems. Prentice Hall, Upper Saddle River, NJ, Second edition, 2002.
Return Home