Lecture material weeks 1-15

->

If this happens to you, just type the semi-colon on the line with the "->" and press Enter.

mysql> create database plastronics -> ;

Query OK, 1 row affected (0.00 sec)

mysql>

The "Query OK" is your signal that the command was accepted and the task performed. Creating a database does not select it for use; you must do that explicitly. Enter the following command:

use plastronics;

The system will respond "Database changed". Now, we can work with the database. Let's take a minute to review the final structure we designed for the order processing database.

We need to set up four tables, that relate to one another as defined in the structure above. We use the CREATE TABLE command which allows us to specify the fields and their contents, as well as primary keys and constraints. Here is the command to create the customer table:

create table customer (cust_no int not null auto_increment, name varchar(40), address varchar(40), phone varchar(12), primary key(cust_no));

After the "create table" part of the command, we name our table "customer". Then, in parentheses, we define the fields. The minimum information required is a fieldname, and a type, indicating what kind of data we want to store in the field.

For cust_no, we want an integer (whole number), which is abbreviated "int" in MySQL. Then, we indicate that cust_no cannot be null (which means empty) and we would like the system to fill the value in for us by auto-incrementing from the previous value, every time we insert a new record.

The other fields are of type "varchar" which means variable-length strings, or sequences of characters. The numbers following "varchar" in the command above indicate maximum length for the data stored in the field. So, the name, address and phone fields are all sequences of characters with maximum lengths 40, 40 and 12.

Finally, we set cust_no to be the primary key.

You can find more information on types and how to use them in the MySQL documentation. For more details on the CREATE TABLE command, check the MySQLreference.

Here is the command to create the orders tables:

create table orders (order_no int not null auto_increment, FK_cust_no int not null, foreign key(FK_cust_no) references customer(cust_no), primary key(order_no));

There are only two fields in this table. The order_no field is a primary key, and is an integer, not null and it will auto increment. The cust_no field is a foreign key. We have named it FK_cust_no in the orders table to distinguish it from the cust_no field in the customer table.

Recall that a foreign key is a field that references a primary key in another table. In the command, we indicate that the FK_cust_no field is a foreign key referencing the cust_no field in customer, indicated by the "foreign key(FK_cust_no) references customer(cust_no)" part of the command. By setting the table up this way, MySQL will enforce constraints, that is, any order that we enter into the orders table must reference a valid customer in the customer table. If we enter a cust_no in orders that does not exist in customers, an error will result.

Note: If you want MySQL to enforce foreign key constraints, you need to add "type=InnoDB" at the end of the CREATE TABLE statement as in:

create table orders (order_no int not null auto_increment, FK_cust_no int not null,

foreign key(FK_cust_no) references customer(cust_no), primary key(order_no)) type=InnoDB;

"type=InnoDB" may or may not be available in your MySQL installation (Note: in some distribution, to avoid "ERROR 1005", you have to add "type=InnoDB" on both table creation statement, customer and orders). Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from source.

Let's see what we have so far. Enter the following command:

show tables;

Then enter the following command to look at the structure of the orders table:

describe orders;

Here is the command to create the items table:

create table items (item_no int not null auto_increment, price int, primary key(item_no));

Practice: Take a couple minutes to make sure you understand all the parts of the command for creating the items table. Using the CREATE TABLE command, create the fourth table, which has two foreign key fields and the count. Call this table "item_details".

Here is the command for creating item_details:

create table item_details (FK_order_no int not null, foreign key(FK_order_no)

references orders(order_no), FK_item_no int not null, foreign key(FK_item_no) references items(item_no), count int);

If your table does not match the structure defined in this command, just delete it by entering:

drop table item_details;

Then, enter the command given above for creating item_details.

Entering and Updating Data

Now that we have our tables created, we need to populate them with data. Enter the following commands. Just copy the whole block and paste at your MySQL command prompt.

insert into customer set name="Joe Boo", address="123 West", phone="412-773-5322"; insert into customer set name="Rich Wrack", address="332 East", phone="412-773-8374"; insert into customer set name="Ken Bend", address="225 Main", phone="412-773-9822"; insert into customer set name="Kim Slim", address="415 Bent", phone="412-773-6721"; insert into customer set name="Tom Plom", address="633 North", phone="412-773-4156"; select * from customer;

The INSERT command sets the fields to the corresponding values. The SELECT command with a "*" outputs all the data. Notice how the cust_no field auto incremented. Next, we populate the items table:

insert into items set price=666; insert into items set price=700; insert into items set price=450; insert into items set price=1200; select * from items;

Now, the orders table:

insert into orders set FK_cust_no=4; insert into orders set FK_cust_no=3; insert into orders set FK_cust_no=4; insert into orders set FK_cust_no=1; insert into orders set FK_cust_no=2; insert into orders set FK_cust_no=1; insert into orders set FK_cust_no=2; insert into orders set FK_cust_no=3; insert into orders set FK_cust_no=4; insert into orders set FK_cust_no=5; select * from orders;

