This chapter introduces more commands typically used by the database administrator. The objectives important to this chapter are:
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)
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 ;
Some variants to the command syntax:
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.
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) ;
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:
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.)