MySQL Data Definition Basics
Implementing Database in MySQL – Part 4
Division 4
Forward: In this part of the series, we look at the basics of MySQL data definition. Remember, we are dealing with MySQL 5.1.
By: Chrysanthus Date Published: 6 Aug 2012
Introduction
By data definition here, I am referring to creating of tables, altering tables, dropping tables, creation of indexes and giving different users, rights to different tables. All that should be done for a database.
Note: If you cannot see the code or if you think anything is missing (broken link, image absent), just contact me at forchatrans@yahoo.com. That is, contact me for the slightest problem you have about what you are reading.
Example
We shall continue with the wholesale example. The tables for the wholesale example are more or less:
Customers(CustomerID, Name, Address, City, State, Country, Phone)
Sales(SaleID, DateAndTime, EmployeeID, CustomerID)
SaleDetails(SaleID, ProductID, Quantity, SoldPrice)
Products(ProductID, ProductName, Category, Quantity, ReorderLever, CostPrice, SellingPrice)
Orders(OrderID, DateAndTime, EmployeeID, SupplierID)
OrderDetails(OrderID, ProductID, Quantity, BoughtPrice, Discount)
Suppliers(SupplierID, Name, Address, City, State, Country, Phone)
Employee(EmployeeID, Name, Address, City, State, Country, Phone, Manager)
These are more or less the tables that were derived in division 2 of the series, Database. The logical relationships are as follows:
One-to-many relationship between the Sales table and the Customers table, through the SaleID column (the same customer can come for different sales).
- One-to-many relationship between the Sales table and the SaleDetails table, through the SaleID column.
- One-to-many relationship between the Products table and the SaleDetails table, through the ProductID column.
- One-to-many relationship between the Products table and the OrderDetails table, through the ProductID column.
- One-to-many relationship between the Suppliers table and the Orders table, through the SupplierID column (the same supplier can supply for different orders).
- One-to-many relationship between the Employee table and the Sales table through the EmployeeID column.
- One-to-many relationship between the Employee table and the Orders table through the EmployeeID column.
- A reflexive relationship within the Employee table, where one manager supervises many employees.
- Define a unique index on a primary key.
- Index all foreign keys.
- Want to insist on unique values for a column, index that column unique index..
- Index frequently searched columns.
- Index columns that frequently determine the sort order.
- The longer the table, the likelihood that it should have indexes.
MySQL and Indexes
MySQL understands the advice for indexes and it gives an index to the primary key on our behalf. If you declare a column (or columns) as primary key, the MySQL DBMS automatically gives a unique index to that column (or columns). So we have to worry about the rest of the other index features above (except the primary key).
Creating the Tables
For the rest of this tutorial, I will give the SQL statements to create each of the tables of the example, and explain each statement. In the next part of the series, I will explain the syntax of the CREATE TABLE SQL statement and then you will create the tables practically.
When creating tables, it is advisable to create the tables, which do not have foreign keys first. Another advice is as follows: In creating MySQL tables, a PRIMARY KEY is placed first, followed by all UNIQUE indexes, and then the nonunique indexes. This helps the MySQL optimizer to prioritize which index to use and also more quickly to detect duplicated UNIQUE keys.
MySQL allows NULL values for unique columns. An empty table cell is a NULL value. We shall not allow NULL values for any unique index of the tables in the wholesale project.
The Customers table notation is:
Customers(CustomerID, Name, Address, City, State, Country, Phone)
The SQL statement to create the Customers table is:
CREATE TABLE Customers
(
CustomerID INTEGER NOT NULL AUTO_INCREMENT,
Name CHAR(40),
Address CHAR(60),
City CHAR(30),
State CHAR(30),
Country CHAR(40),
Phone CHAR(12),
PRIMARY KEY (CustomerID ASC),
INDEX (Name)
);
You have the reserved words, CREATE TABLE; then you have the table name of your choice. Next you have parentheses. Inside the table, you have two sections. The top section deals with the individual columns. The lower section deals with the whole table. In other words the lower section deals with the table constraints (there are a few exceptions to this).
The first column is the CustomerID column. Its data type is INTEGER. It cannot be NULL (NOT NULL). Its is AUTO_INCREMENT; this means that as you go down the column the values would be 1, 2, 3, 4, 5, etc. For the rest of the columns, you have the column name and the data type CHAR. The numbers in the parentheses give the maximum number of characters that each cell in the column should take. The very maximum of the CHAR type is 255 chars (characters), but we would never have that for this table. The column descriptions are separated by commas (end in commas), right to the last one.
You would normally want to see (retrieve) the Customers table, based on the names (alphabetical listing) of the customers. So the name column of the Customers table is a column that frequently determines the sort order, when listing the rows of the Customers table. So at the lower section of the statement, this column is indexed; note how it has been done.
For the table constraint, the primary key for the table is indicated. If you have more than one column for the primary key, you type their names separated by commas in the parentheses. Above, we have just one column name in the parentheses. ASC means that the values in the column increases as you go down the table, numerically, alphabetically or both. The lines in the table constraint section should be separated by commas. The last one doe not need a comma.
In the table creation SQL statement, CREATE, TABLE, INTEGER, NOT, NULL, CHAR, AUTO_INCREMENT, PRIMARY, INDEX and KEY are reserved words. Reserved words are words that have special meaning to the syntax and you should not use them arbitrarily (in any way).
Note: Each SQL statement ends with a semicolon.
Note: The phrase, “lower section” that I am using here is not what the MySQL manual uses. In fact in the manual, there is no such lower section. I am using the phrase and its application in this series for convenience based on my experience in Database programming and teaching.
The Products table notation is:
Products(ProductID, ProductName, Category, Quantity, ReorderLevel, CostPrice, SellingPrice)
The SQL statement to create the Products table is:
CREATE TABLE Products
(
ProductID INTEGER NOT NULL AUTO_INCREMENT,
ProductName CHAR(40),
Category CHAR(30),
Quantity INTEGER,
ReorderLevel INTEGER,
CostPrice DECIMAL(19,2),
SellingPrice DECIMAL(19,2),
PRIMARY KEY (ProductID ASC),
INDEX (ProductName),
INDEX (Category)
);
The explanation is similar to that of the previous table. Here, the ProductName and the Category columns are indexed (columns that frequently determine sort order). I have used DECIMAL(19,2) for the MONEY data type. This means you can have up to 19 digits for all the number and 2 decimal digits. This is the type you should use for money.
The primary key does not have to be AUTO_INCREMENT (autonumber). The company may have their own way of giving values for the primary key. MySQL may have a function (code) for producing such values. With AUTO_INCREMENT, the number increases by 1 generally as you go down the column. MySQL may have a way of producing higher incrementing values than 1; however, I will not go into that in this series. See the manual or some other document for that.
The Suppliers Table
The Suppliers table notation is,
Suppliers(SupplierID, Name, Address, City, State, Country, Phone)
The SQL statement to create the Suppliers table is:
CREATE TABLE Suppliers
(
SupplierID INTEGER NOT NULL AUTO_INCREMENT,
Name CHAR(40),
Address CHAR(60),
City CHAR(30),
State CHAR(30),
Country CHAR(40),
Phone CHAR(12),
PRIMARY KEY (SupplierID ASC),
INDEX(Name)
);
The explanation is similar to that of the Customers table.
The Employee table notation is,
Employee(EmployeeID, Name, Address, City, State, Country, Phone, Manager)
The SQL statement to create the Employee table is:
CREATE TABLE Employee
(
EmployeeID INTEGER NOT NULL AUTO_INCREMENT,
Name CHAR(40),
Address CHAR(60),
City CHAR(30),
State CHAR(30),
Country CHAR(40),
Phone CHAR(12),
Manager INTEGER,
PRIMARY KEY (EmployeeID ASC),
INDEX (Name)
);
The explanation is similar to that of the Customers table.
The Sales Table
The Sales table notation is,
Sales(SaleID, DateAndTime, EmployeeID, CustomerID)
The SQL statement to create the Sales table is:
CREATE TABLE Sales
(
SaleID INTEGER NOT NULL AUTO_INCREMENT,
DateAndTime TIMESTAMP,
EmployeeID INTEGER,
CustomerID INTEGER,
PRIMARY KEY (SaleID ASC),
FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) ON UPDATE CASCADE ON DELETE CASCADE
);
Creation of tables is similar. However, there are certain things to note here. For the DateAndTime column, the data type is timestamp (calendar date and time of day when action took place – when row was added). The DBMS gives this value automatically for you; just make sure the date and time of your server computer is correct. There are two independent foreign keys here, which are the EmployeeID column and the CustomerID column.
In the table constraint section of the SQL statement, there are two foreign key lines. The phrase “ON UPDATE CASCADE ON DELETE CASCADE” has been added to each of the lines. It says “on update cascade” and “on delete cascade”. Each of the foreign key lines in the SQL statement is complete. That is how to enforce referential integrity in MySQL.
There is a one-to-many relationship between the EmployeeID column of the Sales table and the primary key column of the Employee table. There is also a one-to-many relationship between the CustomerID column of the Sales table and the primary key column of the Customer table. Each foreign key references a primary key in another table. Use commas to separate the lines in the table constraint section of the SQL statement; the very last line does not need a comma.
The SaleDetails table notation is,
SaleDetails(SaleID, ProductID, Quantity, SoldPrice)
The SQL statement to create the SalesDetails table is:
CREATE TABLE SaleDetails
(
SaleID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
Quantity INTEGER,
SoldPrice DECIMAL(19,2),
PRIMARY KEY (SaleID, ProductID),
FOREIGN KEY (SaleID) REFERENCES Sales (SaleID) ON UPDATE CASCADE ON DELETE CASCADE
);
Points to note: There is a composite (two columns) primary key here. The primary key line in the table constraint column section in the SQL statement takes care of this with the two column names (separated by comma) in its parentheses.
Now even though SaleID is part of the primary key, it behaves like a foreign key here, so far as the Sales table is concerned. So a line in the table constraint section in the SQL statement should be there to that effect; note the line.
The Orders Table
The Orders table notation is,
Orders(OrderID, DateAndTime, EmployeeID, SupplierID)
The SQL statement to create the Orders table is:
CREATE TABLE Orders
(
OrderID INTEGER NOT NULL AUTO_INCREMENT,
DateAndTime TIMESTAMP,
EmployeeID INTEGER,
SupplierID INTEGER,
PRIMARY KEY (OrderID ASC),
FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID) ON UPDATE CASCADE ON DELETE CASCADE,
FOREIGN KEY (SupplierID) REFERENCES Suppliers (SupplierID) ON UPDATE CASCADE ON DELETE CASCADE
);
The Orders table can be explained in a similar way to the Sales table, but here, you are dealing with Orders (and suppliers) and not Sales (and customers).
The OrderDetails Table
The OrderDetails table notation is,
OrderDetails(OrderID, ProductID, BoughtPrice, Discount)
The SQL statement to create the OrderDetails table is:
CREATE TABLE OrderDetails
(
OrderID INTEGER NOT NULL,
ProductID INTEGER NOT NULL,
BoughtPrice DECIMAL(19,2),
Discount FLOAT,
PRIMARY KEY (OrderID, ProductID),
FOREIGN KEY (OrderID) REFERENCES Orders (OrderID) ON UPDATE CASCADE ON DELETE CASCADE
);
The OrderDetails table can be explained in a similar way to the SaleDetails table, but here you are dealing with orders and not sales. The data type I have given for the Discount column is FLOAT. This value is in percentage, (the percentage sign is not indicated).
I have explained the SQL statements for the 8 tables. These statements do not have any error. When any of these tables is implemented successfully using the mysql command prompt, the table is automatically saved in the database (directory). There is no save command to save the table.
Well, let us end there for this tutorial. I hope you are appreciated the implementation of database (design). We continue in the next part.
Chrys
Related Links
Major in Website DesignWeb Development Course
HTML Course
CSS Course
ECMAScript Course
NEXT