Database Principles

1
Database Principles

• College of Computer Science and Technology
• Chongqing University of Posts Telecom.

2
Chapter 5 More SQL

• Database Modification
• Defining a Database Schema
• Views

3
More SQL

• Database Modification
• Defining a Database Schema
• Views

4
Database Modifications

• A modification command does not return a result
  (as a query does), but changes the database in
  some way.
• Three kinds of modifications
• Insert a tuple or tuples.
• Delete a tuple or tuples.
• Update the value(s) of an existing tuple or
  tuples.

5
Insertion

• To insert a single tuple
• INSERT INTO ltrelationgt
• VALUES ( ltlist of valuesgt )
• Example add Sydney Greenstreet to the list of
  stars of The Maltese Falcon.
• INSERT INTO StarsIn VALUES(The Maltese
  Falcon, 1942, Sydney GreenStreet)

6
Specifying Attributes in INSERT

• We may add to the relation name a list of
  attributes.
• Two reasons to do so
• We forget the standard order of attributes for
  the relation.
• We dont have values for all attributes, and we
  want the system to fill in missing components
  with NULL or a default value.

7
Example Specifying Attributes

• Another way to add Sydney Greenstreet to the list
  of stars of The Maltese Falcon.
• INSERT INTO StarsIn(movieTitle, movieYear,
  starName)
• VALUES(The Maltese Falcon, 1942, Sydney
  GreenStreet)

8
Inserting Many Tuples

• We may insert the entire result of a query into a
  relation, using the form
• INSERT INTO ltrelationgt
• ( ltsubquerygt )

9
Example Insert a Subquery

• Using Studio and Movie, add to the relation
  Studio all movie studios that are mentioned in
  the relation Movie, but dont appear in Studio.

10
Solution

• INSERT INTO Studio(name)
• (SELECT DISTINCT studioName
• FROM Movie
• WHERE studioName NOT IN
• (SELECT name
• FROM Studio))

11
Deletion

• To delete tuples satisfying a condition from some
  relation
• DELETE FROM ltrelationgt
• WHERE ltconditiongt

12
Example Deletion

• Delete from relation StarsIn the fact that Sydney
  GreenStreet was a star in The Maltese Falcon
• DELETE FROM StarsIn
• WHERE movieTitle The Maltese Falcon AND
• movieYear 1942 AND
• starName Sydney
  Greenstreet

13
Example Delete all Tuples

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

14
Example Delete Many Tuples

• Delete from MovieExec all movie executives whose
  net worth is low-less than ten million dollars.
• DELETE FROM MovieExec
• WHERE netWorth lt 10000000

15
Updates

• To change certain attributes in certain tuples of
  a relation
• UPDATE ltrelationgt
• SET ltlist of attribute assignmentsgt
• WHERE ltcondition on tuplesgt

16
Example Update

• Modify the relation MovieExec by prepending the
  title Pres. In front of every movie executives
  who is the president of a studio
• UPDATE MovieExec
• SET name Pres. name
• WHERE cert IN
• (SELECT presC FROM Studio)

17
Defining a Database Schema

• A database schema comprises declarations for the
  relations (tables) of the database.
• Several other kinds of elements also may appear
  in the database schema, including views, indexes,
  and triggers, which well introduce later.

18
Creating (Declaring) a Relation

• Simplest form is
• CREATE TABLE ltnamegt (
• ltlist of elementsgt
• )
• To delete a relation
• DROP TABLE ltnamegt

19
Elements of Table Declarations

• Most basic element an attribute and its type.
• The most common types are
• INT or INTEGER
• REAL or FLOAT
• CHAR(n )
• fixed-length string of n characters.
• VARCHAR(n )
• variable-length string of up to n characters.

20
Example Create Table

• CREATE TABLE MovieStar (
• name CHAR(30),
• address VARCHAR(255),
• gender CHAR(1),
• birthdate DATE
• )

21
Dates and Times

• DATE and TIME are types in SQL.
• The form of a date value is
• DATE yyyy-mm-dd
• Example DATE 2004-09-30

