Database Design Problem

Poet's circle database phase 1: analysis

The following information is gleaned from speaking to the various stakeholders at Poet's Circle. They want to develop a database system to track the poets they have recorded, the poems they write, the publications they appear in, as well as the sales to customers that these publications make.

The designer asks various questions to get more detailed information, such as "What is a poet, as far as the system goes? Does Poet's Circle keep track of poets even if they haven't written or published poems? Are publications recorded even before there are any associated poems? Does a publication consist of one poem, or many? Are potential customer's details recorded?" The following summarizes the responses in our example:

  • Poet's Circle is a publisher that bases its choices of publications on an active poetry community on its website. If enough of the community wants a poem published, Poet's Circle will do so.
  • A poet can be anybody who wants to be a poet, not necessarily someone who has a poem captured in the system or someone who has even written a poem.
  • Poems can be submitted through a web interface, by email or on paper.
  • All captured poems are written by an associated poet, whose details are already in the system. There can be no poems submitted and stored without a full set of details of the poet.
  • A publication can be a single poem, a poetry anthology, or a work of literary criticism.
  • Customers can sign up through a web interface and may order publications at that point in time, or express interest in receiving updates for possible later purchases.
  • Sales of publications are made to customers whose details are stored in the system. There are no anonymous sales.
  • A single sale can be for one publication, but many publications can also be purchased at the same time. If more than one customer is involved in this sale, Poet's Circle treats it as more than one sale. Each customer has their own sale.
  • Not all publications make sales some may be special editions, and others simply never sell any copies.

Steps

  1. Find the Entities
  2. What data attributes (fields) are associated with each entity
  3. What is a unique (primary key) attribute(s) for each entity
  4. Find Relationships amount the entities.
    • Name the relationship
    • What are the carnalities of the relationship?
    • Are there attributes for the relationships?
  5. Make an Entity-Relationship Diagram
  6. Convert many to many relationships into one to many
  7. Make tables

Solution

Topic attachments
I AttachmentSorted ascending History Action Size Date Who Comment
PNGpng rental.png r1 manage 95.3 K 2018-02-07 - 17:26 JimSkon  
Topic revision: r1 - 2018-02-07 - JimSkon
 
This site is powered by the TWiki collaboration platform Powered by PerlCopyright © 2008-2019 by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback