Topics Related to Attribute Values

1. Topics Related to Attribute Values Objectives of the Lecture : • To consider sorting relations by attribute values. • To consider Triggers and their use for maintaining a DB. • To consider non-scalar attribute values.

2. The Need to Sort Relations • A relation is a set. • The elements of a set have no order. The tuples of a relation have no order. PROBLEM !!Sometimes we need sorted data. Example : a telephone directory is just a set of entries, but it would be useles if not sorted into alphabetic order. PROBLEM !!Sometimes we need the same data sorted into different orders. Example : at different times, we require employee details sorted by name, by company ID, by salary, and by department.

3. Solution Sort a relation when it is retrieved. This also leaves the DBMS free to physically store the relation’s data in any desirable way, and most efficiently serve a variety of different uses and users. Note : • Typically retrieved relations are not whole DB relations but the result of a query expression. the data may well need to be physically sorted anyway in order to return tuples in the desired order. • Once a sorted copy of the relation is outside the DB, we can treat it how we like.

4. Sorting • Sort relation on the values of 1 or more attribute(s). • Attribute(s) sorted on must have an orderable type for sorting to be possible.Example : numbers and text can be sorted, photos cannot. • Sorting can be ascending or descending. • If several attributes are used to sort a relation : • sort tuples on the 1st attribute’s values, • sort tuples with the same 1st attribute value on the 2nd attribute’s values, • sort tuples with the same 1st and 2nd attribute values on the 3rd attribute’s values, • etc. • Also expressed as “sort on 3rd attribute within 2nd attribute within 1st attribute”. • Thus a major-to-minor attribute order must be specified.

5. Sorting in SQL Add an ORDER BY phrase to the end of aSELECT statement. The ORDER BY phrase must always be the last phrase in the SELECT statement. Examples : • SELECT * FROMEMPORDER BY EName ; Orders the rows in alphabetical order of employees’ names. • SELECT * FROMEMPORDER BY Sal DESC, EName ASC ; Orders rows into ascending/alphabetical order of employees’ names within decreasing salaries. ASC is the default if no order is given..

6. Triggers Definition :Piece of code consisting of procedural & declarative statements that is activated by the DBMS if a specific operation is executed on the DB under specific conditions. In SQL DBMSs, triggers are stored in the ‘SQL catalog’. • Their purpose is to provide a facility that will carry out an appropriate action if an error or some specified event occurs. • The motivation and concepts are similar to the handling of interrupts in Operating Systems and exceptions in programming languages. • A trigger has a name & consists of 3 parts : • an event, • a condition (optional), • an action.

7. Trigger Actions Processing “Special Event” detected.Processing halts. SQL : Trigger Another process runsto handle the“Special Event”. SQL : Triggered Procedure Normal processing resumes.

8. Using Triggers • Handling errors. • Managing changes to the DBfor example : • Cascaded Deletes • Cascaded Updates • Setting NULLs • Setting default values • Prohibiting Changes • Summation Updates to ensure that changes can be made without violating any rules execute useful supplementary activities

9. Action SQL Example CREATE TRIGGER CAR_OWNER_DELETE BEFORE DELETE ON EMP FOR EACH ROW BEGIN UPDATE CAR SET OWNER = NULL WHERE OWNER = :OLD.EMP_NO; END; Name of Trigger Event for each row in the deletion refers to old versionof triggering table EMP No condition in this example

10. SQL Syntax - Main Points CREATE TRIGGER Trigger_Name ON Table_Name FOR EACH WHEN Condition BEGIN Trigger-Action END; BEFORE AFTERINSTEAD OF INSERT DELETEUPDATE OF colname(s) ROW STATEMENT

11. Example : Summation Updates (1) Summary oftransactions Record oftransactions INSERT INTO TRANS VALUES ('004', 'A002', 30);

12. Example : Summation Updates (2) Summary oftransactions Record oftransactions Updated value Inserted row

13. SQL Trigger for Summation Update CREATE TRIGGER ADD_TO_DAILY_TOTAL AFTER INSERT ON TRANSFOR EACH ROW BEGIN UPDATE DAILY_TRANS SET TOTAL = TOTAL + :NEW.AMOUNT; END; refers to new version oftriggering table TRANS

14. RegNo Type Owner NA03 RTY Corsa 1.3 E3 DH56 LGR Primera GLi E5 JON 1 Jaguar XK E8 NK07 YXK Volvo S80 E6 Handling Referential Integrity (1) CAR EMP Supposing employee ‘E8’ is deleted from EMP. car ‘JON 1’ breaks referential integrity. Solutions : 1 Delete car. 2. Employee deletion disallowed. 3. Set car owner to defaultvalue.4. Set car owner to NULL.

15. Handling Referential Integrity (2) • Could use triggers to carry out desired compensatory action. • SQL allows phrases to be added to the Referential Integrity constraint to carry out the chosen solution if the constraint is broken. Example : Create Table CAR (RegNo Char(9) Primary Key,Type Varchar2(24),Owner Char(2) References EMP( EmpNo ) ) ; On Delete CASCADEOn Delete RESTRICTOn Delete SET DEFAULT On Delete SET NULL Add oneof these.

16. Non-Scalar Attribute Values Attribute values are no longer constrained to be scalar values of traditional type. Attributes can also have the following kinds of type : • ‘Enhanced’ basic types - e.g. multimedia, XML. • Object classes. Typically an object is defined to comprise several values of traditional type, i.e. numbers, text, date. SQL row objects may be considered a special case of this. • Collections. Currently Standard SQL4 (of 2003) only permits arrays and bags. All values in an array/bag must have the same type. • Nested relations. An attribute value consists of a relation. In principle there are 2 possibilities : the contents of the nested relation are / are not visible in the outer/holding relation.

17. Owner Picture Children E3 E5 Ros E6 Alf Jane Ed Suzi Alf Conceptual Example Values of type ‘Picture’ Values each of which is an array of values of type ‘Text’

18. SQL Examples CREATE TYPE Money AS DECIMAL(7,2) ; CREATE TYPE Address AS (HouseNo INTEGER,Street VARCHAR2(30),Town VARCHAR2(30),Postcode CHAR(8) ) ; CREATE TABLE Employee (Emp_ID CHAR(4),Home Address,Salary Money ); Create scalar type Create row type Use new types to create a table.