Transcript Slides

Database Modifications
•
A modification command does not return a
result as a query does, but it changes the
database in some way.
•
There are three kinds of modifications:
1. Insert a tuple or tuples.
2. Delete a tuple or tuples.
3. Update the value(s) of an existing tuple or
tuples.
Insertion
• To insert a single tuple:
INSERT INTO <relation>
VALUES ( <list of values> );
• Example:
INSERT INTO MovieExec
VALUES('Melanie Griffith', '34 Boston Blvd', 700, 3000000);
Specifying Attributes in INSERT
•
We may add to the relation name a list of attributes.
INSERT INTO MovieExec(name, address, cert, netWorth)
VALUES('Melanie Griffith', NULL, 700, 3000000);
•
There are two reasons to do so:
1. We forget the standard order of attributes for the relation.
2. We don’t have values for all attributes.
Inserting Many Tuples
• We may insert the entire result of a query into a relation, using the
form:
INSERT INTO <relation>
( <subquery> );
• Example: Using the Movie relation, enter into a new relation
DisneyMovies(name, year) all of Disney’s movies:
INSERT INTO DisneyMovies
(SELECT title, year
FROM Movie
WHERE studioName = 'Disney'
);
Before we have to create DisneyMovies table:
CREATE TABLE DisneyMovies(name VARCHAR2(25), year INT);
Deletion
• To delete tuples satisfying a condition from
some relation:
DELETE FROM <relation>
WHERE <condition>;
• Delete from Movie the Disney’s movies:
DELETE FROM Movie
WHERE studioName ='Disney';
Example: Delete all Tuples
• Make the relation Movie empty:
DELETE FROM Movie;
• Note no WHERE clause needed.
Updates
• To change certain attributes in certain tuples of a
relation:
UPDATE <relation>
SET <list of attribute assignments>
WHERE <condition on tuples>;
• Change the length of 'Godzilla' to 200:
UPDATE Movie
SET length = 200
WHERE title = 'Godzilla';
Updates (example)
• Suppose that Brown’s movies have approximately
20 min of info before starting.
• So, a modification would be to change the movie
lengths by taking that 20 min off.
UPDATE Movie
SET length = length - 20
WHERE (title, year) IN
(SELECT title, year
FROM Movie, Movieexec
WHERE Movie.producerc = Movieexec.cert
AND name = 'Brown');
Declaring a Relation
• The simplest form is:
CREATE TABLE <name> (
<list of elements>
);
• And you may remove a relation from the
database schema by:
DROP TABLE <name>;
Elements of Table Declarations
• The principal element is a pair consisting of an
attribute and a type.
• The most common types are:
–
–
–
–
–
INT or INTEGER (synonyms).
REAL
FLOAT
CHAR(n ) = fixed-length string of n characters.
VARCHAR(n ) = variable-length string of up to n
characters.
– DATE
Example: Create Table
create table Movie(
title char(20),
year int,
length int,
inColor char(1),
studioName char(20),
producerC int,
primary key (title, year)
);
Oracle NUMBER I
• The NUMBER datatype stores zero, positive, and negative
fixed and floating-point numbers with magnitudes between 1.0
x 10-130 and 9.9...9 x 10125 (38 nines followed by 88 zeroes)
with 38 digits of precision.
• Specify a fixed-point number using the following form:
NUMBER(p,s) where:
– p is the precision, or the total number of digits.
– s is the scale, or the number of digits to the right of the
decimal point. The scale can range from -84 to 127.
Oracle NUMBER II
Examples
Actual Data
----------7456123.89
7456123.89
7456123.89
7456123.89
7456123.8
7456123.8
7456123.89
7456123.89
Specified as
-----------NUMBER
NUMBER (9)
NUMBER (9,2)
NUMBER (9,1)
NUMBER (6)
NUMBER (15,1)
NUMBER (7,-2)
NUMBER(7,2)
Stored as
--------7456123.89
7456124
7456123.89
7456123.9
exceeds precision
7456123.8
7456100
exceeds precision
• If the scale is negative, the actual data is rounded to the specified
number of places to the left of the decimal point.
– For example, a specification of (10,-2) means to round to hundreds.
Oracle NUMBER III
• We can specify an integer using the following form:
NUMBER(p)
– This represents a fixed-point number with precision p and
scale 0 and is equivalent to NUMBER(p,0).
• INT is a synonym for NUMBER(38), i.e. NUMBER(38,0)
• Specify a floating-point number using the following form:
NUMBER
• The absence of precision and scale designators specifies the
maximum range and precision for an Oracle number.
Oracle NUMBER IV
CREATE TABLE A1(
attrib
NUMBER(3,2)
);
INSERT INTO A1 VALUES(100);
What happens?
CREATE TABLE A2(
attrib1
NUMBER,
attrib2
INT );
DESC A2;
INSERT INTO A2 VALUES (100.34, 200);
Oracle NUMBER V
• A number(5) is not any different from a
number(38) if the number(38) only holds
data the number(5) can.
• The 5 is just an "edit", a format, an
integrity constraint. Nothing more,
Nothing less. It doesn’t affect the
physical storage at all.
Dates and Times
• DATE and TIME are types in SQL.
• No TIME type in ORACLE, but DATE also keeps the time.
• The form of a date value is:
'yyyy-mm-dd'
– Example: DATE '2002-09-30' for Sept. 30, 2002.
create table Movie(
title char(20),
year int,
length int,
inColor char(1),
studioName char(20),
producerC int,
my_date DATE DEFAULT SYSDATE,
primary key (title, year)
);
Getting a Date in/out
INSERT INTO Movie(title, year, length, inColor, studioName, producerC,
my_date)
VALUES('Godzilla', 1998, 120.45, 'C', 'Paramount', 123, '12-Feb-1998');
INSERT INTO Movie(title, year, length, inColor, studioName, producerC,
my_date)
VALUES('Pretty Woman', 1990, 120, 'C', 'Disney', 234, '13-09-90');
VALUES('Pretty Woman', 1990, 120, 'C', 'Disney', 234, '13-09-90')
*
ORA-01843: not a valid month
INSERT INTO Movie(title, year, length, inColor, studioName, producerC,
my_date)
VALUES('Pretty Woman', 1990, 120, 'C', 'Disney', 234, TO_DATE('13-09-90',
'dd-mm-yy'));
Getting a Date in/out (II)
Getting the date out:
SELECT TO_CHAR(my_date, 'DD-MON-YYYY:HH:MI:SS')
FROM movie;
For more info: http://www-db.stanford.edu/~ullman/fcdb/oracle/or-time.html
PRIMARY KEY Versus UNIQUE
• The SQL standard allows DBMS
implementers to make their own distinctions
between PRIMARY KEY and UNIQUE.
• Example: some DBMS might automatically
create an index (data structure to speed
search) in response to PRIMARY KEY, but
not UNIQUE.
Required Distinctions
•
However, standard SQL requires these
distinctions:
1. There can be only one PRIMARY KEY for a
relation, but several UNIQUE attributes.
2. No attribute of a PRIMARY KEY can ever be
NULL in any tuple. But attributes declared
UNIQUE may have NULL’s, and there may
be several tuples with NULL.
Example
CREATE TABLE T5 (
A1 NUMBER(5) PRIMARY KEY,
A2 NUMBER(5) UNIQUE
);
SQL> desc t5
Name
Null?
----------------------------------------- -------A1
NOT NULL
A2
Type
---------------------------NUMBER(5)
NUMBER(5)
Other Declarations for Attributes
•
Two other declarations we can make for
an attribute are:
1. NOT NULL means that the value for this
attribute may never be NULL.
2. DEFAULT <value> says that if there is no
specific value known for this attribute’s
component in some tuple, use the stated
<value>.
Adding Attributes
• We may change a relation schema by adding a
new attribute (“column”) by:
ALTER TABLE <name> ADD <attribute declaration>;
• Example:
ALTER TABLE MovieExec ADD
phone CHAR(16) DEFAULT 'unlisted';
Deleting Attributes
• Remove an attribute from a relation schema by:
ALTER TABLE <name> DROP COLUMN <attribute>;
ALTER TABLE MovieExec DROP COLUMN phone;
Modifying Attributes
• Remove an attribute from a relation schema by:
ALTER TABLE <name> MODIFY <attribute>;
ALTER TABLE MovieExec MODIFY phone CHAR(18);
Views
• A view is a “virtual table,” a relation that is
defined in terms of the contents of other
tables and views.
• Declare by:
CREATE VIEW <name> AS <query>;
• In contrast, a relation whose value is really
stored in the database is called a base table.
Example: View Definition
CREATE VIEW DisneyMovie AS
SELECT title, year, producerc
FROM Movie
WHERE studioName = 'Disney';
Example: Accessing a View
• You may query a view as if it were a base table.
• Examples:
SELECT title
FROM DisneyMovie
WHERE year = 1973;
SELECT DISTINCT name
FROM DisneyMovie, MovieExec
WHERE producerc = cert;
View on more than one relation;
renaming the attributes
CREATE VIEW MovieProd(movieTitle, prodName) AS
SELECT title, name
FROM Movie, MovieExec
WHERE producerc = cert;
Same as:
CREATE VIEW MovieProd2 AS
SELECT title AS movieTitle, name AS prodName
FROM Movie, MovieExec
WHERE producerc = cert;
What Happens When a View Is
Used?
• The DBMS starts by interpreting the query
as if the view were a base table.
– Typical DBMS turns the query into something
like relational algebra.
• The queries defining any views used by the
query are also replaced by their algebraic
equivalents, and “spliced into” the
expression tree for the query.
Updateable Views
Only when:
1. There is only one relation in the WHERE clause of
the view.
2. There isn’t a subquery
3. The list in the SELECT clause includes enough
attributes that for every tuple inserted into the view,
we can fill the other attributes out with NULL or the
default, and have a tuple that will yield the inserted
tuple of the view.
INSERT INTO DisneyMovie
VALUES ('Star Trek', 1979);
Why this insertion is not possible?
This insertion will fail.
The rationale for this behavior is:
• The above insertion, were it allowed to get through, would insert
a tuple with NULL for studioName in the underlying Movie
table.
• However, such a tuple doesn't satisfy the condition for being in
the DisneyMovie view!
• Thus, it shouldn't be allowed to get into the database through the
DisneyMovie view.
CREATE VIEW DisneyMovie2 AS
SELECT studioName, title, year
FROM Movie
WHERE studioName = 'Disney';
DELETE FROM DisneyMovie2
WHERE title = 'Star Trek 2';
INSERT INTO DisneyMovie2
VALUES ('Disney', 'Star Trek 2', 2005);
Both succeed.
Example: View Expansion
PROJtitle
SELECTyear=1973
DisneyMovie
PROJtitle, year
SELECT
Movie