Part 1: 150 words, response to discussion.
This discussion exercise provides me hands on experience on how to normalize as well as demoralize tables.
The goal of data normalization is to eliminate data redundancy and inconsistency which are important consideration we should take seriously as a database professional because storing objects in a relational database that maintains the same information in several places is incredibly difficult.
One important point to notice is the application of primary and foreign keys in the new tables. The ProspectName table has kept PROSPECT_Id, which was the original primary key of prospect table, as its primary key. To maintain the relationship back to prospectName, the new tables include the PROSPECT_ID column. Clearly, prospect_id is not unique for other tables and additional keys are needed to form composite primary keys.
The only change I would make is merging the two tables ProspectEmail and ProspectPhone into one as ProspectContacts because from practical point of view we will need to back out of normalizations for performance reasons and both phone number and email address are contacts we need to find on one table.
Part 2: 150 words, response to discussion
You learned to explain the results of normalized tables, and found out that:
Query 2 utilizes a subquery construction to create a new table name ProspectEmail. ProspectEmail has the attributes characterizing the "email" of a student prospect. All attributes are simple and the primary key PROSPECT_ID uniquely identifies the email of the student prospect;
Query 3 uses a subquery construction to create a new table name ProspectAddress. ProspectAddress has the attributes characterizing the "address" of a student prospect. All attributes are indivisible and the primary key PROSPECT_ID uniquely identifies the address of the student prospect.
Query 4 utilizes a subquery construction to create a new table name ProspectPhone. ProspectPhone has the attributes characterizing the "phone" of a student prospect. All attributes are atomic and the primary key PROSPECT_ID uniquely identifies the phone of the student prospect.
Query 5 uses a subquery construction to create a new table name ProspectEmployer. ProspectEmployer has the attributes characterizing the "employer" of a student prospect. All attributes are indivisible and the primary key PROSPECT_ID uniquely identifies the employer of the student prospect.
Query 6 utilizes a subquery construction to create a new table name ProspectAudit. ProspectAudit has the attributes characterizing the "audit" of a student prospect. All attributes are simple and the primary key PROSPECT_ID uniquely identifies the audit of the student prospect.
You learned to explain the normalization process of a table and to normalize a table in the First, second and Third Normal Forms. Each repeating group from the prospect table is moved to a separate table and redundant data are eliminated, thus we end up with 4 new tables: Contact, Address, Employer, and Registration. Upon eliminating columns not dependent on keys, I got 7 tables in the 3rd Normal