In the Personnel System demonstration application database, there is an example of the Department, Section and Employee tables. For these tables, the rules required are as follows:
1. A Department Code cannot be deleted from the Department table if there are any records in the Section table which require that Department Code. For example, you should not be able to delete Department Code ADM unless there are no records in the Section table with code ADM, otherwise all the Sections for ADM have no parent record.
2. Department Code cannot be used in the Sections table unless the Department Code exists in the Department table. For example, you cannot create a new section in a department XYZ unless XYZ exists in the Department table.
3. A Section Code cannot be deleted from the Section table if there are any records in the Employee table which require that Section Code. For example, you should not be able to delete section 01 unless there are no records in the Employee table with Section Code 01, (otherwise the employee would not be able to refer to this record).
4. A Department and Section Code cannot be used in the Employee table unless the codes already exists in the Section table. For example, you cannot add an employee to department XYZ section 01 unless it already exists in the Section table.
To define referential integrity rules, you must understand the business application and the data model. For example, no referential integrity rules are needed between Department and Employee because Employee refers to the Section not the Department. Employee data is based strictly on the Section table which, in turn, is based on the Department table. For example, if you create a department ABC, an employee cannot work for that department ABC until a Section is created.
