Is it possible for a weak entity, already related to a strong entity, to get related to another weak entity in an ER diagram

database-design

I have a problem statement (briefed),

Database for a Fitness Club –

Client's Attributes: Name, Address, Ph. No.

Membership Plan's Attributes: Beg. date, Sub. Fee, Monthly Fee, Duration

Trainer's Attributes: ID, Name, Salary

Relationship-Types: Client is 'Enrolled In' a Mem. Plan, and Client 'Meets' Trainers

Enrolled In: Client has total participation while Mem. Plan does not.

Meets: Client & Trainer, both have total participation.

By Meets, it means, a Client entity attends a session guided by a Trainer entity.

So, Client & Mem. Plan are weak entity types & Trainer is a strong entity type.

When converted to an ER diagram, Client & Mem. Plan are related to each other, and Client is related to Trainer.

Is it a possible to have such an ER diagram or is there another way to rectify the same?

Edit: This ER diagram represents what I think it should be (without partial keys) and consider arrows in diagram as straight lines connecting two blocks/shapes.

enter image description here

Feel free to correct me if I'm wrong anywhere.

I also have an entity 'Workout Session', which has a relation with Trainer. A Workout Session is conducted by one Trainer only, but the problem states to keep data of only Trainers which a Client meets, not the Sessions he/she attends. But since that wasn't related to the question I needed to ask, so I didn't add that part.

I think there is no single attribute of either Client or Membership Plan which I can use to distinguish their respective entities uniquely, so I think Client and Membership Plan are weak entity types.

I have provided as much details as I can as I can't disclose the complete statement due some reasons.

Best Answer

Depending on how much you briefed your problem statement, it can be stated that:

  • Trainer is a strong entity (it has an ID)

  • Neither Client nor Membership Plan seem to be weak or strong entities.

    They both could be considered strong entities provided that a primary key is defined on them*; e.g. Client.(Name, Phone) and Membership Plan.("Beginning Date", Duration)

In your Chen diagram, Meets entity should have both ends total participation, but there's an arrow between Trainer and Meets?

Other than that, it seems to be alright.

* A strong entity is defined as having a key that does not depend on any other key; a weak entity as as having a key that depends on a foreign key. E.g. Order has a primary key order_id which is independent, and that makes it a strong entity, whilst Order_Detail has a primary key order_detail_id and unique key order_id, line_no, which makes it a weak entity since order_detail_id does not provide a meaningful independent ID, and the unique key depends on Order table. See weak entity entry in Wikipedia.

They're independent from other entities, so they are candidates for being considered strong entities provided that they have a primary key, which is not known if they have and is not clear if they could in the definition.

Related Question