Question

Define the "integrity rules" as it relates to database systems

Answer 1

Integrity rules are needed to inform the DBMS about certain constraints in the real world.

Specific integrity rules apply to one specific database.

Example: part weights must be greater than zero.

General integrity rules apply to all databases.

Two general rules will be discussed to deal with: primary keys and foreign keys.

PRIMARY KEYS

Primary key is a unique identifier for a relation.

There could be several candidate keys, as long as the they satisfy two properties:

1. uniqueness

2. minimality

From the set of candidate keys, one is chosen to be the primary key.

The others become alternate keys.

EXAMPLE: The relation R has several candidate keys.

ID SSN License_Number NAME

If we select ID to be the primary key, then the other candidate keys become alternate keys.

THE ENTITY INTEGRITY RULE

No component of the primary key of a base relation is allowed to accept nulls.

WHAT ARE NULLS?

Null may mean "property does not apply". For example, the supplier may be a country, in which case the attribute CITY has a null value because such property does not apply.

Null may mean "value is unknown". For example, if the supplier is a person, then a null value for CITY attribute means we do not know the location of this supplier.

Nulls cannot be in primary keys, but can be in alternate keys.

EXAMPLE: SSN may be null for one and only one person (why?)

FOREIGN KEYS

A foreign key is an attribute of one relation R2, whose values are required to match those of the primary key of some other relation R1 (R1 and R2 can be identical)

EXAMPLE: SP relation has attribute S#, and S relation has primary key S#. Then S# in SP is considered a foreign key.

SP is called the "referencing relation". S is called the "referenced relation".

We can draw a "referential diagram"

SP ---S#---> S

or simply

SP --------> S

WHY ARE FOREIGN KEYS IMPORTANT?

Foreign-to-primary-key matching are the "glue" which holds the database together.

Another way of saying it

Foreign keys provide the "links" between two relations.

A relation's foreign key can refer to the same relation.

EXAMPLE: EMP ( EMP#, SALARY, MGR_EMP#, ... )

EMP# is the primary key MGR_EMP# is the foreign key

EMP is a "self-referencing relation".

THE REFERENTIAL INTEGRITY RULE

The database must not contain any unmatched foreign key values.

REFERENTIAL INTEGRITY RULE

EXAMPLE: The three 3NF relations are:

SP(S#,P#,QTY)

SC(S#,CITY)

CS(CITY,STATUS)

The referential diagrams are:

SP ---S#---> SC ---CITY---> CS

DELETE INTEGRITY RULE:

We should not delete (S5,London) from SC if S5 is present in SP.

INSERT INTEGRITY RULE:

We should not insert (S3,P2,200) into SP unless S3 is present in SC.

If you don't like the answer please don't dislike, let me know in the comments.