Hi I have a bit of a problem with an E-R diagram, and I'm wondering if it's a limitation of the software or a more general flaw in the design.

I need to model the entities and relationships of a small franchised business. there are multiple Branches with multiple members of Staff, one of whom is the Manager.

The way I lay out these three entities (excluding all other external relationships) is attached. (Each Branch has one Manager and at least one member of Staff).

I'm trying to avoid having a Entity called Manager, because I feel this is pointless when I can set the manger to one of the members of staff.

To get the cardinality correct in Visio I had to set the both relationships to Identifying, but this has mean the FKs are also treated as PKs. Is this wrong?

I'd welcome any help on it.

p.s. I should add that there's plenty other entities that I've removed for clarity

The relationship between tables can't be identifying on both ends. While that gets the cardinality to 1:1, it creates a paradox. An identifying relationship means that the "parent" side defines the existance of the "child" side, meaning that the child depends on the existance of its parent. The mutual dependancy is impossible to satisfy, since both sides have to exist first (English kind of breaks down describing this, but if you think about it too hard you'll laugh).

The way that I'd solve this problem is to make the two entities independant. A branch doesn't really rely upon the existance of any given employee. The employee doesn't rely on the existance of a branch. Both of these relationships can be butchered in a company reogranization with no real damage to either entity (the branch and the employee can exist nicely independantly).

Making these entities independant leaves you with a bit of a problem, since there is no longer a clear way to show the relationship between an employee and a branch or between a branch and an employee (the manager). Creating a new BranchEmployee table that describes the real-world relationship between a branch and its employees can fix this problem. This type of relationship table adds value in many ways, because it allows you to record the dates that begin and end a relationship, attributes such as "manager" and other things that describe the relationship instead of the branch or the employee. True cardinality can still be a problem because this is much more expressive than a simple FK, but if that is a problem you can create a second relationship table just for current managers to resolve this conflict.

-PatP

There are probably better ways to model this situation as Pat has pointed out. I don't quite agree with Pat that the mandatory 1:1 situation creates a paradox though.

Such relationships are not so uncommon, the Staff and Branch Manager example being a familiar example. This kind of relationship does NOT in fact imply that "A must exist before B and B must exist before A". The correct implication is that that A and B must exist simultaneously. Unfortunately, most SQL DBMSs have very poor support for this situation. I don't know of any SQL DBMS that permits multiple assignment. Some go half way to supporting it (multi-table INSERT statement in Oracle for example) but can't cope with this particular example. The only standard method to do it is using the messy kludge of "deferrable constraints", which means the constraints are not evaluated until the end of a transaction.

Given the limitations of mainstream DBMS software, a change is probably advisable if you want to have something you can actually work with. That doesn't mean you did anything fundamentally wrong. You have just come up against one of the brick walls in SQL's model of doing things.

Hi there,

Thanks for both of your great responses, it's much appreciated. Since making the post I attempted to implement that E-R in Access only to find out that it *was impossible* as dportas mentions. So, I've taken Pat's advice and have created a BranchStaff entity which holds Position, idBranch and idStaff. This seems to be working out alright for now. I'm still having some trouble implementing the rest of the E-R, but I think that's down to the fact I have to use MS Access, when I'd rather forward-engineer the Visio diagram to SQL statements and be done with it.

Time is running sparse so I'm charging ahead and have resigned myself to the fact that my final implementation may not match the E-R model as closely as I'd hoped. I have plenty of other brick walls to deal with for now..

Thanks again,
A

I've taken the advice and have employed the use of an intermediary table to solve the branch/staff/manager issue. However I'm not convinced it's syntactically correct.

I've attached the full ERD below for a taxi firm this time, though the principle is the same - one manager per branch, 0 or more other staff.

For this problem,
Taxi Drivers/Owners aren't members of "Staff".
A Taxi has one Owner.
Each Driver can drive any Taxi in the Branch.
Not all Driver's own a Taxi.

I don't know if I should be worried but when I reverse engineer an Access implementation of this model, the cardinality is wrong and I need to edit those parameters in Visio. I'm struggling a bit with Access, but if the model is correct at least I know I'm in the right area..

I'm also worried about the vague as anything verb/inverse phrases I'm using, for instance a Driver drives a Hire AND a Taxi. However I can't really think of another way to do this for now. I actually discovered everyone else in Uni is doing this in groups, and idiot me has been doing it on my own. Any suggestions welcomed.

looks like its developing nicely.....

