1 / 35

Database Modifications

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

tso
Télécharger la présentation

Database Modifications

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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 <relation> VALUES ( <list of values> ); • 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 don’t 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 <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);

  5. 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';

  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 <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';

  8. 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');

  9. 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>;

  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 doesn’t 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-YYYY:HH:MI:SS') FROM movie; For more info: http://www-db.stanford.edu/~ullman/fcdb/oracle/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 NULL’s, and there may be several tuples with NULL.

  22. Example CREATE TABLE T5 ( A1 NUMBER(5) PRIMARY KEY, A2 NUMBER(5) UNIQUE ); SQL> 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 <value> says that if there is no specific value known for this attribute’s component in some tuple, use the stated <value>.

  24. 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';

  25. Deleting Attributes • Remove an attribute from a relation schema by: ALTER TABLE <name> DROP COLUMN <attribute>; ALTER TABLE MovieExec DROP COLUMN phone;

  26. Modifying Attributes • Remove an attribute from a relation schema by: ALTER TABLE <name> MODIFY <attribute>; 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 <name> AS <query>; • 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 isn’t 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. PROJtitle SELECTyear=1973 DisneyMovie Example: View Expansion PROJtitle, year SELECT Movie

More Related