Chapter 6 Database Administration

1
Chapter 6Database Administration
2
Objectives

• Understand, create, and drop views
• Recognize the benefits of using views
• Grant and revoke users database privileges
• Create, use, and drop an index
• Understand the purpose, advantages, and
  disadvantages of using an index

3
Objectives

• Understand and obtain information from the system
  catalog
• Use integrity constraints to control data entry

4
Introduction

• Database administration
• The process of managing a database
• Database administrator
• A person or an entire group charged with managing
  the database

5
Views

• Base tables
• Existing, permanent tables in a relational
  database
• View
• A derived table where data is derived from a base
  table
• User interacts with the view
• View provides security

6
Views

• A view is defined by creating a defining query
  (SQL command that indicates the rows and columns
  that will appear in the view)

7
Example 1

• Define a view named HOUSEWARES that consists of
  the part number, part description, units on hand,
  and unit price of all parts in item class HW

8
Creating the HOUSEWARES View
9
HOUSEWARES View
10
Views

• Data does not exist in the form of the view
• Query acts as a window into the database (see
  Figure 6.3)
• As far as the user is concerned the entire
  database consists of the dark shaded portion of
  the PART table

11
Premiere Products Sample Data
12
Using the HOUSEWARES View
13
Using the HOUSEWARES View

• In Figure 6.4, the query first is merged with the
  query that defines the view, producing the
  following statement
• SELECT PART_NUMBER, PART_DESCRIPTION,
  UNITS_ON_HAND, UNIT_PRICE
• FROM PART
• WHERE ITEM_CLASS HW
• AND UNITS_ON_HAND gt 100

14
CREATE VIEW Format

• The formulation of the view definition is
• CREATE ltview namegt AS ltquerygt
• The defining query can be any valid SQL query

15
Example 2

• Define a view named HOUSEWARES that consists of
  the part number, part description, units on hand,
  and unit price of all parts in item class HW
• In this view, rename the PART_NUMBER column to
  NUM, the PART_DESCRIPTION column to DSC, the
  UNITS_ON_HAND column to OH, and the UNIT_PRICE
  column to PRCE

16
Renaming Columns When Creating a View
17
HOUSEWARES VIEW

• The HOUSEWARES view is an example of
  row-and-column subset view because it consists of
  a subset of the rows and columns in some base
  table

18
Example 3

• Define a view named SALES_CUST
• The view consists of the sales rep number (named
  SNUMB), sales rep last name (named SLAST), sales
  rep first name (named SFIRST), customer number
  (named CNUMB), customer last name (named CLAST),
  and customer first name (named CFIRST) for all
  sales reps and matching customers in the
  SALES_REP and CUSTOMER tables

19
Creating the SALES_CUST View
20
Using the SALES_CUST View
21
Example 4

• Define a view named CRED_CUST that consists of
  each credit limit (CREDIT_LIMIT) and the number
  of customers who have this credit limit
  (NUMBER_CUSTOMERS)

22
Creating the CRED_CUST View
23
View Benefits

• Views provide data independence
• Different users can view the same data in
  different ways because each user has their own
  view
• It can contain only those columns required by a
  given user
• Greatly simplifies user perception of database
• Furnishes a measure of security since user as
  access to data contained only in their view

24
Row-and-Column Subsets

• A row-and-column subset view that contains the
  primary key of the underlying base table is
  updateable
• Figure 6.9 illustrates a view that contains
  serious update problems due to the exclusion of
  the primary key

25
Creating the SALES_CRED View
26
Joins

• In general, views that involve joins of base
  tables can cause problems at update
• If two base tables have the same primary key and
  the primary key is used as the join column,
  updating the database is not a problem

27
SLSREP_DEMO and SLSREP_FIN tables
28
Creating the SLSREP View
29
Using the SLSREP View
30
SALES_REP vs. SALES_CUST

• SALES_REP view is updateable
• Add, change, delete do not cause problems
• View is derived from joining two base tables on
  the primary key of each table
• SALES_CUST view is not updateable
• View is derived by joining two tables by matching
  the primary key of one table with a column that
  is not the primary key in the other table
• Even more severe problems are encountered if
  neither of the join columns is a primary key
  column

31
Statistics

• CRED_CUST view contains the most troublesome view
  of all because it involves statistics calculated
  from one or more base tables
• Rows cannot be added to a view that includes
  calculations

32
Example 5

• The SLSREP view is no longer necessary, so remove
  it

33
Dropping a View
34
DROP View Command

• Deletes a view definition only
• Table and data on which view is based still exists

