Creating Faculty Tables

First, we should figure out what tables we need. In the table above, note the following:

Every professor has a first and last name, but not all professors have a middle initial.
Every professor has a unique email address based off their USF username. This means we can use this username as a primary key for each professor. (This might not be space efficient, however, if we expect this primary key to be repeated in multiple tables.)
Only some professors have GitHub usernames listed. Each GitHub username is unique.
Each professor teaches a different number of undergraduate courses.

We do not want to over-complicate things by creating too many tables, but we also do not want a lot of wasted space in each table. We will strike this balance as follows:

For columns that have one-to-many relationships, like courses, we will create separate tables.
For columns that have one-to-one relationships where the columns are likely to be non-empty, like first name, last name, and username, we will keep them in the same table.
For columns that have a one-to-one relationship where the columns are likely to be empty, like GitHub username and middle initial, we will demonstrate the two options: keeping middle initial in the same table, but splitting GitHub usernames to a separate table.

We will need to setup some relationships for these tables, namely that the GitHub and courses tables refer to professors that already exist in the faculty table.

Create Faculty Table

We will create a faculty_names table with 1 row per professor that stores:

The USF username (named usfid), which must be unique, may not be null, and can be used as the primary key for this table and the foreign key for other tables.
The first, middle, and last names in separate columns. The middle initial may be null. None of these columns need to be unique. For example, more than one person has the first name David in the department.

Exercise: See if you can create a faculty_names table that will match the following:

Field	Type	Null	Key	Default
usfid	char(20)	NO	PRI	NULL
first	varchar(20)	NO		NULL
middle	char(1)	YES		NULL
last	varchar(20)	NO		NULL

See Answer

Once you have the table created, you can insert values as follows: