Question

Help me normalize this data for a text document. I am struggling to grasp this concept and I keep getting stuck and losing points.

1. UNF:
   student(Name,HomeMobilePhoneNumbers,StudentID,MajorCode1,MajorTitle1,MajorCode2,MajorTitle2,MajorCode3,MajorTitle3)
   
   
   3NF:
   
   Table field meanings are:
   
   Three data rows:

   Do not place any extra text in the text file other than as directed in steps 3 thru 5.
   The student table field meanings are:
   • Name is the student's full name.
   • HomeMobilePhoneNumbers is the student's home phone number followed by the student's mobile phone number. Not all students have both numbers.
   • studentID is a unique ID number assigned to the student.
   • MajorCode1, MajorCode2, and MajorCode3 are the major codes for the student's majors. Not all students have three majors.
   • MajorTitle1, MajorTitle2, and MajorTitle3 are the names or the student's majors.
   Business logic:
   • Each student may have more than one major. (no limit - which the UNF table does not allow)
   • No student may have the same major more than once.
   • No student may have the same phone number more than once. Note: The home number is listed first; then the mobile number.
   • Each major has one code and one name.
   • Each major code is unique.
2. Normalize the data and define the new tables.
   Under the 3NF: line:
   1. Show the 3NF table definitions you have created.
      1. Use:
         • * to denote a primary key field.
         • Use @ to denote a foreign key field.
         • Use ^ to denote a unique field that is not a primary key.
      2. Name the tables as you see fit.
      3. Rename the fields as you see fit. You must describe their meanings under the Table field meanings are: line of the text file.
      4. Create new fields as you see fit. You must describe their meanings under the Table field meanings are: line of the text file.
   Hide entire lab data set ▲

   student(Name,HomeMobilePhoneNumbers,studentID,MajorCode1,MajorTitle1,MajorCode2,MajorTitle2,MajorCode3,MajorTitle3)
   Name	HomeMobilePhoneNumbers	studentID	MajorCode1	MajorTitle1	MajorCode2	MajorTitle2	MajorCode3	MajorTitle3
   Avram Hinton	103-391-7811;676-683-6301	34673656	NSA	Network Systems Administration	CSS	Cyber Security Specialist	CST	Computer Support Technician
   Hayes Gibson	447-149-5334	83681840	CST	Computer Support Technician
   Kristen Acosta	;972-658-1570	94086534	CP	Computer Programmer	MAD	Mobile Application Development
   Colette Gibson	447-149-5334;676-683-6301	96299266	NSA	Network Systems Administration	CSS	Cyber Security Specialist
   Kylee Pugh	168-554-8071;362-673-1296	49622000	CSS	Cyber Security Specialist
   Nissim Moreno	831-774-5128	12755860	MAD	Mobile Application Development	WP	Web Programming
   Lael Abbott	733-772-2949;298-846-3570	84228748	CST	Computer Support Technician
   Ethan Gomez	484-285-4061;344-277-7057	40208573	CP	Computer Programmer	WP	Web Programming
   Jena Sosa	814-888-1642;895-585-6779	99496218	CST	Computer Support Technician
   Coby Fox	860-521-5683;418-765-5461	21479092	CST	Computer Support Technician
   Amos McCarthy	435-975-9981;721-269-1301	25357066	CST	Computer Support Technician	NSA	Network Systems Administration
   Caleb Madden	943-541-5201	98159249	CSS	Cyber Security Specialist	CP	Computer Programmer
   Xerxes Hayes	877-544-4230;210-202-5196	83312932	WP	Web Programming	CP	Computer Programmer
   Mira Gentry	;461-854-5007	42683650	MAD	Mobile Application Development	WP	Web Programming	CP	Computer Programmer
   Harper Mcmahon	267-222-8035;530-888-7381	55593577	NSA	Network Systems Administration	CSS	Cyber Security Specialist
   Bruce Maxwell	465-255-0860;217-387-8653	44107438	CP	Computer Programmer
   Lev Fulton	;945-553-7908	32388303	CSS	Cyber Security Specialist
   Montana Dickson	;491-349-5614	53068679	MAD	Mobile Application Development	WP	Web Programming
   Hayden Mcintyre	277-380-2418;785-939-8667	31441510	CST	Computer Support Technician
   Hayes Knapp	779-778-5915;754-114-8609	78171647	WP	Web Programming	MAD	Mobile Application Development
   Lucy Merritt	;294-399-5728	22255753	NSA	Network Systems Administration	CST	Computer Support Technician
   Dorian Morris	780-424-6179;149-150-0084	15665209	WP	Web Programming	MAD	Mobile Application Development	CSS	Cyber Security Specialist
   Halee Bryan	454-713-2195;622-825-8598	10063618	WP	Web Programming
   Craig Warner	302-746-8454;147-544-8901	46483879	CST	Computer Support Technician	CST	Computer Support Technician
   Lev Albert	;620-799-3350	52300353	WP	Web Programming	CP	Computer Programmer	CST	Computer Support Technician

3. Under the Table field meanings are:
   1. Describe any new fields.
   2. Describe any renamed fields, with old field name(s) listed.
   You need only describe new or renamed fields here.
4. Show the first three rows of lab data as they would be stored in the 3NF tables.
   Under the Three data rows: line:
   For each normalized table:
   1. Again, show the table definition.
   2. Show the data that would be placed in the table given the data from the first three rows of the lab data.
5. 
   Show the first three rows of lab data as they would be stored in the 3NF tables.
   Under the Three data rows: line:
   For each normalized table:
6. Again, show the table definition.
7. Show the data that would be placed in the table given the data from the first three rows of the lab data.
   Hide the first three data rows ▲

   Name	HomeMobilePhoneNumbers	studentID	MajorCode1	MajorTitle1	MajorCode2	MajorTitle2	MajorCode3	MajorTitle3
   Avram Hinton	103-391-7811;676-683-6301	34673656	NSA	Network Systems Administration	CSS	Cyber Security Specialist	CST	Computer Support Technician
   Hayes Gibson	447-149-5334	83681840	CST	Computer Support Technician
   Kristen Acosta	;972-658-1570	94086534	CP	Computer Programmer	MAD	Mobile Application Development

Answer 1

Solution:

Student Table

StudentDetails(*studentId,Name)

StudentDetails - This table store the record of the each student

*studentId - This filed is the primary key for student table which will help in uniquely identifying each record.

Name - This filed store the name of the student.

Contact Detail Table

StudentContact(*contactId, @studentId, ^contactNumber)

StudentContact - This table store the information about the contact details of each student.

*contactId - It is the field which is primary key to uniquely identify each record.

@studentId - It is the field which is foreign key to StudentContact table from StudentDetails table, which will help identifying the number belongs to which student.

^contactNumber : This field the store the contact number which can be either phone number or home number and this number will be unique for each student.

Major

Major(*MajorId, ^MajorTitle)

Major - This table store the majors that can be assign to each student.

*MajorId - This field is the primary key, which will help in uniquely identifying each record.

^MajorTitle - This field stores the title of each major and it is unique key therefore title cannot be repeated

Student Major

StudentMajor(*StMajId, @studentId, @MajorId)

StudentMajor - This table store the details about the major subjects hold by the student.

*StMajId - This is the primary key and should be generated by the combination of studentId,MajorId so that a major can occur for a student for one time only.

@studentId - This is the foreign key to StudentMajor table from the student table, which store the student id.

@MajorId - This is the foreign key to StudentMajor table from the Major table, which store the Major id.

We have create StudentMajor table, now we will able to store the n number of majors that a student can have.

Above is the normalized structure for the given table.