Update your database as the structure changes

1. Update your database as the structure changes

Well done so far. You now have a database consisting of five different tables. Now it's time to migrate the data.

2. The current database model

Here's the current entity-relationship diagram, showing the five tables.

3. The current database model

At this moment, only the "university_professors" table holds data. The other four, shown in red, are still empty. In the remainder of this chapter, you will migrate data from the green part of this diagram to the red part, moving the respective entity types to their appropriate tables. In the end, you'll be able to delete the "university_professors" table.

4. Only store DISTINCT data in the new tables

One advantage of splitting up "university_professors" into several tables is the reduced redundancy. As of now, "university_professors" holds 1377 entries. However, there are only 1287 distinct organizations, as this query shows. Therefore, you only need to store 1287 distinct organizations in the new "organizations" table.

5. INSERT DISTINCT records INTO the new tables

In order to copy data from an existing table to a new one, you can use the "INSERT INTO SELECT DISTINCT" pattern. After "INSERT INTO", you specify the name of the target table – "organizations" in this case. Then you select the columns that should be copied over from the source table – "unviversity_professors" in this case. You use the "DISTINCT" keyword to only copy over distinct organizations. As the output shows, only 1287 records are inserted into the "organizations" table. If you just used "INSERT INTO SELECT", without the "DISTINCT" keyword, duplicate records would be copied over as well. In the following exercises, you will migrate your data to the four new tables.

6. The INSERT INTO statement

By the way, this is the normal use case for "INSERT INTO" – where you insert values manually. "INSERT INTO" is followed by the table name and an optional list of columns which should be filled with data. Then follows the "VALUES" keyword and the actual values you want to insert.

7. RENAME a COLUMN in affiliations

Before you start migrating the table, you need to fix some stuff! In the last lesson, I created the "affiliations" table for you. Unfortunately, I made a mistake in this process. Can you spot it? The way the "organisation" column is spelled is not consistent with the American-style spelling of this table, using an "s" instead of a "z". In the first exercise after the video, you will correct this with the known "ALTER TABLE" syntax. You do this with the RENAME COLUMN command by specifying the old column name first and then the new column name, i.e., "RENAME COLUMN old_name TO new_name".

8. DROP a COLUMN in affiliations

Also, the "university_shortname" column is not even needed here. So I want you to delete it. The syntax for this is again very simple, you use a "DROP COLUMN" command followed by the name of the column. Dropping columns is straightforward when the tables are still empty, so it's not too late to fix this error. But why is it an error in the first place?

9. A professor is uniquely identified by firstname, lastname only

Well, I queried the "university_professors" table and saw that there are 551 unique combinations of first names, last names, and associated universities. I then queried the table again and only looked for unique combinations of first and last names. Turns out, this is also 551 records. This means that the columns "firstname" and "lastname" uniquely identify a professor.

10. A professor is uniquely identified by firstname, lastname only

So the "university_shortname" column is not needed in order to reference a professor in the affiliations table. You can remove it, and this will reduce the redundancy in your database again. In other words: The columns "firstname", "lastname", "function", and "organization" are enough to store the affiliation a professor has with a certain organization.

11. Let's get to work!

Time to prepare the database for data migration. After this, you will migrate the data.

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.