Managing Database Tables

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.

SHOW DATABASES;

3. Utilize the photo_album database.

USE photo_album;

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

CREATE TABLE customers (
email_customer VARCHAR(50) NOT NULL DEFAULT '',
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',
PRIMARY KEY (email_customer)
) ENGINE=INNODB;


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).

CREATE TABLE logins (
login_name VARCHAR(30) NOT NULL DEFAULT '',
email_customer VARCHAR(50) NOT NULL DEFAULT '',
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',
PRIMARY KEY (login_name),
INDEX (email_customer),
FOREIGN KEY (email_customer)
REFERENCES customers (email_customer)
ON UPDATE CASCADE
) ENGINE=INNODB;


---- Note: The reason we do not want a DELETE CASCADE on this FK relationship is due to the
fact that we do not want to lose the customer information. By setting the active_customer to
False, we can simply code a response to the customer when they attempt to log in that there
account has been closed. If they wish to pay us, we will be happy to open their account again and
they have lost nothing. However, a policy would have to be in place on when data is archived.

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.

CREATE TABLE images (
id_image INT UNSIGNED AUTO_INCREMENT NOT NULL,
login_name VARCHAR(30) NOT NULL DEFAULT '',
image_size VARCHAR(30) 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',
PRIMARY KEY (id_image),
INDEX (login_name)
) ENGINE=MYISAM;


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.

CREATE TABLE comments (
id_comment INT UNSIGNED AUTO_INCREMENT NOT NULL,
login_name VARCHAR(30) NOT NULL DEFAULT '',
id_image INT UNSIGNED NOT NULL DEFAULT 0,
comments TEXT NOT NULL DEFAULT '',
log_date TIMESTAMP NOT NULL,
active_comment ENUM('True', 'False') NOT NULL DEFAULT 'True',
PRIMARY KEY (id_comment),
INDEX (id_image)
) ENGINE=MYISAM;
8. View the list of tables located in the photo_album database.
SHOW TABLES;

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.

SHOW CREATE TABLE customers\G
SHOW CREATE TABLE logins\G
SHOW CREATE TABLE images\G
SHOW CREATE TABLE comments\G