Chapter 6 Database Administration

1. Chapter 6Database Administration

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

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

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

5. Creating the HOUSEWARES View

6. HOUSEWARES View

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

8. Using the HOUSEWARES View

9. 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 > 100;

10. CREATE VIEW Format • The formulation of the view definition is: • CREATE <view name> AS <query> • The defining query can be any valid SQL query

11. Renaming Columns When Creating a View

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

13. Row-and-Column Subsets and Updates through a View • 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

14. Creating the SALES_CRED View

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

16. Statistics • Views involving statistics calculated from one or more base tables won’t allow updates • Rows cannot be added to a view that includes calculations

17. Dropping a View

18. DROP View Command • Deletes a view definition only • Table and data on which view is based still exists

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

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

21. Example 6 • User Jones must be able to retrieve data from the SALES_REP table • GRANT SELECT ON SALES_REP TO JONES;

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

23. GRANT and REVOKE Format • GRANT <privilege> TO <user> • REVOKE <privilege> FROM <user> • 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

24. Example 14 • User Jones is no longer allowed to retrieve data from the SALES_REP table • REVOKE SELECT ON SALES_REP FROM JONES;

25. Indexes • Create and use an index to speed the searching process

26. Index for CUSTOMER Table on CUSTOMER_NUMBER Column

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

28. Creating Indexes

29. Dropping an Index • Command to delete an index is DROP INDEX • DROP INDEX CREDNAME;

30. 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);

31. 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)

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

33. Tables Owned by PRATT

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

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

36. 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’)

37. ADD PRIMARY KEY Clause • To indicate that SLSREP_NUMBER is the primary key for the SALES_REP table • PRIMARY KEY (SLSREP_NUMBER)

38. 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 • ADD FOREIGN KEY (SLSREP_NUMBER) REFERENCES SALES_REP

39. Assigning a Primary Key

40. Adding Foreign Keys