A Guide to SQL, Seventh Edition

1

• A Guide to SQL, Seventh Edition

2
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

A Guide to SQL, Seventh Edition
3
Objectives

• Create, use, and drop an index
• Understand and obtain information from the system
  catalog
• Use integrity constraints to control data entry

A Guide to SQL, Seventh Edition
4
Views

• A view is an application programs or individual
  users picture of the database
• Base tables are the existing, permanent tables in
  a relational database
• A view is a derived table because data in it is
  retrieved from the base table

A Guide to SQL, Seventh Edition
5
Views

• To user, a view appears as an actual table, but
  it is not
• A view usually includes less information than the
  full database
• Its use represents a great simplification
• Provides a measure of security by omitting
  sensitive information ?unavailable to user

A Guide to SQL, Seventh Edition
6
Defining Views

• A view is defined by creating a defining query
• Indicates rows and columns to include
• Use CREATE VIEW command
• CREATE VIEW, followed by the name of the view,
  AS, and then a query

A Guide to SQL, Seventh Edition
7
Defining Views

• MySQL does not support views at this time
• Access users would create the defining query and
  save the query with view name
• Data shown in the view does not exist in this
  form, nor will it ever
• Not a temporary table

A Guide to SQL, Seventh Edition
8
A Guide to SQL, Seventh Edition
9
A Guide to SQL, Seventh Edition
10
Queries Involving Views

• If a query involves a view, SQL changes the query
  to select data from the table in the database
  that created the view
• DBMS does not execute the query in the form
• Entered query is merged with original query that
  defined the view to create the final query that
  is executed

A Guide to SQL, Seventh Edition
11
The query executed by SQL
A Guide to SQL, Seventh Edition
12
Renaming Columns In a View

• Column names can be assigned in new view
• Include new column names in parentheses,
  following the name of the view
• Output will display new column names

A Guide to SQL, Seventh Edition
13
A Guide to SQL, Seventh Edition
14
View Joining Two Tables

• Defining query of a view can be any valid SQL
  query
• A view can join two or more tables

A Guide to SQL, Seventh Edition
15
A Guide to SQL, Seventh Edition
16
A View with Statistics

• A view can involve statistics

A Guide to SQL, Seventh Edition
17
Benefits of Views

• Views provide data independence
• Views can often be used even after database
  structure changes
• Different users can view same data differently
• A view can contain only those columns required by
  a given user

A Guide to SQL, Seventh Edition
18
Row-and-Column Subsets

• When attempting to add a row to a row-and-column
  subset view, the DBMS must determine how to enter
  data
• Provided every column not included in a view can
  accept nulls, use INSERT
• Data may be rejected on some attempts when
  problems arise

A Guide to SQL, Seventh Edition
19
Row-and-Column Subsets

• Updates or deletes are not a problem in this view
• Not every row-and-column subsets are updatable

A Guide to SQL, Seventh Edition
20
Joins

• Views that involve joins of base tables can cause
  problems at update

A Guide to SQL, Seventh Edition
21
A Guide to SQL, Seventh Edition
22
Updatable Views

• Updatable view is when a view is derived by
  joining two tables on primary key of each table
• Views involving joins by matching the primary key
  of one table with a column that is not the
  primary key are not updatable
• More severe problems are encountered if neither
  of the join columns is a primary key

A Guide to SQL, Seventh Edition
23
Statistics

• Most difficult views to update are those that
  involve statistics
• You can not add rows to a view that includes
  calculations

A Guide to SQL, Seventh Edition
24
Dropping a View

• Remove a view that is no longer needed with DROP
  VIEW command
• The DROP VIEW command removes only the view
  definition
• Table and data remain unchanged

A Guide to SQL, Seventh Edition
25
Security

• Security is the prevention of unauthorized access
  to a database
• Some may be able to retrieve and update anything
  in a database
• Others may be able to retrieve data but not
  change data
• Others may be able to access only a portion of
  data

A Guide to SQL, Seventh Edition
26
Access Using the GRANT Command

• Main mechanism for providing access to a database
  is the GRANT command
