Database Modifications

1
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
• Insert a tuple or tuples.
• Delete a tuple or tuples.
• Update the value(s) of an existing tuple or
  tuples.

2
Insertion

• To insert a single tuple
• INSERT INTO ltrelationgt
• VALUES ( ltlist of valuesgt )
• Example
• INSERT INTO MovieExec
• VALUES('Melanie Griffith', '34 Boston Blvd', 700,
  3000000)

3
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
• We forget the standard order of attributes for
  the relation.
• We dont have values for all attributes.

4
Inserting Many Tuples

• We may insert the entire result of a query into a
  relation, using the form
• INSERT INTO ltrelationgt
• ( ltsubquerygt )
• Example Using the Movie relation, enter into a
  new relation DisneyMovies(name, year) all of
  Disneys 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)

5
Deletion

• To delete tuples satisfying a condition from some
  relation
• DELETE FROM ltrelationgt
• WHERE ltconditiongt
• Delete from Movie the Disneys movies
• DELETE FROM Movie
• WHERE studioName 'Disney'

6
Example Delete all Tuples

• Make the relation Movie empty
• DELETE FROM Movie
• Note no WHERE clause needed.

7
Updates

• To change certain attributes in certain tuples of
  a relation
• UPDATE ltrelationgt
• SET ltlist of attribute assignmentsgt
• WHERE ltcondition on tuplesgt
• Change the length of 'Godzilla' to 200
• UPDATE Movie
• SET length 200
• WHERE title 'Godzilla'

8
Updates (example)

• Suppose that Browns 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')

9
Declaring a Relation

• The simplest form is
• CREATE TABLE ltnamegt (
• ltlist of elementsgt
• )
• And you may remove a relation from the database
  schema by
• DROP TABLE ltnamegt

10
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

11
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)
• )

12
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.

13
Oracle NUMBER II

• Examples
• Actual Data Specified as Stored as
• ----------- ------------ ---------
• 7456123.89 NUMBER 7456123.89
• 7456123.89 NUMBER (9) 7456124
• 7456123.89 NUMBER (9,2) 7456123.89
• 7456123.89 NUMBER (9,1) 7456123.9
• 7456123.8 NUMBER (6) exceeds precision
• 7456123.8 NUMBER (15,1) 7456123.8
• 7456123.89 NUMBER (7,-2) 7456100
• 7456123.89 NUMBER(7,2) 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.

14
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.

15
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)

16
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
  doesnt affect the physical storage at all.

17
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)
• )

18
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'))
19
Getting a Date in/out (II)
Getting the date out SELECT TO_CHAR(my_date,
'DD-MON-YYYYHHMISS') FROM movie For more
info http//www-db.stanford.edu/ullman/fcdb/orac
le/or-time.html
20
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.

21
Required Distinctions

• However, standard SQL requires these
  distinctions
• There can be only one PRIMARY KEY for a relation,
  but several UNIQUE attributes.
• No attribute of a PRIMARY KEY can ever be NULL in
  any tuple. But attributes declared UNIQUE may
  have NULLs, and there may be several tuples with
  NULL.

22
Example

• CREATE TABLE T5 (
• A1 NUMBER(5) PRIMARY KEY,
• A2 NUMBER(5) UNIQUE
• )
• SQLgt desc t5
• Name Null?
  Type
• -----------------------------------------
  -------- ----------------------------
• A1 NOT
  NULL NUMBER(5)
• A2
  NUMBER(5)

23
Other Declarations for Attributes

• Two other declarations we can make for an
  attribute are
• NOT NULL means that the value for this attribute
  may never be NULL.
• DEFAULT ltvaluegt says that if there is no specific
  value known for this attributes component in
  some tuple, use the stated ltvaluegt.

24
Adding Attributes

• We may change a relation schema by adding a new
  attribute (column) by
• ALTER TABLE ltnamegt ADD ltattribute declarationgt
• Example
• ALTER TABLE MovieExec ADD
• phone CHAR(16) DEFAULT 'unlisted'

25
Deleting Attributes

• Remove an attribute from a relation schema by
• ALTER TABLE ltnamegt DROP COLUMN ltattributegt
• ALTER TABLE MovieExec DROP COLUMN phone

26
Modifying Attributes

• Remove an attribute from a relation schema by
• ALTER TABLE ltnamegt MODIFY ltattributegt
• ALTER TABLE MovieExec MODIFY phone CHAR(18)

27
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 ltnamegt AS ltquerygt
• In contrast, a relation whose value is really
  stored in the database is called a base table.

28
Example View Definition

• CREATE VIEW DisneyMovie AS
• SELECT title, year, producerc
• FROM Movie
• WHERE studioName 'Disney'

29
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

30
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
31
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.

32
Updateable Views

• Only when
• There is only one relation in the WHERE clause of
  the view.
• There isnt a subquery
• 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.

33

• 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.

34
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.
35
Example View Expansion
PROJtitle, year SELECT
Movie