Question

A Customer can have many Venues. A Venue can only be controlled by one Customer. Page 2 Introducing Access Office 2010 A Venue can have many Taps. particular Tap belongs to only one Venue.ie serial no.12456-1 is only found A in Venue X A Tap Type can have many Taps. A Tap can belong to only one particular Tap Type that one Tap can be installed in one Venue, but one Venue can have many Taps installed. Therefore we need to include Vid (the primary key of the Venue table) in the Taps table, creating a one-to-many relationship. This has already been done for you D Create the relationship Why should you enforce referential integrity? What does this mean? What does Cascade update Related field/ Delete related Records mean? RHXEEUH

Answer 1

Why should you enforce referential integrity?

A referential integrity asserts a relationship between two tables such that the values in a column of one table must match the values in a column of the second table. Traditionally, the two tables have a parent-child relationship:

• The parent table has a column, called the primary key, containing values against which other values are compared. The primary key is normally unique.

• The child table has a column, called the foreign key, whose values must match those of the primary key in the parent table.

A primary key does not have to be referenced by a foreign key (that is, there can be a parent without a child). However, every foreign key must match a primary key. There cannot be a child without a parent (that is, an orphan)--this constitutes a referential integrity violation.

For example, for the parent table, create a rule to fire on an update or delete of the primary key (an insert simply creates a parent without a child, which is not an integrity violation). The database procedure can check for foreign keys that reference the primary key and enforce the referential integrity.

For example, for the child table, create a rule to fire on an update or insert of the foreign key. The database procedure checks to make sure there is a parent.

The advantage of using a rule (as opposed to a constraint) to enforce referential integrity is that the actions performed by a rule can be more complex than merely checking for the existence of a primary key in a parent table. For example, a rule can fire a procedure to create an appropriate parent record if one does not already exist.

What does this mean?

Referential integrity (RI) is a relational database concept, which states that table relationships must always be consistent. In other words, any foreign key field must agree with the primary key that is referenced by the foreign key. Thus, any primary key field changes must be applied to all foreign keys, or not at all. The same restriction also applies to foreign keys in that any updates (but not necessarily deletions) must be propagated to the primary parent key.

What does Cascade update Related field/ Delete related Records mean?

The Cascade update related fields’ option which basically means that if a user changes. The contents of a related field for example a primary key field. Then that change is rippled through all related tables

Cascade Delete – this option means that if you delete a record from one table, corresponding records in the other table are also deleted. Restrict Delete – this option means that if you attempt to delete a record from one table but there is a corresponding record in the other table, the delete operation is not allowed.