35
Security

• Security is the prevention of unauthorized access
  to the database
• Two security mechanisms
• Views
• GRANT command

36
GRANT and REVOKE Commands

• Grant different privileges to users and revoke
  them later, if necessary
• Ability to select rows from a table
• Insert new rows
• Update existing rows

37
Example 6

• User Jones must be able to retrieve data from the
  SALES_REP table
• GRANT SELECT ON SALES_REP TO JONES

38
Example 7

• Users Smith and Brown must be able to add new
  parts to the PART table.
• GRANT INSERT ON PART TO SMITH, BROWN

39
Example 8

• User Anderson must be able to change the last
  name, first name, and street address of customers
• GRANT UPDATE ON CUSTOMER (LAST, FIRST, STREET) TO
  ANDERSON

40
Example 9

• User Martin must be able to delete order lines
• GRANT DELETE ON ORDER_LINE TO MARTIN

41
Example 10

• Every user must be able to retrieve part numbers,
  part descriptions, and item classes
• GRANT SELECT ON PART (PART_NUMBER,
  PART_DESCRIPTION, ITEM_CLASS) TO PUBLIC

42
Example 11

• User Roberts must be able to create an index on
  the SALES_REP table
• GRANT INDEX ON SALES_REP TO ROBERTS

43
Example 12

• User Thomas must be able to change the structure
  of the CUSTOMER table
• GRANT ALTER ON CUSTOMER TO THOMAS

44
Example 13

• User Wilson must have all privileges for the
  SALES_REP, CUSTOMER, and ORDERS tables
• GRANT ALL ON SALES_REP, CUSTOMER, ORDERS TO
  WILSON

45
Privileges

• Privileges that can be granted are
• SELECT
• UPDATE
• DELETE
• INSERT
• INDEX
• For a user to pass the privilege on to others the
  database administrator must use GRANT statement
  and include WITH GRANT OPTION

46
GRANT and REVOKE Format

• GRANT ltprivilegegt TO ltusergt
• REVOKE ltprivilegegt FROM ltusergt
• WITH GRANT OPTION is not meaningful part of
  REVOKE command
• Revoke cascades so privileges granted with the
  WITH GRANT OPTION are revoked for all who were
  granted privileges

47
Example 14

• User Jones is no longer allowed to retrieve data
  from the SALES_REP table
• REVOKE SELECT ON SALES_REP FROM JONES

48
Example 15

• Allow sales rep number 03 (Mary Jones) to access
  any data concerning the customers she represents,
  but do not permit her to access data concerning
  any other customer
• CREATE VIEW SLSR3CST AS
• SELECT
• FROM CUSTOMER
• WHERE SLSREP_NUMBER 03
• GRANT SELECT ON SLSR3CST TO MARY JONES

49
Indexes

• Create and use an index to speed the searching
  process

50
CUSTOMER Table With Row Numbers
51
Index for CUSTOMER Table on CUSTOMER_NUMBER Column
52
Index for CUSTOMER Table on CREDIT_LIMIT Column
53
Indexes

• Advantages
• Makes certain types of retrieval more efficient
• Disadvantages
• occupies disk space and is technically
  unnecessary
• must be updated whenever corresponding data in
  the database is updated

54
Example 16

• Create an index named BALIND on the BALANCE
  column in the CUSTOMER table
• Create an index named CUSTNAME on the combination
  of the LAST and FIRST columns in the CUSTOMER
  table
• Create an index named CREDNAME on the combination
  of the CREDIT_LIMIT, LAST, FIRST columns in the
  CUSTOMER table, with the credit limits listed in
  descending order

55
Creating Indexes
56
Dropping an Index

• Command to delete an index is DROP INDEX
• DROP INDEX CREDNAME

57
Unique Indexes

• When a column that is not the primary key
  requires unique values, create a unique index
  using the CREATE UNIQUE INDEX command
• CREATE UNIQUE INDEX SSN ON CUSTOMER
  (SOC_SEC_NUMBER)

58
The System Catalog

• Information concerning tables known to the DBMS
  is kept in the system catalog, or the data
  dictionary
• System catalog contains tables
• SYSTABLES (in Oracle DBA_TABLES)
• SYSCOLUMNS (in Oracle DBA_TAB_TABLES)
• SYSVIEWS (in Oracle DBA_VIEWS)

59
System Catalog

• System catalog is a relational database
• Users need special privileges to view the data in
  the system catalog

60
Example 17

• List the name of every table for which the owner
  (creator of the table) is PRATT

