• 20 hours
  • Medium

Free online content available in this course.

course.header.alt.is_video

course.header.alt.is_certifying

Got it!

Last updated on 11/25/19

Create a database

Log in or subscribe for free to enjoy all this course has to offer!

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  condo :

  • type that in the Database  name  field

  • 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!

  • Click "Create."

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!

Create 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:

Domain diagram describing the relationships between the apartment, dog, and cat tables.

We'll start with the dog table.  This is what the class diagram looked like for that class:

The dog 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.

Creating the dog table with 5 rows
Creating the dog table with 5 rows

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

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.

A big list of types of Type!
A big list of types of Type!

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

  •  BLOB  for 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.

Length Column

For attributes with the type  INT ,  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.

For  VARCHAR  attributes,  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.

Index Column

Skip ahead to the Index column.  Here you'd choose PRIMARY because we want this attribute to be the primary key. 

A_I Column

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

Type in  apt_id  in the Name column.  

The  column apt_id  will be an  INT  just like  dog_id .  Its length should be the same as what you used for  dog_id .

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

The type for this column is  TINYINT  and its length is  1 .

Then click Preview SQL and copy that into a text editor so we can look at it in a later chapter. Now click Save!

More tables!

Create the  apartment  and the  cat  tables.

Here are the class diagrams for those classes:

The apartment class and the cat class
The apartment class and the cat class

For the  apartment  table:

  • I planned that  apt_id  and  cat_id  should be auto incremented, so don't forget to click that box!

  • Also because  apt_id  and  cat_id  are 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  INT .

  • In this imaginary building, no apartment will be larger than 10,000 square feet. The length of the  INT  should be 5.  Also, the field would hold just the integer, so it would be  1000  and not  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  INT  or a  TINYINT  and the length should be 1.

Creating the Junction Table

Here's the class diagram for the  fed_at  class.

The fed_at association class
The fed_at association class

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. 

For the  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:

cat_id

apt_id

fed

1

2

Pro Plan Focus Urinary Tract Health Canned Cat Food

2

13

mice

44

1

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. 

Create the  fed_at  table in phpMyAdmin

The columns  cat_id  and  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  255 .

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:

AUTO_INCREMENT is enabled for cat_id
AUTO_INCREMENT is enabled for cat_id

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.

Example of certificate of achievement
Example of certificate of achievement