10 - Referential Integrity

 1. Referential integrity refers to the accuracy and consistency of data within a relationship
Referential integrity is a subset of data integrity, which is concerned with
 the accuracy and consistency of all data (relationship or otherwise). 
Maintaining data integrity is a crucial part of working with databases



 2. Fill in the blanks for the following excerpt
In relationships, data is linked between two or more tables. 
This is achieved by having the foreign key (in the associated table)
reference a primary key value (in the primary – or parent – table). 
Because of this, we need to ensure that data on both sides of the
relationship remain intact.

So, referential integrity requires that, whenever a foreign key value 
is used it must ______________________________________________________

  reference a valid, new foreign key in all tables (independent of the primary key)

  reference a valid, existing primary key in the parent table.

  use multiple primary keys in all tables

  use a valid index in all tables

 3. In this example, if we delete record number 15 in a primary table, we need to be sure that ______________________________

  there is a specific foreign key in all related tables with the value of 15

  None of these answers are valid

  there's no primary key in any other table with any value of 15 or under

  there’s no foreign key in any related table with the value of 15

 4. In reference to the above example, we should only be able to delete a primary key if ______________. Otherwise, we would end up with an orphaned record.

  there are no additional or duplicate primary keys

  there are no associated records.

  there is at least one associated record

  there are more than one associated records

 5. Referential integrity ensures the relationships between tables in a database __________________to prevent users or applications from entering inaccurate data or pointing to data that doesn't exist

  remain accurate by applying constraints

  remain flat file by applying references

  remain accurate by applying multiple indexes

  remain duplicated by applying constraints

 6. In referential integrity, we would say that every foreign key value has a matching value in the corresponding ____________.


  secondary key


  primary key

 7. Referential integrity uses techniques to ensure that there are no orphan records i.e. it prevents you from ________________

  updating a record

  adding related records

  deleting related records

  finding duplicate records

 8. Referential integrity can alert you if you try to delete a record which is _____________________

  related to another one

  not related to another one

  not related to an index or primary key

  directly related or dependent on the primary or foreign key

 9. The simplest way to enforce referential integrity is ______________ to a primary key

  to allow changes

  not to allow changes

  to make duplicates that tie in

  to create indexes that are attached

 10. Fill in the blanks for the following excerpt
A foreign key is an identifier in a table that matches the primary key of a different table.

The foreign key creates the relationship with a different table. Referential integrity
refers to the ______________________________________.

  the primary key's dependence on the main table

  relationship between these tables

  primary key's relationship with any given index

  tables and their relationships with the primary key

 11. When one table has a foreign key to another table, the concept of referential integrity states that you ______________

  may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table

  may not add a foreign key to any table

  may not add any duplicate primary keys

  may add a record to the table that contains the foreign key in any situation

 12. Referential integrity includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are _________________

  not shown in any table

  found in the indexes

  reflected in the primary table

  linked via the foreign key

 13. Which of the following are advantages of referential integrity constraints?
Prevents the entry of duplicate data

Prevents one table from pointing to a nonexistent field in another table

Guarantees consistency between "partnered" tables

Prevents the deletion of a record that contains a value referred 
to by a foreign key in another table

Prevents the addition of a record to a table that contains a 
foreign key unless there is a primary key in the linked table

  None of them

  All of the items on the list are advantages

  Only the last two items on the list

  Only 1 and 2

 14. Read the following excerpt. Which one of the following 'rules' is incorrect?
Consider, for example, the situation where you have two tables: Employees and Managers.

The Employees table has a foreign key attribute entitled ManagedBy, which points to the 
record for each employee’s manager in the Managers table. Referential integrity enforces
 the following three rules:

1 - You can add a record to the Employees table even if the ManagedBy attribute does not point to 
a valid record in the Managers table. Referential integrity prevents the insertion of incorrect details 
into a table. Any operation that doesn't satisfy the referential integrity rule fails.

2 - If the primary key for a record in the Managers table changes, all corresponding records in the
 Employees table are modified using a cascading update.

3 - If a record in the Managers table is deleted, all corresponding records in the Employees table 
are deleted using a cascading delete.

  No. 1

  No. 2

  All of them are correct

  No. 3

 15. A lack of referential integrity in a database can lead to ______________. This could result in records being “lost” in the database, because they’re never returned in queries or reports

  complete data sets being returned, without any sorted order

  erroneous data being returned, which is duplicated due to the lack of order

   incomplete data being returned, usually with no indication of an error.

  indexed data being returned, without primary keys