• Database administrator can grant different types
  of privileges to users and revoke them later
• Privileges include rights to select, insert,
  update, index, and delete table data

A Guide to SQL, Seventh Edition
27
Using the REVOKE Command

• Database administrator uses the REVOKE command to
  remove privileges from users
• Format is similar to GRANT command

A Guide to SQL, Seventh Edition
28
Indexes

• An index speeds up the searching of tables
• Similar to an index in a book
• Indexes are more complicated than that shown in
  the figures

A Guide to SQL, Seventh Edition
29
A Guide to SQL, Seventh Edition
30
Indexes

• The DBMS system manages indexes
• User determines the columns on which to build
  indexes
• Disadvantages
• Index occupies disk space
• DBMS must update the index as data is entered

A Guide to SQL, Seventh Edition
31
A Guide to SQL, Seventh Edition
32
Dropping an Index

• The command to drop or delete an index is DROP
  INDEX
• DROP INDEX followed by the name of the index to
  drop
• Permanently deletes index

A Guide to SQL, Seventh Edition
33
Unique Indexes

• To ensure uniqueness of non-primary key data, you
  can create a unique index
• Command is CREATE UNIQUE INDEX
• A unique index will reject any update that would
  cause a duplicate value in the specified column

A Guide to SQL, Seventh Edition
34
System Catalog

• Information about tables in the database is kept
  in the system catalog or data dictionary
• Describes types of items kept in the catalog
• Also describes the way in which you can query it
  to access information about the database structure

A Guide to SQL, Seventh Edition
35
System Catalog

• The DBMS automatically maintains system catalog
• SYSTABLES, information about the tables known to
  SQL
• SYSCOLUMNS, information about the columns within
  those tables
• SYSVIEWS, information about the views that have
  been used

A Guide to SQL, Seventh Edition
36
A Guide to SQL, Seventh Edition
37
A Guide to SQL, Seventh Edition
38
A Guide to SQL, Seventh Edition
39
A Guide to SQL, Seventh Edition
40
Integrity Rules in SQL

• An integrity constraint is a rule for the data in
  the database
• Examples in Premier Products
• A sales reps number must be unique
• The sales rep number for a customer must match an
  exiting sales rep number
• Item classes for parts must be AP, HW, or SG

A Guide to SQL, Seventh Edition
41
Integrity Support

• Integrity support is the process of specifying
  integrity constraints for the database
• Clauses to support three types of integrity
  constraints that can be specified within a CREATE
  TABLE or ALTER TABLE command

A Guide to SQL, Seventh Edition
42
Adding and Changing Integrity Constraints

• An ALTER TABLE command is followed by ADD to
  indicate the addition of the constraint
• To change an integrity constraint, simply enter a
  new constraint
• New constraint will immediately replace the
  original

A Guide to SQL, Seventh Edition
43
Types of Constraints

• Primary keys
• Use ADD PRIMARY KEY clause on ALTER TABLE command
  to add after creating a table
• Foreign keys
• A column in one table whose value matches the
  primary key in another
• Legal values
• The CHECK clause ensures only legal values are
  allowed in a given column

A Guide to SQL, Seventh Edition
44
A Guide to SQL, Seventh Edition
45
A Guide to SQL, Seventh Edition
46
Parent and Child

• When specifying a foreign key, the table
  containing the foreign key is the child
• Table referenced by the foreign key is the parent

A Guide to SQL, Seventh Edition
47
CHECK Clause

• The CHECK clause of the ALTER TABLE command is
  used to ensure only legal values satisfying a
  particular condition are allowed in a given column

Check (Class in (AP, HW, SG) ) or Check
(Class AP OR CLASS HW OR CLASS SG)
A Guide to SQL, Seventh Edition
48
A Guide to SQL, Seventh Edition
49
Summary

• The purpose, creation, use, and benefit of views
• Examined the features related to security
• The purpose, advantages, and disadvantages of
  using indexes
• Add and drop indexes
• System catalog information
• Integrity constraints

A Guide to SQL, Seventh Edition