|
Thursday Practical Tables In this Section…
|
tarix | 16.08.2018 | ölçüsü | 0,54 Mb. | | #63157 |
|
In this Section… Topics Covered - SQL Query and View
- Integrity
- Triggers
Examples Classes
Outer Join Operation In an equi-join, tuples without a ‘match’ are eliminated Outer join keeps all tuples in R1 or R2 or both in the result, padding with nulls - Left outer join R1 R2
- keeps every tuple in R1
- select * from R1, R2 where R1.a = R2.a (+)
- Right outer join R1 R2
- keeps every tuple in R2
- select * from R1, R2 where R1.a (+) = R2.a
- Double outer join R1 R2
- keeps every tuple in R1 and R2
- select * from R1, R2 where R1.a (+) = R2.a (+)
Outer Join Operator
Outer Join Operator
Ordering results select * from enrol, student where labmark is not null and student.studno = enrol.studno order by hons, courseno, name default is ascending
Completeness of Relational Algebra Five fundamental operations σ π X ∪ — Additional operators are defined as combination of two or more of the basic operations, e.g. R1 ∩ R2 = R1 ∪ R2 — ((R1 — R2) ∪ (R2—R1) R1 R1 = σ(R1 X R2)
Aggregation Functions Aggregation functions on collections of data values: average, minimum, maximum, sum, count Group tuples by value of an attribute and apply aggregate function independently to each group of tuples
Aggregation Functions in SQL select studno, count(*), avg(labmark) from enrol group by studno
Aggregation Functions in SQL select studno, count(*), avg(labmark) from enrol group by studno having count(*) >= 2
Nested Subqueries Complete select queries within a where clause of another outer query Creates an intermediate result No limit to the number of levels of nesting - List all students with the same tutor as bloggs
- select studno, name, tutor
- from student
- where tutor =(select tutor
- from student
- where name = ‘bloggs’)
Nested Subqueries select distinct name from student where studno in (select studno from enrol, teach, year where year.yeartutor = teach.lecturer and teach.courseno = enrol.courseno)
Union compatibility in nested subqueries select distinct studno from enrol where (courseno, exammark) in (select courseno, exammark from student s, enrol e where s.name = ‘bloggs’ and e.studno = s.studentno);
Nested subqueries set comparison operators Outer query qualifier includes a value v compared with a bag of values V generated from a subquery Comparison v with V evaluates TRUE - v in V if v is one of the elements V
- v = any V if v equal to some value in V
- v > any V if v > some value in V (same for <)
- v > all V if v greater than all the values in V (same for <)
Subqueries May be used in these situations: to define the set of rows to be inserted in the target table of an insert, create table or copy command to define one or more values to be assigned to existing rows in an update statement to provide values for comparison in where, having and start with clauses in select, update and delete commands
Correlated subqueries A condition in the where clause of a nested query references some attribute of a relation declared in the outer (parent) query The nested query is evaluated once for each tuple in the outer (parent) query - select name
- from student
- where 3 > (select count (*)
- from enrol
- where student.studno=enrol.studno)
Exists and correlated sub queries Exists is usually used to check whether the result of a correlated nested query is empty Exists (Q) returns TRUE if there is at least one tuple in the results query Q and FALSE otherwise - select name
- from student
- where exists (select *
- from enrol, teach
- where student.studno=enrol.studno and
- enrol.courseno = teach.courseno and
- teach.lecturer = ‘Capon’)
- Retrieve the names of students who have registered for at least one course taught by Capon
Exists and correlated sub queries Not Exists (Q) returns FALSE if there is at least one tuple in the results query Q and TRUE otherwise - select name
- from student
- where not exists (select *
- from enrol
- where student.studno=enrol.studno)
- Retrieve the names of students who have no enrolments on courses
Conclusions The only logical structure is that of a relation Constraints are formally defined on the concept of domain and key Operations deal with entire relations rather than single record at a time Operations are formally defined and can be combined in a declarative language to implement user queries on the database
Views
External View of Ansi-Sparc Architecture In most relational DBMS a view is a virtual relation which acts as a dynamic window on the base relations Materialised views are a research issue and raise consistency issues A view can be queried as if it were a base relation, but may not be updatable as if it were a base relation
Views are recomputed from the base relations each time they are used Views are useful for - logical data independence
- simplification
- security
- integrity management
Views: Security
Views: Query Simplification
Views: Query Simplification
Updatability of Views (Oracle 8) If the view query contains any of the following constructs it is not inherently modifiable, and you therefore cannot perform inserts, updates, or deletes on the view: - set operators
- group functions
- the DISTINCT operator
- joins (a subset of join views are updatable if they are key-preserving- all keys to base tables must be in view and the keys in the view must still be unique and not null)
If a view contains pseudocolumns or expressions, you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
Views: Logical Data Independence
Views: Data Integrity Check option enforces the constraint that any insert or update on the view satisfy the where clause
Conclusions on SQL Retrieval: many ways to achieve the same result—though different performance costs Comprehensive and powerful facilities Non-procedural Can’t have recursive queries Limitations are overcome by use of a high-level procedural language that permits embedded SQL statements
Examples Class
Integrity
The correctness and consistency of the data and its information Implicit - of the data model
- specified and represented in schema
Explicit - additional constraints of world
- can’t directly represent in data model
Inherent - assumed to hold by the definition of the data model
- don’t have to be specified
- e.g. attribute is atomic
Classification of constraints State constraints - Constraints on the database state
- State is consistent if it satisfies all the state constraints
Transition constraints - Constraint on the transition from one state to another, not an individual state
- e.g. labmark of a student can only be increased
- ∴ need to know the new value of labmark and the old value of labmark
- newlabmark >= oldlabmark
Explicit Integrity Constraints on EER Model 1.Student’s tutor must be employed by a department that the student is registered with 2. A student can only be enrolled for a course which is appropriate to the year that the student is in 3. Only staff who are employed by a department can teach a course offered by the department 4. Staff can only be appraised by a member of staff in the same department 5. Staff who don’t lecture must tutor 6. Average mark for a course > 30 7. Labmarks can only increase REGWITH can be represented by either a) STUDENT(studno, familyname, givenname, hons, tutor, slot, dept1, dept2) or b) REGWITH(studno, dept)
Classification of state integrity constraints Uniqueness: no two values of the same attribute can be equal Entity Integrity: no part of the key of a relation can be null Non-Null: all values of an attribute must be non-null Domains (value sets): all values of an attribute must lie within a defined domain, e.g. 0 < x < 100 Inter-domain matches: would not be sensible to match disparate domains Domain cardinality: the number of values for an attribute must lie in a defined range , e.g. number of natural parents living: 0, 1 or 2
Classification of state integrity constraints Relationship cardinality : the number of times an entity instance can participate in a relationship instance e.g. a student can take many courses and a course can be taken by many students; students can only enrol for up to 5 courses. Relationship participation: entity instances can optionally or mandatorally participate with a relationship instance e.g. A child must mandatorally be related through a mother relationship to a person but a person can be optionally related to a child
Classification of state integrity constraints Inclusion: all values of one attribute are also values of another e.g. set of appraisers ⊂ set of staff set of undergraduates ⊂ set of students Covering: all values of one attribute are also values of one of a set of attributes e.g. cars ∪ boats ∪ planes = vehicles undergraduates ∪ postgraduates = students Disjointedness: the value of an attribute cannot be at the same time for a particular entity more than one value e.g. male and female Referential: a value under one attribute is guaranteed to exist if there is a corresponding value under another attribute; e.g. every student’s tutor attribute must match a staff entity
General More general constraints consisting of a predicate over values under an attribute or across attributes. Sometimes known as business rules Inter-attribute constraints - date of birth < date of entry
- quantity ordered = quantity delivered
Domain set functions - average mark of students > 30
Derived attributes number of students enrolled on a course = studno ƒ COUNT courseno (ENROL) total mark for a course = exammark + labmark
Specifying Constraints in the Relational Model Inherent - already in model
- e.g. atomic domain values
Implicit - in the Data Definition Language
- e.g. referential integrity
Explicit - Declaratively assertions or triggers
- Procedurally transactions
- e.g. year tutors supervise two fewer students than other staff
Domain integrity in SQL2 Create domain name_type as char(20); create table student (studentno number(8) primary key, givenname name_type, surname name_type, hons char(30) check (hons in ('cis','cs','ca','pc','cm','mcs')), tutorid number(4), yearno number(1) not null, etc.....
Extensions to Referential Integrity in SQL2 create table YEAR (yearno number(8), yeartutorid number(4) constraint fk_tut references STAFF(staffid) on delete set null on update cascade), constraint year_pk1 primary key (yearno)); create table STAFF (staffid number(4) primary key, givenname char(20), surname char(20), title char(4) check (title in ('mrs','mr','ms','prof','rdr','dr')), roomno char(6), appraiserid number(4) not null default ‘22’, constraint app_fk foreign key (appraiserid) references STAFF(staffid) disable on delete set default on update cascade);
Controlled redundancy in Transactions An atomic (all or nothing) program unit that performs database accesses or updates, taking a consistent (& correct) database state into another consistent (& correct) database state A collection of actions that make consistent transformations of system states while preserving system consistency An indivisible unit of processing
Controlled redundancy in Transactions STUDENT(studno, name, numofcourses) COURSE(courseno,subject,numofstudents) ENROL(studno,courseno) Students can only enrol for up to 5 Courses. Add student S to course C - 1. select course C
- 2. select student S
- 3. count number of courses S already enrolled for
- if < 5 then step 4 if = 5 then halt END
- 4. select enrol for student S
- 5. check whether S already enrolled on C
- if no then step 6 if yes then halt END
- 6. Insert enrol instance (S,C)
- 7. Increment numofcourses in student for S
- 8. Increment numofstudents in course for C
- END
Constraints Managed Procedurally Problems: - load on programmer
- changing constraints
- no centralised enforcement
- no central record
In Oracle, transactions written in host programming languages (e.g. C) or PL/SQL PL/SQL programs can be saved in the Data Dictionary as - Functions
- Procedures
- Packages
Triggers
Database Triggers Centralized actions can be defined using a non declarative approach (writing PL/SQL code) with database triggers. A database trigger is a stored procedure that is fired (implicitly executed) when an INSERT, UPDATE, or DELETE statement is issued against the associated table. Database triggers can be used to customize a database management system: - value-based auditing
- automated data generation
- the enforcement of complex security checks
- enforce integrity rules
- enforce complex business rules
Trigger Structure A trigger has three basic parts: Event - a triggering event or statement
- the SQL statement that causes a trigger to be fired
Condition - a trigger restriction or condition
- specifies a Boolean expression that must be TRUE for the trigger to fire. The trigger action is not executed if the trigger restriction evaluates to FALSE or UNKNOWN.
Action - a trigger action
- the procedure (PL/SQL block) that contains the SQL statements and PL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE.
Example : maintaining derived values CREATE OR REPLACE TRIGGER increment_courses AFTER INSERT ON enrol - FOR EACH ROW
- BEGIN
- update students
- set numofcourses = numofcourses + 1
- where students.studno = :new.studno
- END;
Example : Integrity Trigger in Oracle CREATE TRIGGER labmark_check BEFORE INSERT OR UPDATE OF labmark ON enrol - DECLARE
- bad_value exception;
- WHEN (old.labmark IS NOT NULL OR new.labmark IS NOT NULL)
- FOR EACH ROW
- BEGIN
- IF :new.labmark < :old.labmark
- THEN raise bad_value ;
- END IF;
- EXCEPTION
- WHEN bad_value THEN
- raise_application_error(-20221,‘New labmark lower than old labmark’ );END;
Example Reorder Trigger in Oracle
Row and Statement Triggers/ Before and After For a single table you can create 3 of each type, one for each of the commands DELETE, INSERT and UPDATE making 12 triggers. (There is also an INSTEAD_OF trigger) You can also create triggers that fire for more than one command
Multiple Triggers Multiple triggers of the same type for the same statement for any given table. - two BEFORE statement triggers for UPDATE statements on the ENROL table.
Multiple types of DML statements can fire a trigger, - can use conditional predicates to detect the type of triggering statement, hence
- can create a single trigger that executes different code based on the type of statement that fires the trigger.
CREATE TRIGGER at AFTER UPDATE OR DELETE OR INSERT ON student DECLARE typeofupdate CHAR(8); BEGIN IF updating THEN typeofupdate := 'update'; …..END IF; IF deleting THEN typeofupdate := 'delete'; ……END IF; IF inserting THEN typeofupdate := 'insert'; ……END IF; …..
Some Cautionary Notes about Triggers Triggers are useful for customizing a database. But the excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in a large application. E.g., when a trigger is fired, a SQL statement within its trigger action potentially can fire other triggers. When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.
The Execution Model for Triggers A single SQL statement can potentially fire up to four types of triggers: BEFORE row triggers, BEFORE statement triggers, AFTER row triggers, and AFTER statement triggers. A triggering statement or a statement within a trigger can cause one or more integrity constraints to be checked. Triggers can contain statements that cause other triggers to fire (cascading triggers). Oracle uses an execution model to maintain the proper firing sequence of multiple triggers and constraint checking
How Triggers Are Used Could restrict DML operations against a table to those issued during regular business hours. Could restrict DML operations to occur only at certain times during weekdays. Other uses: - automatically generate derived column values
- prevent invalid transactions
- enforce referential integrity across nodes in a distributed database
- provide transparent event logging
- provide sophisticated auditing
- maintain synchronous table replicates
- gather statistics on table access
Triggers vs. Declarative Integrity Constraints Triggers allow you to define and enforce integrity rules, but is not the same as an integrity constraint. A trigger defined to enforce an integrity rule does not check data already loaded into a table. You use database triggers only - when a required referential integrity rule cannot be enforced using the following integrity constraints: NOT NULL, UNIQUE key, PRIMARY KEY, FOREIGN KEY, CHECK, update CASCADE, update and delete SET NULL, update and delete SET DEFAULT
- to enforce referential integrity when child and parent tables are on different nodes of a distributed database
- to enforce complex business rules not definable using integrity constraints
Modifying Views Modifying views has inherent problems of ambiguity. - Deleting a row in a view could either mean
- deleting it from the base table or
- updating some column values so that it will no longer be selected by the view.
- Inserting a row in a view could either mean
- inserting a new row into the base table or
- updating an existing row so that it will be projected by the view.
- Updating a column in a view that involves joins might change the semantics of other columns that are not projected by the view.
Triggers and Views Triggers can be defined only on tables, not on views but triggers on the base table(s) of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against a view. INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through SQL DML statements (INSERT, UPDATE, and DELETE). Oracle fires the INSTEAD OF trigger instead of executing the triggering statement. The trigger performs update, insert, or delete operations directly on the underlying tables. Users write normal INSERT, DELETE, and UPDATE statements against the view and the INSTEAD OF trigger works invisibly in the background to make the right actions take place. By default, INSTEAD OF triggers are activated for each row. CREATE VIEW tutor_info AS SELECT s.name,s.studno,s.tutor,t.roomno FROM student s, staff t WHERE s.tutor = t.lecturer;
For Next Lecture I expect you to have SKIM Read the notes for the next lecture before it’s delivered. The sequence of: skim read; lecture delivery; SAQ will make revision a whole lot easier.
Background
The Execution Model for Triggers 1. Execute all BEFORE statement triggers that apply to the statement. 2. Loop for each row affected by the SQL statement. - a. Execute all BEFORE row triggers that apply to the statement.
- b. Lock and change row, and perform integrity constraint checking. (The lock is not released until the transaction is committed.)
- c. Execute all AFTER row triggers that apply to the statement.
3. Complete deferred integrity constraint checking. 4. Execute all AFTER statement triggers that apply to the statement.
Example of an INSTEAD OF Trigger CREATE TRIGGER tutor_info_insert INSTEAD OF INSERT ON tutor_info REFERENCING NEW AS n -- new tutor FOR EACH ROW BEGIN IF NOT EXISTS SELECT * FROM student WHERE student.studno = :n.studno THEN INSERT INTO student(studentno,name,tutor) VALUES(:n.studno, :n.name, :n.tutor); ELSE UPDATE student SET student.tutor = :n.tutor WHERE student.studno = :n.studno; END IF; IF NOT EXISTS SELECT * FROM staff WHERE staff.lecturer = :n.tutor THEN INSERT INTO staff VALUES(:n. staff.tutor, :n.roomno); ELSE UPDATE staff SET staff.roomno = :n.roomno WHERE staff.lecturer = :n.tutor; END IF; END;
Dostları ilə paylaş: |
|
|