SQL Chapter 6
Database Administration
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
Create, use, and drop an index
Understand and obtain information
from the system catalog
Use integrity constraints to
control data entry
Introduction
Database administration
l The process of managing a database
Database administrator
l A person or an entire group charged with managing the database
Views
Base tables
l Existing, permanent tables in a relational database
View
l A derived table where data is derived from a base table
l User interacts with the view
l View provides security
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
When a query involves a view, SQL
changes the query to select data from the table(s) in the database that created
the view
The use of views provides several
benefits
l Views provide data independence
l The display of data can be customized to meet each user’s needs
l 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
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
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
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
Dropping a View
When a view is no longer needed,
remove it by using the DROP VIEW command
Example:
l DROP VIEW HSEWRES;
Security
Security is the prevention of
unauthorized access to the database
Two security mechanisms:
l Views
l GRANT command
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
Security
Problem:
l User Johnson must be able to retrieve data from the REP table.
Solution:
GRANT SELECT ON REP TO JOHNSON;
Security
The privileges that can be granted
are:
l SELECT to retrieve date
l UPDATE to change data
l DELETE to delete data
l INSERT to add new data
l INDEX to create an index
l ALTER to change the table structure
Security
The REVOKE command is used to
revoke privileges from users
Problem:
l User Johnson is no longer allowed to retrieve data from the REP table.
Solution:
REVOKE SELECT ON REP FROM JOHNSON;
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
Advantages
l Makes certain types of retrieval more efficient
Disadvantages
l Occupies disk space and is technically unnecessary
l Must be updated whenever corresponding data in the database is updated
Creating an Index
Problem:
l Create an index named BALIND on the BALANCE column in the CUSTOMER table.
l Create an index named REPNAME on the combination of the LAST_NAME and FIRST_NAME columns in the REP table.
l 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.
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);
Dropping an Index
The command used to drop (delete)
an index is DROP INDEX
Problem:
l Delete the CREDNAME index
Solution:
l DROP INDEX CREDNAME;
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
Unique Indexes
Problem:
l Create a unique index named SSN on the SOC_SEC_NUM column of the REP table
Solution:
l CREATE UNIQUE INDEX SSN ON REP(SOC_SEC_NUM);
System Catalog
Information about tables in the
database is kept in the system catalog or the data dictionary
System catalog contains tables:
l SYSTABLES (in Oracle: DBA_TABLES)
l SYSCOLUMNS (in Oracle: DBA_TAB_TABLES)
l SYSVIEWS (in Oracle: DBA_VIEWS)
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
The DBMS updates the system
catalog automatically
Users should not use SQL queries
to update the catalog directly because this might produce inconsistent
information
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
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:
l Primary keys
l Foreign keys
l Legal values
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
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
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
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
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
SQL Project Six Completed
Good Luck
H. Zamanzadeh