Database Design – Clarification on Cardinality and Participation

constraintdatabase-designerdforeign keyprimary-key

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.

Best Answer

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!

Related Solutions

ER Diagram translation to tables

Enforcing option (c), using a type attribute:

CREATE TABLE Computer
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType IN ('D', 'L'))
) ;

CREATE TABLE Laptop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, battery WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'L')
, FOREIGN KEY (computerType, computerID)
    REFERENCES Computer (computerType, computerID)
) ;

CREATE TABLE Desktop
( computerID INT NOT NULL
, computerType CHAR(1) NOT NULL
, monitor WHATEVER
, PRIMARY KEY (computerType, computerID)
, CHECK (computerType = 'D')
, FOREIGN KEY (computerType, computerID)
    REFERENCES Computer (computerType, computerID)
) ;

and using deferred foreign key constraints on nullable columns:

CREATE TABLE Computer
( computerID INT NOT NULL
, laptopID INT NULL                                    -- these 2 columns 
, desktopID INT NULL                                   -- are NULLable
, PRIMARY KEY (computerID)
, UNIQUE (laptopID)
, UNIQUE (desktopID)
, CHECK ( laptopID IS NOT NULL AND desktopID IS NULL   -- but only one of them 
       OR desktopID IS NOT NULL AND laptopID IS NULL   -- is NULL
        )
, CHECK (laptopID = computerID)                        -- and the non-NULL one is
, CHECK (desktopID = computerID)                       -- equal to computerID
) ;

CREATE TABLE Laptop
( laptopID INT NOT NULL
, battery VARCHAR(20) NOT NULL
, PRIMARY KEY (laptopID)
, FOREIGN KEY (laptopID)
    REFERENCES Computer (computerID)
) ;

CREATE TABLE Desktop
( desktopID INT NOT NULL
, monitor VARCHAR(20) NOT NULL
, PRIMARY KEY (desktopID)
, FOREIGN KEY (desktopID)
    REFERENCES Computer (computerID)
) ;

ALTER TABLE Computer
  ADD CONSTRAINT Laptop_Computer_FK
  FOREIGN KEY (laptopID)
    REFERENCES Laptop (laptopID)
    DEFERRABLE INITIALLY DEFERRED
, ADD CONSTRAINT Desktop_Computer_FK 
  FOREIGN KEY (desktopID)
    REFERENCES Desktop (desktopID)
    DEFERRABLE INITIALLY DEFERRED ;

Tested in Postgres, at SQL-Fiddle

Sql-server – PRIMARY KEY CONSTRAINT fundamentals

The distinction is mainly historical. The relational model was developed in part in response to the way IMS handled data.

The presently released version of IMS provides the user with a choice for each file: a choice between no indexing at all (the hierarchic sequential organization) or indexing on the primary key only . . .

Source: A Relational Model of Data for Large Shared Data Banks

Codd retained that term. (Same source.)

Normally, one domain (or combination of domains) of a given relation has values which uniquely identify each element (n-tuple) of that relation. Such a domain (or combination) is called a primary key.

It's not unusual for a relation to have more than one "combination of domains" that uniquely identify each tuple. The relational model offers no theoretical basis for elevating the status of one of them to "primary". As far as the relational model is concerned, all candidate keys are created equal.

As far as standard SQL is concerned, any column or combination of columns that's declared UNIQUE can be the target of a foreign key constraint. In the past, dbms platforms differed slightly in how they handled NULL in a column declared UNIQUE. That's part of the reason old-school database designers lean toward NOT NULL UNIQUE declarations; the other part is that NOT NULL UNIQUE is functionally equivalent to PRIMARY KEY.

There is a semantic difference between a UNIQUE index and a UNIQUE constraint. I prefer to see constraints expressed as constraints.

Best Answer

Related Solutions

ER Diagram translation to tables

Sql-server – PRIMARY KEY CONSTRAINT fundamentals

Related Question