A Guide to SQL, Seventh Edition

A Guide to SQL, Seventh Edition

Objectives • Understand, create, and drop views • Recognize the benefits of using views • Grant and revoke user’s database privileges • Understand the purpose, advantages, and disadvantages of using an index A Guide to SQL, Seventh Edition

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

Views • A view is an application program’s or individual user’s 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

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

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

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

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

The query executed by SQL A Guide to SQL, Seventh Edition

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

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

A View with Statistics • A view can involve statistics A Guide to SQL, Seventh Edition

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

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

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

Joins • Views that involve joins of base tables can cause problems at update A Guide to SQL, Seventh Edition

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

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

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

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

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

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

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

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

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

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

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

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

Integrity Rules in SQL • An integrity constraint is a rule for the data in the database • Examples in Premier Products • A sales rep’s 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

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

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

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

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

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

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

A Guide to SQL, Seventh Edition