Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    86

    Entity Relationship Diagram

    I need to draw a single Entity Relationship Diagram (ERD) to represent the following situation, which is described by means of text and a relational data model.


    Situation
    A relational database has been designed for a company which sells merchandise through magazine advertising. Customers who want to place an order call a toll-free telephone number and place an order for the items that they require. This order is entered through an on-line terminal and is later filled and shipped by the stock room. The customer may pay for the purchase by credit card or pay ‘collect’ (that is, they pay at the time the goods are delivered).
    Each table is described below, with the primary key underlined and foreign keys defined.


    Table structure:

    CUSTOMER
    customer_number (PK)
    name
    address
    phone_number
    credit_rating

    ORDER
    order_no (PK)
    customer_number (FK)
    order_date
    shipping_address
    credit_card_type
    credit_card_number
    foreign key (customer_number) references Customer


    ITEM
    stock_no (PK)
    description
    weight
    colour
    unit_price

    ORDERED_ITEM
    order_no (PK)
    stock_no (FK)
    quanity_required
    unit_price
    foreign key (order_no) references Order
    foreign key (stock_no) references item

    MAGAZINE
    magazine_code (PK)
    name
    address
    phone_number

    EDITION
    magazine_code (PK, FK)
    issue_date
    foreign key (magazine_code) references magazine

    ADVERTISED_ITEM
    magazine_code (PK, FK)
    issue_date (FK)
    stock_no (FK)
    page_no
    advertised_price
    foreign key (stock_no) references Item
    foreign key (Magazine_code, Issue_date) references Edition

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when is it due, how much of it have you done so far, can we see it, and what's your question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    86
    Originally posted by r937
    when is it due, how much of it have you done so far, can we see it, and what's your question?
    How do you start a diagram, do you follow the flow of the primary and foreign keys? How do you represent the relationships? I read that they should be expressed as verbs, why can't the primary keys be used in the diagram?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    How do you start a diagram
    by using boxes to represent entities

    do you follow the flow of the primary and foreign keys?
    they don't actually "flow"

    How do you represent the relationships?
    usually with lines connecting related boxes

    I read that they should be expressed as verbs
    yes, that's a good idea, and remember to have two verbs, one for each direction

    why can't the primary keys be used in the diagram?
    but they can
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Posts
    86
    How would the flow be illustrated with the sample table data? Can you give me a sample on how this would flow?

  6. #6
    Join Date
    Feb 2004
    Posts
    86
    Originally posted by kram66
    How would the flow be illustrated with the sample table data? Can you give me a sample on how this would flow?
    Based on the database structure, is this the correct flow?
    tables in capitals and relationships in lower case

    CUSTOMER
    places
    ORDER
    for
    ORDERED_ITEM
    from
    ITEM
    get
    ADVERTISED_ITEM
    from
    EDITION
    advertised
    MAGAZINE

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
      CUSTOMER
    +----------------------+
    customer_number (PK) |
    +----------------------+
    name                 |
    address              |
    phone_number         |
    credit_rating        |
    +----------+-----------+
               | 
      
    customer |
       
    places  |
       
    order   order
               
    placed
               
    |   by 
               
    customer
               

      
    ORDER    ^
    +----------------------+
    order_no (PK)        |
    +----------------------+
    customer_number (FK) |
    order_date           |
    shipping_address     |
    credit_card_type     |
    credit_card_number   |
    +----------------------+ 
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Feb 2004
    Posts
    86
    Thanks, but I'm still confused how to use the relationships and how to name them, can you help?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •