Clarification on Cardinality and Participation I'm currently redesigning a schema with 20 or so tables and my first task is to create an ERD from the existing tables/constraints/relationships. This has proven more difficult than I expected because while I'm good at creating ERDs from business rules, extracting that same information from a database that was poorly designed has been challenging.

My specific questions revolve around translating the constraints and relationships from the DDLs into cardinality and participation on the ERD. An example:

The following DDLs for two sample tables (I included only relevant attributes, ie FKs and PKs):

CREATE TABLE SCHEMA.SCHEDULE (
    SCHEDULE_ID NUMBER(22,0),
    CONSTRAINT SCHEDULE_PK PRIMARY KEY (SCHEDULE_ID)
) ;

CREATE TABLE SCHEMA.OBJECTS (
    OBJECT_ID NUMBER,
    SCHEDULE_ID NUMBER,
    CONSTRAINT OBJECT_PK PRIMARY KEY (OBJECT_ID),
    CONSTRAINT OBJECTS_SCHEDULE_FK FOREIGN KEY (SCHEDULE_ID) REFERENCES SCHEMA.SCHEDULE(SCHEDULE_ID)
) ;


From the above DDL, I understand the following:
- SCHEMA.SCHEDULE is the parent, and SCHEMA.OBJECTS is the child

I then was able to make the following ERD:



What I can't figure out, is the cardinality and participation. I am guess that for SCHEDULE it is (1,M) and OBJECTS it is (0,m). Is this correct?

Any resources or excellent clear explanations with lots of examples is also appreciated. I am creating the ERD with min/max notation.

Thank you.
After further research, and for anyone else in the future, I thought I would post what I have learned looking into this topic. Credit mostly goes to "Data Modeling and Database Design" by Narayan S. Umanath and Richard W. Scamell (2nd Edition).

I came up with following participation and cardinality:


In reference to the relationship above, I determined the participation and cardinality for the entity "Objects" in the "has" relationships to be optional (0) with a cardinality of many (m). The reason is because the foreign key "schedule_ID" in the "Objects" table is an optional attribute, meaning "Objects" does not have mandatory participation in the relationship. Additionally, the business logic dictates that each tuple in the "Objects" table can only correspond to one tuple in the "schedule" table.

For the (0,m) that defines the participation and cardinality of the "schedule" entity in the relationship "has", parent entities in relationships are rarely total participation, meaning in majority of cases (unless specific business logic requires it) they have partial partition (denoted by the "0"). For the cardinality, each tuple in the "schedule" table can be related to many objects.

Additionally, I compiled the following notes while looking into this that might be helpful in these types of cases (creating ERD for existing databases):
- by default, relationships between parents and children are (0,m)(0,1).
- can not enforce mandatory participation on parent side without the use of triggers.
- in order to for participation on child entity, put a NOT NULL constraint on the foreign key fields.
- to specify alternative keys (candidate keys), you can place a UNIQUE constraint.

I invite others to offer comments and critique. Hope this helps someone!