SQL Tutorial: Update your database as the structure changes

Want to learn more? Take the full course at https://learn.datacamp.com/courses/introduction-to-relational-databases-in-sql at your own pace. More than a video, you'll learn hands-on coding & quickly apply skills to your daily work.

---

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

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

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.

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.

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.

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.

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

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?

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.

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.

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