E/R Design Example

Tom Kelliher, CS 318

Feb. 8, 2002



We won't explicitly cover 5.5--7 in class. As with other assigned reading, you're responsible.


Read 6.1.

From Last Time

Intro to E/R methodology.


  1. E/R design example.

Coming Up

Relational algebra.

E/R Design Example

  1. Domain description:
    We want to design a mailing list server, to support any number of mailing lists. Each list has one owner (the list administrator). Only the owner may designate ``senders'' --- those who are allowed to post messages to the list. The owner may or may not be a sender. Owner and senders must also be readers. For each list, there may be an unlimited number of senders. Anyone may subscribe to any list as a reader. They may also unsubscribe themselves. Assume that everything is web-based. Messages are retained for an amount of time specific to each list and consist of a subject and text. Messages should indicate sender. E-mail addresses and passwords are used for authentication purposes. Passwords may be list-specific. Because everything is web-based, e-mail is not used to send messages, ordinarily. E-mail may be used for private communication between a list owner and a reader of that list. There are no limitations as to how many lists a user may own or how many lists a user may send to. Lists have names and descriptions and exist for a fixed amount of time.
    Allocate ten minutes for each of the following activities.

  2. The entities:
    1. Users.

    2. Lists.

    3. Messages.

    What are the attributes?

  3. The relationships:
    1. Administers.

    2. SendsTo.

    3. Reads.

    4. SentBy/To.

    What are the attributes and roles?

  4. The constraints: What are they here?

  5. Design E/R diagrams and determine constraints.

  6. Implement as a relational schema.

Thomas P. Kelliher
Wed Feb 6 09:20:46 EST 2002
Tom Kelliher