CIS 331: Database Management Using SQL

A Guide to MySQL, Chapter 7: Database Administration

Objectives:
 

This chapter introduces more commands typically used by the database administrator. The objectives important to this chapter are:

  1. Creating and using views
  2. User privileges to databases
  3. Understanding and using indexes
  4. Creating, using, and dropping indexes
  5. Using the system catalog
  6. Using integrity constraints
Concepts:

Some versions of SQL do not support the commands in this chapter. The version of MySQL we are using at our school is one of them. You are still responsible for learning this material.

A view of a database is used to give users access to the database when those users are only allowed to see part of the data, or are restricted from seeing all the data. To understand the concept, think of the set of all the data in a table as the base table. A view will be a subset of the base table. That subset can be just a subset of columns, if you do not use a WHERE clause, or it can be a subset of columns and rows if you do use a WHERE clause.

A view can be created with a defining query that begins with the command CREATE VIEW.

CREATE VIEW view_name (names_to_use_for_columns) AS
SELECT
list_of_columns
FROM
table_name
WHERE criteria_to_match ;

  • CREATE VIEW is followed by the name you are giving the view. This is followed, optionally, by a set of parentheses enclosing a comma delimited list of names to use in the view for the actual columns you will select. This clause ends with the word AS.
  • SELECT is followed by the columns you want to use in the view.
  • FROM is followed by the name of the table (or tables) you are using to create the view.
  • WHERE is followed by criteria identifying which records to show in the view. Compound criteria are allowed.

The creation of a view can result in what appears to the user as a custom table. It is not really a separate table. The text makes the point that updates to the base tables used in the view will be available to the view immediately. If the view actually used a new table, that table would have to be recreated to show any updates to the base tables.

Once the view is created, it can be used in commands as though it were the name of a table. When you give a command like this, the DBMS merges the command using the view with the components of the command that created the view. In other words, a command that uses a view is actually rewritten by the database in an expanded form. It is like running the commands that create the view, storing the output in a temporary memory space, and then executing the command you actually wanted to run on that temporary output.

If the relationships of the base tables involved in a view are changed, the view must be changed as well, or it will not retrieve the intended data. Change the view by creating it again.

A point is made about a view that is useful with large (wide) tables. If users in different parts of a company have specific needs for different portions of a table, views provide a means to give them access to their data without seeing what they are not allowed to see, or getting lost in other confusing details.

Views can cause seemingly odd behaviors in your DBMS. If a user attempts to insert a new row in a view, it may take place, but may not. Since the user only sees selected columns in a view, the columns in the base tables that the user does not see must be able to accept null entries, or an INSERT command is not possible. Also, it is not possible to use INSERT to place a row in a table if the data you are attempting to enter in the primary key column already exists.

In general, there are no problems with views that are used only to read data, but various problems can arise with updates. View can be changed by recreating them. To erase a view from the DBMS, use the command DROP VIEW.

DROP VIEW name_of_view ;

Security is defined as "prevention of unauthorized access". The text explains that views are one means of applying security to a database. Another security feature is the GRANT command. Its opposite is the REVOKE command. These commands may not be available in all systems, and should not be used except by those empowered to do so. Format:

GRANT command ON table TO user_ID, user_ID ;

  • GRANT is followed by the command you will allow a user to use
  • ON is followed by the name of the table (or view) the user is allowed execute the command on
  • TO is followed by a list of users who are being granted this privilege

Some variants to the command syntax:

  • To grant a privilege to all system users, the user_ID part of the command is replaced by the word PUBLIC.
  • To grant all privileges to a user, the word ALL is used in place of the name of a specific command.
  • To empower a user to grant privileges to other users, you grant the privilege by adding the clause WITH GRANT OPTION.

The REVOKE command is used the same way as the GRANT command. Instead of TO, use the word FROM. Be aware that if you remove a privilege from a user who has granted it to someone else, you will remove it from both of them.

GRANT and REVOKE fit with views in that you can use these commands to allow users to use views, or to prevent them from using views.

