Transcript Chapter 5 Updating Data

Chapter 6
Database Administration
A Guide to SQL, Sixth Edition
1
Objectives
Understand, create, and drop views
Recognize the benefits of using views
Grant and revoke users' database
privileges
Understand the purpose, advantages,
and disadvantages of using an index
2
Objectives
Create, use, and drop an index
Understand and obtain information from
the system catalog
Use integrity constraints to control data
entry
3
Introduction
Database administration

The process of managing a database
Database administrator

A person or an entire group charged with
managing the database
4
Views
Base tables

Existing, permanent tables in a relational
database
View
A derived table where data is derived from
a base table
 User interacts with the view
 View provides security

5
Views
A view is defined by creating a defining
query
A defining query is a SQL command
that indicates the rows and columns that
will appear in the view
To create a view definition, use the
CREATE VIEW command
6
Views
When a query involves a view, SQL
changes the query to select data from
the table(s) in the database that created
the view
7
Views
The use of views provides several
benefits
Views provide data independence
 The display of data can be customized to
meet each user’s needs
 A view contains only those columns required
by a given user, which has two advantages:

A view greatly simplifies the user’s perception of
the database
 Views provide a measure of security

8
Row-and-Column Subsets
A row-and-column subset view that
contains the primary key of the
underlying base table is updateable
Figure 6.9 illustrates a view that
contains serious update problems due
to the exclusion of the primary key
9
Row-and-Column Subsets
Figure 6.9
10
Joins
In general, views that involve joins of
base tables can cause problems at
update
If two base tables have the same
primary key and the primary key is used
as the join column, updating the
database is not a problem
11
Statistics
CRED_CUST view contains the most
troublesome view of all because it
involves statistics calculated from one
or more base tables
Rows cannot be added to a view that
includes calculations
12
Dropping a View
When a view is no longer needed,
remove it by using the DROP VIEW
command
Example:

DROP VIEW HSEWRES;
13
Security
Security is the prevention of
unauthorized access to the database
Two security mechanisms:
Views
 GRANT command

14
Security
The database administrator can grant
different types of privileges to users and
then revoke them later, if necessary
User privileges can be granted and
revoked using the GRANT and
REVOKE commands, respectively
15
Security
Problem:

User Johnson must be able to retrieve data
from the REP table.
Solution:
GRANT SELECT ON REP TO JOHNSON;
16
Security
The privileges that can be granted are:
SELECT to retrieve date
 UPDATE to change data
 DELETE to delete data
 INSERT to add new data
 INDEX to create an index
 ALTER to change the table structure

17
Security
The REVOKE command is used to
revoke privileges from users
Problem:

User Johnson is no longer allowed to
retrieve data from the REP table.
Solution:
REVOKE SELECT ON REP FROM
JOHNSON;
18
Indexes
An index can be created for any column
or combination of columns in any table
After creating an index, the DBMS uses
it to speed up data retrieval
19
Indexes
Advantages

Makes certain types of retrieval more
efficient
Disadvantages
Occupies disk space and is technically
unnecessary
 Must be updated whenever corresponding
data in the database is updated

20
Creating an Index
Problem:



Create an index named BALIND on the BALANCE
column in the CUSTOMER table.
Create an index named REPNAME on the
combination of the LAST_NAME and
FIRST_NAME columns in the REP table.
Create an index named CREDNAME on the
combination of the CREDIT_LIMIT and
CUSTOMER_NAME columns in the CUSTOMER
table, with the credit limits listed in descending
order.
21
Creating an Index
Solution:
CREATE INDEX BALIND ON
CUSTOMER(BALANCE);
CREATE INDEX REPNAME ON
REP(LAST_NAME, FIRST_NAME);
CREATE INDEX CREDNAME ON
CUSTOMER(CREDIT_LIMIT DESC,
CUSTOMER_NAME);
22
Dropping an Index
The command used to drop (delete) an
index is DROP INDEX
Problem:

Delete the CREDNAME index
Solution:

DROP INDEX CREDNAME;
23
Unique Indexes
When a column that is not the primary
key requires unique values, create a
unique index
A unique index is created by using the
CREATE UNIQUE INDEX command
24
Unique Indexes
Problem:

Create a unique index named SSN on the
SOC_SEC_NUM column of the REP table
Solution:

CREATE UNIQUE INDEX SSN ON
REP(SOC_SEC_NUM);
25
System Catalog
Information about tables in the database
is kept in the system catalog or the data
dictionary
System catalog contains tables:
SYSTABLES (in Oracle: DBA_TABLES)
 SYSCOLUMNS (in Oracle:
DBA_TAB_TABLES)
 SYSVIEWS (in Oracle: DBA_VIEWS)

26
System Catalog
The system catalog is a relational
database
Information can be retrieved by using
the same types of queries which are
used to retrieve data in a relational
database
27
System Catalog
The DBMS updates the system catalog
automatically
Users should not use SQL queries to
update the catalog directly because this
might produce inconsistent information
28
Integrity Rules in SQL
An integrity constraint is a rule for the
data in the database
If a user enters data in the database
that violates any of the integrity
constraints, the database will develop
serious problems
29
Integrity Rules in SQL
Integrity support is the process of
specifying integrity constraints for a
database that the DBMS will enforce
The types of constraints supported in
SQL are:
Primary keys
 Foreign keys
 Legal values

30
Integrity Rules in SQL
In most cases, a table’s primary key is
indicated when the table is created
To add a primary key after creating a
table, use the ADD PRIMARY KEY
clause of the ALTER TABLE command
31
Integrity Rules in SQL
A foreign key is a column in one table whose
values match the primary key in another table
When a table contains a foreign key, it is
identified by using the ADD FOREIGN KEY
clause of the ALTER TABLE command
After creating a foreign key, the DBMS will
reject any update that violates the foreign key
constraint
32
Integrity Rules in SQL
The CHECK clause of the ALTER
TABLE command is used to ensure that
only legal values satisfying a particular
condition are allowed in a given column
If a user enters data that violates the
condition, the DBMS rejects the update
automatically
33
Summary
A view contains data that is derived from
existing base tables when users attempt to
access the view
GRANT command: gives users access
privileges to data in the database
REVOKE command: terminates previously
granted privileges
You can create and use an index to make
data retrieval more efficient
34
Summary
The DBMS maintains information about the
tables, columns, indexes, and other system
elements in the system catalog (catalog) or
data dictionary
Integrity constraints are rules that the data in
the database must follow to ensure that only
legal values are accepted in specified
columns and that primary and foreign key
values match between tables
35
SQL Project Six Completed
Good Luck
H. Zamanzadeh
36