SQL Tutorial: Update your database as the structure changes

Well done so far. You now have a database consisting of 5 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. University 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'll 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 organization column is spelled is not consistent with the American style spelling of this table, using an S instead of a set. In the first exercise after the video, you'll 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. That is Rename column old name to new name. Also, the University short name 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 column's first name and last name uniquely identify a professor, so the university short name 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 first name, last name, 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'll.