MySQL Database Administration

 

Introduction:

There are some special issues involved in managing a database. This process, often called database administration, is especially important when the database is used by more than one person. In a business organization, a person or an entire group known as the database administrator is charged with managing the database.

changing the structure of a database. In this chapter you will learn about additional tasks of the database administrator. You will see how each user can be given his or her own view of the database. You will use the GRANT and REVOKE commands to assign different database privileges to different users, You will use indexes to, improve database performance. You will see how SOL keeps information about the database structure in a special object called the system catalog. Using the system catalog, the database administrator can obtain helpful information concerning the database structure, and specify integrity constraints to establish rules that the data in the database must satisfy.

SECURITY:
The prevention of unauthorized access to the database is a top job of the dba (database administrator).  Based on the company policies and job-requirements for each user, the dba will determined the access necessary for each user.  Most SQL database's provide two mechanisms for security, we have already seen one, the VIEW.  The other is the SQL-GRANT command.  The grant command is used to give users privileges such as the ability to select rows from a table, insert new rows, update existing rows, and so on.

Sample DBA Commands:

Account Management Statements
Table Maintenance Statements
SET Syntax
SHOW Syntax
Other Administrative Statements

 

Sample DDL (data definition language) commands.

ALTER DATABASE Syntax
ALTER TABLE Syntax
CREATE DATABASE Syntax
CREATE INDEX Syntax
CREATE TABLE Syntax
DROP DATABASE Syntax
DROP INDEX Syntax
DROP TABLE Syntax
RENAME TABLE Syntax

 

INDEXES:
Usually when you query a database you are searching for a row (or collection of rows) that satisfies some condition. Examining every row in a table to find the desired rows often takes too much time to be practical, especially if there are thou- sands of records in the table. Fortunately, you can create and use an index to speed up the searching process significantly.

B*Tree Indexes:  This is the most common type of index and one of two that Oracle supports.  This type of index works very well with binary searches and is most useful when the index contains a large proportion of unique values such as social-security numbers, names, phone#'s, birthdates, etc. 

an index in a DBMS is similar to an index in a book. If you want to find a discussion of a given topic in a book, you can scan the entire book from start to finish and look for references to the topic. More than likely, however, you wouldn't resort to this time-consuming method. If the book has an index, you can use it to locate the page numbers on which your topic is discussed.

For relational model systems that run on both mainframes and microcomputers, such as Oracle, the main mechanism for increasing the efficiency with which data is retrieved from the database is by using indexes.

Tables in most database?s have an extra column called a row-id.  This column is invisible to user and cannot be updated by the user.  The row-id is internally used by Oracle to perform a binary-search against the table.

The index is a separate file that contains two columns. The first column contains the coulmn you want to search on (for example customer_number), and the second column contains the number of the row in which the row is found. To find a customer, Oracle uses a binary-search to find the value in the first column. The value in the second column indicates which row to retrieve from table; then the row for the desired customer is retrieved.

The primary disadvantage to an INDEX is that whenever you make an update to the base table for that index, you must also update all of a tables corresponding indexes.  However the advantages of speed usually far out weigh the additional update time.  A general rule of thumb is that if the table has less than a 1000 rows, don?t bother with an index.  In larger database?s columns that you regularly use for retrieving and updating such as customer_number, customer_name, slsrep_number, etc. are good candidates for indexes.

The SQL command for creating an INDEX is CREATE INDEX

Example:

To create an index for customer name do the following:

CREATE INDEX custname ON customer (last, first);

 

Unique Indexes
When you indicate the primary key for a table, the DBMS ensures automatically that the values for the primary key are unique. An attempt to add a second customer whose number is 124, for example, would automatically be rejected because customer number 124 already exists. Thus, you don't need to take any special action to make sure that values in the primary key column are unique; the DBMS does it for you.

Occasionally, a column that is not the primary key might need unique values. For example, in the CUSTOMER table, the primary key is CUSTOMER_NUMBER.  If the CUSTOMER table also contains a column for Social Security numbers, the values in this column also must be unique. Because the Social Security number column is not the table's primary key, however, you need to take special action in order for the DBMS to ensure that values in this column do not contain duplicates.

To ensure this uniqueness, create a special type of index called a unique index by using the CREATE, UNIQUE INDEX command. To create a unique index named SSN on the SOC-SEC-NUMBER column of the CUSTOMER table, for example, the command would be as follows:

CREATE UNIQUE INDEX SSN ON CUSTOMER (SOC_SEC_NUMBER);

The unique index has all the properties of indexes already discussed along with one additional property.  The DBMS refuses to accept any update that would cause the customer whose Social Security number is the same as that of another customer in the database.

DROPPING AN INDEX:

DROP INDEX ssn;

Views:
Most database management systems, including Oracle, are capable of giving each user his or her own picture of the data in the database. In SQL this is done using views. A view in Oracle is similar to a query in MS Access.  The existing, permanent tables in a relational database are called base tables. A view is a derived table because the data in the view is derived from the base table. It appears to the user to be an actual table. In many cases, a user can interact with the database using a view. Because a view usually includes less information than the full database, using a view can represent a great simplification. Views also provide a measure of security, because omitting sensitive tables or columns from a view renders them unavailable to anyone who is accessing the database through the view. To illustrate the idea of a View, suppose that Juan is interested in the part number, part description, units on hand, and unit price of parts in item class HW. He is not interested in any other columns in the PART table, nor is he interested in any of the rows that correspond to parts in other item classes. Whereas Juan cannot change the structure of the PART table and omit some of its rows for his purposes, he can do the next best thing. He can create a view that consists of only the rows and columns that he needs. 

A View is defined by creating a defining query. The defining query is a
SQL command that indicates the rows and columns that will appear in the view.
The command to create the view for Juan, including the defining query, is
illustrated below:

CREATE VIEW  view_name AS subquery
   

CREATE VIEW housewares AS
SELECT PART_NUMBER, PART_DESCRIPTION, UNITS_ON_HAND, UNIT_PRICE
FROM PART
WHERE ITEM_CLASS = 'HW';

Given the current data in the Premiere Products database, this view contains the data shown below:  

HOUSEWARES

PART_NUMBER PART_DESCRIPTION UNITS_ON_HAND         UNIT_PRICE
AX12                    Iron                                104                                 $24.95
BH22                     Cornpopper                    95                                   $24.95
CA14                    Griddle                            78                                   $39.99
CX11                    Blender                          112                                 $22.95

The data does not actually exist in this form, however, nor will it ever exist in this form. When this view is used, it is tempting to think that the query will be executed and produce some sort of temporary table, named HOUSEWARES, that the user can access at any time. This is not what happens. Instead, the query acts as a sort of 'window' into the database. As far as a user of this view is concerned.  

Once, created you can use a view just like a table, however the database will translate the VIEW QUERY into a query against the base table.

Example:
SELECT * FROM housewares
WHERE units_on_hand > 100

-         The above will be translated by the database into

 
SELECT part_number, part_description, units_on_hand, unit_price
FROM part
WHERE item_class ='HW'
AND units_on_hand > 100;

 

Notice that the selection is from the PART table rather than from the HOUSEWARES view; the asterisk is replaced by just those columns in the HOUSE- WARES view; and the condition includes the condition in the query entered by the user together with the condition stated in the view definition. This new query is the one that the DBMS actually executes.

The user, however, is unaware that this kind of activity takes place.

One advantage of this approach is that because the HOUSEWARES view never exists in its own right, any update to the PART table is reflected immediately in the HOUSEWARES view and is apparent to anyone accessing the database through the view. If the HOUSEWARES view were an actual stored table, this immediate update would not be the case.  

It is also possible to create a view that has different names for the columns than in the base table.  When renaming columns, you include the new column names in
parentheses following the name of the view as shown below:  

CREATE VIEW housewares (num, dsc, oh, price) AS
SELECT part_number, part_description, units_on_hand, unit_price
FROM part
WHERE item_class = 'HW';

In this case, anyone accessing the HOUSEWARES view will refer to PART-NUMBER as NUM to PART_DESCRIPTION as DSC, to UNITS-ON-HAND as OH, and to UNIT-PRICE as PRICE. If you select all columns from the HOUSEWARES view, the new column names will display as shown below:

 NUM                     DSC                      OH          PRICE
AX12                    Iron                       104          $24.95
BH22                     Cornpopper           95          $24.95
CA14                    Griddle                  78          $39.99
CX11                    Blender                 112          $22.95

Multi-Table Views:
The above example consist of a single table view, however a view can be based on a legal SQL query, including joined tables.
 

CREATE VIEW sales_cust (snumb, slast, sfirst, cnumb, clast, cfirst) AS
SELECT sales_rep.slsrep_nr, sales_rep.lname, sales_rep.fname,
          Customer_number, customer.lname, customer.fname

FROM salesrep sales_rep JOIN customer ON 
sales_rep.slsrep_nr = customer.slsrep_number;
 

SELECT * FROM sales_cust;

SNUMB SLAST      SFIRST      CNUMB CLAST                   CFIRST      
---------- ---------- --------------- ---------- --------------- ------------ 
        
3 Jones                   Mary               124  Adams              Sally                                                                      
        
3 Jones                 Mary                   412  Adams                Sally
  
     3 Jones                Mary                   622  Marin           Dan                                                                                     12 Diaz                   Miguel                 311 Charles                Don
        12 Diaz                   Miguel                 405 Williams         Al                                                                                       12 Diaz                   Miguel                 522 Nelson         Mary 

Using views has several benefits:

1.  Views provide data independence. If the database structure changes (adding columns, changing the way objects are related, etc.), the user still can access the same view. If adding extra columns to tables in the database is the only change and these columns are not required by the user, the defining query might not need to be changed. If relationships change, the defining query might be different, but this difference is unknown to the user. The user continues to access the database through the same view, as though nothing has changed.

