Creation and Design of MySQL Schema Objects

Required Knowledge
> Design and create normalized databases
> Create and modify tables using appropriate data types and indexing
> Describe and create table constraints enforcing data integrity
> Creating and modifying views
> Identify and use various methods to obtain metadata for MySQL database objects

> Design and create normalized databases

Normalization means that you will structure the data in such a way that you will remove the possibilities of having redundant data. More often than not, you will end up reducing the columns in your original table into smaller tables such that repeating data in tables, or duplicate data in columns is removed.

For example, if you have a database to deal with coffee shops and their employees, you might have a table that has the following columns: Employee ID, Employee Name, Store Name.

Someone may enter in the store name as “La Encantada” and for another employee, they may enter “La Encanada” (without the “t”). This means that if you do a search for employees at that store, the one with the misspelled name will not show up. Also, if you wanted to add in addresses for a store, then you would need to add those fields in for every employee, which would duplicate the data. If the store address ever changed for whatever reason, you would then need to update every employee row with the new data.  If there were misspellings in the name, then only the correctly spelled rows would be updated.

Obviously, removing the stores from the employee table, and creating a table that contains only store data: Store ID, Store Name, Store Address, Store City, Store State, Store Zip. Any changes to store details would automatically affect all searches that include the Store ID, as this is the only place where store details are saved.

Then, the employee table can contain only Employee Data: Employee ID, Employee Given Name, Employee Middle Name, Employee Surname. Any changes to an employee would automatically display everywhere, as the only place where employee details are stored.

If an employee can only work at one store, you might add in the Store ID as a column on the employee table, but to future proof, you might want to add a lookup table that allows you to assign an employee to more than one store: Employee ID, Store ID.

This is a simple example, but the basic process is the same for more complex tables: reduce all data to the smallest subset of information where it will only be stored once. If the data is still duplicated somewhere else in other tables, then you need to continue to restructure the data until all information in the database is stored in only one location. Once the data is in this format, you can appropriately use SQL to combine all the information in an appropriate query.

Creating Databases

You create a database in MySQL by using the following statement:

--
CREATE DATABASE [IF NOT EXISTS] mydatabase;
-- or
CREATE SCHEMA [IF NOT EXISTS] mydatabase;
--

The keyword SCHEMA is interchangeable with DATABASE, so anywhere you might use DATABASE you can use SCHEMA (e.g. SHOW SCHEMAS, SHOW CREATE SCHEMA, etc). The IF NOT EXISTS will keep MySQL from creating the database if the database already exists on the server.

The CREATE DATABASE command allows for two optional clauses: CHARACTER SET (a set of symbols and encodings) and COLLATE (rules for comparing the symbols in the character set).

--
CREATE DATABASE mydatabase CHARACTER SET utf8 COLLATE utf8mb4_unicode_ci;
--

All databases are stored in MySQL’s data directory, and the name of the directory corresponds to the name you gave the database. In other words, the above command would create a directory called “mydatabase” in the default data directory; on my local machine this is in /var/lib/mysql/mydatabase. This directory then stores all the information about the options selected (mydatabase.opt) and the table information (mydatabase.frm, mydatabase.MYD, mydatabase.MYI in the MyISAM engine; .idb and .frm in InnoDB). These extensions contain your “format” (frm), your “data” (MYD), and your “indices” (MYI).

Altering Databases

The ALTER command changes options for an existing database, and uses the same two options as the CREATE statement does. If you have selected a database schema to USE, then you don’t need to enter the database name in the ALTER command; if you didn’t select a database, then an error occurs.

--
USE mydatabase;
ALTER DATABASE CHARACTER SET latin1 COLLATE latin_swedish_ci;
--

Dropping Databases

If you don’t need a database anymore, you can delete it by using the DROP command.

--
DROP DATABASE [IF EXISTS] mydatabase;
--

The optional IF EXISTS will create a warning that you can view with SHOW WARNINGS, but if not included, MySQL will throw an error if the table does not exist. MySQL does not care if the database is not empty, so using the DROP command is dangerous, as there is no “undo” for the DROP command.

Metadata

There are several metadata queries that will give you information about the database but not the actual data. In my own experience, the most useful are:

--
SHOW DATABASES [LIKE 'blah%'];
-- and
SHOW CREATE DATABASE mydatabase;
--

The above will show you all the databases on the server, or the code that was used to create the schema; if the defaults were used, the SHOW CREATE DATABASE command will include what MySQL added as collation and character set.

You can also get information about the character set and collation by performing a select query on the information_schema:

--
SELECT * FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='mydatabase';
--

> Create and modify tables using appropriate data types and indexing

Each table in a database has one or more physical files associated with it on disk. Every table has a format file (.frm), though any extra files change according to the database engine you select. As mentioned above, MyISAM includes MYD and MYI files; InnoDB uses a shared tablespace, so each table will have a .frm file to define structure, but the table information will be stored in that shared tablespace.

Creating Tables

Creating a table has a similar syntax to creating the database, but of course, you must also add the column definitions if you are creating the table explicitly. The basic syntax includes the column data type definitions and attributes:

--
create table [IF NOT EXISTS] `students` (
student_id int(11) auto_increment not null,
student_name varchar(75) default null,
student_whatever tinytext,
primary key(student_id)
);
--

