M ANAGING D ATABASES .1 Learning Objectives

This chapter provides the foundational knowledge to manage databases within MySQL. At the completion of this chapter, you will exhibit knowledge of MySQL by being able to:

• Describe Database Properties

• Implement Good Design Practices in database projects

• Utilize MySQL Identifiers

• Create Databases

• Alter Databases

• Drop Databases 70

5.2 Database Properties

The heart of any database is the tables, because the tables contain the data that is used to provide information to the end users. Tables are contained in databases, and in MySQL each database has an associated "data directory" to store all the physical components of the database, to include tables. This physical representation on disk of the tables is dependent on the type of storage engine that the table is utilizing;

however, every table, no matter of the type of storage engine that is being used, contains a format file (table_name.frm) which describes the table definition and structure.

In addition to the physical representation of the table, there is also the better known logical aspect of the table. This logical aspect is represented in rows and columns. Rows within a table, consist of a collection of values that describe an entity (ex. an employee). Columns within a table, consist of a collection of similar data among rows (ex. employee surnames). The intersection of row and column contains individual data items called values. Values are always atomic, in that each position in a table may contain only one datum (piece of data).

Without the ability to obtain information from the database, there is no need for the data to be stored.

However, the tables are of little value unless they are contained in a unified and associated manner that allow for the storage, retrieval and manipulation of the data. This is where the database comes in. For its part, the database can be thought of as the container for the tables (which are the containers for the data). In MySQL, databases manage their associated components in locations called "data directories". These data directories (meaning there can be more than one database that the MySQL server can manage) are located in a central sub-directory of the mysql installation and can be seen through the operating system on which the MySQL Server is running. MySQL "data directories" have the following common structures:

• Data Directory - In MySQL, there is a parent directory for all the databases that are managed by that instance of the MySQL Server.

• Database Name - The database name given to the represent the database in the MySQL server is also the name of the associated data sub-directory. This limits the naming conventions that can be used when giving databases a name.

• Database Components - The data directory is responsible for managing the majority of component associated with the associated database in the MySQL server. This includes such components as the tables, stored procedures, triggers, views, etc.

• Database Character Set Support - Each database is defined with a default character set and collation (a character set is a set of symbols and encodings and a collation is a set of rules for comparing characters in a character set). This behavior ensures that each table created within the database takes on the same characteristics associated with the default values; however, tables themselves can also be created with different character sets and collations than their database container.

• Database Hierarchy - Databases are "flat" in the sense that they can not be nested, meaning a database can not contain a database itself.

• Unlimited Databases - The MySQL server has no limit on the number of databases that it can contain; however, the operating system may limit the number of sub-directories that a directory can contain thus limiting the number of databases that can be created.

71

Database or Schema?

From its conception, MySQL has used the term "Database" to refer to the container that manages the tables and other database components. This is a common term in the industry and is not by any means against the norm.

However, the word "Schema", which comes from the Greek word "σχήμα" (skhēma) which when translated means "plan", has come to be associated with databases. A database schema is basically the plan (or description) of the structure of a database. Thus the words, "Database" and "Schema" are used interchangeably in the database community. However, the SQL standard is to use "Schema". For consistency, the word

"database" will be used unless otherwise noted.

5.3 Database Design Practices

In database discussions, it is common to hear the term normalization or database design come up. However, for the most parts these discussions revolve around splitting up the data that is stored between different tables to improve performance and to eliminate duplication. This is extremely important and should be discussed in any discussion on tables but for the most part the discussions do not involve splitting up data between databases. This is either assumed or just not put into practice as often as it should be. Either way, it is important to consider when designing an application that will need to access data.

Information Commonality Data could be defined as the actual values that are stored in the database itself and are static until they are changed by some process (either manually or automated).

Data by itself is useless if not processed in such a way as to give it meaning. Information, on the other hand, is data processed in such a way as to give it meaning and is constantly changing based on the data stored. In addition, the majority of data collected can be

