Transcript What is a Database System?

Data manipulation operations on views
Data manipulation operations on views
1
Data manipulation operations on views
Outline
 retrieval operations
 in theory
 in practice (SQL92 and PostgreSQL)
 update operations
 in theory - basics
 in practice
• SQL92
• PostgreSQL
 in theory - more advanced issues
2
Data manipulation operations on views
Example
 suppose




Student ( S_id, Name, Address, Programme, Tutor, …)
Tutor ( T_id, Name, Address, …)
Course ( C_id, Name, Length, …)
C_Reg ( S_id, C_id, ... )
 suppose all tutors need access to all course-lists,
but are not allowed to see other information; this
can be achieved via a view.
3
Data manipulation operations on views
View “Course-lists”
CREATE VIEW Course-lists (Student, Tutor, Course) AS
SELECT (Student.Name, Tutor.Name, Course.Name)
FROM
Student, Tutor, Course, C_Reg
WHERE Tutor = T_id AND Course.C_id = C_Reg.C_id
AND Student.S_id = C_Reg.S_id ;
4
Data manipulation operations on views
Retrieving from “Course-lists”
-- all students that take “Database Systems”
SELECT Student FROM Course-lists
WHERE Course = ‘Database Systems’ ;
--Marian’s tutees and the courses they take
SELECT Student, Course FROM Course-lists
WHERE Tutor = ‘Marian’ ;
--etc.
5
Data manipulation operations on views
Retrieval operations
 suppose
• DB - is the set of base relations in a a database
• a view V can be considered as a function V = X(DB)
 a retrieval operation R on the view is defined in terms of
the materialisation of V as
• R(V) = R(X(DB))
/* X(DB) is a materialisation of V */
 in practice is more efficient to use substitution
• R(V) = (RX)(DB)
• in principle works perfectly (100%) well, but in practice, sometimes
fails; e.g. “Restrictions on views [in SQL]” (Connolly, 1999, p. 446)
6
Data manipulation operations on views
Principle
 a view should look like and behave exactly like a
base relation
• satisfied, in theory, in case of retrieval operations
• in practice, even in case of retrieval operations, it is
sometimes violated; e.g. in SQL92 (Connolly, 1999, p.446):
– columns based on aggregate functions cannot be used in a
WHERE clause or as an argument of an aggregate function
– a grouped view cannot be joined with a base table
7
Data manipulation operations on views
Updating “Course-lists”
--insert the fact that “Bob Marley”, who’s personal tutor is
--“Marian Ursu”, has registered for “Database Systems”
INSERT INTO Course-lists
VALUES (“Bob Marley”, “Marian Ursu”, “Database Systems”);
 will this work? why?
 “Course-lists” is a virtual relation; the base tables have to
be updated instead; how should this be done?
8
Data manipulation operations on views
Updating a view
 suppose V is a view of the database DB, written as
V = X(DB); then





UPDATE(V) means UPDATE(X(DB))
however, X(DB) does not exist in reality
therefore it is not updateable
therefore, UPDATE1 must be found, such that
UPDATE(X(DB)) = X(UPDATE1(DB))
 view updating is governed by sets of rules for
determining UPDATE1 given X and UPDATE
 such sets of rules must satisfy some general
(updating) principles
9
Data manipulation operations on views
Main updating principle
 a given tuple can appear in a given relation
only if that tuple satisfies the relation
predicate for that relation; this is true both for
base relations and views
10
Data manipulation operations on views
Predicate - the meaning of a relation
 suppose the relation
• Student(Id, Name, Address, Course, Tutor)
• its meaning is: the student with the identifier Id having the name
Name lives at the Address address, takes the Course course and
has the Tutor tutor
• this is formally expressed by the predicate of the relation
 predicate = truth valued function, true for all the tuples that
should be in the relation and false for the rest
• student(5334,’Scott Sherman’,’USA’,’CIS’,’Rob Hierons’) - true
• student(-21,’Mick Jagger’,’Richmond’,’Rock’,’Duda’) - false
11
Data manipulation operations on views
DBMSs and relation predicates
 the DBMS does not (cannot) know, a priori,
exactly, the predicate for a given relation
 however, it knows
 the set of all integrity constraints / rules
 therefore
 the meaning of a relation = the logical AND of all
integrity constraints / rules that apply to that
relation
12
Data manipulation operations on views
Meaning of a relation - example
CREATE TABLE Suppliers
(S_id Id_number NOT NULL,
S_name Name,
S_status Status,
S_city City,
PRIMARY KEY (S_id),
UNIQUE (S_name),
CHECK (Status > 0 AND Status < 101) );
13
Data manipulation operations on views
Main updating principle
 the predicate for a relation constitutes the
criterion for update acceptability; remember:
 a given tuple can appear in a given relation only if