The next topic is indexing. To understand indexes, first you must know something that seems obvious, but has not been discussed in the texts so far. Every row in a table is assigned a row number. The numbers are stored in a column called ROW_NUMBER. This is done automatically by the database when rows are created, and it has not been shown in any query up to now.

An index is a table that holds two columns from another table. The first column contains the data column from the table that you are indexing, CustomerNum or CustomerName, for example. The second column shows the row that the matching value in the indexed column is stored in. In the example below, you see the names of customers, and the rows where those customers' records actually appear in the database.

CustomerName ROW_NUMBER
Allen, Barry 5
Curry, Arthur 27
Kent, Clark 1
Prince, Diana 1900
Queen, Oliver 351
Wayne, Bruce 2

The table is sorted on the values in the first column. The purpose of the index is to allow the DBMS to rapidly find specific rows in the table the index is based on. It finds the name in the name index file, learns the row number for that name, then retrieves that row from the Customer table. Remember that we were told that the order of records was unimportant? True enough, but it becomes important when you consider the time it takes to retrieve and order data for output. An index file makes this much faster.

If you create an index on a column that does not contain unique values in each row, the second column of the index will contain the number of each row that matches the value in the first column. This seems to violate the rule of first normal form. It does, and it doesn't. An index is only used to look up rows in another table. It is not part of the relational database itself, so it does not take our database out of normalization.

The reason you use indexes is to retrieve records faster. The larger a data file is, the longer it takes to search it for matches to queries. Indexes are small files that tell you what rows to look at in the larger files.

The advantage to an index is faster, more efficient query results. The disadvantage is that it is an extra file that must be maintained or recreated each time the table it is based on is changed.

The syntax for creating an index is:

CREATE INDEX index_name ON table_name(column_name) ;

  • CREATE INDEX is followed by the name you are assigning to the index
  • ON is followed by the name of the table you are indexing, and a set of parentheses around the name of the column you are indexing. More than one column name can be used by making a comma separated list inside the parentheses. Columns are sorted in ascending order by default. The column will be sorted in descending order if the column name is followed by DESC.

Indexes can be erased from the system with the command DROP INDEX.

It is also possible to create a unique index file, which will enforce a rule that may be helpful to you. The DBMS will normally make sure that you do not enter a new record in a table if that record contains a value in the primary key field that you have already stored in another record. For this rule to apply to a column that is not the primary key, you create an index for that field, as shown above, but you insert the word UNIQUE between CREATE and INDEX. This is an example of an integrity constraint, in that it will not allow you to enter a value in a column that has been identified as a unique value column, if the value already exists in it.

The system catalog is a relational database about your data tables. It typically has several files in it. Three are described in the text, which may be called by the names given in the text, or by other names:

  • SYSTABLES - Oracle calls this one DBA_TABLES. It contains information about the tables in your database.
  • SYSCOLUMNS - Oracle calls this one DBA_TAB_COLUMNS. It contains information about the columns in your tables.
  • SYSVIEWS - Oracle calls this one DBA_VIEWS. It contains information about views in your database.

Once you know the name and structure of a system catalog table, you can run queries on it like any other table.

Integrity rules can take many forms. Three kinds of integrity rules are described for SQL. They can be set as clauses in a CREATE TABLE command or an ALTER TABLE command. (If used in an ALTER TABLE command, the clause follows the word ADD.)

  • PRIMARY KEY - This has been discussed in previous chapters. The integrity constraint it imposes is that you cannot enter a value in this column if that value already exists in another row.
  • FOREIGN KEY - A foreign key is a column that is the primary key in another table. The integrity constraint in imposes is that you cannot enter a value in this column unless that value already exists in the same column in the table where it is the primary key. Syntax:
    ADD FOREIGN KEY (column_name) REFERENCES other_table_name ;
  • CHECK - This clause provides a list of values to the database. It imposes the rule that only a choice from this list will be allowed as a value in the column specified. Syntax:
    CHECK (column_name IN ('value_1', 'value_2', 'value_3') )
    This can also be entered as:
    CHECK ( column_name = 'value_1' OR column_name = 'value_2' OR column_name = 'value_3' )

.