processed in multiple ways to provide multiple meaningful outputs. In databases, many developers look at the data that is contained versus the information that the data is designed to provide. Databases should be developed in such a way that each database produces a common set of information. Tables that would hold data associated with human resource information should not be contained in the same database as tables that would contain parts for aircrafts in an airline database system. If there is a need for the data in the inventory database to interact with data in the human resource database to provide the proper information, the MySQL server can pull data from the tables holding the data (even though they are in two different databases) to provide such information.

Exclusive Data

In such situations where there are separate entities that utilize the same data structures, separating out the data may be the safest and most reliable way to ensure data integrity. An example would be an accounting firm that is responsible for the payroll of two or three companies. The application that would be created to support such as system could utilize the same data structures but have separate databases containing the actual data for each company. This would eliminate any chance that data from one company could interact with data from another company which would create an insecure and potentially precarious situation. This would require time and energy put into ensuring that the database and its underlying components were solid enough to avoid the need for structural changes after implentation.

Copying database structures from one database to another is an easy process in MySQL; however, any future changes would have to be done in the individual databases separately.

5.4 MySQL Identifiers

The are five kinds of identifiers that need to be considered in MySQL: databases, tables, columns, indexes and aliases. Databases, as stated are the containers for the tables and other associated database components.

Tables hold the data that is used to produce information. Columns are the individual entities of the tables that contain the smallest portion of the data. Indexes in databases refer to the data structures that improve the speed of operations in a table. Aliases are temporary names assigned to database components. The identifiers that can be assigned to each component have their limitations:

• Size - The following table describes the max length each database identifier can be assigned:

Identifier Maximum Length Database 64 Bytes

Table 64 Bytes Column 64 Bytes Index 64 Bytes Alias 255 Bytes

The length of the identifier is in bytes, not characters. The maximum length will depend on the byte count of all the characters used and should be taken into consideration if using multi-byte characters in the identifier names.

• Case Sensitivity - In MySQL, databases correspond to directories within the data directory. Each table within a database corresponds to at least one file within the database directory (and possibly more, depending on the storage engine). Consequently, the case sensitivity of the underlying operating system determines the case sensitivity of database and table names. This means database and table names are case sensitive in most varieties of Unix, and not case sensitive in Windows.

Although database and table names are not case sensitive on some platforms, database or table names should not be referred to using different cases within the same statement. The following statement would not work because it refers to a table both as my_table and as MY_TABLE:

mysql> SELECT * FROM my_table WHERE MY_TABLE.col=1;

• Character Restrictions - It is best practice to include only alphanumeric characters (and underscore or dashes) in the identifier names; however, other characters can be used if no other way is acceptable. Here is a list or restrictions that also need to be considered in such an event:

○ No identifier can contain ASCII 0 (0x00) or a byte with a value of 255

○ The use of identifier quote characters in identifiers is permitted, although it is best to avoid doing so if possible

○ Database, table, and column names should not end with space characters

○ Before MySQL 5.1.6, database names and table names cannot contain “/”, “\”, “.”, or characters that are not allowed in a directory name

○ Identifiers may begin with a digit but unless quoted may not consist solely of digits

○ It is recommended that names of the form Me or MeN, where M and N are integers. For example, avoid using 1e or 2e2 as identifiers, because an expression such as 1e+3 is ambiguous. Depending on context, it might be interpreted as the expression 1e + 3 or as the number 1e+3.

74

5.5 Creating Databases

To create a new database, use the CREATE DATABASE statement. The following statement creates a database named my_db:

CREATE DATABASE my_db;

If an attempt is made to create a database that already exists, an error occurs. To ensure that the database exists, add an IF NOT EXISTS clause to the statement:

CREATE DATABASE IF NOT EXISTS my_db;

With the additional clause, the statement creates the database only if it does not already exist. Otherwise, the statement does nothing and no error occurs. This can be useful in applications that need to ensure that a given database is available, without disrupting any existing database with the same name.