Finally, the item_details table:

insert into item_details set FK_order_no=1, FK_item_no=4, count=12; insert into item_details set FK_order_no=1, FK_item_no=3, count=56; insert into item_details set FK_order_no=2, FK_item_no=1, count=10;

insert into item_details set FK_order_no=3, FK_item_no=2, count=43; insert into item_details set FK_order_no=3, FK_item_no=4, count=16; insert into item_details set FK_order_no=4, FK_item_no=2, count=87; insert into item_details set FK_order_no=5, FK_item_no=1, count=62; insert into item_details set FK_order_no=5, FK_item_no=2, count=48; insert into item_details set FK_order_no=5, FK_item_no=3, count=5; insert into item_details set FK_order_no=6, FK_item_no=3, count=87; insert into item_details set FK_order_no=7, FK_item_no=2, count=32; insert into item_details set FK_order_no=7, FK_item_no=1, count=27; insert into item_details set FK_order_no=8, FK_item_no=4, count=91; insert into item_details set FK_order_no=9, FK_item_no=2, count=34; insert into item_details set FK_order_no=9, FK_item_no=3, count=72; insert into item_details set FK_order_no=10, FK_item_no=4, count=2; select * from item_details;

If you need to edit a record, MySQL provides an UPDATE command:

update item_details set count=12 where FK_order_no=7 and FK_item_no=2; select * from item_details where FK_order_no=7 and FK_item_no=2;

Notice how we can define the exact record for both UPDATE and SELECT using the WHERE clause. We have also used the AND connector. UPDATE can also be used to edit a group of records. For example, we could set count=12 for both of the items in order #7:

update item_details set count=12 where FK_order_no=7; select * from item_details where FK_order_no=7;

We can also DELETE in a similar manner:

delete from item_details where FK_order_no=7; select * from item_details where FK_order_no=7;

Let's put those records back in now:

insert into item_details set FK_order_no=7, FK_item_no=2, count=32; insert into item_details set FK_order_no=7, FK_item_no=1, count=27;

For more information on these MySQL commands, check the MySQL Documentation.

Practice: See if MySQL checks for primary key constraints by trying to insert a new record in a table with a primary key that has the same primary key value as a record already in the table. Note that foreign key constraints are only checked if you created the tables using "type=InnoDB".

LECTURE 15. DESIGN TOOLS AND MANAGMENT OF DATABASES 3

Transactions in MySQL

A transaction is a sequence of individual database operations that are grouped together. A transaction will never be complete unless each individual operation within the group is successful. If any operation within the transaction fails, the entire transaction will fail. Transactions are a relatively new addition to MySQL but not to relational database systems in general.

A good example where transactions are useful is in banking. Say you want to transfer $100 between two accounts. In order to deposit money into one account, you must first take money from another account. Without using transactions, you would have to do the following MySQL operations:

1.Check that the balance of the first account is greater than $100.

2.Deduct $100 from the first account.

3.Add $100 to the second account.

If we think of this sequence as a transaction, then if any one operation fails, the whole transaction fails and we rollback, that is, the tables and the data inside them revert to their previous state. If the transaction is successful, we commit the changes to the database. This is much easier than dealing with possible errors between each step. For example, without transactions we need to skip steps #2 and #3 if the balance is less than $100; we need to skip step #3 if for some reason, we were unable to deduct $100 from the first account; and so on. Transactions can simplify the processing.

Transactions have the following four properties, usually referred to by the acronym ACID:

∙Atomicity: An atom is meant to be something that cannot be divided. The operations that make up a transaction must either all be carried out, or none at all (as with our banking example).

∙Consistency: The database changes state upon a successfully committed transaction.

∙Isolation: Data being used for one transaction cannot be used by another transaction until the first transaction is complete. This enables transactions to operate independently of and transparent to each other.

∙Durability: This ensures that the result or effect of a committed transaction persists in case

of a system failure.

To use transactions in MySQL, you must use a transaction-safe table type. The default MySQL table type, MyISAM, does not support transactions. BerkeleyDB and InnoDB are the transaction-safe table types available in open source MySQL, version 3.23.34 and greater. Support for InnoDB tables requires a specific compilation parameter when compiling MySQL from source. If your MySQL installation supports InnoDB tables, simply add a "TYPE=InnoDB" definition to the table creation statement. You can check if you have InnoDB support by entering the following command:

show variables like 'have_innodb';

In MySQL, transactions begin with the statement BEGIN WORK or START TRANSACTION and end with either a COMMIT or a ROLLBACK statement. The SQL commands between the beginning and ending statements form the operations of the transaction. Going back to the banking example, the following statements:

update account set balance = balance - 100 where acctnumber = 1; update account set balance = balance + 100 where acctnumber = 2;