1 / 17

Integrity Checking

Integrity Checking. Edel Sherratt. From before. Entities all become relations (tables) Relationships some become relations (tables) some are implemented by use of primary and foreign keys Enterprise Rules

yamka
Download Presentation

Integrity Checking

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Integrity Checking Edel Sherratt

  2. From before Entities • all become relations (tables) Relationships • some become relations (tables) • some are implemented by use of primary and foreign keys Enterprise Rules • Some are enforced by primary and foreign keys (entity integrity and referential integrity), or by uniqueness constraints • Some need additional coding • using DBMS facilities • using application code if necessary • we know which by their cardinality signatures

  3. Simple Case 1 - example ‘Many’ end contains foreign key reference to ‘one’ end Integrity rules: Every tour is provided by exactly one operator; an operator may provide no tours, or may provide several.

  4. Simple Case 1 - SQL • CREATE TABLE Operator ( • code VARCHAR(5) PRIMARY KEY, • name VARCHAR(50), • tel VARCHAR(20), • UNIQUE(code,name,tel) • ); • CREATE TABLE Tour ( • ID INTEGER PRIMARY KEY, • Start VARCHAR(20), • Visiting Varchar(20), • Operator VARCHAR(5) NOT NULL • REFERENCES Operator(code)), • UNIQUE(ID,Start,Visiting,Operator) • );

  5. Simple Case 2 - example Many-to-many relationship → relation with composite key Integrity rules: A student may take no modules, or may take one or more modules. A module may have no registered students, or may have several.

  6. Simple Case 2 - sql • CREATE TABLE module ( • module_idVARCHAR(6) PRIMARY KEY, • title TEXT); • CREATE TABLE student ( • student_id VARCHAR(10) PRIMARY KEY, • name TEXT, • scheme TEXT); • CREATE TABLE takes ( • student_idVARCHAR(10) REFERENCES student(student_id), • module_code VARCHAR(6) REFERENCES module(module_id), • PRIMARY KEY (student, module));

  7. Simple Case 3 - example • CREATE TABLE sponsor ( • Company_codeVARCHAR(6) PRIMARY KEY, • Company_name TEXT, • UNIQUE(Company_code,Company_name)); • CREATE TABLE student ( • student_id VARCHAR(10) PRIMARY KEY, • name TEXT, • scheme TEXT); • CREATE TABLE sponsoring ( • Student_id VARCHAR(10) REFERENCES student(student_id), • Company_codeVARCHAR(6) REFERENCES module(module_id), • PRIMARY KEY (student_id));

  8. Simple Case 3 - SQL New relation with same key as ‘many’ end Integrity rules: A student may have at most one sponsor. A sponsor may have no students, or may have several.

  9. Lecturer Module 1..* 1..1 1..1 1..* Problem Case - example With primary and foreign keys we cannot do better than the optional case. Plant the key of the ‘one’ side in the ‘many’ side

  10. Problem case (continued) Lecturer{name} Module{name, lecturer* references Lecturer(name)} Mandatory participation (each lecturer teaches at least one module) cannot be enforced using entity and referential integrity constraints

  11. Problem case (continued) • How can we ensure that every instance of Lecturer is involved in at least one relationship with a Module? • Cannot enforce it using primary and foreign keys • Can use functions and triggers • http://www.aber.ac.uk/~dcswww/Dept/Teaching/CourseNotes/current/CS27020/ • Lectures/integrity-checking/createMandatory.sql • Lectures/integrity-checking/createMandatoryFull.sql

  12. Mandatory participationSQL implementation CREATE TABLE lecturer ( name text PRIMARY KEY); CREATE TABLE module ( name text PRIMARY KEY, lecturer text REFERENCES lecturer (name) INITIALLY DEFERRED);

  13. A PL/pgSQL function CREATE FUNCTION check_teaching_duties () RETURNS trigger as $has_module$BEGIN IF NOT EXISTS( SELECT * FROM module WHERE module.lecturer LIKE NEW.name) THEN RAISE EXCEPTION 'Lecturer % must refer to module', NEW.name; END IF; RETURN NEW;END;

  14. A PL/pgSQL trigger $has_module$ LANGUAGE plpgsql;CREATE TRIGGER has_module AFTER UPDATE OR INSERT ON lecturer FOR EACH ROW EXECUTE PROCEDUREcheck_teaching_duties();

  15. A transaction to load data BEGIN;INSERT INTO module (name, lecturer) VALUES ('Databases', 'Hardy'), ('Law', 'Bott'), ('Maths', 'Holstein'), ('Robots', 'Hardy');INSERT INTO lecturer (name) VALUES ('Bott'), ('Hardy'), ('Holstein');COMMIT;

  16. PL/pgSQL functions Disabling and re-enabling triggers usually happens in the context of functions that carry out transactions Like adding a new lecturer and allocating a module as one transaction (example online) What about deleting a module? When should that not be allowed? Need to exercise judgement!

  17. Integrity Checking - summary

More Related