Once you are in phpMyAdmin, click on the database tab at the top of the page. My page now looks like this:
Now let's make a database for our community who lives in the condominiums.
Let's call the database
type that in the Database
in the Collation pulldown,
choose utf8mb4_unicode_ci, which is probably the very last one in a very long list. Collation is the character set the the database will use!
On the right side of the screen, you should see condo as one of the current databases!
Go ahead and click condo to drill down and see what is there.
You'll notice nothing is there because we have to make some tables!
I've simplified the relational model from the previous UML section and updated the naming convention.
Let's create the tables in the database that correspond to these UML classes:
We'll start with the dog table. This is what the class diagram looked like for that class:
You can see that there are 5 attributes, so you'll need to create 5 columns for this table. Input the name and the number of columns.
Now we have a list of 5 rows (1 for each attribute) and loads of columns to specify information about how each attribute is to be handled. Don't be too intimidated! We don't need to specify much information.
The first field will the name of the first attribute in the dog class, namely
In the class diagram, this attribute is the primary key (PK). I want the database to create this ID automatically by auto incrementing it. I'll be clarifying this very soon.
The next column after Name is Type, and it contains a very long pulldown list.
Types of Type
The type limits what is acceptable to put in this field. If you try to put a person's name into a field that is only for dates, the database will throw an error.
The basic types are:
INT- short for an integer
VARCHAR- this is for all the keys on your keyboard for data that is less that 255 characters long. You can set the length to be shorter too.
TEXT- is used for longer text and doesn't have a limit
DATETIME- for dates that include the time
BLOBfor binary information
There are sub-types of these types to further specify what is allowed. We'll go over some of the sub-types in the next chapter. Let's start with the basic types.
Choosing the type is part of the formality of setting up and using a database. It helps ensure that the data's format does not change and that the right information is being put in a specific field.
The cool thing is that we are setting the rules and the data has to play by those rules (and so do the other programmers who write code that
UPDATE s or
INSERT s information into the database).
Another benefit to setting the data-type is that the database will know what to expect, so it won't become a memory hog.
The tricky thing is that if we give it too general a description then the database will use too many resources. On the other hand if we are too restrictive, our data will get rejected.
So in the case of
dog_id , we want the database to automatically generate the primary key (PK) for the table. The database will choose an integer, so we should choose
INT for the type.
For attributes with the type
Length specifies how large the display size should be. This does not correspond to how large an
INT can be. Does that sound confusing? It does to me! For integers of all types, I just accept the default for this field by leaving it blank.
Length specifies the maximum number of characters that can be saved in this field.
If you don't specify a value, the database assumes you need to reserve the largest size for the type. So if you don't put anything in this field the database will have room for millions of dogs. Maybe that is overkill. So you could make it something like 7 - which would certainly work for our purposes, or you could just leave it blank.
Skip ahead to the Index column. Here you'd choose PRIMARY because we want this attribute to be the primary key.
A_I is a checkbox and it stands for Auto Increment, which is exactly what we want it to do to it, so check that.
That is all we need for this row. The rest will be even easier!
Inputting the rest of the dog table's columns
The apt_id column
apt_id in the Name column.
column apt_id will be an
INT just like
dog_id . Its length should be the same as what you used for
The name column
The next attribute is name. And the name of the name is
name so type that in.
The type will be
VARCHAR . We can limit the length to 10 and that will work for most dogs. However, if there is a dog named Mr. Trumpington's Footman, he will either have a truncated name or will get rejected. Let's therefore allow 50 characters as the maximum length for a dog's name.
The breed column
The next row is breed. This is also a
VARCHAR . 50 should work, but let's put 100 here just to be sure.
Does your dog bite?
The next row is bites. This attribute records the answer to the question, "Does your dog bite?"
The answer is either Yes or No.
"Does your dog bite?" is a Boolean question. We therefore save it with the type
The type for this column is
TINYINT and its length is
Then click Preview SQL and copy that into a text editor so we can look at it in a later chapter. Now click Save!
apartment and the
Here are the class diagrams for those classes:
I planned that
cat_idshould be auto incremented, so don't forget to click that box!
cat_idare the PK's, don't forget to tell the phpMyAdmin that these are the primary keys.
I imagined that the size attribute would hold the square footage of the apartment. This should be an
In this imaginary building, no apartment will be larger than 10,000 square feet. The length of the
INTshould be 5. Also, the field would hold just the integer, so it would be
1000sf( sf for square feet).
For the bedrooms attribute, I image that there won't be an apartment with more than 9 bedrooms, so the
TINYINTand the length should be 1.
Creating the Junction Table
Here's the class diagram for the
This table does not have a single primary key (PK) because it is a special type of table - a junction table. Junction tables occur in "many to many" relationships. In the UML domain diagram, they are not connected directly to another table. Rather, they are directly connected to the line representing the relationship between the tables.
fed attribute, I planned that this would be filled with the kind of food the cat was fed in this apartment.
A few rows of the
fed_at table could look like this:
Pro Plan Focus Urinary Tract Health Canned Cat Food
Blue Buffalo Freedom Grain Free Indoor Chicken Recipe Adult Canned Cat Food
We are not saving the date the cat slept at this apartment - just that it stays here sometimes - and that it eats a specific kind of food in this apartment.
fed_at table in phpMyAdmin
apt_number will have the same type and length that they did in the cat and the apartment tables.
Look in the table above about how long cat food names can be!
fed has got to be a
VARCHAR with a length of
Junction tables have 1 row for each instance of a relationships between tables. As a result, both the
cat_id and the
apt_number are equally important when defining what makes this row unique .
Consequently, individually, neither is the primary key. But we can use them together to make a composite primary key.
And because a primary key is unique, only 1 association of a cat and an apartment is permitted. This means that there cannot be more than 1 row where a specific cat and a specific apartment appear together.
That limitation is just what we want!
To set up a composite primary key, make sure you have the
fed_at table selected with the Structure tab active.
Then select the
cat_id and the
apt_number rows and then click the word Primary to make them the composite keys.
Updating the structure of a table
If you forgot, click the table's name on the left side of the screen. Then click the "Structure" tab at the top of the screen. You can either click the word Primary on the row and then agree to altering the table's structure, or you can click the pencil with the word "change" on the row you want to be the primary key. If when you click the word Primary and nothing happens - that means that it is already the primary key.
Verify that you've made the primary key of the cat table with the AUTO_INCREMENT checked. If you are not sure, your screen should look like this:
In my version of phpMyAdmin I can tell that
cat_id is the primary key because there is that symbol of a key next to
cat_id . Your version may be different, but it will indicate somewhere on the screen that
cat_id is the primary key. It is important to know where that "somewhere" is.
Next we'll look into Foreign Keys and how to register them with the database.