Concepts of Database Management, Fifth Edition

1
Concepts of Database Management, Fifth Edition

• Chapter 4
• The Relational Model 3
• Advanced Topics

2
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

3
Views

• A View is an application programs or individual
  users picture of the database
• An individual can use a view to examine table
  data, its use can represent a great
  simplification.

4
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

5
Example 1 Create a Houseware view

• Suppose Juan is interested in the part number,
  part description, units on hand, and unit price
  for those Premier Products parts that are in
  class HW.
• He is not interest in any of the other fields in
  the Part table, nor is he interested in any of
  the rows that correspond to parts in other item
  classes.

6
Example 1 Create a Houseware view

• As far as Juan is concerned, the entire database
  is just the darker shaded portion of the Part
  table

7
Example 1 Create a Houseware view

• Although you cannot change the structure of the
  Part table and omit some of its rows just for
  Juan, you can do the next best thing. You can
  provide him a view that consists of precisely the
  rows and fields he needs to access.

8
Figure 4.1 SQL to Create View
CREATE VIEW Housewares AS SELECT PartNum,
Description, OnHand, Price FROM Part WHERE
ClassHW
9
Figures 4.3 Access Query Design of the
Housewares View
10
Figures 4.4 Access Housewares view datasheets
11
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.

12
Example 2. Query on a Housewares View

• Suppose Juan needs to retrieve all fields in the
  Housewares view where OnHand values is less than
  25.

13
Example 2. Query on a Housewares View

• Selects data only from Tables created in the view

Simplification
SELECT FROM Housewares WHERE OnHandlt 25
Actually executes as
SELECT PartNum, Description, OnHand, Price FROM
Part WHERE ClassHW AND OnHandlt 25
Housewares view
14
Example 3 Access Query Design View with Changed
Field Names

• Suppose that Francesca needs to know the number
  and name of each sales rep, along with the number
  and name of the customers represented by each
  sales rep.
• Suppose she would also like these fields to be
  named SNum, SLast, SFirst, CNum, And CName,
  respectively.

15
Example 3 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

CREATE VIEW SalesCust (SNum, SLast, SFirst,
CNum, CName) AS SELECT Rep.RepNum, LastName,
FirstName, CustomerNum, CustomerName FROM Rep,
Customer WHERE Rep.RepNumCustomer.RepNum
16
Figures 4.8 Access Query Design of SalesCust
View
17
Figures 4.9 Datasheet for the SalesCust View
18
Example 4 Query on a SalesCust view

• Suppose Francesca needs to retrieve the number
  and name of all customer in the SalesCust view
  with part number values is greater than 20.

SELECT CNum, CName FROM SalesCust WHERE SNum gt
20
19
Advantages of Views

• Provides data independence
• Same data viewed by different users in different
  ways
• Contains only information required by a given
  user

20
Exercises

• Using data from the Premiere Products database,
  define a view named PartOrder. It consist of the
  part number, description, price, order number,
  order date, number ordered, and quoted price for
  all order lines currently on file.
• Using SQL, write the view definition for
  PartOrder.
• Write an SQL query to retrieve the part number,
  description, order number, and quoted price for
  all orders in the PartOrder view for parts with
  quoted prices that exceed 100.
• Convert the query you wrote in Question 3b to the
  query that the DBMS will actually execute.

21
Indexes

• The problem
• If you want to find a discussion of a given topic
  in a book, you could can the entire book from
  start to finish, looking for references to the
  topic you had in mind.
• If the book had a good index, you could use it to
  quickly identify the pages on which your topics
  is discussed.

22
(No Transcript)
23
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

24
Figure 4.10 Customer Table with Record Numbers
25
Figure 4.11 Index for the Customer Table on
CustomerNum

• The index has two fields.
• The first field contains a customer number and
  the second field contains the number of the
  record on which the customer number is found.

CREATE INDEX NameIndex1 ON Customer
(CustomerNum)
26
Figure 4.12 Table Indexes on CreditLimit.