that tuple satisfies the relation predicate for that
relation; this is true both for base relations and
views
 this is the main principle; other principles may
exist as well
14
Data manipulation operations on views
Updating principles
 view update is a semantic (not syntactic) issue; i.e. should not
depend upon the form in which the view is stated;
 create view V as select S_name, City from Suppliers
where Status > 25 or City = ‘Paris’
 create view V as
(select S_name, City from Suppliers where Status > 25 )
union
(select S_name, City from Suppliers where City = ‘Paris’)
 both or neither should be updateable; however, in SQL the first is
updateable but the second not;
15
Data manipulation operations on views
Updating principles
 the view updating mechanism must work correctly when
the view is a base relation
 the updating rules must preserve symmetry where
applicable
 updates on views must be implemented by the same kind
of updates on the underlying relations (e.g. DELETE by
means of DELETES)
 the rules cannot assume that the database is well
designed
 etc. (Date, 1995, p.473)
16
Data manipulation operations on views
Updating rules
 comply with the general principles
 given for simple relational expressions
(A <relational operator> B)
 for complex expressions - recursive application
 convention
• the SQL syntax is going to be used for defining the views
in the following examples; however, note that THE
RULES ARE GENERIC; THEY ARE NOT SQL RULES
17
Data manipulation operations on views
Rules for updating set operators views
 updating views defined by expressions as
•
•
•
•
A UNION B
A INTERSECT B
A MINUS B
where A and B are relational expressions
 in detail: INSERT into a UNION
18
Data manipulation operations on views
Updating UNION views - INSERT
 the new tuple must satisfy at least PA or PB; the new
tuple must not already exist in either A or B; if it
satisfies PA, it is inserted into A (providing it wasn’t
already inserted as a result of inserting it into B); if it
satisfies PB, it is inserted into B (providing it wasn’t
already inserted as a result of inserting it into A);
• no temporal order exist between the two attempted insertions
19
Data manipulation operations on views
Example #1
 Suppliers1(S_id, S_name, Status, City)
 constraint: City IN (‘London’, ‘Paris’, ‘Madrid’, ‘Rome’, ‘Berlin’)
 Suppliers2(S_id, S_name, Status, City)
 constraint: City IN (‘London’, ‘New York, ‘Washington’, ‘Boston’)
 CREATE VIEW Test1 AS
Suppliers1 UNION Suppliers2
20
Data manipulation operations on views
Example #1
 INSERT INTO Test1 VALUES
(S6, Smith, 50, London);
 satisfies City IN ... therefore it is
inserted into the first relation
Suppliers1
if it was not already there
 satisfies City IN ... therefore it is
inserted into the second relation
Suppliers2
if it was not already there
 INSERT INTO Test1 VALUES
(S7, Jones, 40, Paris);
 satisfies City IN ... therefore it is
inserted into the first relation
Suppliers1
if it was not already there
 does not satisfy City IN ...
therefore it is not inserted into the
second relation
Suppliers2
21
Data manipulation operations on views
Example #2
 Suppliers(Id, Name, Address, …, Status, …)
 CREATE VIEW Test2 AS
(SELECT * FROM Suppliers WHERE Status > 25)
UNION
(SELECT * FROM Suppliers WHERE City = ‘Paris’)
S_id
S_name
Status
City
S2
Jones
10
Paris
S3
Blake
30
Paris
S5
Adams
30
Athens
22
Data manipulation operations on views
Example #2
 INSERT INTO Test2 VALUES
(S6, Smith, 50, London);
 satisfies Status > 25 therefore it is
inserted into the first relation
(Suppliers WHERE Status > 25)
if it isn’t already there
 does not satisfy City = ‘Paris’
therefore it is not inserted into the
second relation
(Suppliers WHERE City = ‘Paris’)
 as a final result, the tuple is
inserted once in Suppliers
 INSERT INTO Test2 VALUES
(S7, Jones, 40, Paris);
 satisfies Status > 25 therefore it is
inserted into the first relation
(Suppliers WHERE Status > 25)
if it isn’t already there
 satisfies City = ‘Paris’ therefore it
is inserted into the second relation
(Suppliers WHERE City = ‘Paris’)
if it isn’t already there
 however, only one insertion is
performed on Suppliers, since
both expressions refer to the
same base relation (Suppliers)
23
Data manipulation operations on views
Example #3
 a similar view definition, but this time over two base relations:
DEFINE VIEW Test 3 AS Sstat UNION Scity
Sstat
Constraint: Status > 25
Scity
Constraint: City = Paris
S_id S_name Status City
S3
S5
Blake
Adams
30
30
Paris
Athens
S_id S_name Status City
S2
S3
Jones
Blake
10
30
Paris
Paris
24
Data manipulation operations on views
Example #3
 INSERT INTO Test3 VALUES
