Sam Alkhalili. Lesson 4. 2. Objectives. Examine the meaning and implementation of referential integrityExplore how to design of fields can promote data accuracyExplore how to promote data accuracy within and between tablesReview and finalize the database design. Sam Alkhalili. Lesson 4. 3. Referential Integrity.
1. Relational Database Design Lesson 4
2. Sam Alkhalili Lesson 4 2 Objectives Examine the meaning and implementation of referential integrity
Explore how to design of fields can promote data accuracy
Explore how to promote data accuracy within and between tables
Review and finalize the database design
3. Sam Alkhalili Lesson 4 3 Referential Integrity Referential Integrity - part of the definition of a true relational database product is that it supports referential integrity. Referential Integrity principles may be stated by:
"Every non-null foreign key value must match an existing primary key value"
If a value exists in the foreign key field of a table, then there must be a matching value in the primary key field of the table to which it is related. Referential Integrity is all about preserving the validity of the foreign key values.
Depending on the database product, the database designer will probably need to invoke referential integrity between tables. Usually this will be as part of the table relationship definition, or in a way you are joining tables.
If we take a look at the following example of two tables that are related in a One-To-Many relationship:
4. Sam Alkhalili Lesson 4 4 Referential Integrity
5. Sam Alkhalili Lesson 4 5 Referential Integrity In the above, we see that the DepartmentCode field is the foreign key field in the Employees table. With Referential Integrity, we are stating that if a value is entered into the DepartmentCode field in this table, it must match the primary key field (DepartmentCode) in the Departments table.
Referential Integrity is important as it ensures that the database contains valid and usable data and records by preserving the connections between related tables. Without it, the relationships could quickly become meaningless, and results returned via queries on the data would return unreliable results.
Referential Integrity will not allow you to change values in the primary or parent table that would create orphan records in the related or child table.
Note: Orphan record - a record in a related, or child table, with no matching record in the primary or parent table.
6. Sam Alkhalili Lesson 4 6 Referential Integrity As with anything in the real world, things can alter and you will need to ensure that the database can cope with this. Code names such as DepartmentCode will get revised, and departments can close or merge, therefore we need to be able to maintain the data when changes required will violate referential integrity rules.
RDBMS products generally handle these changes through cascading updates and deletes (different products may handle this differently, and have different names and techniques for this). In some database products you may need to create rules or triggers or use an operator.
7. Sam Alkhalili Lesson 4 7 Referential Integrity How does cascading updates work?
In the example tables above, if we change a DepartmentCode in the Departments table (for example we change the IS code to IT), all occurrences of the IS code in the Employees table will automatically change to IT. The change in the Departments table "cascades" to any related tables. This keeps that data valid and in compliance with referential integrity.
8. Sam Alkhalili Lesson 4 8 Business Rules and Levels of Enforcement Referential Integrity is enforced at the database level
It controls the integrity of the data between tables.
Business rules should be implemented to ensure that the data entered meets the requirements of a particular setting for the database.
Do things at both field and table levels to help ensure data integrity.
Business rules should be documented as they are implemented.
This should detail each rule, where and how it is implemented and enforced within the database design.
It is important that each rule is tested.
9. Sam Alkhalili Lesson 4 9 Field Level Integrity Using Field Properties
Each of the fields that are contained in the database has properties associated with it. These properties may be referred to as elements or attributes of the field. These enable you, as the database designer, to place constraints on the values that may be entered into that field.
10. Sam Alkhalili Lesson 4 10 Field Level Integrity Data Types
The most obvious constraint that can be placed on the fields in your database will be done with the selection of a data type for the field. Data types may vary by RDBMS, however in general they will be pretty much the same; usually, you will also be able to create custom data types through code.
A number or numeric data type
A currency data type can eliminate rounding errors
A text field can contain basically anything
Memo data types
A Boolean (Yes/No data type in Microsoft Access)
11. Sam Alkhalili Lesson 4 11 Entry and Editing Constraints
Database fields can be set as required, ensuring that the user is unable to save the record until a value is entered.
Other Data Validation Techniques
A default value property enables you to designate a value that will be entered into a field
Table Level Integrity
Field Comparisons - Database tables also have properties that you can use to set a validation rule on records in the table.
A validation table is created to promote data integrity.
12. Sam Alkhalili Lesson 4 12 Validation Tables - Example
13. Sam Alkhalili Lesson 4 13 Finalizing the Database Ensure validations
Implement any business rules
Document your design – What and why