Concepts of database management fifth edition
Sponsored Links
This presentation is the property of its rightful owner.
1 / 48

Concepts of Database Management, Fifth Edition PowerPoint PPT Presentation


  • 125 Views
  • Uploaded on
  • Presentation posted in: General

Concepts of Database Management, Fifth Edition. Chapter 4: The Relational Model 3: Advanced Topics. Objectives. Define, describe, and use views Use indexes to improve database performance Examine the security features of a database management system (DBMS)

Download Presentation

Concepts of Database Management, Fifth 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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -

Presentation Transcript


Concepts of Database Management, Fifth Edition

Chapter 4:

The Relational Model 3:

Advanced Topics


Objectives

  • Define, describe, and use views

  • Use indexes to improve database performance

  • Examine the security features of a database management system (DBMS)

  • Discuss entity, referential, and legal-values integrity

  • Make changes to the structure of a relational database

  • Define and use the system catalog

Concepts of Database Management, 5th Edition


Views

  • Application program’s or individual user’s picture of the database

  • Less involved than full database

  • Offers simplification

  • Provides measure of security

    • Sensitive tables or columns omitted where not appropriate

Concepts of Database Management, 5th Edition


SELECT Command

  • Called the defining query

  • Indicates precisely what to include in the view

  • Query acts as a sort of window into the database

  • Does not produce a new table, only the view of the table

Concepts of Database Management, 5th Edition


Figure 4.1: SQL to Create View

CREATE VIEW Housewares AS

SELECT PartNum, Description, OnHand, Price

FROM Part

WHERE Class=‘HW’

;

Concepts of Database Management, 5th Edition


Query on a View

  • With a query that involves a view, the DBMS changes the query to one that selects data from table(s) in the database that created the view

  • The DBMS merges the query with the query that defines the view to form the query that is actually executed

  • One advantage of this approach is that the view never exists in its own right so any update to the table is immediately available in the view

  • If the view were a table, this would not be the case

Concepts of Database Management, 5th Edition


Query on a View

  • Selects data only from Tables created in the view

  • Query is merged with query used to create view

SELECT *

FROM Housewares

WHERE OnHand< 25

;

Actually executes as

SELECT PartNum, Description, OnHand, Price

FROM Part

WHERE Class=‘HW’

AND OnHand< 25

;

Concepts of Database Management, 5th Edition


Figures 4.3 - 4.4: Access Query Design of View

Concepts of Database Management, 5th Edition


Access Query Design View with Changed Field Names

  • SQL can be used to change the field names in a view by including the new field names in the CREATE VIEW statement

  • The CREATE VIEW statement would be:

CREATE VIEW

SalesCust (Snum, SLast, SFirst, Cnum, CName) AS

SELECT Rep.RepNum, LastName, FirstName,

CustomerNum, CustomerName

FROM Rep, Customer

WHERE Rep.RepNum=Customer.RepNum

;

Concepts of Database Management, 5th Edition


Figures 4.5-4.6: Access Query Design of View

with Changed Field Names

Concepts of Database Management, 5th Edition


Row and Column Subset View

  • Consists of a subset of the rows and columns in some individual table

  • Because the query can be any SQL query, a view could also join two or more tables

Concepts of Database Management, 5th Edition


Advantages of Views

  • Provides data independence

  • Same data viewed by different users in different ways

  • Contains only information required by a given user

Concepts of Database Management, 5th Edition


Indexes

  • Conceptually similar to book index

  • Increases data retrieval efficiency

  • Automatically assigns record numbers

  • Used by DBMS, not by users

  • Fields on which index built called Index Key

Concepts of Database Management, 5th Edition


Figure 4.10: Customer Table with Record Numbers

Concepts of Database Management, 5th Edition


Figure 4.11: Customer Table Index on CustomerNum

Concepts of Database Management, 5th Edition


Figure 4.12:

Table Indexes on CreditLimit, RepNum

Concepts of Database Management, 5th Edition


Pros/Cons of Indexes

  • Can be added or dropped without loss of function

  • Can make retrieval more efficient

  • Occupies space that might be required for other functions

  • DBMS must update index whenever corresponding data are updated

Concepts of Database Management, 5th Edition


SQL to Create Index

CREATE INDEX CustomerName

ON Customer (CustomerName)

;

Concepts of Database Management, 5th Edition


Creating Indexes

  • Single-field index – an index whose key is a single field

  • Multiple-field index

    • An index with more than one key field

    • List the most important key first

    • If data for either key appears in descending order, follow the field name with the letters DESC

Concepts of Database Management, 5th Edition


SQL to Delete Index

DROP INDEX RepBal

;

Concepts of Database Management, 5th Edition


Figure 4.13: Index on Single Field in Access

Concepts of Database Management, 5th Edition


Figure 4.14: Index on Multiple Fields in Access

Concepts of Database Management, 5th Edition


Security

  • Prevention of unauthorized access to database

  • Two SQL security mechanisms

    • GRANT provides privileges to users

    • REVOKE removes privileges from users

GRANT SELECT ON Customer TO JONES

;

REVOKE SELECT ON Customer FROM JONES

;

