DATABASE TABLES
AUTO_INCREMENT,
-> name CHAR(35) NOT NULL, -> country_code CHAR(3)NOT NULL, -> PRIMARY KEY (id),
-> INDEX (country_code), -> FOREIGN KEY (country_code) -> REFERENCES country_parent
(code)
-> ON UPDATE CASCADE -> ON DELETE CASCADE -> ) ENGINE = InnoDB;
These two tables define the country-city relationship explicitly. We'll call these tables country_parent and city_child to illustrate that the country records are the parent records and the city records are the child records that depend on them.
ACTION (You Do) COMPUTER RESPONSE / Comment 21. View the create statement for both tables to
confirm the index settings:
mysql> SHOW CREATE TABLE country_parent\G
…and…
mysql> SHOW CREATE TABLE city_child\G
The two SQL statements show the attributes for both tables, including the index/key settings, as specified by the CREATE TABLE statements.
Lab 6-C
In this exercise, you will create the tables that will be associated with our online photo album web application.
1. Login in to the mysql client using the login name and password provided by your instructor.
2. View the existing databases that the MySQL Server is maintaining.
3. Utilize the photo_album database.
4. Create the first table called customers that will store the information related to the customers who have purchased access to the online photo album. The table will contain the following columns:
customers
email_customer VARCHAR(50) NOT NULL DEFAULT '', PRIMARY KEY
f_name VARCHAR(20) NOT NULL DEFAULT ''
l_name VARCHAR(30) NOT NULL DEFAULT ''
addr1 VARCHAR(30) NOT NULL DEFAULT ''
addr2 VARCHAR(30) NULL
addr_city VARCHAR(25) NOT NULL DEFAULT ''
addr_state CHAR(3) NOT NULL DEFAULT ''
addr_zip CHAR(10) NOT NULL DEFAULT ''
primary_phone CHAR(12) NOT NULL DEFAULT ''
secondary_phone CHAR(12) NULL
term_date DATE NOT NULL DEFAULT 0
active_customer ENUM('True', 'False') NOT NULL DEFAULT 'True'
5. Create the second table called logins that will contain the login authentication data. Each customer will be able to have up to 5 logins for their site (this could be given to family and friends to access their photos). The table will contain the following columns:
logins
login_name VARCHAR(30) NOT NULL DEFAULT '', PRIMARY KEY email_customer VARCHAR(50) NOT NULL DEFAULT '', FOREIGN KEY
f_name VARCHAR(20) NOT NULL DEFAULT ''
l_name VARCHAR(30) NOT NULL DEFAULT ''
passwd VARCHAR(30) NOT NULL DEFAULT ''
last_login DATETIME NOT NULL DEFAULT 0
active_login ENUM('True', 'False') NOT NULL DEFAULT 'True' 98
6. Create the third table called images that will contain the information about the photos. The image information will include information to connect the images to other tables, the size of the image and the name of the image that was stored. Later in the course, the code will be created to upload these images with the image information being stored in the database. The table will contain the following columns:
images
id_image INT, UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY
KEY
login_name VARCHAR(30) NOT NULL DEFAULT ''
image_size VARCHAR(30) NOT NULL DEFAULT ''
image_name VARCHAR(60) NOT NULL DEFAULT ''
file_name VARCHAR(60) NOT NULL DEFAULT ''
image_date DATE NOT NULL DEFAULT 0
active_image ENUM('True', 'False') NOT NULL DEFAULT 'True'
7. Create the last table called comments that will be used to keep track of the comments attached to each picture. Each login associated with the images will be able to provide comments against the picture. The table will contain the following columns:
comments
id_comment INT, UNSIGNED AUTO_INCREMENT NOT NULL, PRIMARY KEY
login_name VARCHAR(30) NOT NULL DEFAULT ''
id_image INT, UNSIGNED NOT NULL
comments TEXT NOT NULL DEFAULT ''
log_date TIMESTAMP NOT NULL
active_comment ENUM('True', 'False') NOT NULL DEFAULT 'True' 8. View the list of tables located in the photo_album database.
9. Review the details of each table created and verify that the tables were designed properly. If they are not correct, use the ALTER TABLE command to make the changes to correct the problems.
6.9 Chapter Summary
In this chapter, you have learned to:
• Define Table Properties 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. 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.
• List the Most Common Storage Engines Available
There are numerous storage engines that MySQL supports; however the most common storage types that are used with PHP are MyISAM, InnoDB, NDB and MEMORY.
• Understand MySQL Data Types In MySQL the data types available can be broken down into three major categories: Numeric, String and Temporal
• Create a Table After the database structure is designed and the database has been created, individual tables can be added. Using accurate assignment of data types and their associated options, tables can be added to the database.
• Alter a Table After creating a table, there might be a change of requirements that forces changes to the structure of the table. If that happens, the table's structure can be modified without having to re-create the table.
• Remove a Table DROP TABLE removes one or more tables. All table data and the table definition are removed, so be careful with this statement!
• Explain and Use Indexes An index in MySQL serves to assist in finding row data more quickly and easily, much like an index in the back of a technical manual. Database indexes are used to locate rows in a table. Indexes are special structures that, unlike normal tables, are kept in a specific order. Instead of containing all of the data about an entity, however, an index contains only the column(s) used to locate rows in the data table. It also contains information describing where the rows are physically located. Usually, all the indexes required are created when the tables are created.
• Assign and Utilize Foreign Keys The InnoDB storage engine supports the use of foreign keys (unlike other storage engines). This capability enables the declaration of relationships between columns in different tables, and InnoDB maintains integrity between the tables by prohibiting operations that violate those relationships.
99