Database Modifications, Data Types, Views

1
Database Modifications,Data Types,Views
2
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.

3
Insertion

• To insert a single tuple
• INSERT INTO
• VALUES ( )
• Example
• Consider MovieExec(name, address, cert,
  netWorth)
• INSERT INTO MovieExec
• VALUES('Melanie Griffith', '34 Boston Blvd', 700,
  300000)

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

5
Inserting Many Tuples

• We may insert the entire result of a query into a
  relation, using the form
• INSERT INTO
• ( )
• Example
• CREATE TABLE DisneyMovies(
• name VARCHAR2(25),
• year INT
• )
• INSERT INTO DisneyMovies
• (SELECT title, year
• FROM Movie
• WHERE studioName 'Disney'
• )

6
Deletion

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

7
Example Delete all Tuples

• Make the relation Movie empty
• DELETE FROM Movie
• No WHERE clause needed here.

8
Updates

• To change certain attributes in certain tuples of
  a relation
• UPDATE
• SET
• WHERE
• Example
• Change the length of 'Godzilla' to 200.
• UPDATE Movie
• SET length 200
• WHERE title 'Godzilla'

9
Another Example

• Suppose that Browns movies have approximately 20
  min of info before starting.
• So, lets take 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')

10
Exercise

• Product(maker, model, type)
• PC(model, speed, ram, hd, rd, price)
• Laptop(model, speed, ram, hd, screen, price)
• Printer(model, color, type, price)
• Using two INSERT statements, store in the
  database the fact that PC model 1100 is made by
  manufacturer C, has speed 1800, RAM 256, hard
  disk 80, a 20x DVD, and sells for 2499.
• Insert the facts that for every PC there is a
  laptop with the same manufacturer, speed, RAM and
  hard disk, a 15-inch screen, a model number 1000
  greater, and a price 500 more.
• Delete all PCs with less than 20 GB of hard
  disk.
• Delete all laptops made a manufacturer that
  doesnt make printers.
• Manufacturer A buys manufacturer B. Change all
  products made by B so they are now made by A.
• For each PC, double the amount of RAM and add 20
  GB to the amount of hard disk.
• For each laptop made by manufacturer B, add one
  inch to the screen size and subtract 100 from
  the price.

11
Data Types

• The principal element in a table creation 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

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

13
Oracle NUMBER

• NUMBER(p,s), where
• p is the precision, which in ORACLE is the total
  number of digits.
• s is the scale, which in ORACLE is the number of
  digits to the right of the decimal point.
• If the scale is negative, the actual data is
  rounded to the specified number of places to the
  left of the decimal point.
• 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
• CREATE TABLE A1( attrib NUMBER(3,2) )
• INSERT INTO A1 VALUES(100)

• NUMBER(p) is equivalent to NUMBER(p,0).
• INT is a synonym for NUMBER(38), i.e.
  NUMBER(38,0)
• A NUMBER(5) is not any different from a
  NUMBER(38) space-wise.
• The 5 is just an "edit", a format, an integrity
  constraint. It doesnt affect the physical
  storage at all.
• In absence of precision and scale, the default is
  the maximum range and precision for an Oracle
  number.

14
Dates and Times

• DATE and TIME are types in SQL.
• No TIME type in ORACLE, but DATE also keeps the
  time.
• 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)
• )

15
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'))
16
Getting a Date in/out (II)
Getting the date and time out SELECT
TO_CHAR(my_date, 'DD-MON-YYYYHHMISS') FROM
Movie For more info http//www-db.stanford.edu/
ullman/fcdb/oracle/or-time.html
17
Adding/Deleting/Modifying Attributes

• ALTER TABLE MovieExec ADD salary INT
• ALTER TABLE MovieExec ADD
• phone CHAR(16) DEFAULT 'unlisted'
• ALTER TABLE MovieExec DROP COLUMN phone
• ALTER TABLE MovieExec MODIFY phone CHAR(18)
• Also in ORACLE
• ALTER TABLE starsIN RENAME COLUMN title TO
  movieTitle

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 AS
• In contrast, a relation whose value is really
  stored in the database is called a base table.
• Example
• CREATE VIEW DisneyMovie AS
• SELECT title, year, producerC
• FROM Movie
• WHERE studioName 'Disney'

19
Accessing a View

• 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

20
View on more than one relation renaming the
attributes
CREATE VIEW MovieProd(movieTitle, movieYear,
prodName) AS SELECT title, year, name
FROM Movie, MovieExec WHERE producerc
cert Same as CREATE VIEW MovieProd2 AS
SELECT title AS movieTitle, year AS movieYear,
name AS prodName FROM Movie, MovieExec
WHERE producerc cert
21
Updateable Views