but a few questions spring to mind.. they are intended tomake you think about your data model. they are not intended to be exhaustive or appropriate, nor do I expect answers.. after all its your data model, its your coursework
does a driver exist as a separate entity, or are they members of staff who drive
can a member of staff/driver drive any taxi, or any taxi not owned by another driver/member of staff
are drivers self employed
should you have two tables which contain largely the same stuff (eg name, DoB, Gender etc.....)
should fail reason be a PK to a table identifying failreasons
can a member of staff move between different branches, and if so do you need to identify that
is the FK identfying where the booking was made correct to be at a member of staff who took the booking or at the branch level.
is the make of taxi important (or other features say towbar, extra luggage space) when making the booking

Some great questions there, which got the grey matter moving again.

An attempt to clear a few questions up in one fell swoop: "Every Owner drives Taxis; not every Driver is an Owner; Drivers are not 'members of Staff'/employess; Staff members cannot be Drivers; Drivers can drive any Taxi".

That last one is pretty vague, and in reality of course this wouldn't be the case, there would be subtle variations in this, not *every* Driver could drive a particular Taxi, just the ones who had an arrangement with the Owner of that Taxi. For the purpose of this assessment, I have chosen for the far simpler, though less believable free-for-all system as I'm not sure my lecturer wants that fine a level of detail.

I had thought of creating an Entity called Individual which would hold all the common properties of a person - such as address, sex, DOB, phone number etc. However I decided against it as for one, I need more info about drivers & staff than I do for customers (where usually I just have last name, phone, and a pickup address); and the coursework specified that Drivers are not members of Staff (so no need for NI numbers / tax ref). Because of that specific requirement I decided to keep them all separate, but I did and still do see the fallacy in that decision in terms of keeping the model as simple as possible.

I think the Hire FK idBranchStaff is correct, otherwise I don't see how I could query individual telephone operator's performance. I'm always willing to be shown up as a fool though, so feel free.

Properties of a Taxi: I thought the one that really comes up often is the number of seats. All taxis would have a certain size boot, and so I'm avoiding making the system more complex than it needs. Any out of ordinary booking, would be arranged on an ad-hoc basis.

I like the idea of a table of Fail Reasons, it would avoid the need to manually inspect those reason fields and would be useful in speeding up problem reporting.

Anyway thanks for those questions, and sorry for answering them

The simplistic approach to the "people problem" would be to have entities for people, drivers, staff, clients, etc. Let each of the specific formst (drivers, staff, clients) have an FK to People where you store the information common to all people.

All taxi's have common characteristics such as number of passengers, etc. Some taxi's (such as a rickshaw or a water taxi/boat) would have no boot at all. Some might have special features such as being handicapped accessible or have special religious features (I've been told that this is important in some cultures). There are probably as many differences as similarities.

In general, any "fill in the blank" attribute that you expect to ever need to analyze (count, average, correlate, etc) is a strong candidate for being a foreign key even if you have to allow an "Other" option which allows the user to type what they wish.

-PatP

Friendlyfolk

Is this thread active, are you watching it; is the assignment complete ? If not, there are still a few unresolved issues, such as:

1 Entities not identified yet. Before the Driver is assigned/chooses a Taxi, it is implied that the Taxi is assigned to a Branch; the Driver chooses/is assigned a Taxi from that Branch pool. The model does not show that, right now the Taxis are available to the entire franchise.

2 It may be beyond the scope of the exercise (only you will know), but there is no disinction between:
- Taxis sitting there, owned by Owners, and available to Branches
- Taxis assigned to Drivers, available for hire
- Taxis hired

3 Minor attribute issues. Area is most probably Locality. If the Address were tightly defined (Normalised), it would contain Locality, and a separate Area would not be required (unless a driver wished to be identified as being available in a Locality other than their own)

4 I do not get the relation between BranchStaff and Staff. If it is 1:1 and non-optional at both ends, then it is one entity (two separate entities is a breach)

5 You are correct to remove the vagueness/ambiguity from the verb phrase (that is a requirement of the exercise). (The model is entirely present time, is does not record history, so to speak; if it did, you would need quite a few more columns; I will remain in the present time only context.) Therefore:
- a Driver is assigned a Taxi (for a shift or whatever) and then drives it
- separately a Driver accepts or contracts a Hire

6 You cannot (a) create a reasonable or correct Relational Data Model and then (b) implement it in a non-Relational tool such as Access; anything more than the simplest relations will fail. Most Unis provide a real Relational tool for you to complete the assignment (otherwise it is impossible); you have to use a RDBSM to implement a Relational Model tool.

the posts are dated.

Right, bitten by the old american date format problem again "09-08" vs "08-09". I didn't realise this forum was THAT inactive. Thanks.