Database Modifications Data Types Views Database Modifications

Database Modifications, Data Types, Views

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

Insertion • To insert a single tuple: INSERT INTO <relation> VALUES ( <list of values> ); Example • Consider Movie. Exec(name, address, cert#, net. Worth) INSERT INTO Movie. Exec VALUES('Melanie Griffith', '34 Boston Blvd', 700, 300000);

Specifying Attributes in INSERT • We may add to the relation name a list of attributes. INSERT INTO Movie. Exec(name, address, cert, net. Worth) 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 CREATE TABLE Disney. Movies( name VARCHAR 2(25), year INT ); INSERT INTO Disney. Movies (SELECT title, year FROM Movie WHERE studio. Name = 'Disney' );

Deletion • To delete tuples satisfying a condition from some relation: DELETE FROM <relation> WHERE <condition>; Example • Delete from the Movie table the Disney’s movies: DELETE FROM Movie WHERE studio. Name ='Disney';

Example: Delete all Tuples • Make the relation Movie empty: DELETE FROM Movie; • No WHERE clause needed here.

Updates • To change certain attributes in certain tuples of a relation: UPDATE <relation> SET <list of attribute assignments> WHERE <condition on tuples>; Example • Change the length of 'Godzilla' to 200. UPDATE Movie SET length = 200 WHERE title = 'Godzilla';

Another Example • • Suppose that Brown’s movies have approximately 20 min of info before starting. So, let’s 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');

Exercise Product(maker, model, type) PC(model, speed, ram, hd, rd, price) Laptop(model, speed, ram, hd, screen, price) Printer(model, color, type, price) a) b) c) d) e) f) g) 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 20 x 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 PC’s with less than 20 GB of hard disk. Delete all laptops made a manufacturer that doesn’t 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.

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

Example: Create Table CREATE TABLE Movie( title CHAR(20), year INT, length INT, in. Color CHAR(1), studio. Name CHAR(20), producer. C INT, PRIMARY KEY (title, year) );

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 -----7456123. 89 7456123. 89 Specified as Stored as ------NUMBER 7456123. 89 NUMBER(9) 7456124 NUMBER(9, 2) 7456123. 89 NUMBER(9, 1) 7456123. 9 NUMBER(6) exceeds precision NUMBER(15, 1) 7456123. 8 NUMBER(7, -2) 7456100 NUMBER(7, 2) exceeds precision • • • CREATE TABLE A 1( attrib NUMBER(3, 2) ); INSERT INTO A 1 VALUES(100); What happens? • 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 doesn’t affect the physical storage at all. In absence of precision and scale, the default is the maximum range and precision for an Oracle number.

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, in. Color CHAR(1), studio. Name CHAR(20), producer. C INT, my_date DATE DEFAULT SYSDATE, PRIMARY KEY (title, year) );

Getting a Date in/out INSERT INTO Movie(title, year, length, in. Color, studio. Name, producer. C, my_date) VALUES('Godzilla', 1998, 120. 45, 'C', 'Paramount', 123, '12 -Feb-1998'); INSERT INTO Movie(title, year, length, in. Color, studio. Name, producer. C, 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, in. Color, studio. Name, producer. C, 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 and time 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

Adding/Deleting/Modifying Attributes ALTER TABLE Movie. Exec ADD salary INT; ALTER TABLE Movie. Exec ADD phone CHAR(16) DEFAULT 'unlisted'; ALTER TABLE Movie. Exec DROP COLUMN phone; ALTER TABLE Movie. Exec MODIFY phone CHAR(18); Also in ORACLE: ALTER TABLE stars. IN RENAME COLUMN title TO movie. Title;

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 CREATE VIEW Disney. Movie AS SELECT title, year, producer. C FROM Movie WHERE studio. Name = 'Disney';

Accessing a View • Query a view as if it were a base table. Examples SELECT title FROM Disney. Movie WHERE year = 1973; SELECT DISTINCT name FROM Disney. Movie, Movie. Exec WHERE producer. C = cert;

View on more than one relation; renaming the attributes CREATE VIEW Movie. Prod(movie. Title, movie. Year, prod. Name) AS SELECT title, year, name FROM Movie, Movie. Exec WHERE producerc = cert; Same as: CREATE VIEW Movie. Prod 2 AS SELECT title AS movie. Title, year AS movie. Year, name AS prod. Name FROM Movie, Movie. Exec WHERE producerc = cert;

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

CREATE VIEW Paramount. Movie AS SELECT title, year FROM Movie WHERE studio. Name = 'Paramount' WITH CHECK OPTION; INSERT INTO Paramount. Movie 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 studio. Name in the underlying Movie table. • However, such a tuple doesn't satisfy the condition for being in the Paramount. Movie view! • Thus, it shouldn't be allowed to get into the database through the Paramount. Movie view.

CREATE VIEW Paramount. Movie 2 AS SELECT studio. Name, title, year FROM Movie WHERE studio. Name = 'Paramount' WITH CHECK OPTION; INSERT INTO Paramount. Movie 2 VALUES ('Paramount', 'Star Trek', 1979); Now it succeeds. Why?

Deleting DELETE FROM Paramount. Movies WHERE year=2008; is translated into DELETE FROM Movie WHERE year=2008 AND studio. Name='Paramount';

Updating UPDATE Paramount. Movies 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 studio. Name = 'Paramount';

Materialized Views CREATE MATERIALIZED VIEW Movie. Prod AS SELECT title, year, name FROM Movie, Movie. Exec WHERE producer. C =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 a number opportunities to do only incremental changes.

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

Example • Suppose we delete a movie from Movies, say the movie with – title ='Dumb&Dumber' and year = 1994. • The DBMS has only to delete this one movie from Movie. Prod by: DELETE FROM Movie. Prod WHERE title = 'Dumb & Dumber' AND year = 1994;

Example • Suppose we insert a tuple into Movie. Exec, and that tuple has – cert=34567 and name = 'Max. Bialystock'. • Then the DBMS may have to insert into Movie. Prod some movies that were not there because their producer was previously unknown. The operation is: INSERT INTO Movie. Prod SELECT title, year, 'Max Bialystock' FROM Movie WHERE producer. C = 34567;

Example • Suppose we delete the tuple with cert=45678 from Movie. Exec. • Then the DBMS must delete from Movie. Prod all movies that have producer. C=45678. • Thus, the DBMS executes: DELETE FROM Movie. Prod WHERE (title, year) IN (SELECT title, year FROM Movie WHERE producer. C =45678);

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.

Example CREATE MATERIALIZED VIEW Movie. Prod AS SELECT title, year, name FROM Movie, Movie. Exec WHERE producer. C =cert; • Consider query Q that asks for the names of the stars of movies produced by Max Bialystock. Movie(title, year, length, genre, studio. Name, producer. C) Stars. In(movie. Title, movie Year, star. Name) Movie. Exec(name, address, cert, net Worth) SELECT star. Name FROM Stars. In, Movies, Movie. Exec WHERE movie. Title = title AND movie Year = year AND producer. C=cert AND name = 'Max Bialystock';

Example • View-Based Rewriting: SELECTstar. Name FROM Stars. In, Movie. Prod WHERE movie. Title =title AND movie. Year =year AND name= 'Max Bialystock';