• Only when
• There is only one relation, say R, in the FROM
  clause (of the query defining the view).
• There isnt a subquery involving R in the WHERE
  clause (of the query defining the view).
• Not a problem for ORACLE.
• 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 in the view.
• This is only checked for views defined WITH
  CHECK OPTION.

22

• CREATE VIEW ParamountMovie AS
• SELECT title, year
• FROM Movie
• WHERE studioName 'Paramount'
• WITH CHECK OPTION
• INSERT INTO ParamountMovie
• VALUES ('Star Trek', 1979)
• This insertion will fail!
• Why this insertion is not possible?
• 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 ParamountMovie view!
• Thus, it shouldn't be allowed to get into the
  database through the ParamountMovie view.

23
CREATE VIEW ParamountMovie2 AS SELECT
studioName, title, year FROM Movie
WHERE studioName 'Paramount' WITH CHECK
OPTION INSERT INTO ParamountMovie2 VALUES
('Paramount', 'Star Trek', 1979) Now it
succeeds. Why?
24
Deleting

• DELETE FROM ParamountMovies
• WHERE year2008
• is translated into
• DELETE FROM Movie
• WHERE year2008 AND studioName'Paramount'

25
Updating

• UPDATE ParamountMovies
• SET year 1979
• WHERE title 'Star Trek the Movie'
• is equivalent to the base-table update
• UPDATE Movies
• SET year 1979
• WHERE title 'Star Trek the Movie' AND
• studioName 'Paramount'

26
Materialized Views

• CREATE MATERIALIZED VIEW MovieProd AS
• SELECT title, year, name
• FROM Movie, MovieExec
• WHERE producerC cert
• Useful to speed up queries
• In principle, the DBMS needs to recompute a
  materialized view every time one of its base
  tables changes in any way.
• However, there are a number opportunities to do
  only incremental changes.

27
Example

• Suppose we insert a new movie into Movie, say
• title 'Kill Bill', year 2003, and producerC
  23456.
• Then we only need to look up
• cert23456 in MovieExec.
• Since cert is the key for MovieExec, there can be
  at most one name returned by the query
• SELECT name FROM MovieExec
• WHERE cert 23456
• As this query returns name 'Quentin Tarantino',
  the DBMS can insert the proper tuple into
  MovieProd by
• INSERT INTO MovieProd
• VALUES('Kill Bill', 2003, 'Quentin Tarantino')

28
Example

• Suppose we delete a movie from Movies, say the
  movie with
• title 'DumbDumber' and year 1994.
• The DBMS has only to delete this one movie from
  MovieProd by
• DELETE FROM MovieProd
• WHERE title 'Dumb Dumber' AND year 1994

29
Example

• Suppose we insert a tuple into MovieExec, and
  that tuple has
• cert34567 and name 'MaxBialystock'.
• Then the DBMS may have to insert into MovieProd
  some movies that were not there because their
  producer was previously unknown. The operation
  is
• INSERT INTO MovieProd
• SELECT title, year, 'Max Bialystock'
• FROM Movie
• WHERE producerC 34567

30
Example

• Suppose we delete the tuple with cert45678 from
  MovieExec.
• Then the DBMS must delete from MovieProd all
  movies that have producerC45678.
• Thus, the DBMS executes
• DELETE FROM MovieProd
• WHERE (title, year) IN
• (SELECT title, year
• FROM Movie
• WHERE producerC 45678)

31
Rewriting Queries to Use Materialized Views

• A materialized view can be referred to in the
  FROM clause of a query, just as a virtual view
  can.
• However, because a materialized view is stored in
  the database, it is good to try rewriting a query
  to use a materialized view, even if that view was
  not mentioned in the query as written.
• Such a rewriting may enable the query to execute
  much faster.

32
Example

• CREATE MATERIALIZED VIEW MovieProd AS
• SELECT title, year, name
• FROM Movie, MovieExec
• WHERE producerC cert
• Consider query Q that asks for the names of the
  stars of movies produced by Max Bialystock.
• Movie(title, year, length, genre, studioName,
  producerC)
• StarsIn(movieTitle, movie Year, starName)
• MovieExec(name, address, cert, net Worth)
• SELECT starName
• FROM StarsIn, Movies, MovieExec
• WHERE movieTitle title AND movie Year year
  AND
• producerCcert AND name 'Max Bialystock'

33
Example

• View-Based Rewriting
• SELECTstarName
• FROM StarsIn,MovieProd
• WHERE movieTitle title AND movieYear year AND
• name 'Max Bialystock'