• If you wanted to quickly access all customer with
  a specific credit limit. You might choose to
  create and use an index on credit limit.

CREATE INDEX NameIndex2 ON Customer
(CreditLimit)
27
Figure 4.12 Table Indexes on RepNum

• If you wanted to quickly access all customer that
  are represented by a specific sales rep. You
  might choose to create and use an index on sales
  rep number.

CREATE INDEX NameIndex3 ON Customer (RepNum)
28
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

29
SQL to Create Index
Index Name
CREATE INDEX NameIndex4 ON Customer
(CustomerName)
Table
Field (s)
SQL to Delete Index
DROP INDEX NameIndex4
30
Figure 4.13 Index on Single Field in Access
31
Creating multiple-field indexes

• To create an index named RepBal with the keys
  RepNum and Balance and with the balances listed
  in descending order, you could use the following
  SQL command

CREATE INDEX RepBal ON Customer (RepNum, Balance
DESC)
32
Creating multiple-field indexes

• To create multiple-field indexes in Access, click
  the Indexes button on the toolbar, enter a
  name for the index, and then select the fields
  that make up the index key.

33
Figure 4.14 Index on Multiple Fields in Access
34
Exercises

• Create the following indexes.
• Create an index named ParIndex1 on the ParNum
  field in the OrderLine table.

• Create an index named ParIndex2 on the Warehouse
  field in the Part table.

• Create an index named ParIndex3 on the Warehouse
  and Class fields in the Part table.

• Create an index named ParIndex4 on the Warehouse
  and OnHand fields in the Part table and list
  units on hand in descending order.

35
Integrity Rules

• A relational DBMS must enforce two important
  integrity rules
• Entity integrity and
• Referential integrity.
• Defined by Dr. E.F. Codd
• Both rules are related to primary keys and
  foreign keys.

36
Entity integrity

• The primary key cannot allow null values.
• Entity integrity guarantees that each record will
  indeed have its own identity.
• 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

37
Figure 4.15 Primary Key in Access
PRIMARY KEY (CustomerNum)
38
Figure 4.16 Multi-Field Primary Key in Access
PRIMARY KEY (OrderNum, PartNum)
39
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

40
Figure 4.17 Relationships Window to Relate
Tables in Access
FOREIGN KEY (RepNum) REFERENCES Rep
41
Cascade Delete

• Cascade delete - ensures that the deletion of a
  master record deletes all records in sub tables
  related to it.
• For example The deletion of a sales rep record
  also deletes all customer records related to that
  sales rep.

42
Cascade Update

• Cascade update ensures that changes made to the
  primary key of the master table are also made in
  the related records
• For example The changes made to the primary key
  of a sales rep record are also made in the
  related customer record.

43
Figure 4.18 Specifying Referential Integrity
44
Legal-Values Integrity

• In Addition to the two integrity rules, there is
  a third type of integrity, called legal-values
  integrity. Often there is a particular set of
  values, called the legal values, that are
  allowable in a field.
• Use SQL CHECK clause
• Validation rule in Access, a rule that data
  entered into a field must follow

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

45
Figure 4.19 Validation Rule in Access
46
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
• Made using the SQL ALTER TABLE command

47
Figure 4.22 Add Field in Access
ALTER TABLE Customer ADD CustType CHAR(1)
48
Figure 4.23 Change Field Characteristic in Access
ALTER TABLE Customer CHANGE COLUMN CustomerName
TO CHAR(40)
49
Figure 4.24 Delete Field in Access
ALTER TABLE Part DELETE Warehouse
50
Figure 4.25 Delete Table in Access
DROP TABLE SmallCust
51
Summary

• Views - used to give each user his or her own
  view of the data in a database
• Indexes are often used to facilitate data
  retrieval from the database
• Entity integrity is the property that states that
  no field that is part of the primary key can
  accept null values

52
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
• The ALTER TABLE command allows you to add fields
  to a table, delete fields, or change the
  characteristics of fields
• The DROP TABLE command lets you delete a table
  from a database