Implementing Mandatory Relationships, and Referential Integrity

1
Implementing Mandatory Relationships,
andReferential Integrity

• Class 16
• DSC 544/444
• Fall 2007

2
Agenda

• Addendum to discussion on conceptual?logical
  mapping
• Specifically, implementing mandatory
  relationships and referential integrity

3
Implementing Mandatory Relationships

• We often face situations for which we know a
  relationship is mandatory
• In a conceptual model (an E-R diagram), we change
  the drawing notation to indicate that fact
• What does it mean in terms of the actual database
  design?

4
Implementing Mandatory Relationships

• Mandatory relationship conceptual model
  notation
• Player table

Name
PenaltyNum
gets
1
M
Penalty
Player
PlayerID
Type
Date
Time
How do we implement the relationship, and enforce
our requirement that the relationship be
mandatory?
5
Implementing Mandatory Relationships

• Implementing a mandatory relationship
• Penalty table

PenaltyNum
Name
gets
1
M
Penalty
Player
PlayerID
Type
Date
Time
Foreign key is the relationship,so how do we
make it mandatory?
6
Implementing Mandatory Relationships

• To make the relationship mandatory, we specify
  that the foreign key column must be NOT NULL
• this is equivalent to saying that the DBMS should
  never let a cell in that column be blank
• incidentally, a tables primary key must also be
  NOT NULL (and unique)
• It is easy to tell the DBMS that a column should
  be non-null
• E.g., in standard SQL
• ALTER TABLE PenaltyMODIFY PlayerID NOT NULL

7
Referential Integrity

• Do not confuse the mandatory (NOT NULL)
  requirement with another important database
  concept referential integrity.
• Reference integrity means that when one entity
  (table) relates to another, all relationship
  instances (meaning, non-null foreign key values)
  must be valid references.
• foreign key values must be valid
• relates to the data in the table

8
Referential Integrity

• Is there a violation of referential integrity
  with this data?

Complete Player table
Complete Penalty table
9
Implementing Referential Integrity

• To make the DBMS insist on referential integrity
  for a relationship, we must instruct the DBMS
• If we dont tell the DBMS to restrict the data in
  this way, then data such as on the prior slide
  would be allowed by the DBMS
• It is easy to tell the DBMS to enforce
  referential integrity for a foreign-key column
• E.g., in standard SQL
• ALTER TABLE PenaltyADD FOREIGN KEY (PlayerID)
  References Player

10
Referential Integrity

• Microsoft Access Relationships tool

11
Warnings Re Microsoft Access Relationships tool

• The existence of relationships depends upon your
  table designs (specifically, what attributes you
  put in your tables).
• The only way to create or remove relationships in
  a database design is by adding or removing
  attributes.
• The Access relationships tool should be called
  (and thought of) as a tool to enforce referential
  integrity.

12
Summary

• Implementing a mandatory relationship
• Require that the corresponding foreign key column
  be NON NULL
• Referential integrity
• Each foreign-key data value must must refer to an
  existing piece of data in the related table
• The MS-Access relationships tool does not create
  relationships, but it does enable you to enforce
  referential integrity