How to make this an ERD cardinality?

Question

In our assignment, our prof gave a specific number of entity, let's say:

1. The restaurant has 20 employees. so, is this 1:M or one is to 1..*?

2. The restaurant has 2 or more employees? again, is this 1:M or one is to 1..*?

i searched the net and found this one, Why is a specific cardinality not allowed in the ERD?

i'm confused about the example, it is stated there that

a relationship between flights and pilots where each flight has exactly 2 pilots present, would have to be represented as:

|flight| 0..N <------> 1..N |pilot|

rather than

|flight| 0..N <------> 2 |pilot|

isn't it that the cardinality above is 1:M

Thanks!

Answer 1

In ERDs, and in database design in general, we rarely concern ourselves with numbers other than "1" and "many".

The reason for this is because, (a) At least the way current databases work, it usually does not alter the structure if there is a specific number versus simply "many". (b) It is rare for their to be a fixed number other than 1.

To take (b) first: In your question you mention a restaurant with 20 employees. But it is very unlikely that the restaurant always had exactly 20 employees and always will. If they have 20 today, maybe tomorrow somebody quits and there are only 19, and then the owner hires 5 people for his peak season so now there are 24, then he lays off 2 of the temps so now there are 22, etc etc.

As to (a), think about how you design tables. Suppose you have a Restaurant table and an Employee table. The relationship is 1:M. So each Employee record has a posted foreign key to the Restaurant record. Whether a Restaurant has 2 employees or 2000, the table structure is the same. If we regularly shuffle Employees between Restaurants -- I'm thinking of a chain here -- maybe there's a M:M relationship, so we need a new table, Restaurant_employee, with restaurant_id and employee_id, to make the connections. But again, whether an employee works for 2 restaurants or 10 doesn't change the structure.

If you need to enforce specific numbers, like if we have a rule that says that no restaurant can ever have more than 49 employees (perhaps because if it did, then we cease to qualify as a "small business" under government regulations and so have all sorts of additional costs, or whatever), we don't normally enforce that with database structure, but with rules in the program code.

When there is a specific number and it is small, database designers sometimes hard-code this into the database design by making several fields for pointers instead of a separate table. Like create columns in the Restaurant table for "employee 1", "employee 2", "employee 3", etc. This violates normalization rules and is almost always a bad idea for many reasons, but people do it.

Answer 2

In erd notations, generally, > 1 means many or M. Some newer notations and UML and XSD use * for many. It is unusual to use an actual number in the notation.