Since I assume that most people have created tables, and do so regularly, I’ll just point out some additional items that may be on the exam.

Using the IF NOT EXISTS will give you a warning that the table exists, but it doesn’t check to see if the structure of the table that exists is different from the one that you have just specified. If you leave out the IF NOT EXISTS, you will receive an error if the table exists, rather than a warning.

Storage Engine

When you create a table, you can specify the exact storage engine that MySQL should use for the table. The important thing to know is that you can use a mix of storage engines for the tables in your database. If you do not specify the engine, the default (or global) setting will be used.

Use “SHOW ENGINES” to see what storage engines are available on your server. If you attempt to set a storage engine in the CREATE TABLE statement that does not exist on your server, MySQL will issue a warning, and then use the global or default storage engine.

Because MySQL knows about the engine option on each table, you can use the ALTER TABLE command to change the engine:

--
ALTER TABLE tablename ENGINE=MyISAM;
--
Creating from existing tables

Rather than defining a table specifically, you can create a table from another existing table. To do so, you use the CREATE TABLE syntax, but add a SELECT statement to the end of it.  The following are valid for creating a copy of an existing table:

--
CREATE TABLE studentCopy SELECT * FROM student;
CREATE TABLE studentHighSchool SELECT * FROM student WHERE student_grade > 8;
--

If you create a table in this manner, all the column attributes and indexes of the original table are retained.

If you want to create an empty copy of the table, you can do it in two ways:

--
CREATE TABLE studentCopy SELECT * FROM student WHERE 0;
CREATE TABLE studentCopy LIKE student;
--

The important difference between WHERE 0 and LIKE is that WHERE 0 will create an empty table, but it will not retain things like column attributes or primary keys, but using LIKE will retain those details.

FOREIGN KEYS definitions are not maintained in any case.  You must always redefine them after you copy a table.

DATA DIRECTORY and INDEX DIRECTORY attributes are likewise also not stored when copying a table.

Temporary Tables

Temporary tables are different from regular tables in the following way:

  • Visible only to the connection that created it. This means multiple temporary tables with the same name can be created without conflict.
  • Only exists for the duration of the session.
  • >>> A temporary table may have the same name as a non-temporary table, but the non-temporary table becomes hidden to the client until the temporary table is dropped.
  • Can only be renamed with ALTER TABLE; you cannot use RENAME TABLE.

Altering Tables

If you’ve created a table, and realize that the structure needs to change before any data has been added, you might simply DROP and reCREATE the table. But if the table already has data, modifying the table structure is safer.  To do that, you’ll need to use the ALTER TABLE syntax.

Adding and Dropping Columns

Just as when creating a table, if you ADD a column definition, you must specify the data type and any attributes:

--
ALTER TABLE students ADD student_something_else VARCHAR(100);
--

Without any specification of where this column should come, MySQL will add the column at the end of the table.  If you wish to add the column to the beginning of the table use FIRST, and if you wish to add it after a particular column, use the AFTER keyword:

--
ALTER TABLE students ADD student_something_important VARCHAR(100) FIRST;
--
ALTER TABLE students ADD student_something_important VARCHAR(100) AFTER student_name;
--

To DROP a column:

--
ALTER TABLE students DROP student_something_important;
--
Modifying Existing Columns

Modifying a table is very similar to adding a new column, as you need to specify the column name, and give it’s data type definition and any attributes, but instead of using the ADD keyword, you use MODIFY:

--
ALTER TABLE students MODIFY student_something_important INT(10) UNSIGNED ZEROFILL NOT NULL;
--

If you simply want to rename a column, you need to specify that you wish to CHANGE the field name, but you will also need to specify the column definitions and attributes once again:

--
ALTER TABLE students CHANGE student_something_important student_unique_id INT(10) UNSIGNED ZEROFILL NOT NULL;
--

If you want to modify multiple things on a table, just mix and match the above commands making sure to separate those commands with a comma.

Renaming a Table

If you want to rename the table you can use either “ALTER TABLE tablename RENAME TO new_tablename” or “RENAME TABLE tablename TO new_tablename.” As mentioned before, you cannot use RENAME on temporary tables; only ALTER TABLE works for temporary tables.

Dropping a Table

If you want to DROP a table, simply use “DROP TABLE tablename.” Similar to other uses, IF NOT EXISTS added to this command will produce a warning rather than an error. The warning can be viewed with SHOW WARNINGS.

Emptying Tables

There are two ways to delete the data from a table:

--
DELETE FROM tablename;
--
TRUNCATE TABLE tablename;
--

Both of these commands will completely delete the data in the table without deleting the table itself.  The important differences to remember here:

  • TRUNCATE will reset any auto_increment fields, while DELETE will leave them intact and start with the next increment
  • DELETE will fail if any foreign key constraints are broke, while TRUNCATE may just ignore them (depending on the engine)
  • DELETE will fire any ON DELETE triggers, while TRUNCATE will not
  • DELETE is logged, while TRUNCATE is not

If you only wish to delete some rows, DELETE can take a WHERE clause:

--
DELETE FROM tablename WHERE active=0;
--

Part 2 Coming Soon

Leave a Reply

Your email address will not be published. Required fields are marked *