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

Concepts of Database Management, Fifth Edition PowerPoint PPT Presentation


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

Concepts of Database Management, Fifth Edition

Chapter 4:

The Relational Model 3:

Advanced Topics


Objectives

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

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

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

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

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 view1

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


Concepts of database management fifth 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

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


Concepts of database management fifth 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

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

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

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


Concepts of database management fifth edition

Figure 4.10: Customer Table with Record Numbers

Concepts of Database Management, 5th Edition


Concepts of database management fifth edition

Figure 4.11: Customer Table Index on CustomerNum

Concepts of Database Management, 5th Edition


Concepts of database management fifth edition

Figure 4.12:

Table Indexes on CreditLimit, RepNum

Concepts of Database Management, 5th Edition


Pros cons of indexes

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

SQL to Create Index

CREATE INDEX CustomerName

ON Customer (CustomerName)

;

Concepts of Database Management, 5th Edition


Creating indexes

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

SQL to Delete Index

DROP INDEX RepBal

;

Concepts of Database Management, 5th Edition


Concepts of database management fifth edition

Figure 4.13: Index on Single Field in Access

Concepts of Database Management, 5th Edition


Concepts of database management fifth edition

Figure 4.14: Index on Multiple Fields in Access

Concepts of Database Management, 5th Edition


Security

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

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

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


Concepts of database management fifth edition

Figure 4.15: Primary Key in Access

PRIMARY KEY (CustomerNum)

Concepts of Database Management, 5th Edition


Concepts of database management fifth edition

Figure 4.16: Multi-Field Primary Key in Access

PRIMARY KEY (OrderNum, PartNum)

Concepts of Database Management, 5th Edition


Referential integrity

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


Concepts of database management fifth 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 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


Concepts of database management fifth edition

Figure 4.18: Specifying Referential Integrity

Concepts of Database Management, 5th Edition


Enforcing referential integrity

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


Concepts of database management fifth edition

Figure 4.19: Violating Referential Integrity on Adding

Concepts of Database Management, 5th Edition


Legal values integrity

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

Validation Rule in Access

Concepts of Database Management, 5th Edition


Structure changes

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

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


Concepts of database management fifth edition

Figure 4.22: Add Field in Access

Concepts of Database Management, 5th Edition


Concepts of database management fifth edition

Figure 4.23: Change Field Characteristic in Access

Concepts of Database Management, 5th Edition


Structure changes delete

Structure Changes - Delete

Deleting field

ALTER TABLE Part

DELETE Warehouse

;

Delete SQL Table

DROP TABLE SmallCust

;

Concepts of Database Management, 5th Edition


Concepts of database management fifth edition

Figure 4.24: Delete Field in Access

Concepts of Database Management, 5th Edition


Concepts of database management fifth edition

Figure 4.25: Delete Table in Access

Concepts of Database Management, 5th Edition


System catalog

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

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


Concepts of database management fifth edition

Figure 4.26: Systables Table

Concepts of Database Management, 5th Edition


Summary

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


Summary1

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


Summary2

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