ER Modelling - Subclass relationships & Relationship Entities

Question

I have a couple of specific questions regarding ER modelling.

To provide some specific context, this is for a university project and I seem to not be able to hunt down specific answers to these questions.

Background

• The ER model is depicting a small database for an accounting firm for them to manage their clients, their audit jobs (annual job), the time and fees for these jobs and a few other minor bits and pieces. The bits and pieces are silly (e.g. personal assistant typingSpeed, but are requirements for the project)
• Each client must have 1 audit completed every year and that audit is completed by 1 or more staff members who work at the accounting firm
• The client (and the audit job) are managed by a manager. Each client has only one manager who manages them and each manager can manage multiple clients
• As staff work on the audit, they charge time to the audit. The cost of the audit is calculated as their charge out rate multiplied by the total number of hours charged to the audit
• Each member of staff works for 1 and only 1 team. A team has 1 or more staff members working in it.
• A partner is a subclass of the staff superclass, with a unique rca number identifying them (accounting jargon). Each partner leads a team and each team has one and only one partner
• Each partner has a personal assistant and each personal assistant works for only 1 partner

ER Model

Audit Time & Fee ER Model

Questions

1. Partner and Personal Assistant are both subclass entities and per my diagram there is a mandatory 1..1 relationship. Is it "legal" to have a specific relationship between subclasses?

2. I have used an optional, or relationship for the subclasses of Staff, which I think is fine as a staff member cannot be any of the roles at the same time. The only omitted role as a subclass is that of "auditor". If I were to include this, would "optional, or" change to "mandatory, or", as all possible options are shown and the staff member must be one of those 4 roles?

3. Between Annual_audit and Staff I have a relationship entity (I could be calling this something wrong there). Is this the correct way to show the relationship? I tried using a ternary relationship as well, but ended up going backwards and forwards several times.

4. Any general feedback or pointing out of errors is most welcome

Many thanks for any help anyone is able to provide.

Answer 1

Partner and Personal Assistant are both subclass entities and per my diagram there is a mandatory 1..1 relationship. Is it "legal" to have a specific relationship between subclasses?

It's perfectly valid to have relationships between subtypes and/or supertypes.

I have used an optional, or relationship for the subclasses of Staff, which I think is fine as a staff member cannot be any of the roles at the same time. The only omitted role as a subclass is that of "auditor". If I were to include this, would "optional, or" change to "mandatory, or", as all possible options are shown and the staff member must be one of those 4 roles?

That sounds correct. I'd rather use the term "disjoint" than "or".

Between Annual_audit and Staff I have a relationship entity (I could be calling this something wrong there). Is this the correct way to show the relationship? I tried using a ternary relationship as well, but ended up going backwards and forwards several times.

There's no such thing as a "relationship entity" in the ER model. You may be thinking of associative entities which are used in the network data model to decompose many-to-many relationships. The ER model supports many-to-many relationships directly and don't need associative entities for this purpose, though they still have a place in subjecting relationships to other relationships.

Annual_audit in your diagram is a weak entity relation. Staff is a regular entity relation. Between them is Charges time which is a many-to-many relationship relation. The notation is readable enough if you know the concepts. Chen's notation would represent the concepts more clearly though, while diagrams that look like table schemas work better for physical models.

One problem with your diagram is the arrow heads. They're inconsistent with the cardinality indicators. They cardinality indicators look correct, but the arrow heads mostly don't point in meaningful directions. I would remove them all except the one pointing from the subtypes to Staff.

Finally, I suggest you don't mix OOP and data modeling concepts and terminology. Data modeling is for representing knowledge, OOP is for modeling systems. Use "subtyping" for subtypes/subsets of entity sets, "subclassing" for derived classes in a programming context.