Concepts of Database Management, 5th Edition


Integrity Rules

  • Related to foreign keys and primary keys

  • Defined by Dr. E.F. Codd

  • Entity integrity

    • No field that is part of the primary key may accept null values

Concepts of Database Management, 5th Edition


Integrity Rules (con’t)

  • To specify primary key, enter a PRIMARY KEY clause in either an ALTER TABLE or a CREATE TABLE command

  • Foreign key – a field (or collection of fields) in a table whose value is required to match the value of the primary key for a second table

Concepts of Database Management, 5th Edition


Figure 4.15: Primary Key in Access

PRIMARY KEY (CustomerNum)

Concepts of Database Management, 5th Edition


Figure 4.16: Multi-Field Primary Key in Access

PRIMARY KEY (OrderNum, PartNum)

Concepts of Database Management, 5th Edition


Referential integrity

  • If Table A contains a foreign key matching the primary key of Table B, then values must match for some row in Table B or be null

  • Usually a foreign key is in a different table from the primary key it is required to match

  • The only restriction is that the foreign key must have a name that is different from the primary key because the fields are in the same table

Concepts of Database Management, 5th Edition


Figure 4.17: Relationships Window to

Relate Tables in Access

FOREIGN KEY (RepNum) REFERENCES Rep

Concepts of Database Management, 5th Edition


Cascade Delete and Update

  • Cascade delete - ensures that the deletion of a master record deletes all records in sub tables related to it

  • Cascade update – ensures that changes made to the primary key of the master table are also made in the related records

Concepts of Database Management, 5th Edition


Figure 4.18: Specifying Referential Integrity

Concepts of Database Management, 5th Edition


Enforcing Referential Integrity

  • With referential integrity enforced, users are not allowed to enter a record that does not match any sales rep currently in the Rep table

  • An error message, such as the one shown in Figure 4.19, appears when an attempt is made to enter an invalid record

Concepts of Database Management, 5th Edition


Figure 4.19: Violating Referential Integrity on Adding

Concepts of Database Management, 5th Edition


Legal-Values Integrity

  • States no record can exist with field values other than legal ones

  • Use SQL CHECK clause

  • Validation rule – in Access, a rule that data entered into a field must follow

  • Validation – in Access, text to inform the user of the reason for the rejection when the user attempts to enter data that violates the rule

CHECK (CreditLimit IN (5000, 7500, 10000, 15000)) ;

Concepts of Database Management, 5th Edition


Validation Rule in Access

Concepts of Database Management, 5th Edition


Structure Changes

  • Can change the database structure

    • By adding and removing tables and fields

    • By changing the characteristics of existing fields

    • By creating and dropping indexes

  • The exact manner in which these changes are accomplished varies from one system to another

  • Most systems allow all of these changes to be made quickly and easily

  • Made using the SQL ALTER TABLE command

Concepts of Database Management, 5th Edition


Structure Changes – Add and Change

Adding new field

ALTER TABLE Customer

ADD CustType CHAR(1)

;

Changing field properties

ALTER TABLE Customer

CHANGE COLUMN CustomerName TO CHAR(50)

;

Concepts of Database Management, 5th Edition


Figure 4.22: Add Field in Access

Concepts of Database Management, 5th Edition


Figure 4.23: Change Field Characteristic in Access

Concepts of Database Management, 5th Edition


Structure Changes - Delete

Deleting field

ALTER TABLE Part

DELETE Warehouse

;

Delete SQL Table

DROP TABLE SmallCust

;

Concepts of Database Management, 5th Edition


Figure 4.24: Delete Field in Access

Concepts of Database Management, 5th Edition


Figure 4.25: Delete Table in Access

Concepts of Database Management, 5th Edition


System Catalog

  • Information about database kept in system catalog

  • Maintained by DBMS

  • Example catalog has two tables

    • Systables – information about the tables known to SQL

    • Syscolumns – information about the columns or fields within these tables

Concepts of Database Management, 5th Edition


System Catalog (con’t.)

  • Other possible tables

    • Sysindexes – information about the indexes that are defined on these tables

    • Sysviews – information about the views that have been created

Concepts of Database Management, 5th Edition


Figure 4.26: Systables Table

Concepts of Database Management, 5th Edition


Summary

  • Views - used to give each user his or her own view of the data in a database

  • View is defined in structured query language (SQL) by using a defining query

  • Indexes are often used to facilitate data retrieval from the database

  • Security is provided in SQL systems using the GRANT and REVOKE commands

  • Entity integrity is the property that states that no field that is part of the primary key can accept null values

Concepts of Database Management, 5th Edition


Summary

  • Referential integrity - property stating that the value in any foreign key field must either be null or match an actual value in the primary key field of another table

  • Legal-values integrity is the property that states that the value entered in a field must be one of the legal values

  • The ALTER TABLE command allows you to add fields to a table, delete fields, or change the characteristics of fields

Concepts of Database Management, 5th Edition


Summary

  • The DROP TABLE command lets you delete a table from a database

  • The system catalog is a feature of many relational DBMSs that stores information about the structure of a database

Concepts of Database Management, 5th Edition


  • Login