2.   Because each user has his or her own view, the same data can be viewed by different users in different ways. In other words, the data can be customized to meet each user's need. 

3.   A view should contain only those columns required by a given user. This practice accomplishes two things. First, because the view usually contains fewer columns than the overall database and because the view is a single table rather than a collection of tables, a view can simplify greatly the user's perception of the database. Second, views furnish a measure of security. Columns that are not included in the view are not accessible to the user. For example, omitting the BALANCE column from the view ensures that a user of the view cannot access any customer's balance.  Likewise, rows that are not included in the view are not accessible. A user of the HOUSEWARES view, for example, cannot obtain any information about sporting goods, even though both housewares and sporting goods are stored in the same base table (PART).

These benefits hold true only when views are used for retrieval purposes. When up-dating the database, the issues involved in updating data through a view depend on the type of view, as a general rule DON'T update views..

INSERT INTO VIEWS:
Consider the row and column subset view named HOUSEWARES. There are columns in the underlying base table (PART) that are not present in the view. Thus, if you attempt to add a row with the data ('BB99','PAN',50,14.95), the system must determine how to enter the data in those columns from the PART table that are not included m the HOUSEWARES view (ITEM-CLASS and WAREHOUSE-NUMBER). in this case, it is clear what data to enter in the ITEM-CLASS column. According to the view definition, all rows are item class NW. But it is not clear what data to enter in the WAREHOUSE-NUMBER column. The only possibility would be NULL. Thus, provided that every column not included in a view can accept nulls, you can add new rows using the INSERT command. There is another problem, however. Suppose the user attempts to add a row containing the data ('AZ52','POT',25,9.95). This attempt must be rejected, because there is a part number AZ52 that already exists in the PART table. Because this part is not in item class HW, this rejection certainly will seem strange to the user, because there is no such part in the user's view.

   

UPDATING AND DELETING VIEWS:
Updates or deletions cause no particular problem in this view. If the description of part number CA14 changes from skillet to pan, this change is made in the PART

table. If part number CX11 is deleted, this deletion occurs in the PART table. One surprising change could take place, however Suppose that ITEM-CLASS is included as a column in the HOUSEWARES view and then a user changes the item class of part number CX11 from HW to AP. Because this item would no longer satisfy the criterion for being included in the HOUSEWARES view, part number CX11 would disappear from the user's view.

Whereas some problems do have to be overcome, it seems possible to update the database through the HOUSEWARES view. This does not imply that any row and column subset view is updateable, however.  Consider the view below (You use the word DISTINCT to omit duplicate rows from the view.)  

CREATE VIEW sales_cred AS 
SELECT DISTINCT credit_limit, slsrep_number
FROM customer ; 

How would you add the row (1000, 6) to this view? In the underlying base table

(CUSTOMER) at least one customer must be added whose credit limit is $1000 and

whose sales rep number is 6, but who is it? You can't leave the customer_number null, because it is the primary-key.  Also, even if you could make the insertion, the view would not change because there is already a view with (1000, 6)

A view that contains the primary key of the underlying base table is updateable (subject, of course, to some of the concerns we have discussed). 

 

JOINS:
In general, views that involve joins of base tables can cause problems at update.

Consider the relatively simple view SALES-CUST, for example, described earlier. The fact that some columns in the underlying base tables are not seen in this view

presents some of the same problems discussed earlier. Even assuming that these problems can be overcome through the use of nulls, there are more serious problems inherent in the attempt to update the database through this view. On the surface, change the row (6,'Smith',Williarn','256','Samuels','Ann?) to (6,'Baker','Nancy','256','Samuels','Ann?) might not appear to pose any problems other than some inconsistency in the data. (In the new version of the row, the name of sales rep number 6 is Nancy Baker; in the next row in the table, the name of sales

rep number 6, the same sales-rep, is William Smith.)

 

The problem is actually more serious than that making this change is not possible. The name of a sales rep is stored only once in the underlying SALES-REP table. Changing the name of sales rep number 6 from William Smith to Nancy Baker in this one row of the view causes the change to be made to the single row for sales rep number 6 in the SALES-REP table. Because the view simply displays data from the base tables, every row in which the sales rep number is 6 now shows the name as Nancy Baker. In other words, it appears that the same change has been made in

all the other rows. In this case this change probably would be a good thing. In general, however, the unexpected changes caused by an update are not desirable.

Not all joins create this problem. If two base tables happen to have the same primary key and the primary key is used as the Join column, updating the database is not a problem.

Statistics:
A view that involves statistics calculated from one or more base tables is the most troublesome view of all, because calculated or derived columns cannot be updated.
 

Dropping a View:
When a view is no longer needed, you can remove it by using the DROP VIEW command.

DROP VIEW sales_cust;