Transcript CS206 --- Electronic Commerce

Chapter 6
More SQL
 Database Modification
 Defining a Database Schema
 Views
1
More SQL
Database Modification
Defining a Database Schema
Views
2
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:
1. Insert a tuple or tuples.
2. Delete a tuple or tuples.
3. Update the value(s) of an existing tuple or tuples.
3
Insertion
 To insert a single tuple:
INSERT INTO <relation>
VALUES ( <list of values> );
 Example: add Sydney Greenstreet to the list of stars of The
Maltese Falcon.
INSERT INTO StarsIn
VALUES(‘The Maltese Falcon’, 1942, ’Sydney GreenStreet’ );
4
Specifying Attributes in INSERT
 We may add to the relation name a list of
attributes.
 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, and we want
the system to fill in missing components with NULL or
a default value.
5
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’);
6
Inserting Many Tuples
We may insert the entire result of a query into a
relation, using the form:
INSERT INTO <relation>
( <subquery> );
7
Example: Insert a Subquery
Using Studio and Movie, add to the relation Studio
all movie studios that are mentioned in the relation
Movie, but don’t appear in Studio.
8
Solution
INSERT INTO Studio(name)
(SELECT DISTINCT studioName
FROM Movie
WHERE studioName NOT IN
(SELECT name
FROM Studio));
9
Deletion
To delete tuples satisfying a condition from some
relation:
DELETE FROM <relation>
WHERE <condition>;
10
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’;
11
Example: Delete all Tuples
Make the relation Likes empty:
DELETE FROM Likes;
Note no WHERE clause needed.
12
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 < 10000000;
13
Example: Delete With subqueries
Delete from relation Movie in which Sydney
GreenStreet was a star :
DELETE FROM Movie
WHERE Exists (
SELECT *
FROM StarsIN
WHERE movieTitle title AND
movieYear = year);
14
Updates
To change certain attributes in certain tuples of a
relation:
UPDATE <relation>
SET <list of attribute assignments>
WHERE <condition on tuples>;
15
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);
16
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 we’ll introduce later.
17
Creating (Declaring) a Relation
Simplest form is:
CREATE TABLE <name> (
<list of elements>
);
To delete a relation:
DROP TABLE <name>;
18
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.
19
Example: Create Table
CREATE TABLE MovieStar (
name
CHAR(30),
address
VARCHAR(255),
gender
CHAR(1),
birthdate DATE
);
20
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;
21
Adding Attributes
We may add a new attribute (“column”) to a
relation schema by:
ALTER TABLE <name> ADD
<attribute declaration>;
Example:
ALTER TABLE MovieStar ADD phone CHAR(16);
22
Deleting Attributes
Remove an attribute from a relation schema by:
ALTER TABLE <name>
DROP <attribute>;
Example: we don’t really need the license
attribute for bars:
ALTER TABLE MovieStar DROP birthdate;
23
Default values
When we create or modify tuples, we sometimes
don’t 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.
24
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 SHORTDATETIME DEFAULT ‘0000-00-00’
);
25
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.
26
Example of Sparse Index Files
27
Example of a B+-tree
B+-tree for account file (n = 3)
28
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);
29
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 timeconsuming.
30
Views
A view is a “virtual table” = a relation defined
in terms of the contents of other tables and
views.
Declare by:
CREATE VIEW <name> AS <query>;
Antonym: a relation whose value is really
stored in the database is called a base table.
31
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’;
32
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;
33
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;
34
Define a query based on views and base
tables
Example:
SELECT DISTINCT starName
FROM ParamountMovie, StarsIn
WHERE title = movieTitle AND year = movieYear;
35
Renaming attributes
We can give a view’s attributes names of our own
choosing. For example:
CREATE VIEW MovieProd(movieTitle, prodName) AS
SELECT title, name
FROM Movie, MovieExec
WHERE producerC# = cert#;
36
Delete a view
If a view becomes unuseful, we can delete it. For
instance:
DROP VIEW ParamountMovie;
37
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 Joe’s Bar has some
address, but we don’t know what it is.
 Inapplicable : e.g., the value of attribute spouse for
an unmarried person.
38
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 >, the result is UNKNOWN. The
value UNKNOWN is another truth-value, like
TRUE and FALSE.
39
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
40
Comparing NULL’s 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).
41
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.
42
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.
43
 (7)Find the number of all the projects that didn’t 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.
44
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.
45