22
Times as Values

• The form of a time value is
• TIME hhmmss
• with an optional decimal point and fractions of
  a second following.
• Example TIME 153002.5

23
Modifying relation schemas

• We can use ALTER to modify a relation schema. We
  have several options, the most important of which
  are
• ADD followed by a column name and its data type
• DROP followed by a column name

24
Adding Attributes

• We may add a new attribute (column) to a
  relation schema by
• ALTER TABLE ltnamegt ADD
• ltattribute declarationgt
• Example
• ALTER TABLE MovieStar ADD phone CHAR(16)

25
Deleting Attributes

• Remove an attribute from a relation schema by
• ALTER TABLE ltnamegt
• DROP ltattributegt
• Example we dont really need the license
  attribute for bars
• ALTER TABLE MovieStar DROP birthdate

26
Default values

• When we create or modify tuples, we sometimes
  dont have values for all components.
• To address this problem, SQL provides the NULL
  value.
• However, there are times when we would prefer to
  use default value, the value that is placed in a
  component if no other value is known.

27
Example

• We might wish to use the character ? as the
  default for an unknown gender, and we might also
  wish to use the earliest possible date, DATE
  0000-00-00 for an unknown birthdate.
• CREATE TABLE MovieStar (
• name CHAR(30),
• address VARCHAR(255),
• gender CHAR(1) DEFAULT ?,
• birthdate DATE DEFAULT DATE 0000-00-00
• )

28
Indexes

• An index on an attribute A of a relation is a
  data structure that makes it efficient to find
  those tuples that have a fixed value for
  attribute A.

29
To create a index

• Create an index on attribute year for the
  relation Movie
• CREATE INDEX YearIndex ON Movie(year)
• From Movie, create an index on title and year
• CREATE INDEX KeyIndex ON Movie(title, year)

30
To delete a index

• If we wish to delete the index, we simply use its
  name in a statement like
• DROP INDEX YearIndex
• Selection of indexes requires a trade-off by the
  database designer
• The existence of an index on an attribute greatly
  speeds up queries in which a value for that
  attribute is specified.
• On the other hand, ervery index built for an
  attribute of some relation makes insertions,
  deletion, and updates to that relation more
  complex and time-consuming.

31
Views

• A view is a virtual table a relation defined
  in terms of the contents of other tables and
  views.
• Declare by
• CREATE VIEW ltnamegt AS ltquerygt
• Antonym a relation whose value is really stored
  in the database is called a base table.

32
Example View Definition

• To define a view that is a part of the Movie
  relation, specifically, the titles and years of
  the movies made by Paramount Studio
• CREATE VIEW ParamountMovie AS
• SELECT title, year
• FROM Movie
• WHERE studioName Paramount

33
Example Accessing a View

• Query a view as if it were a base table.
• Also a limited ability to modify views if it
  makes sense as a modification of one underlying
  base table.
• Example query
• SELECT title
• FROM ParamountMovie
• WHERE year 1979

34
What Happens When a View Is Used?

• The SQL system will translate the query on the
  view ParamountMovie into a query about the base
  table Movie that has the same effect as our
  original query.
• SELECT title
• FROM Movie
• WHERE studioName Paramount AND year 1979

35
Define a query based on views and base tables

• Example
• SELECT DISTINCT starName
• FROM ParamountMovie, StarsIn
• WHERE title movieTitle AND year movieYear

36
Renaming attributes

• We can give a views attributes names of our own
  choosing. For example
• CREATE VIEW MovieProd(movieTitle, prodName) AS
• SELECT title, name
• FROM Movie, MovieExec
• WHERE producerC cert

37
Delete a view

• If a view becomes unuseful, we can delete it. For
  instance
• DROP VIEW ParamountMovie

38
NULL Values

• Tuples in SQL relations can have NULL as a value
  for one or more components.
• Meaning depends on context. Two common cases
• Missing value e.g., we know Joes Bar has some
  address, but we dont know what it is.