61
Tables Owned by PRATT
62
Example 18

• List the name of every view whose owner is PRATT

63
Views Owned by PRATT
64
Example 19

• List every column and its associated data type in
  the CUSTOMER table whose owner is PRATT

65
Columns in the Customer Table
66
Example 20

• List every table owned by PRATT that contains a
  column named CUSTOMER_NUMBER

67
Names of Tables Owned by PRATT That Include the
CUSTOMER_NUMBER Column
68
Integrity in SQL

• An integrity constraint is a rule that the data
  in the database must follow
• Examples
• No two sales reps can have the same sales rep
  number
• The sales rep number for a customer must match
  the number of a sales rep currently in the
  database
• Item classes for parts must be AP, HW, or SG

69
Integrity Support

• To prevent violations, the DBMS provides
  integrity support
• Types of constraints supported in SQL
• Legal values
• Primary keys
• Foreign keys
• CHECK clause ensures that only legal values that
  satisfy a particular condition are allowed in a
  given column

70
CHECK Clause

• To ensure the only legal values for item class
  are AP, HW, or SG
• CHECK (ITEM_CLASS IN (AP, HW, SG))
• OR
• CHECK (ITEM_CLASS AP OR ITEM_CLASS HW OR
  ITEM_CLASS SG)

71
ADD PRIMARY KEY Clause

• To indicate that SLSREP_NUMBER is the primary key
  for the SALES_REP table
• PRMARY KEY (SLSREP_NUMBER)

72
Foreign Key

• A foreign key is a column on one table whose
  values match the primary key of another table
• To specify a foreign key, specify both the column
  that is a foreign key and the table it matches
• FOREIGN KEY (SLSREP_NUMBER) REFERENCES SALES_REP

73
Example 21

• When the SALES_REP table was created, no primary
  key was assigned
• Assign the SLSREP_NUMBER column as the primary key

74
Assigning a Primary Key
75
Example 22

• Assign a foreign key for every table in the
  Premiere Products database

76
Adding Foreign Keys
77
Violating Foreign Key Constraints
78
Parent and Child

• Figure 6.24 includes the words parent and
  child
• When a foreign key is used, the table containing
  the foreign key is the child and the tabled
  referenced by the foreign key is the parent

79
Example 23

• Specify that the units on hand values in the PART
  table must be greater than or equal to zero and
  less than or equal to 900
• In addition, specify the valid item classes as
  AP, HW, or SG

80
Adding Additional Integrity Constraints
81
Violating an Integrity Constraint
82
Summary

• A view is a pseudotable whose contents are
  derived form data in existing base tables
  whenever users attempt to access the view
• To define a view, use the CREATE VIEW statement
• This statement includes a defining query that
  describes the portion of the database included in
  the view
• When a user retrieves data from the view, the
  query entered by the user is merged with the
  defining query, producing the query that SQL
  actually executes

83
Summary

• Views provide data independence, allow database
  access control, and simplify the database
  structure for users
• You cannot update views that involve statistics
  and views with joins of non-primary key columns
• In this case you must make all updates to the
  base table
• Use the DROP VIEW command to delete a view

84
Summary

• Use the GRANT command to give users access
  privileges to various portions of the database
• Use the REVOKE command to terminate previously
  granted privileges
• You can create an use an index to make data
  retrieval more efficient
• Use the CREATE INDEX command to create an index
• Use the CREATE UNIQUE INDEX command to enforce a
  rule so that only unique values are allowed in a
  non-primary-key column

85
Summary

• Use the DROP INDEX command to delete an index
• The DBMS, not the user, makes the choice of which
  index to use to accomplish a given task
• The DBMS maintains information about the tables,
  columns, indexes, and other system elements in
  the system catalog
• Information about tables is kept in the SYSTABLES
  table, information about columns is kept in the
  SYSCOLUMNS table, and information about views is
  kept in the SYSVIEWS table
• In Oracle these same tables are named DBA_TABLES,
  DBA_TAB_COLUMNS, and DBA_VIEWS

86
Summary

• Use the SELECT command to obtain information from
  the system catalog
• The DBMS updates the system catalog continuously
  users do not update the catalog directly
• Integrity constraints are rules that the data in
  the database must follow to ensure that only
  legal values are accepted in specified columns,
  or that primary and foreign key values match
  between tables
• To specify a general integrity constraint, use
  the CHECK clause
• To specify a primary key, use the PRIMARY KEY
  clause
• To specify a foreign key, use the FOREIGN KEY
  clause