1 / 49

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.

Download Presentation

A Guide to SQL, Seventh Edition

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. A Guide to SQL, Seventh Edition

  2. 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

  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 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

  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 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

  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

More Related