• Inapplicable e.g., the value of attribute
  spouse for an unmarried person.

39
Two important rules

• When we operate on a NULL and any other value,
  including another NULL, using an arithmetic
  operator like or , the result is NULL.
• When we compare a NULL value and any value,
  including another NULL, using a comparison
  operator like or gt, the result is UNKNOWN. The
  value UNKNOWN is another truth-value, like TRUE
  and FALSE.

40
To ask if x has the value NULL

• x IS NULL, this expression have the value TRUE if
  x has the value NULL and it has FALSE otherwise.
• x IS NOT NULL, this expression have the value
  FALSE if x has the value NULL and it has TRUE
  otherwise

41
Comparing NULLs to Values

• The logic of conditions in SQL is really 3-valued
  logic TRUE, FALSE, UNKNOWN.
• But a query only produces a tuple in the answer
  if its truth value for the WHERE clause is TRUE
  (not FALSE or UNKNOWN).

42
Join Expressions

• SQL provides several versions of joins.
• These expressions can be stand-alone queries or
  used in place of relations in a FROM clause.

43
Products and Natural Joins

• Natural join
• R NATURAL JOIN S
• Product
• R CROSS JOIN S
• Example
• Likes NATURAL JOIN Serves
• Relations can be parenthesized subqueries, as
  well.

44
Theta Join

• R JOIN S ON ltconditiongt
• Example using Movie and StarIn
• Movie JOIN StarIn ON
• title movieTitle AND year movieyear

45
Outerjoins

• R OUTER JOIN S is the core of an outerjoin
  expression. It is modified by
• Optional NATURAL in front of OUTER.
• Optional ON ltconditiongt after JOIN.
• Optional LEFT, RIGHT, or FULL before OUTER.
• LEFT pad dangling tuples of R only.
• RIGHT pad dangling tuples of S only.
• FULL pad both this choice is the default.

46
Example

• MovieStar NATURAL FULL OUTER JOIN MovieExec
• Where FULL can be replaced by LEFT or RIGHT
• Movie FULL OUTER JOIN StarIn ON title
  movieTitle AND year movieYear
• Where FULL can be replaced by LEFT or RIGHT

47
Exercises of SQL

• Answer the following questions, based on the
  database below.
• Supplier(SNO, SNAME, STATUS, CITY)
• Part(PNO, PNAME, COLOR, WEIGHT)
• Project(JNO, JNAME, CITY)
• SPJ(SNO, PNO, JNO, QTY)
• The schema has four relations. The key attributes
  for each relation are shown in red.

48

• 1.Give suitable declarations for each relation.
• 2.Write the following queries
• (1)Find the name and city of all the Supplier.
• (2)Find the name, color and weight of all the
  parts.
• (3)Find the number of all the projects using the
  parts that provided by S1.
• (4)Find the name and quantity of all the parts
  used by J2.
• (5)Find the number of all the parts made in
  ShangHai.
• (6)Find the name of all the projects which have
  used the parts made in ShangHai.

49

• (7)Find the number of all the projects that
  didnt used the parts made in TianJin.
• (8)Update all the parts which color is red with
  blue.
• (9)Update the Supplier S5 of the part P6 used by
  J4 with Supplier S3.
• (10)Delete all the records about S2 from relation
  Supplier, and delete corresponding records from
  relation SPJ.
• (11)Insert a new record(S2, J6, P4, 200) into
  relation Supplier.
• (12)Grant the INSERT privilege on table Supplier
  to user John, and he includes the grant option
  with this privilege.
• (13)Grant the SELECT privilege on table SPJ and
  UPDATE privilege on attribute QTY of SPJ to user
  Allice.

50

• 3.Construct a view ThirdProj giving the Supplier
  number(Sno), part number(Pno), supporting
  quantity(QTY) of all Supplier who provide parts
  for Third Project. Write each of the queries
  using this view.
• (1)Find the part number and supporting quantity
  of all parts used by Third Project.
• (2)Find the supporting relation of S1.