Trying to understand cardinality in an entity rela

Question

I'm quite new to relational databases and have been having a lot of trouble recently trying to understand an entity relationship diagram I've been given.

Here it is: Solicitor ERD (ERD is for a made up solicitor company)

Basically my task is to take this ERD and write an SQL script to create the database, obviously filling in the tables with data I can make up. The SQL syntax isn't the thing I'm having a hard time with, it's simply understanding the cardinality in the diagram.

To me the terms '1 to 1', '1 to many', 'many to many' just don't click, I have no idea what they mean and how they affect where primary and foreign keys go.

I can take these tables and easily create relevant columns, for example I know the 'client' table will include something like 'client_name'. However, when it comes to, say, linking the 'client' table with the 'case' table, how do I know where foreign keys go?

Would the 'client' table contain 'caseID' from the 'case' table as well as the 'case' table containing 'clientID' from the 'client' table? or would only one table have a foreign key? It's things like this I just don't understand.

Sorry for the long post, if anyone could explain in plain english how I go about developing this ERD it would be most appreciated! I've been puzzled for two days now :(

Answer 1

ERDs are a fantastic tool and I'm sure once you get your head around them you will agree.

The relationships are always important to enforce. In your database the relationship between client and case is a 1 to many. This means that each case must have one and only one client but each client must have at least one case but they can have many. In this case each client should have a client_id which is the primary key and must be unique etc. this will be referenced in the case table as a foreign key so that the case table will have a client_id for each case. This will enforce the 1 to many relationship between the two tables.

As you can see most of the relationships in this diagram are 1 to many which is how a well designed database should be to enforce referential integrity. The only inconsistency with this is the relation ship between case and corporate case where the relationship is 1 to zero or 1. This means that a case may not have a corporate assigned to it and that if it does it must only have 1. In this case I would recommend using a PK in corporate case and link that to a FK in case.

If you need more information on how these relationships translate to English this page may help http://www.informit.com/articles/article.aspx?p=27281&seqNum=3

Good luck.

Answer 2

This style of diagram

There is an entity type/class/set & table for every box and a relationship type/class/set & table for every labeled line.

The ends of a line point to entity types participating in the relationship type. You need to know what the entities and relationships are in terms of the application domain. A row of a table represents an entity/relationship instance. There will be a foreign key from relationship type table to the entity type table for every endpoint. They will reference the candidate keys of the entity types.

The cardinality at the end of a line tells you for a given entity instance how many relationship instances/rows it can appear in. If a given instance/value doesn't have to appear in an instance/row then that's 0. (A person doesn't have to own a pet.) If it can only appear in one instance/row then that's 1. (A person has to possess a head.) If it can appear more than one time then that's MANY. (A person can own many pets. So person-owns-pet would be 0-or-MANY.) In general we put the number of possibilities on either end of a line and say "possibility-to-possibility" or "possibility:possibility" in the direction that we read the label.

There are "has" or "is" or "is associated with" lines with no explicit relationship type/table for the label while the real relationship is displayed as an entity type (a reified/associative entity type/class/set). The lines are really just foreign keys from the table to participant entity tables. (A person can be married to another person; a relationship instance of that is also an associative entity instance of a marriage.) Presumably it's how this style shows n-ary relationships. (Confusing.) (The actual relationship for such a label is represented by a projection of the associative entity type table.)

Other conventions

Some methods restrict possibilities to particular choices. Sometimes "1" means "0-or-1". Some methods distinguish 0-or-1 participation in a relationship via a relationship row being absent or present vs via an obligatory but nullable foreign key. Some methods allow relationships with more than two participants. (Good idea.) Then you just draw another line from the label to an entity. It's X:Y:Z:.... Some methods label the far end of a line from an entity with its cardinality. (Doesn't handle n-ary relationships unless you encode them as associative entitites so all "relationships" are binary.) Some methods have symbols for labels. Some methods have unlabelled lines which are just foreign keys.

There happens to be a good article in the wiki.

Foreign keys

We do not need foreign keys to know what the relationships mean or to update or query a database. There will be candidate/primary key column sets of entity tables appearing in relationship tables because those entities participate in that relationship. We query by combining relationships and conditions into other relationships while the DBMS builds a corresponding table expression and calculates its value.

A foreign key just says that a subrow value for a column list must be a subrow value for another column list that is unique in its table. So when that's so, say so by declaring a foreign key. (That helps the DBMS reject erroneous updates and optimize, and can help people understand the relationships or notice errors.) Which is generally when an entity table key column list appears in a relationship table because that entity participates in that relationship. (Some methods & tools unfortunately call foreign keys relationships, which they are not; they are just true statements. See how in your own diagram there are is/has/associated labels that aren't relationships but are drawn like them.)