(S6, Smith, 50, London)
 will be inserted into Sstat (satisfies
the predicate)
 will not be inserted into Scity
(does not satisfy the predicate)
 intuitively “correct” behaviour
 INSERT INTO Test3 VALUES
(S7, Jones, 40, Paris)
 will be inserted into Sstat (satisfies the
predicate)
 will also be inserted into Scity (satisfies
the predicate)
 intuitively “incorrect” - a certain kind of
information has to be kept in only one
place (relation)
 this is an example of a surprising result;
the cause is the bad design of the
database (the two predicates are not
mutually exclusive)
25
Data manipulation operations on views
Updating views - in practice
 in practice, the rules for updating views are
 simpler
• to simplify the DBMS
 defined in an ad-hoc manner
• i.e. they do not necessarily comply with the generic
principles previously stated
26
Data manipulation operations on views
SQL2 rules for updating views
 the FROM clause of the SELECT statement of the defining
query contains exactly one table reference (the view is defined
on exactly one table), i.e. the view definition does not contain
JOIN, UNION, INTERSECT or EXCEPT
 every element in the SELECT statement of the defining query is
a column name (rather than a calculated field, aggregate
function, …)
 the SELECT clause defining the view does not contain the word
DISTINCT
 the defining query does not include a GROUP BY clause
 etc … (refer to Date, 1995, p. 490 or Connolly, 1999, p.449)
27
Data manipulation operations on views
PostgreSQL rules for updating views
 views in Postgres are not updateable (yet)
28
Data manipulation operations on views
Conclusions
 view - named virtual relation, behaving similarly with a base
relation
 logical data independence (restructuring)
 security and macro-capability
 retrieval operations - substitution - in principle works fine in
any case; in practice is does not always work fine
 update principles - i theory
 update rules specific to relational algebra operators - in theory
 update rules specific to certain implementations - in practice
29
Data manipulation operations on views
Extra reading
30
Data manipulation operations on views
Updating UNION views - UPDATE
 the updated version must satisfy PA or PB; if the tuple appears
in A it is deleted from A without performing any triggered action
and without checking the relation predicate; similarly for B; if the
updated version of the tuple satisfies PA then it is inserted into A
(provided it wasn’t already inserted, as a result of inserting it into
B); similarly for B.
31
Data manipulation operations on views
Activities
 deduce (study) rules for updating
 UNION views - DELETE
 INTESECT views
 DIFFERENCE views
32
Data manipulation operations on views
Updating pure relational operators views
 updating views defined by
• a RESTRICTION
• a PROJECTION
• a JOIN
 in detail:
• INSERT into a PROJECTION
• INSERT into a many-to-many JOIN
33
Data manipulation operations on views
INSERT into a PROJECTION
 X is the set of attributes of the projection
 Y is the remaining set of attributes
 let the tuple to be inserted be (x); let the default value for Y be
(y); if a default value does not exist the insertion cannot be
performed; if the tuple (x, y) satisfies PA (where A is the original
relation) then it is inserted into A
 corollary: if the projection (view) does not include all candidate
keys, the underlying relation will not permit insert via the view
34
Data manipulation operations on views
Example
 DEFINE VIEW Test AS
SELECT S_id, S_name, City FROM Suppliers
-- the default value for Status is 50
S_id S_name City
S1
S2
S3
S4
S5
Smith
Jones
Blake
Cark
Adams
London
Paris
Paris
London
Athens
 INSERT INTO Test
VALUES (S6, Marian, Cluj);
succeeds
 INSERT INTO Test
VALUES (S1, Marian, Cluj);
fails; why?
35
Data manipulation operations on views
INSERT into A JOIN B
 the new tuple j = (a, b) must satisfy PJ (i.e. PA (a) AND PB (b));
if the A portion of j (i.e. a) does not appear in A, it is inserted into
A; if the B portion of j (i.e. b) does not appear in B, it is inserted
into B.
36
Data manipulation operations on views
Example
 DEFINE VIEW Test AS <Rel1 JOIN Rel2 OVER City>
S_id S_name City
P_id P_name City
S1
S2
S3
S4
P1
P2
P3
P4
Smith
Jones
Blake
Clark
London
Paris
Paris
London
Nut
Screw
Bolt
Cam
London
London
Paris
London
37
Data manipulation operations on views
The view
S_id S_name City
P_id
P_name
S1
S1
S1
S2
S2
S4
S4
S4
P1
P2
P4
P3
P3
P1
P2
P4
Nut
Screw
Cam
Bolt
Bolt
Nut
Screw
Cam
Smith
Smith
Smith
Jones
Blake
Clark
Clark
Clark
London
London
London
Paris
Paris
London
London
London
 INSERT INTO Test VALUES (S6, Green, London, P5, Cog);
38
Data manipulation operations on views
Question
 how many tuples will be added to the view?
39