Transcript Here

Schema Management and SQL as DDL
 Professor Kedem’s changes, if any, are marked in green, they
are not copyrighted by the authors, and the authors are not
responsible for them.
Dennis writes in blue.
Database System Concepts
6.1
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
DDL -- Data Definition Language
 SQL, as part of the ANSI standard, has certain DDL capabilities.
Unfortunately, they are not sufficient to really manage the
database.
 We will therefore describe the main features of the standard,
while also describing some common nonstandard extensions.
 We will continue with the example of our database consisting of
the relations:
• PLANTS(P#,PNAME,CITY,MARGIN)
• CUSTOMERS(C#,CNAME,CITY,P#)
• ORDERS(O#,AMT,DATE,C#)
Database System Concepts
6.2
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Example
PLANTS(P#,PNAME,CITY,MARGIN)
CUSTOMERS(C#,CNAME,CITY,P#)
ORDERS(O#,AMT,DATE,C#)
Plants talks about factories, each has a profit margin
Customers are assigned to plants
Orders are placed by customers
Database System Concepts
6.3
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Defining Base Relations
 Base relations, that is the relations in the conceptual level, are
defined using the CREATE command.
 At the minimum, this command specifies:
• The name of the relation.
• The names of the columns of the relation.
• The domains for each column. Permitted domains are:
CHARACTER (CHAR), NUMERIC, DECIMAL (DEC), INTEGER
(INT), SMALLINT, FLOAT, DOUBLE PRECISION, REAL, with
various lengths and precision specifications.
Database System Concepts
6.4
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
A Simplest Definition
 CREATE TABLE PLANTS
(P# DECIMAL(5),
PNAME CHAR(20),
CITY CHAR (25),
MARGIN REAL);
 There are many additional aspects of the relation that can
generally be specified, and we will examine them in turn by
means of making the example more and more comprehensive.
Database System Concepts
6.5
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
NULLS And Default Values
 It is possible to specify for a column that:
• It cannot have a NULL value, or
• What is the default, if the value is not specified (say in an insertion)
• CREATE TABLE PLANTS
(P# DECIMAL(5) NOT NULL,
PNAME CHAR(20) DEFAULT 'MISSING',
CITY CHAR (25) NOT NULL DEFAULT 'CHICAGO',
MARGIN REAL);
Database System Concepts
6.6
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Integrity Constraints
 We can specify certain integrity constraints:
• CREATE TABLE PLANTS
(P# DECIMAL(5),
PNAME CHAR(20),
CITY CHAR (25),
MARGIN REAL,
CHECK ( P# BETWEEN 1 AND 44444),
CHECK ( CITY IN ('CHICAGO', 'DAYTONA')),
CHECK ( CITY <> 'CHICAGO' OR MARGIN > 0.0));
• Note that the last integrity constraint is equivalent to:
• CITY = 'CHICAGO'  MARGIN > 0.0.
 So we have in general CHECK ( predicate )
Database System Concepts
6.7
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Specifying Keys
 We can specify alternate keys (or superkeys). For instance, to
specify that both C# by itself and the pair PNAME, CITY uniquely
identify a tuple we could write:
• CREATE TABLE PLANTS
P# DECIMAL(5),
PNAME CHAR(20),
CITY CHAR (25),
MARGIN REAL,
UNIQUE (P#),
UNIQUE (PNAME,CITY));
 They are supersets of keys and not necessarily keys, because
some proper subset may also be unique. For example, we could
specify UNIQUE(P#, City).
 UNIQUE fields may be null, but should not be.
Database System Concepts
6.8
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Primary Keys
 We could specify a set of columns that uniquely identifies a tuple as a
primary key.
 Only one such set can be defined for any relation.
 We could therefore write:
• CREATE TABLE PLANTS
P# DECIMAL(5),
PNAME CHAR(20),
CITY CHAR (25),
MARGIN REAL,
PRIMARY KEY (P#),
UNIQUE (PNAME,CITY));
 Attributes of a primary cannot be NULL (this does not need be specified
but is assumed automatically for attributes of primary key, this will not
permit us to naturally use COUNTRY, STATE/TERRITORY, NAME as
primary key with allowing null STATE/TERRITORY)
 Primary keys, and foreign keys (defined next) are used for certain
integrity checks.
 Most database systems establish an index for each unique combination.
If yours does not, then build an index yourself.
Database System Concepts
6.9
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
!Foreign Keys
 Assume that the application is such that if a value appears as
some C# in the relation orders then it specifies a single definite
tuple in the relation customers.
 We specify this foreign key constraint by;
• CREATE TABLE ORDERS
(...,
...,
...,
FOREIGN KEY C#
REFERENCES CUSTOMERS(C#));
 This could be done only if C# was defined as primary key in the
relation CUSTOMERS. (It is a coincidence that in the example in
both relations the same name, C#, was given to customer
number.)
 For some variants of SQL:
REFERENCES CUSTOMERS (no need to specify primary key)
Database System Concepts
6.10
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Maintenance of Referential Integrity
 In order to maintain this referential integrity constraint, the
system will reject any operation that will violate it.
 This constraint “will act” when:
• An INSERT or an UPDATE on ORDERS is attempted that would
create a value of of C# that does not exist in CUSTOMERS.
• A DELETE or an UPDATE on CUSTOMERS is attempted that will
leave tuples in orders in which the value of C# does not appear in
any tuple of CUSTOMERS.
 Unless anything additional is specified in the constraint then
there will be NO ACTION, that is the above will not be permitted
Database System Concepts
6.11
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Maintenance of Referential Integrity
(Cont.)
 Other options to maintain integrity when actions violating the
referential integrity are attempted on the table that has the
primary key.
• CASCADE
Here the new value of the primary key is copied to the foreign key
• SET NULL
Here the new value of the foreign key is NULL
• SET DEFAULT
Here the new value of the foreign key is a specified default value
Database System Concepts
6.12
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
More About Constraints
 table constraint is:
[CONSTRAINT constraint name] {PRIMARY KEY (column name.,..)} |
{UNIQUE (column name.,..)} | {FOREIGN KEY (column name.,..)}
REFERENCES table name [(column name.,..)] [referential
specification]} | {CHECK predicate} [[INITIALLY DEFERRED | INITIALLY
IMMEDIATE] [ [NOT] DEFERRABLE] ]
 column constraint is:
[CONSTRAINT constraint name] {NOT NULL} | {PRIMARY KEY} |
{UNIQUE} | {REFERENCES table name [(column name)] [referential
specification]} | {check predicate} [[INITIALLY DEFERRED | INITIALLY
IMMEDIATE] [ [NOT] DEFERRABLE] ]
 referential specification is:
[MATCH {FULL | PARTIAL}] [ON UPDATE {CASCADE | SET NULL |
SET DEFAULT | NO ACTION } ] [ON DELETE {CASCADE | SET NULL |
SET DEFAULT | NO ACTION } ]
 Note: it is a good idea to name constraints, as they can be later dropped
while referring to their names
Database System Concepts
6.13
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
More About Constraints (Cont.)
 Constraints are checked as tables are modified (immediately or deferred
until later).
• Essentially, each row of the table has to satisfy the constraint
 The actual checking is done either after each statement or at the end of
a transaction
• It is done at the end, to allow changes that cannot be done in a single
statement
 Formal meaning of the CHECK constraint:
It is violated when the following set is not empty:
SELECT *
FROM tables
WHERE NOT predicate
 Conclusion: An empty relation satisfies all CHECK conditions!!!
Database System Concepts
6.14
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Assertions (optional)
 Assertion is like a CHECK constraint that is not attached to a
table definition; it is “free floating”
 CREATE ASSERTION SMALL_ENTERPRISE
CHECK
( (SELECT COUNT (*) FROM PLANTS) + (SELECT COUNT
(*)
FROM CUSTOMERS) < 1000)
 Assertions are more natural then previously described
constraints, especially when refererring to several tables
 Often deleted though if they are expensive to run. In fact, it is
rare to see constraints or assertions other than simple value
constraints (e.g. bal >= 0), unique, or foreign key constraints.
Database System Concepts
6.15
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Assertions vs. Checks
 Assertions can specify conditions that cannot be specified by standard
constraints (and are quite expensive)
 CREATE TABLE PLANTS
(P# DECIMAL(5),
PNAME CHAR(20),
CITY CHAR (25),
MARGIN REAL,
CHECK (SELECT COUNT (*) FROM PLANTS > 0) );
 This constraint does not check anything. The empty table PLANTS
satisfies it, because
Each row of the empty table satisfies it
 But the assertion
CREATE ASSERTION NICE_ASSERTION
CHECK (SELECT COUNT (*) FROM PLANTS > 0)
will work, as it is not a row-by-row condition
Database System Concepts
6.16
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Views
 We now proceed to the definition of the user level, that is to the
definition of views. Generally speaking, a view consists of
“continuously current” relation that is derived by means of a
SELECT statement from other relations.
 For example, we could write
• CREATE VIEW GOODPLANTS
AS SELECT *
FROM PLANTS
WHERE MARGIN > .0;
 We could now execute a query against the view
• SELECT P#
FROM GOODPLANTS
WHERE CITY = 'CHICAGO';
 This will give all P# for CHICAGO where margin is positive.
Database System Concepts
6.17
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Views Versus Snapshots
 View is not a snapshot (which is also known as a materialized
view), which is static. View can be thought of as a procedure call.
Therefore we should think of the following procedure for
computing the answer to the last query:
 The system computes the value of the relation GOODPLANTS.
 The system executes the query against the relation
GOODPLANTS.
 In fact, the system may compute the answer differently. However,
the result will be equivalent to the canonical procedure described
above.
Database System Concepts
6.18
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Views Defined by Queries
 In general, almost any query definition could be used to define a
view, so we could have:
• CREATE VIEW CUSTOMERS_IN_THE_CITY
AS SELECT CNAME
FROM PLANTS, CUSTOMERS
WHERE (PLANTS.CITY = CUSTOMERS.CITY
AND PLANTS.C# = CUSTOMERS.C#
AND MARGIN >= .1);
 Views can also be defined with CHECK option, which we will
discuss later.
Database System Concepts
6.19
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Most Views Cannot Be Updated
 Consider the view:
• CREATE VIEW MARGIN_ON_DATE
AS SELECT MARGIN, DATE
FROM PLANTS, ORDERS, CUSTOMERS
WHERE (PLANTS.P# = CUSTOMERS.P#
AND ORDERS.C# = CUSTOMERS.C#);
 There is no meaning to the update
• INSERT INTO MARGIN_ON_DATE
VALUES (0.9,1987.3.12);
• Why?
Because there is no well-defined way to this update in the base
relations.
Database System Concepts
6.20
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Views That Are Updateable in SQL
 The following are the major conditions that must be true for an
updatable view
• Is drawn from one table
• no joins, unions, differences, intersections
• If the underlying table is a view, it must be updateable
• The SELECTed columns are column references (each column at
most once and without DISTINCT) and not values or aggregates
• No GROUP BY
Database System Concepts
6.21
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Some Views That Could Be Updated
 It may make sense to update views that SQL standard does not
allow to update
 If we have two tables, in which SSN is a key
• R(SSN,SALARY)
• S(SSN,ADDRESS)
 The view
CREATE VIEW RS
AS SELECT *
FROM R, S
WHERE R.SSN = S.SSN
can be meaningfully updated, but is not updateable following
SQL standard
Database System Concepts
6.22
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Privileges
 Privileges can be granted to user or PUBLIC for
• Operations
• References
on
• Base relations
• Views
Database System Concepts
6.23
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Types of Privileges
 Select
 Insert
 Update
 Delete
 References
Database System Concepts
6.24
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Examples of Privileges
 A typical instruction is:
• GRANT SELECT, INSERT
ON CUSTOMERS
TO LI, BROWN;
 Privileges can be restricted to columns:
• GRANT SELECT
ON CUSTOMERS.CITY
TO LI, BROWN;
 It is possible to grant all privileges by:
• GRANT ALL
ON CUSTOMERS
TO LI, BROWN;
Database System Concepts
6.25
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Passing Privileges
 It is possible to allow the users to pass the privileges to other
users by issuing:
• GRANT SELECT, INSERT
ON CUSTOMERS
TO LI, BROWN
WITH GRANT OPTION;
 Then LI can issue
• GRANT SELECT
ON CUSTOMERS.CITY
TO JONES;
Database System Concepts
6.26
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Privilege to Reference
 It is possible to allow a user to specify columns in a relation as
foreign keys referring to primary keys in a relation to which the
user has no privileges:
• GRANT ALL
ON ORDERS
TO LI;
• GRANT REFERENCES (C#)
ON CUSTOMERS
TO LI
Needed to make sure that information can leak. In general,
modifications on ORDERS could give information on CUSTOMERS,
e.g., an attempt to insert a fake order into ORDERS for C# = 17 will
tell a user where there is C#.=17 in CUSTOMERS, depending on
whether the database accepts or rejects this insertion. So this
privilige must be explicitly set
Database System Concepts
6.27
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Privileges on Views
 It is possible to grant privileges on views.
• Of course, the privilege must be meaningful. That is a privilege to
update can be given only on a view that can be updated, etc.
Database System Concepts
6.28
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Revoking Privileges
 Privileges can be revoked
 There are various way to specify what happens with privileges
granted by somebody from whom a privilege is taken away
Database System Concepts
6.29
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,
Changing a Table
 ALTER TABLE table name {ADD [COLUMN] column definition} | {ALTER
[COLUMN] column name alter action} | {DROP [COLUMN] column
name RESTRIC | CASCADE } | {ADD table constraint definition} |
{DROP CONSTRAINT constraint name RESTRICT | CASCADE };
 Alter action refers to default values
 In the DROP COLUMN action RESTRICT and CASCADE deal with the
case when the column is referenced by a view(s).
• RESTRICT does not allow dropping if the column is referenced
• CASCADE will delete all such views
 In the DROP CONSTRAINT action RESTRICT and CASCADE deal with
the case of primary key constraint, when it is used as foreign key
elsewhere
• RESTRICT does not allow dropping if there is such foreign key reference
• CASCADE will delete all such references
Database System Concepts
6.30
©Zvi
M. Kedem Korth and Sudarshan
©Silberschatz,