Need help in Entity Diagram Relationship (ERD)

paradox86 SG

Anyone familiar with this? if yes i’ll post the question. TIA Very Happy

wee_ws SG

Hi,

Please do. Thanks.

Cheers,
Wen Shih

paradox86 SG

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

wee_ws SG

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 SG

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 SG

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 SG

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 SG

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