Transcript Week 05 - Pravin Shetty > Resume

Lecture 5.
More on SQL
Joins
What’s in a DBMS
Data Dictionary
Data Types
CSE3180 Semester 1 2005 week 5 / 1
Lecture Content
In this lecture we will :
–
–
–
–
Be looking at ‘mirror’ tables,
Share some thoughts about types of joins
Examine the function of ‘division’
Have a much closer examination of the facilities of a
DBMS
–
–
–
–
Introduce Data Dictionary and Database Administration
Develop Integrity and Integrity Constraints in Schemas
Examine an example of ‘Triggers’ and ‘Procedures’
And have a quick look at the ‘Data Types’ which exist in
Oracle
CSE3180 Semester 1 2005 week 5 / 2
Mirror Tables
Base Tables (disk located) and Virtual Tables (memory)
Consider a table ‘employee’ which contains
staff detail
supervisor detail
manager details
Select a.name,b.name,c.name,d.name,e.name
from employee a, employee b, employee c, employee d,
employee e;
would produce a results table of all names in 5 columns of
output
CSE3180 Semester 1 2005 week 5 / 3
Joining a Table to Itself
Typical Query: For each employee, list the employee number, name
Manager and Manager’s name
Select X.EMPNO, X.NAME, X.MGR, Y.NAME
from EMP X, EMP Y
where X.MGR = Y.EMPNO
Result:
EMPNO
10
20
30
40
NAME
SMITH
JONES
BLACK
BROWN
MGR
40
40
40
50
NAME
BROWN
BROWN
BROWN
WHITE
The Primary Key and the Foreign are both in the same table
Two virtual tables are created for joining (‘alias’ feature)
CSE3180 Semester 1 2005 week 5 / 4
Outer Join
EMP
EmpNo
DEP
Name
Age
DeptNo
Mgr
DeptNo
Name
Loc
10
smith
25
15
40
11
MIS
Caulfield
20
jones
28
15
40
20
Finance
Malvern
30
black
20
40
15
Market
City
40
brown
46
11
50
17
Accounts
Clayton
50
white
42
11
Select d.depno, e.name, e.age
The + appends a null row to the EMP table
From dep d , emp e
for this query and it is used to join to the
where d.depno = e.depno (+)
DEP rows with no matching employees
deptno
name
age
deptno
name
11
brown
46
15
jones
11
white
42
20
black
15
smith
25
17
age
28
CSE3180 Semester 1 2005 week 5 / 5
DIVISION
• Divides a BINARY relation by a UNARY relation and
produces a UNARY relation as a result.
emp-skill
empno
E1
E2
E3
E2
E5
E6
skillcode
S1
S2
S3
S4
S5
S6
skill-reqd
result
empno
skillcode
E2
S2
S4
Divide emp-skill by skill-reqd
to give result
Special note: JOIN, INTERSECTION and DIVISION can be defined
in terms of the other 5 operators (which are known as
the ‘primitive’ operators).
CSE3180 Semester 1 2005 week 5 / 6
A DIVISION example
In the Air Transport Industry, pilots records contain details of
the aircraft they are qualified to fly. And there are also
records of the number and types of aircraft in the hangars
and which Company owns what.
In this case, the table of pilot’s names and the planes they can
fly is the dividend
The details of the planes in the hangars is the quotient
The query is to obtain the names of the pilots who can fly
every type of plane in the hangars
CSE3180 Semester 1 2005 week 5 / 7
Suggested Solution
• create table pilotskill (pilot vchar (150) not null,
plane vchar(15) not null);
• create table hangar (plane vchar(15));
• select pilot from pilotskill ps1, hangar h1
where ps1.plane = h1.plane
group by ps1.pilot
having count(ps1.plane = select count(*) from hangar);
[notice the absence of any ‘division’ operator - this is
effectively performed by the execution plan]
CSE3180 Semester 1 2005 week 5 / 8
Division Examples
A B
1 J
1 K
1 L
2 J
2 K
3 K
3 L
3 J
C
J
K
L
Result
1
3
CSE3180 Semester 1 2005 week 5 / 9
Division Examples
Name
Jones
Jensen
Jensen
Jensen
Smith
Smith
Rogers
Rogers
Degree
B Sc
B Sc
M Sc
PhD
B Sc
M Sc
B Sc
PhD
R1
Jensen
D1
M Sc
B Sc
PhD
D2
B Sc
M Sc
R2
Jensen
Smith
D3
B Sc
R3
Jones
Jensen
Smith
Rogers
CSE3180 Semester 1 2005 week 5 / 10
Data Base Design
4th Generation Environment - User Perception
user terminal
teleprocessing
monitor
report
writer
query
language
electronic
mail
application
programs
e-mail
files
data dictionary
DBMS
Database
structured and non-structured data
images, graphics, video,voice
CSE3180 Semester 1 2005 week 5 / 11
Command Levels
DataBase Administrators
Privileged set of commands.
Sometimes called 'superuser'
Data Administration
Database Developers
Application Developers
Users with Query rights only
Users with Table modification rights
CSE3180 Semester 1 2005 week 5 / 12
DBMS Outline
Multi Server
Dbms
Multi Server
Dbms
Multi Server
Dbms
Multi-Server Logging and Locking System
Data Base
CSE3180 Semester 1 2005 week 5 / 13
DBMS Block Diagram
General Communications
GCF
Sequencer / Dispatcher
SCF
Parser
PSF
Relation
Description
RDF
Optimiser
OPF
Query
Storage
QSF
Query
Execution
QEF
Abstract
Data Type
ADF
C
o
m
p
a
t
i
b
i
l
i
t
y
L
i
b
r
a
r
y
Data Manipulation
DMF
CSE3180 Semester 1 2005 week 5 / 14
DBMS Functions
CSE3180 Semester 1 2005 week 5 / 19
Inspecting a DBMS
CSE3180 Semester 1 2005 week 5 / 20
DBMS Functions
1. Data Storage, Retrieval and Update
2. A User-Accessible Catalogue (Dictionary)
3. Support for Shared Update
4. Backup and Recovery Services
5. Security
6. Integrity
7. Data Independence
8. Utility Services
CSE3180 Semester 1 2005 week 5 / 21
DBMS Functions
The Primary Objectives of a DBMS are to provide
facilities for :
1. Definition of Database Logical Structures
2. Definition of Physical Structures
3. Access to the Database
4. Definition of Storage Structures to store
user data
These components are known as the
‘database architecture’
CSE3180 Semester 1 2005 week 5 / 22
Data Dictionary
CSE3180 Semester 1 2005 week 5 / 23
Data Dictionary
A DATA DICTIONARY contains the fundamental definitions,
characteristics and uses of data
It describes:
What the data is
Characteristics
Uses of Data
User Permits / Restrictions
A DATA DIRECTORY contains information relating to Physical
Data Storage
CSE3180 Semester 1 2005 week 5 / 24
Data Dictionary
A Data Dictionary SYSTEM
stores
maintains
provides access
to the Data Dictionary.
It is a set of software
Also known as the Catalog Function
The Dictionary contains information on
Data
Processes
Environment
CSE3180 Semester 1 2005 week 5 / 25
Data Dictionary
A Data Dictionary is a DATABASE about the data held in
the USER DATABASE
Term Used : META DATA
The Dictionary tables are variously known as Catalog or
System Tables
CSE3180 Semester 1 2005 week 5 / 26
Data Dictionary
A Data Dictionary can provide data about
1. Relationships between dictionary entity types :
item
uses item ,module
table
uses item, group, module
module
uses item, group, file, module
program
uses file, module
system
uses program, system
2. Listing of all entities
Relationship reports (Which programs use record zzz)
Versioning support
Password support
User access and exits
CSE3180 Semester 1 2005 week 5 / 27
Data Dictionary
system
planning
Requirements
definition
analysis
Design
Implementation
Testing
Operations and
maintenance
D
A
T
A
D
I
C
T
I
O
N
A
R
Y
data
base
CSE3180 Semester 1 2005 week 5 / 28
Data Dictionary
Database
Administration
Human
Interfaces
------------
Data
Application
Programmers
End
Users
Dictionary
---------
Software and
DBMS Interfaces
Compilers
PreCompilers
Application
Programs/
Report Generators
Integrity
Constraints
CSE3180 Semester 1 2005 week 5 / 29
Data Dictionary
Some Benefits from Data Dictionary Use:
1. Better data management
- Redundancies, Standards,
Documentation
2. Reduction in system development time - Cross reference
listings, Auto copy libraries
3. Reduction in maintenance costs
4. Quicker and More Accurate changes possible
5. Documentation standards
6. Data Audit - cross references, 'where used' listings
CSE3180 Semester 1 2005 week 5 / 30
Database Administration - Oracle
• Some of the Dictionary Tables available to DBA, SYS,
SYSTEM and (some) USERS
• All_Tables: User accessible tables
• DBA_Tables : All tables in the database
• DBA_Tablespaces : All tablespaces
• User_tables : A users own tables
• User_Tablespaces : Accessible tablespaces
• User_catalog : tables,views,synonyms,sequences (primary
key generation)
• User_views
• User_constraints
• User_indexes
• User_triggers ................................. and so on.
CSE3180 Semester 1 2005 week 5 / 33
Integrity
CSE3180 Semester 1 2005 week 5 / 34
Integrity
Integrity is a collection of processes, procedures and
techniques which are used to ensure that data held in a
database is
COMPLETE
ACCURATE
CLEAR
thus ensuring that Information derived from the database
also has these characteristics
CSE3180 Semester 1 2005 week 5 / 35
Integrity
C
C.R.U.D.E.
Column Integrity - Linked to Domain Integrity
R
Referential Integrity
U
User Defined Integrity
D
Domain Integrity - A user defined datatype
E
Entity Integrity
CSE3180 Semester 1 2005 week 5 / 36
Database Integrity
Some terms you will encounter:
Entity Integrity
Referential Integrity
Functional Dependency (constraints between determinants
and attributes. For each value of the determinant there is only one value
for each of the attributes it determines)
Multivalued Dependency
Join Dependency
Domain Constraints
Cardinality Constraint
User Defined Constraints
CSE3180 Semester 1 2005 week 5 / 37
Data Integrity
General Principle: Data compliance with a set of rules
Rules Location: Best embodied in the DBMS
If they are contained in an application, there is the danger
of saturating a network and causing degraded performance.
This is particularly so in client / server computing
CONSTRAINTS: Declarative approach where integrity
constraints are ‘declared’ as part of a table specification.
ANSI SQL-89 and SQL-92, 93 and 99 standards include
specifications for integrity constraints syntax and behaviour
CSE3180 Semester 1 2005 week 5 / 38
Integrity Constraints
DATABASE INTEGRITY
Refers to correctness and consistency of data
Quality Assurance
Usually expressed in terms of CONSTRAINTS
- consistency rules which must not be subverted
CSE3180 Semester 1 2005 week 5 / 39
Forms of Constraints
1. Entity Integrity - Primary Key Value
NO attribute of a primary key value may be NULL
2. Referential Integrity - Foreign Key Values
If a FOREIGN KEY exists in a relation, then either
(1) the foreign key value MUST match the Primary Key
value of some row in its home (or Primary) relation OR
(2) the FOREIGN KEY must be NULL
3. Functional Dependency - Determinant
For each value of the DETERMINANT, there must be
only ONE value for each of the attributes which it
determines
CSE3180 Semester 1 2005 week 5 / 40
Forms of Constraints
4. Multivalued Dependencies
If A,B and C are three sets of attributes, then A
multidetermines B if and only if the set of B values
associated with each A value is independent of the C
values
5. Join Dependency - Relation Reconstruction
A relation can be reconstructed by taking the join of its
projections
6. Domain Constraint - Value restrictions
Possible values of a data item are restricted to a
specific set called the DOMAIN
CSE3180 Semester 1 2005 week 5 / 41
Forms of Constraints
7. Cardinality Constraint
The number of entities which can be related is subject
to a constraint
8. Set Retention Constraint
The deletion of records is subject to limitations
9. Existence Dependency
Hierarchical model (also OODB). Dependency of a child
on the parents limits insertion and deletion of segments
CSE3180 Semester 1 2005 week 5 / 42
Forms of Constraints
10. General Constraints
Those restrictions which can be expressed as arbitrary
predicates about the data.
e.g. no class may be scheduled for Room A.112 after
2.00pm on Fridays
General Comments: DBMS’ have deficiencies in their ability
to express and enforce constraints.
Ingres uses the “Rules and Procedures’ facility, Oracle uses
‘Triggers and Constraints’ and later versions of SQL use a
mechanism called ASSERTIONS.
CSE3180 Semester 1 2005 week 5 / 43
Referential Integrity
Foreign Key Concept - An attribute (or set of attributes)in one
table (the referencing table) occurs as the Primary Key of
another table (the Primary, Lookup or Referenced table)
Referential Integrity Constraint:
The Value of a Foreign Key Must Be a Key Value
in the Referenced Table
OR
The Value of the Foreign Key Must Be Undefined (Null)
This cannot occur if the Foreign Key is part of the Primary Key
of the Referencing Table
CSE3180 Semester 1 2005 week 5 / 44
Possible Referential Integrity Processes
1. LIMITED INSERT : If an incoming Foreign Key DOES NOT
EXIST as a referenced table Primary Key:
ABORT TRANSACTION - REPORT
2. LIMITED UPDATE : If an incoming Foreign Key DOES NOT
EXIST as a referenced table Primary Key
TERMINATE PROCESS
3. RESTRICTED DELETE : If there are referencing FOREIGN
KEYS in a referencing table
TERMINATE DELETE PROCESS ON REFERENCED
TABLE
CSE3180 Semester 1 2005 week 5 / 45
Possible Referential Integrity Processes
4. RESTRICTED UPDATE : If there are referencing Foreign
Keys in a referencing table
INHIBIT UPDATE OPERATION ON THE REFERENCED KEY
5. CASCADE DELETE : If there are Referenced Keys
INITIATE DELETION OPERATION ON REFERENCED
TABLE BY DELETING ALL REFERENCING ROWS
6. CASCADE UPDATE : Commence an UPDATE on the
REFERENCED TABLE by UPDATING the Foreign Keys
on all Referencing Rows in the Referencing Table(s)
CSE3180 Semester 1 2005 week 5 / 46
Possible Referential Integrity Processes
7. NULLIFY DELETE : Commence a DELETE operation on
the
REFERENCED table by setting ALL the FOREIGN
KEYS on the Referencing Table(s) to NULL (watch
Data
Types)
8. NULLIFY UPDATE : Set all of the Foreign Keys of the
Referencing Table to NULL. This will invalidate any
referencing of the Referenced Key (which must not be
NULL)
9. DEFAULT UPDATE : Invalidate references to Updated
Referenced Keys by setting all Referencing Table
Foreign Keys to a DEFAULT value
CSE3180 Semester 1 2005 week 5 / 47
Possible Referential Integrity Processes
10. DEFAULT DELETE : Invalidate references to the deleted
Referencing Key Value(s) by setting all Referencing Foreign
Key values to a DEFAULT value
11. WARNING DELETE : Permit the deletion BUT Warn the
user of the Unattached Foreign Keys which are now
present in the Referencing Table(s)
12. WARNING UPDATE : Permit the Update BUT Warn the
User of Unattached Foreign Keys which are now present in
the Referencing Table(s)
CSE3180 Semester 1 2005 week 5 / 48
Some Integrity Schema Examples
Create table monash1(
city
varchar2(13) not null,
studydate
date not null,
noonread
number(4,1),
midnightread number(4,1),
rainfall
number,
unique (city,studydate) );
Creates a table with the candidate key of city,studydate
There may be a number of Unique constraints
CSE3180 Semester 1 2005 week 5 / 49
Some Integrity Schema Examples
Create table monash1(
city
varchar2(13) not null,
studydate
date not null,
noonread
number(4,1),
midnightread number(4,1),
rainfall
number,
primary key (city,studydate) );
Creates a table with the Primary Key key of city,studydate
and there is only 1 such set of values in the table.
There may be a number of Unique constraints.
CSE3180 Semester 1 2005 week 5 / 50
Some Integrity Schema Examples
Create table monash1(
city
varchar2(13) not null,
studydate
date not null,
noonread
number(4,1),
midnightread number(4,1),
rainfall
number,
constraint pk_citystudy primary key (city,studydate) );
Creates a table with the Primary Key key of city,studydate
and names the constraint citystudy in the Constraints table.
CSE3180 Semester 1 2005 week 5 / 51
Some Integrity Schema Examples
Create table worker (
name char(25) primary key,
age
number check (Age between 18 and 65),
address char(15) references lodging (Lodging)
);
• There is a referenced table named Lodging with an attribute
named lodging. It is a Primary Key in that table and a
Foreign key in the worker table
CSE3180 Semester 1 2005 week 5 / 52
Triggers
• Oracle triggers are used to include more processing power
to the DBMS function for events which affect a database.
• In the following example a Trigger will be set which ensures
that changes to employee records will only take place during
business hours on working days ( security ?)
• See if you agree ...
CSE3180 Semester 1 2005 week 5 / 53
Triggers
Create trigger emp_permit_change
before
delete or insert or update
on emp
declare dummy integer;
begin
/* if today is a Saturday or Sunday, then return an error*/
if (to_char(sysdate, ‘dy’) = ‘sat’ or
to_char (sysdate, ‘dy’) = ‘sun’)
then raise_application_error (-20501,
‘May not change employee table during the weekend’);
end if;
CSE3180 Semester 1 2005 week 5 / 54
Triggers
Perhaps we need this as well :-
If (to_char(sysdate, ‘hh24’) < 8
or to_char(sysdate, ‘hh24’) >= 18)
then raise application_error (-20502,
‘May only change employee table during working hours’);
end if;
end;
which raises and interesting point - what happens with
flexible time and enterprise bargaining ?
CSE3180 Semester 1 2005 week 5 / 55
And now - The Data Types
CSE3180 Semester 1 2005 week 5 / 56
Data Types
• Data types have a number of functions in database design
and data content
• They add to the Integrity features of a database by their
‘built-in’ features associated with
– value limitations of attribute sets triggered by inherent
constraint of the datatype
– limitation or restriction of the representation of content of
an attribute set
CSE3180 Semester 1 2005 week 5 / 57
Data Types - Oracle
• Primary datatypes are:
• CHAR ,VARCHAR2, NCHAR, DATE, LONG, LONGRAW, NUMBER,
RAW and ROWID
• Compatibility with SQL databases allows for these
additional or expansion datatypes:
• CHAR(size) Fixed length. Max size 255. Default 1
– Left justified and padded with blanks
• VARCHAR2(n) Variable length character data. Max size 4000
in Oracle8, 2000 in previous Oracle versions
CSE3180 Semester 1 2005 week 5 / 58
Data Types - Oracle
• VARCHAR Same as char - is to be dropped from future
versions
• NCHAR Character datatype. Supports 16 bit (2 byte)
binary character codes
• DATE Valid dates from Jan 1, 4712 B.C. to December
31, 4712 A.D.
• NUMBER(l,d) Numeric data. l = length, d = number of
decimal digits
• LONG Stores variable-length character data up to 2Gb
CSE3180 Semester 1 2005 week 5 / 59
Data Types - Oracle
• BLOB - Binary Large Object. Up to 4Gb, Binary data
• CLOB - Character Large Object. Up to 4Gb
• BFILE - Binary File. Read only binary stored outside the
database. Length depends on the Operating
System
• NCLOB - A Clob column which supports a multibyte
character set
CSE3180 Semester 1 2005 week 5 / 60
Ingres Datatypes
•
•
•
•
•
•
•
•
•
•
•
Char(n) A string of up to 2000 characters
c(n)
Same as char
varchar(n) Variable length, up to 2000 characters
integer1 -128 to + 127
integer2 -32,768 to + 32,767 (smallint)
integer4 -2,147,483,648 to +2,147,483,647 (integer)
float4
-1.0e+38 to + 1.0e+38 ( 7digit precision)
float8
-1.0e+38 to +1.0e+38 (16 digit precision)
date
date (12 bytes) 1-Jan-1582 to 31-Dec-2382
date interval -800 to +800 years
money
$-99999999999999.99 to
(8 bytes)
$+99999999999999.99
CSE3180 Semester 1 2005 week 5 / 61
Microsoft Access SQL Datatypes
•
•
•
•
•
•
•
•
•
•
•
Binary 1 byte
Bit
1 byte (logical field)
Counter 4 bytes (Long in the Jet db engine)
Currency 8 bytes -922,337,203,658,477.5808 to
+922,337,203,658,477,5807
Datetime 8 bytes between the years 100 and 9999
Single
4 bytes -3.402823e38 to 3.402823e38
Double 8 bytes -1.7976931348623e308 to +1.79 ……..
Short
2 bytes -32,768 to + 32,767
Long
4 bytes -2,147,483,647 to + 2,127,483,647
Text
1 byte per character 0 to 255 characters
Longtext 0 to 1.2 Gb
CSE3180 Semester 1 2005 week 5 / 62
An Exercise
A B C D
CSE3180 Semester 1 2005 week 5 / 63
An Exercise
• In the previous slide there were 4 people (A,B,C,D) who
were attempting to move from 1 side of the bridge to the
other.
• There are some constraints
Person A takes 10 minutes (carries a heavy load)
Person B takes 5 minutes (carries a lighter load)
Person C takes 2 minutes ( Ditto)
Person D takes 1 minute (very athletic)
• In addition, they must go in pairs (it’s night-time and one
must carry a torch back for the next pair to proceed in
safety)
• Arrange their movement so that ALL people have
completed the move in 17 minutes
CSE3180 Semester 1 2005 week 5 / 64