HOW TO CREATE A MYSQL RELATIONAL DATABASE

In this tutorial, will we show you how to create a MySQL database for a Content Management System (CMS) that we will be creating in an upcoming tutorial. Once you create a database, you have three things to do with tables:

1. Create them
2. Relate them
3. Populate them

Before we can create a MySQL database, it is best practice to download a bundle software package to install the necessary resources that are needed to create it. There are a host of packages available (i.e., LAMP, MAMP, ZendAMF, etc.). However, we will download a popular package known as XAMPP.  XAMPP will install Apache, MySQL, Perl and PHP. The "X" in XAMPP represents one of four operating systems (Windows, Mac, Solaris, Linux) that are supported.

Installing XAMPP (If neccessary)

If you do not already have XAMPP installed, follow the procedures below:

1. Download XAMPP to your computer. (Go to http://www.apachefriends.org/en/xampp.html)
2. Double click on the installation file to install XAMPP.  While installing XAMPP, if you accept the default options, it will be installed in the c:/xampp directory unless you specified otherwise.
   NOTE: If you don't have permission you can install XAMPP in whatever folder you want. However, for the sake of this tutorial, we will install it in hte default location mentioned earlier.
3. Open the XAMPP Control Panel Application dialog box (All Programs > ApacheFriends > XAMPP Control Panel) and turn on Apache and MySQL by clicking on the Start button for both.
   NOTE: You can stop either services by clicking on their Stop button.
   
   
   
   NOTE: You should see the word Apache and MySql highlighted in green to let you know that both services started correctly. In  older versions you should see the word Running higlighted next to Apache and MySql.
   
   
4. Test to see if the server is set up correctly to "serve up" pages by typing http://localhost or http://127.0.0.1 into a web browser (i.e., Firefox or Internet Explorer).
   
   NOTE: For Mac users the path is: http://localhost/~username/phpMyAdmin/
   
   
5. Click on a language (i.e., English) on the page that is displayed.
   
   NOTE: You should see anohter page with a title of "XAMPP for Windows" and a note that says "Congratulations: You have successfully installed XAMPP on this system!"

It is important to remember that you type a URL (http://localhost) that points to a FILE DIRECTORY (C://xampp/htdocs/MyApplicationFolder/).
When you test the server, you should see a page that displays XAMPP directory.  If you get a message that states page cannot be displayed, check:

• To make sure you are not running IIS at the same time. If so, turn off IIS (Control Panel > System  Administrative Tools> Services and select IIS and stop service by clicking on the Stop button).
• If the previous step does not work, uninstall and then reinstall XAMPP and test again.

Normalizing Data (Information only)

Duplicated data can cause errors every time records in a database is inserted, updated or deleted and can lead to unpredictable results such as the inability of entering data into a database. Duplicate data happens when two or more fields are inadvertently combined into a single table. To avoid this from happening, if the table is designed using a process called normalization, it will be free from duplicates. To normalize tables, you have to split the tables into separated tables with the correct fields in them. A database is said to be normalized when each fields contain one value and each table has the correct number of fields. Normalization can be summarized as follow:

• Each table should have a primary key that will always to unique that is used to identification a given record.
• Each group of associated data should have its own table.
• Tables should be cross-reference using a primary key in the parent table and a foreign key in its child table.
• Only one item should be stored in each field (i.e., you can not have two phone numbers is a single field).

Creating a MySQL Database

Typically, the first step in creating a dynamic database application is to create a database. 

IMPORTANT NOTE: While the steps are generic; however, the values (in bold and blue) are specific to the upcoming lesson.

1. Type in localhost (or 127.0.0.1) in a browser and then click on the phpMyAdmin button in the left sidebar.
   TIP: A shortcut to phpMyAdmin is localhost/phpmyadmin/.
2. Give the database a name (in our case, type rmcs_corporation1234) in the Create new database field and then click the Create button.
   TIP: It is important to give your database a name the can not be easily hacked. This is the reason the 1234 was added to the database name.
   NOTE: MySQL will automatically convert any database name to all lowercase so you may want to insert underscores between words (i.e., tb_student_directory).
   

• phpMyAdmin confirmed that the database was created.
• phpMyAddmin created a folder in xampp/htdocs/mysql/data/ with the name of the database you gave it (i.e., rmcs_corporation1234). Within that folder you will see a file named db.opt.   When you create tables in the next step, you willl see several additional files with the name of your tables with an ".frm" extension (i.e., tb_studentdirectory.frm).

Creating Tables for the Database

After you create the database, you need to create table(s) inside of it.  In our case, we will be creating the following four tables for our CMS that we will be using in an upcoming lesson:

• Student Directory
• Information Services
• Department Site Management
• Username/Password

It is best practice to create a Entities Relationship Diagram  (ERD) to define tables structure before creating a MySQL database. It can be difficult to change some aspect of a table once it is created and you may have to recreate it if it is not done correctly.  Also, before populating a table, you need to define its structure which includes.

Click on the tab below to see detail information on how to structure a table:

Click on the tab below to see detail information that is common to all tables that you will create.

 

Create First Table:

1. In the table page, type a table name (in our case, type Students) in the Create new table text field.
2. Type the number of fields you need for your database (in our case, type 13) in the Number of Fields text field box and click the Go button to save the table.
3. Type or select the information you need for the first row. In our case, type:
1. Type field name (i.e., StudentID) in the Field column
2. Select data type (i.e., INT) in the Type column
3. Type number of column (i.e., 11) in the Length/Value column or you can not type anything and it will default to 11
4. Select index type (i.e., PRIMARY) for the Index column
5. Select checkbox of AI (Auto Increment) column
6. Leave other columns at their default.
4. Click the Save button to save the current table.
5. Repeat steps 1-3 for the other rows. In our case, in rows 2-13, click the first tab below to see the additional fields we added for each row.
   NOTE: phpMyAdmin will create a link in the sidebar to the table you just created.

Create Other Tables:

6. Click on the database name in the left sidebar. This will allow you to create a new table.
   
   
   
   
7. Repeat steps 1-5 for any additional tables you need to create.  In our case, use the information in the tables below by clicking on a tab below for each table info. Table cells that are left blank below implies that default values will be used or it is not appliable to the value for those fields. For example, you don't need to set a Length/Value for Date or Time because the Data Type determines the lenght/value for it.
   

 

 

Relating Tables (Need Updating to MySQL instead of MS Access)

After a database has been created and tables inserted into it. It is important to create "relationship" between particular tables. Because EACH asset must be assigned to a user from the tbHumanResources table, a relationship between these two tables will be created. Also, because a username and password is assigned to EACH employee from the tbHumanResources table, a relationship between these two table will be created as well.

1. With the database (in our case, rmcs_database1234.mdb) still open, select Tools > Relationships from the menu.
2. In the Show Table dialog box that appears, select all four tables and click the Add button.
   NOTE: Tables must be saved to see all fields listed.
3. Click on the Close button to close the Show Table dialog box.
   NOTE: You should see the four tables in the Relationships window.
   
4. Click the EmployeeID field in the tbInformationServices and drag it onto the EmployeeID field in the tbHumanResources box.
   NOTE: A one-to-many relationship is created  which means that one employee record in the tbHumanResources table can have many records in the tbInformationServices table (i.e., phone, computer, etc.).
5. In the Edit Relationships dialog box, check ALL three options and then click the Create button.
   NOTE: By checking the Referential Integrity, when a record is delete from the tbHumanResources database, the records will also be deleted in the tbInformationServices database.
6. Click and drag the EmployeeID field in the tbUsernamePassword box onto the EmployeeID field in the tbHumanResources box.
7. Again, select all three check boxes and click the Create button to create a relationship between these two tables.
8. Close the Relationships window and save the file.
   NOTE: If you are prompted with a Security Alert, click on the Option button and select the "Enable this content."

Populating Tables

After you create a database and its table(s), you will need to populate those tables. You can either populate the tables from within:

• phpMyAdmin
• The web application (CMS)

We will be populating these tables in upcoming tutorials from within the web application that will be create.

Setting Privilege and Creating A User

When you installed MySQL, it created one registered user called "root" which allow unfeathered access to all of the databases. This should only be used by the administrator with a username and password that is created. It is best practice to create a user access for each specific database as well. Typically, the administrator has complete access and users have limited access.

Create administrative access by creating a password:

1. Click on the Privileges tab at the top of the page to view a list of user accounts.
   NOTE: If this is a new installation, you should only see the root user account.
2. Click on the Edit Privileges icon under the Action column and the row root of the table to open the Edit Privileges screen.
3. In the Global Privileges, click on the Check All link at the top of the screen to select all of the checkboxes for Data, Structure, and Administration sections.
   NOTE: Notice that four Data privileges types matches the SQL command types (i.e., SELECT, INSERT, UPDATE and DELETE).
4. In the Change Password section, select the Password radio button and then type the same password in botth field.
5. Leave Password Hasting at its default.
6. Click the Go button in the Change password section.
   NOTE: If you selected "config" as the authentication type when you set up phpAdmin, you need to update the config.inc.php file manually by updating the following line: $cfg['Servers'] [$i] ['passord'] = 'enterNewPassWordHere' ;

You can create user accounts that can have fewer privilleges, if necessary, for a single database.

Create user privileges:

1. Click on the Privileges tab at the top of the page to view a list of user accounts.
   NOTE: If this is a new installation, you should only see the root user account.
2. Click on the Add a new User link at the bottom of the page.
3. In the Login Information section, change the following settings:
   1. Type a user name in the second field of the User name row.
   2. Click on the drop-down menu next the the word Host to select localhost to show the word localhost in the second field.
   3. Type the same password in teh Password and Re-type fields.
4. In the Global privileges section, simply click the Go button.
   NOTE: You should see a confirmation that says "You have added a new user."
   NOTE: You don't want to grant access to all of the database.
5. In the Database-specific privileges, select the option you need and then click the Go button.
   NOTE: To delete a user, click on the localhost link and the select the user you want to delete by clicking on the checkbox next to it and then click the checkbox in the Remove selected users section and then click the Go button.