more sql data management 2005. topics zintegrity constraints zouter join zunion

More SQL

Data Management 2005

Topics

Integrity ConstraintsOuter JoinUnion

Types of ‘Integrity’ Constraints

Static True for all states State invariant True at all times e.g. for all e in emp,

e.dept in (dept.deptno)

Transition Between two states of

the system S,S’ Conditions that must

apply before a change, and then apply after

pre- and post-conditions e.g. e’.sal > e.sal i.e.

any wage change must be upwards!

Types of Database Function

QueryExtracts existing

facts and derives new facts

Leaves Factbase unchanged

SELECT …

UpdateReflects some

change in the UoDChanges state of

factbaseMust check

integrity constraints

UPDATE .., INSERT.., DELETE..

DDL

CREATE TABLE define the table name define the fields of the table define constraints on the table

MODIFY TABLE add a field or constraint

DROP TABLE delete the table

DDL commands

CREATE TABLE EMP (Empno char(8) not null,…Constraint emp_pk primary key(empno));

ALTER TABLE EMP ADD SPOUSE VARCHAR(40) );

DROP TABLE EMP;

Integrity Constraints and Business Rules

Database integrity Rules which ensure that relationships are

well-definedBusiness Rules

Additional constraints on values and interrelationships

Data integrity

Primary key CONSTRAINT emp_pk PRIMARY KEY (empno)

Not Null Ename NOT NULL

Field Validation CONSTRAINT emp_sal CHECK (sal <6000)CONSTRAINT emp_job CHECK (job IN

(‘analyst,’salesman’, ‘manager’, ‘president’, ‘clerk’))

Foreign Key

Relationship implentation ‘You take the primary key of the one side and

add it to the many side” But there is no reference to which table the

key came from!Foreign key constraint

CONSTRAINT emp-deptfk FOREIGN KEY (deptno) REFERENCES dept

• NO ACTION – reject attempt to delete parent if children exist

• CASCADE – delete all the child records if the parent is deleted

• SET NULL – set foreign key field to null• SET DEFAULT – set foreign key field to its default

dept

deptnodnamelocation

emp

empnoename not nulljob not nullhiredatesalcomm

manager

The EMP DEPT database

Coding constraints Exercise

Code up all the constraints for the emp-dept database

Outer Join

Inner join returns only matching rowsWe can get non-matching rows too

A LEFT JOIN Bincludes all the non matching rows in A as well

A RIGHT JOIN Bincludes all the non matching rows in B as well

a FULL JOIN Bincludes non matching rows from both tables

M F23 fred 23 sue20 joe 30 alice34 bill 34 julie

Select * from M ,F 23 fred 23 sue 23 fred 30 alice 23 fred 34 julie 20 joe 23 sue 20 joe 30 alice 20 joe 34 julie 34 bill 23 sue 34 bill 30 alice 34 bill 34 julie


Select * from M inner join F on M.age = F.age 23 fred 23 sue 23 fred 30 alice 23 fred 34 julie 20 joe 23 sue 20 joe 30 alice 20 joe 34 julie 34 bill 23 sue 34 bill 30 alice 34 bill 34 julie


Select * from M left join on M.age = F.age 23 fred 23 sue 34 bill 34 julie


Select * from M right join on M.age = F.age 23 fred 23 sue 34 bill 34 julie


Select * from M full join on M.age = F.age 23 fred 23 sue 34 bill 34 julie


Select * from M, F on M.age > F.age 23 fred 23 sue 23 fred 30 alice 23 fred 34 julie 20 joe 23 sue 20 joe 30 alice 20 joe 34 julie 34 bill 23 sue 34 bill 30 alice 34 bill 34 julie

Inner Join (natural Join)Select * from M inner join F on M.age=F.age;

Left (Outer) JoinSelect * from M left join F on M.age=F.age;

Right (Outer) JoinSelect * from M right join F on M.age=F.age;

Full (outer) JoinSelect * from M full join F on M.age=F.age;

Show all departments without employees Select dname from dept left join emp on dept.deptno = emp.deptno

shows all the department names

where ename IS NULL;shows only the departments without

employees

Union

We can Union two or more tables in the one statement

Handy if the separate select statements use different tables

what does this query do?Select empno, ename, job from emp where

job='Manager'unionSelect m.empno, m.ename, m.job from emp e,

emp m where m.empno = e.mgr;

more sql data management 2005. topics zintegrity constraints zouter join zunion

Documents