Optional Clauses

The CREATE DATABASE statement has two optional clauses, CHARACTER SET and COLLATE, that assign a default character set and collation for the database. If given, they appear at the end of the statement following the database name. The following statement specifies that the mydb database has a default character set of utf8 and collation of utf8_danish_ci:

CREATE DATABASE my_db CHARACTER SET utf8 COLLATE utf8_danish_ci;

The default character set and collation for the database are used as the defaults for tables created in the database for which no explicit character set or collation of their own are specified. The database defaults are stored in the db.opt file in the database directory. Note: CHARACTER SET is really a column setting. By giving the table or the database a character set, it just affects the columns upon creation.

Using the Database

Creating a database has no effect on which database currently is selected as the default database. To make the new database the default database, issue a USE statement:

USE my_db;

After a database has been created, it can be populated with objects such as tables or stored routines. The CREATE statement for tables is discussed in a later chapter.

Displaying Database Structure

The easiest way to display the database structure (as far as the database container) is to issue a SHOW CREATE DATABASE command. This command shows the CREATE DATABASE statement that was used to create the database (or could be used to recreate the database structure):

mysql> SHOW CREATE DATABASE my_db\G

************************* 1. row *************************

Database: my_db

Create Database: CREATE DATABASE `my_db`

/*!40100 DEFAULT CHARACTER SET utf8 COLLATE utf8_danish_ci */

Note: SCHEMA can be interchanged with DATABASE to perform the above statements. (i.e., CREATE SCHEMA mydb;)

Problems

When a CREATE DATABASE command fails, the two most common problems are insufficient or incorrect permissions, or an attempt to create a database that already exists.

75

5.6 Drop a Database

When a database is no longer needed, it can be removed with DROP DATABASE:

DROP DATABASE my_db;

It is an error if the database does not exist. To cause a warning instead, include an IF EXISTS clause;

DROP DATABASE IF EXISTS my_db;

Any warning generated when IF EXISTS is used can be displayed with SHOW WARNINGS.

Warning vs. Errors

Within MySQL, there are two levels of problem feedback that the server returns to the user. The first is called a warning, and it does exactly that. It warns the end user that there were problems associated with the transaction the just submitted, but it still performs the transaction commands to the best of its ability. To view a specific (and sometimes not so specific) explanation of the warning, use the SHOW WARNINGS command to view the last warning that was sent to the end user.

The second level of problem feedback is called an error and aborts the transaction that was submitted.

Whenever an error is encountered, the commands that were in the last transaction are not completed. To view a specific (and sometimes not so specific) explanation of the error, use the SHOW ERRORS command to view the last error that was sent to the end user. SHOW WARNINGS also displays error level feedback that was captured.

DROP DATABASE does not require the database to be empty. When dropping the database, MySQL removes any objects that it contains; such as tables, stored procedures, triggers, etc.

A successful DROP DATABASE returns a row count that indicates the number of tables dropped. (This actually is the number of .frm files removed, which amounts to the same thing.) Use the SHOW DATABASES command to verify that the database has been dropped.

5.6.1 CAUTION: When Using DROP DATABASE

A database is represented by a directory under the data directory. The server deletes only files and directories that it can identify as having been created by itself (for example, .frm files or RAID directories). It does not delete other files and directories. If a non-table files have been placed in that directory, those files are not deleted by the DROP DATABASE command. This results in failure to remove the database directory and DROP DATABASE fails. In that case, the database will continue to be listed by SHOW DATABASES. To correct this problem, manually remove the database directory and any files within it.

76

Lab 5-A

In this exercise you will use the CREATE DATABASE statement. This will require a MySQL command line client and access to the mysql server.

ACTION (You Do) COMPUTER RESPONSE / Comment

1. Log in to the mysql client (if not already). It is easier and more advantageous to design the database components in the MySQL client versus trying to complete the tasks through the PHP interface.

2. Create a new database called 'db_test' by