PLEASE THE SECOND IMAGE TO COMPLETE THE FIRST ASSIGNMENT FROM THE FIRST IMAGE. THANKS

INFORMATION TO USE TO COMPLETE NORMALIZED TABLE

|NORMALIZING THE STUDENT LIST LAB 2: Convert the student.xlsx "List" spreadsheet into "Normalized tables The following steps will help you understand how to convert the student list (see attached file student.xlsx. into a normalized set of tables. NOTE: Refer to the videogame example from the Aug 11 class example.) 1. Create 2 additional FOREIGN TABLES. Two redundant COLUMNS (MAJOR and STATUS) should have corresponding foreign key tables that hold the redundant data from those columns in the original student list. There should be a row in the new foreign key table for each of the redundant cells from the student list. Each row in the foreign key table should have a primary key value. You must include the table and column names highlighted in yellow. 2. Replace the FOREIGN KEY DATA: Replace the redundant data in the original list with the primary key values from the foreign key table. Note: There are two columns of redundant data that are not specific to the individual student 3. Add a unique STUDENT ID column to the original table. Add a student ID column to the beginning of the student list. Type in a unique value for each student NOTE: Since each student has more than one row of data, you will have their primary key value repeating multiple times. NOTE: Since you still have redundancy in the student ID column, it is not yet unique and cannot be a primary key. 4. Convert the STUDENT ID column into a PRIMARY KEY: Make the student ID column a primary key by creating unique rows. In order to do this, you must delete rows of student information that is redundant. However, you cannot do this because you will lose multiple hobbies listed for each student. The Solution is to create two additional tables by following these steps: a. Create a "HOBBIES table that contains the primary key hobby ID and another column for the hobby name/description. b. Create a "STUDENT_HOBBIES" table that contains the studentID and the hobbyld. This is a very important table called an "associative" or intercept table. It allows the students to have as many hobbies as they want c. Modify the original STUDENTS table by removing the hobby column. NOTE: You have completed the normalization, you should the following table structures and data: (1) STUDENTS, (2) MAJORS, (3) STATUS, (4) HOBBIES, (5) STUDENT_HOBBIES When you have completed making the changes, save your spreadsheet and upload it to D2L. 12 : x fi gaming M N 0 32 F A B D E E F G H 1 J K 1 FIRSTNAME LASTNAME EMAIL PHONE ADDRESS MAJOR ID GPA CREDITHOURS GENDER MEALPL STAT ID HOBBY 2 ANNA BELL ABELL@BELLSOUTH.NET 678-555-4321 34 elm st, overth NURSING SICK PEOPLE 2.70 N ACTIVE gaming 3 ANNA BELL ABELL@BELLSOUTH.NET 678-555-4321 34 elm st, overth NURSING SICK PEOPLE 2.70 32 F N ACTIVE figure skating 4 ANNA BELL ABELL@BELLSOUTH.NET 678-555-4321 34 elm st, overth NURSING SICK PEOPLE 2.70 32 F N ACTIVE interpretive dance 5 LORETTA DECABLEGUY DCG@aol.com 678-555-3333 19 nike lane, far: INFORMATION TECHNOLOGY 1.60 12 F Y ACTIVE paint ball 6 LORETTA DECABLEGUY DCG@aol.com 678-555-3333 19 nike lane, far: INFORMATION TECHNOLOGY 1.60 12 F Y ACTIVE kick boxing 7 LORETTA DECABLEGUY DCG@aol.com 678-555-3333 19 nike lane, far: INFORMATION TECHNOLOGY 1.60 12 F Y ACTIVE guitar hero 8 JOHN DOE JDOE@aol.com 678-555-1234 14 pine street, n UNDECIDED 3.80 22 M Y ACTIVE gaming 9 JOHN DOE JDOE@aol.com 678-555-1234 14 pine street, n UNDECIDED 3.80 22 M Y ACTIVE dragoncon 10 JOHN DOE JDOE@aol.com 678-555-1234 14 pine street, n UNDECIDED 3.80 22 M Y ACTIVE walking 11 JOE SHMOE JOES@aol.com 678-555-2222 22 sesame stree CHEMISTRY 3.35 10 M Y ACTIVE gaming 12 JOE SHMOE JOES@aol.com 678-555-2222 22 sesame stree CHEMISTRY 3.35 10 M Y ACTIVE dragoncon 13 CHRIS TOFFER CHRIS@aol.com 678-555-1111 123 yaheehee la FILM STUDIES & ENTERTAINMENT 4.00 44 Y INACTIVE astron physics 14 CHRIS TOFFER CHRIS@aol.com 678-555-1111 123 yaheehee la FILM STUDIES & ENTERTAINMENT 4.00 44 Y INACTIVE walking 15 CHRIS TOFFER CHRIS@aol.com 678-555-1111 123 yaheehee la FILM STUDIES & ENTERTAINMENT 4.00 44 Y INACTIVE guitar hero 16 17 18 19 20 21 22 List Normalized + |NORMALIZING THE STUDENT LIST LAB 2: Convert the student.xlsx "List" spreadsheet into "Normalized tables The following steps will help you understand how to convert the student list (see attached file student.xlsx. into a normalized set of tables. NOTE: Refer to the videogame example from the Aug 11 class example.) 1. Create 2 additional FOREIGN TABLES. Two redundant COLUMNS (MAJOR and STATUS) should have corresponding foreign key tables that hold the redundant data from those columns in the original student list. There should be a row in the new foreign key table for each of the redundant cells from the student list. Each row in the foreign key table should have a primary key value. You must include the table and column names highlighted in yellow. 2. Replace the FOREIGN KEY DATA: Replace the redundant data in the original list with the primary key values from the foreign key table. Note: There are two columns of redundant data that are not specific to the individual student 3. Add a unique STUDENT ID column to the original table. Add a student ID column to the beginning of the student list. Type in a unique value for each student NOTE: Since each student has more than one row of data, you will have their primary key value repeating multiple times. NOTE: Since you still have redundancy in the student ID column, it is not yet unique and cannot be a primary key. 4. Convert the STUDENT ID column into a PRIMARY KEY: Make the student ID column a primary key by creating unique rows. In order to do this, you must delete rows of student information that is redundant. However, you cannot do this because you will lose multiple hobbies listed for each student. The Solution is to create two additional tables by following these steps: a. Create a "HOBBIES table that contains the primary key hobby ID and another column for the hobby name/description. b. Create a "STUDENT_HOBBIES" table that contains the studentID and the hobbyld. This is a very important table called an "associative" or intercept table. It allows the students to have as many hobbies as they want c. Modify the original STUDENTS table by removing the hobby column. NOTE: You have completed the normalization, you should the following table structures and data: (1) STUDENTS, (2) MAJORS, (3) STATUS, (4) HOBBIES, (5) STUDENT_HOBBIES When you have completed making the changes, save your spreadsheet and upload it to D2L. 12 : x fi gaming M N 0 32 F A B D E E F G H 1 J K 1 FIRSTNAME LASTNAME EMAIL PHONE ADDRESS MAJOR ID GPA CREDITHOURS GENDER MEALPL STAT ID HOBBY 2 ANNA BELL ABELL@BELLSOUTH.NET 678-555-4321 34 elm st, overth NURSING SICK PEOPLE 2.70 N ACTIVE gaming 3 ANNA BELL ABELL@BELLSOUTH.NET 678-555-4321 34 elm st, overth NURSING SICK PEOPLE 2.70 32 F N ACTIVE figure skating 4 ANNA BELL ABELL@BELLSOUTH.NET 678-555-4321 34 elm st, overth NURSING SICK PEOPLE 2.70 32 F N ACTIVE interpretive dance 5 LORETTA DECABLEGUY DCG@aol.com 678-555-3333 19 nike lane, far: INFORMATION TECHNOLOGY 1.60 12 F Y ACTIVE paint ball 6 LORETTA DECABLEGUY DCG@aol.com 678-555-3333 19 nike lane, far: INFORMATION TECHNOLOGY 1.60 12 F Y ACTIVE kick boxing 7 LORETTA DECABLEGUY DCG@aol.com 678-555-3333 19 nike lane, far: INFORMATION TECHNOLOGY 1.60 12 F Y ACTIVE guitar hero 8 JOHN DOE JDOE@aol.com 678-555-1234 14 pine street, n UNDECIDED 3.80 22 M Y ACTIVE gaming 9 JOHN DOE JDOE@aol.com 678-555-1234 14 pine street, n UNDECIDED 3.80 22 M Y ACTIVE dragoncon 10 JOHN DOE JDOE@aol.com 678-555-1234 14 pine street, n UNDECIDED 3.80 22 M Y ACTIVE walking 11 JOE SHMOE JOES@aol.com 678-555-2222 22 sesame stree CHEMISTRY 3.35 10 M Y ACTIVE gaming 12 JOE SHMOE JOES@aol.com 678-555-2222 22 sesame stree CHEMISTRY 3.35 10 M Y ACTIVE dragoncon 13 CHRIS TOFFER CHRIS@aol.com 678-555-1111 123 yaheehee la FILM STUDIES & ENTERTAINMENT 4.00 44 Y INACTIVE astron physics 14 CHRIS TOFFER CHRIS@aol.com 678-555-1111 123 yaheehee la FILM STUDIES & ENTERTAINMENT 4.00 44 Y INACTIVE walking 15 CHRIS TOFFER CHRIS@aol.com 678-555-1111 123 yaheehee la FILM STUDIES & ENTERTAINMENT 4.00 44 Y INACTIVE guitar hero 16 17 18 19 20 21 22 List Normalized +