Need help in Entity Diagram Relationship (ERD)

• paradox86

  2 Feb 10, 17:45

  Anyone familiar with this? if yes i'll post the question. TIA :D
• wee_ws

  2 Feb 10, 23:11

  Hi,

    Please do. Thanks.

  Cheers,
  Wen Shih

• paradox86

  3 Feb 10, 12:42

  Assume you are working for an Electronic Store in Singapore city, and you have been assigned with task of developing a conceptual design and to implement it. Your task is to design a database that allows you to capture the facts described below: � Electronic Store produces three high-cost, low-volume products: P1, P2, and P3. Product P1 is assembled with components C1 and C2; product P2 is assembled with components C1, C3, and C4; and product P3 is assembled with components C2 and C3. � Components may be purchased from several vendors, as shown in the table with Component/Vendor summary given below: VENDOR COMPONENTS SUPPLIED V1 C1, C2 V2 C1, C2, C3, C4 V3 C1, C2, C4 � Each product has a unique serial number, as does each component. Customers purchase either component separately or assemble the product by themselves. � To keep track of product performance, careful records are kept to ensure that each product's components can be traced to the component supplier. � Products are sold directly to final customers; that is, no wholesale operations are permitted. � The sales records include the customer identification and the product serial number. For the above given scenario, draw an ER Diagram, using Crow-Foot notation, with the following requirements: a. Show the complete set of structural constraints (multiplicity, cardinality, participation); b. Add the necessary attributes to each entity aside from those mentioned above;kindly distinguish one attribute to another (primary key, foreign key, additional attributes); and Provide reasonable assumptions to support your diagram Thanks and regards.
• wee_ws

  3 Feb 10, 15:02

  Hi,

    I'll just give a little guidance, so you could get started.

    From the problem description, the following entities are necessary:

    STORE,
    PRODUCT,
    COMPONENT,
    VENDOR,
    CUSTOMER.

    With these, we can establish relationships between them:

    STORE - sells - PRODUCT (1-many),
    PRODUCT - has - COMPONENT (many-many),
    VENDOR - supplies - COMPONENT (many-many),
    CUSTOMER - buys - PRODUCT (many-many),
    STORE - buys - COMPONENT (1-many).

    These tables may be possible:

    PRODUCT (Prod #, ...),
    COMPONENT (Comp #, Vendor #, ...),
    VENDOR (Vendor #, ...),
    CUSTOMER (Cust #, ...),
    CUST_SALE (Sale #, Cust #, Prod #, ...),
    STORE_PURCHASE (Purchase #, Store #, Comp #, ...).

    Hope all these pointers will help you to complete your assignment! Thanks.

  P.S. ERD is not difficult:

  1. The main thing is to identify the main players (i.e. entities) and establish appropriate relationships between them.

  2. For relationships, one should know that there are several types, i.e. 1:1, 1:many, many:many.

  3. For a table specific to an entity, one should always have a unique serial number, plus some logical attributes.

  4. For a table related to two entities, like CUST_SALE, the table must capture the keys of both entities.

  Cheers,
  Wen Shih

• paradox86

  3 Feb 10, 17:24

  Thanks wee_wrs for the explaination. i came out with:

  Im not too sure about the attributes, i got the connections correctly i guess. can you help me with it? gotta do it on MySQL so i gotta get the diagram right. TIA

• wee_ws

  3 Feb 10, 18:05

  Hi,

    Good work, it looks alright and practical at first glance :)

    However, when you start working on SQL, there may be a need for you to tweak the ERD design to make database queries as optimised as possible. It is also important to look at the query requirements for your case study.

    Additionally, you might want to ask if there is need for you to have your tables normalised to 3NF.

    Database design, as with programming, is always about going back and forth. Jiayou!

  Cheers,
  Wen Shih

• paradox86

  3 Feb 10, 18:15

  Ahh thanks! what do you mean by tables normalised to 3NF? im just a week into this so i've got no idea.. Thanks

• wee_ws

  3 Feb 10, 20:57

  Hi,

    Perhaps you will encounter the concept of normalisation later in your course. In any case, normalisation seeks to make all database table attributes depend solely on the primary key.

    This site provides you with the basics through an example:

    http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html

    Enjoy learning! Thanks.

  Cheers,
  Wen Shih