The database is created using MySQL’s command-line tool at the computer running MySQL. The tool is started and two commands are typed in to create the database and use it:
(shell)$ mysql -u root -p Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.22_Debian-6-log Type ’help;’ or ’\h’ for help. Type ’\c’ to clear the buffer. mysql> create database d105a;
Query OK, 1 row affected (0.08 sec) mysql> use d105a;
Database changed
Based on the ER-diagram, we have constructed a list of queries to create the tables in the database. We will present two of them here; one to create categories, and one to create productDetails. A complete list of “CREATE TABLE” queries and resulting tables are available in appendix C. The query for creating categories can be seen in figure 9.1. The categories’ name and
picture is represented using text strings of up to 60 characters wide. The identification number is an auto-incrementing integer, and the description a “text” with a maximum length of 65535 (216
−1) characters. [1]
CREATE TABLE categories (categoryID INT AUTO_INCREMENT, name VARCHAR(60) NOT NULL, description TEXT, picture VARCHAR(60), PRIMARY KEY (categoryID));
+---+---+---+---+---+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+---+---+---+---+
| categoryID | int(11) | | PRI | NULL | auto_increment |
| name | varchar(60) | | | | |
| description | text | YES | | NULL | |
| picture | varchar(60) | YES | | NULL | |
+---+---+---+---+---+---+
Figure 9.1: Creation of the table categories
The next table, productDetails, will take advantage of a foreign key to link itself to categories. The attribute “categoryID” in productDetails is a key referencing the primary key in categories; hence it is a foreign key. This insures that new products must be “put into” an existing category from categories. The query to create productDetails is seen in figure 9.2.
CREATE TABLE productDetails (productID INT AUTO_INCREMENT, categoryID INT NOT NULL, name VARCHAR(60) NOT NULL, vendor VARCHAR(60) NOT NULL, model VARCHAR(30) NOT NULL, price FLOAT NOT NULL, description TEXT, picture VARCHAR(60), PRIMARY KEY (productID), FOREIGN KEY (categoryID) REFERENCES categories (categoryID) ON DELETE CASCADE ON UPDATE CASCADE);
+---+---+---+---+---+---+
| Field | Type | Null | Key | Default | Extra |
+---+---+---+---+---+---+
| productID | int(11) | | PRI | NULL | auto_increment |
| categoryID | int(11) | | MUL | 0 | |
| name | varchar(60) | | | | |
| vendor | varchar(60) | | | | |
| model | varchar(30) | | | | |
| price | float | | | 0 | |
| description | text | YES | | NULL | |
| picture | varchar(60) | YES | | NULL | |
+---+---+---+---+---+---+
Figure 9.2: Creation of the table productDetails
Within the SQL commands creating the tables, the “not null” means that the table’s data field must contain data. The primary keys in a table must contain data or else an error will occur. The foreign keys differ a bit, as seen with “orders” where “employeeID” may be “null” (check out appendix
C). We chose “not null” for that attribute, because an employee is not nec- essarily appointed when an order is created, but instead the appointment occur later when an employee at a warehouse chooses to process the order. In case of the foreign key, the integer in the data field must correspond to a primary key in another table. When the “not null” condition is set on data fields, that means the information is deemed important to the record, such as a customer’s name and address. An error will occur if a data entry is not made into a “not null” attribute when inserting data into a table. [15] When a row is removed or updated from a table, the change does not au- tomatically happen in other tables, even when they contain a foreign key referring to the changed row is the other table. To insure that this does not present a problem, the table must be able to handle such situations. Therefore the options “on delete cascade” and “on update cascade” to the SQL command “create table” will be used. It is also a considerable feature, because it eases the operation of the database, e.g. all products in a specific category can be removed by just removing the category itself. In particu- lar the “on delete cascade” option will be used with caution, because we do not want all orders belonging to a customer to disappear if the customer is deleted. Instead we have chosen to deny deletion of a customer if the cus- tomer is referred to by any order. [15]
Status of the order may only be set to a predefined set of values: “Pending”, “Being processed”, and “Shipped”. We have decided to use a text string for status so that it is easy to print out at the web shop and in the warehouse interface. Text strings are also more expressive than numeric values, e.g. 1, 2, 3. The entity have three foreign keys referring to other entities which are customerID, warehouseID, and employeeID. Except for employeeID, these foreign keys cannot be “null”. At an early stage of the database design the employeeID could not be “null”, but when we realized that an employee was not assigned to an order imidiately, we had to make values of “null” possible. The creation date of an order must be set when creating a new order so it cannot be “null”. That is not the case for the shipping date which is not set until an order has been completely packed in postal packages and shipped. The track & trace number is provided by the mail service when a package is shipped, so it may be “null” when order status is “Pending” and “Being processed”. We represent the number with a text string, because the format differs between mail services and is not limited to numeric values.
The queries listed in appendix C helped us create a working database which is now taking care of persistence in the back-end of our system.