ER Diagram - Supertype and subtype

[chwong]

Hi I am new of ERD design. I have drawn the diagram but for the supertype, subtype and entity associated part, I am not confirm the way I presented is correct or not. I hope to get some advises from you all.

ERD
1. A distribute places an order

2. A distributor will be ranking based on their sales volume per month.
if sales volume = 100
ranking = consultant
if sales volume = 200
ranking = supervisor

3. An order can consist of many product. A product can have many order. In this situation, is it I can generate a associative entity which is call 'ORDERDETAIL' and the attribute = TotalAmount?

[tesuji]

hi

>>> A distribute places an order
is this a customer?

>>> distributor will be ranking based on their sales
are order and sales identical?

>>> An order can consist of many product...
ERM looks like:

distribute? ----< order ----< order_details/item >---- product
(---< is crowfoot)
where order_details is many-to-many relationship which besides the two foreign keys of the related entities may also have additional attributes as for example quantity and item price.

>>> attached docx?
I don't like to open that. You may post pictures/.jpeg or pdf.

-- tesu

[chwong]

Hi Tesu,

Thanks for your reply. I have attached my attachment in PDF format. Please guide.

1. Distributor is a customer.
2. Order and sales are identical.

3. It is possible RANK is a entity?

Assumptions
•Distributor can (and probably will) be recorded before they have made any orders. (Therefore the minimum cardinality from DISTRIBUTOR to SALESORDER is 0.)
•It is not possible to make a payment unless there is at least one salesorder to be paid for. It does not make sense to make a payment for 0 salesorder. (Therefore the minimum cardinality from PAYMENT to SALESORDER is 1.
. It is possible that a product may contain 0 order. (Therefore the minimum cardinality from ORDERDETAIL to PRODUCT is 0)

[tesuji]

He he,

You are ERM master! Your ERM is perfect. (I would only complete the other entities and the relationship by their primary keys as you already did for salesorder, also showing foreign keys would look nice (but this depends on your teacher: some don't like to see keys on erm level))

Also supertyp and subtypes are complete, and well set optional/mandatory cardinalities too. On ERM design level it is a good idea to distinguish between those subtypes. Later when you map this design into relational model (RM) you will have to decide what should happen with all those subtypes.

For there is always a one-to-one relationship between super-/subtypes one can mix them together in RM, in principle. If subtype have various attributes and if they are managed relatively independently from each other, they should have their own tables in RM.

As for 3rd point: Rank is an attribute of subtypes. However, if rank is the only attribute of subtypes, you can simply add rank to supertype/table distributor in relational model (I wouldn't do this in ERM). Later, the values of rank can also be in an extra table in relational model, then it would be kind of look-up table.

-- tesu

[chwong]

Hi Tesu,

Thanks for your guidance. I am really appreciated it.

As you mention, you will not add RANK in supertype table. May I know, how the RANK should be put in order it can be a look-up table in RM?

Regards,
Wong CH

[tesuji]

Hi

A look-up table for ranks could look like:

/*
rankNo salesfrom salesto credit
-----------------------------------
0          0        99   no credit 
1        100       499   Consultant
2        500       999   Supervisor
3       1000      4999   Manager
4       5000    999999   Executive  
   
Primary key: rankNo. (Also salesfrom is possible yet not recommendable.)

Usage, for example in subtype-table manager:

create table manager(distributorNo integer not null,rankNo integer not null,
... further_columns ...,
primary key (distributorNo),
foreign key (distributorNo) references distributor(distributorNo),
foreign key (rankNo) references rank(rankNo)); */

Btw, you should allow to copy from your pdf-file!

-- tesu

[chwong]

Hi Tesu,

thanks for your explanation. I just create the pdf-file using ms office add-on feature, will check it for future. hehehe... sorry for that.

regards,
Wong CH