90.2K

Verified Solution

Link Copied!

The attached file Professor-Students.xlsx has the initial table created during the analysis.

It shows a list of students and courses. How will you change that into a format that can be read into a database program?

So do our Database Design on both files and submit the results.

Be aware that grade depends on both student and course.

But gpa depends on student only.

DB Exercise Here is the initial list- what are the three steps to change it into a normalized DB, as in previous tab? This is the list of students taking courses. Imagine how this would be in real life with 20,000 students and 1,000 professors? course_no course_name credits semester stud_no stud_name gpa prof_no prof_name Prof_phone grade 1 Elem Eco 2 Fa 15 1 Tim 3.2 1 S. Katz 222-3333 A 1 Elem Eco 2 Fa 15 2 Sue 3.5 1 S. Katz 222-3333 B 2 3 Fa15 2 Sue 3.5 2 D. Gold 555-2222 B+ Prog NOTE: Every row in a database must be different than every other row. Also you must have key fields which choose only one record Step 1 - normal form 1 - requires no empty fields. B7-E7 assume those fields are the same as the values in row 6 How do we fix that? Copy the data from row 6. Now you are in Normal Form 1 But you need a key for the database? Solution make a key from 2 fields that are always different. Which fields2? Step 2 - Now you are in normal form1 - how do you get to normal form 2? Some fields depend on only part of the key. To fix, create a new table for those that rely on part 1 and a new table for those that rely on part 2 And a table for those that rely on both parts 1 and 2 Step 3 - Now you are in normal form 2 - how do you get to normal form 3? Some fields do rely on their key, as required. But some fields rely on the key indirectly, by going thru another field. E.g. if you store both name and phone number, the phone number depends on the name which in turn depends on the key. Move out these fields to another table. Now you are completely normalized. DB Exercise Here is the initial list- what are the three steps to change it into a normalized DB, as in previous tab? This is the list of students taking courses. Imagine how this would be in real life with 20,000 students and 1,000 professors? course_no course_name credits semester stud_no stud_name gpa prof_no prof_name Prof_phone grade 1 Elem Eco 2 Fa 15 1 Tim 3.2 1 S. Katz 222-3333 A 1 Elem Eco 2 Fa 15 2 Sue 3.5 1 S. Katz 222-3333 B 2 3 Fa15 2 Sue 3.5 2 D. Gold 555-2222 B+ Prog NOTE: Every row in a database must be different than every other row. Also you must have key fields which choose only one record Step 1 - normal form 1 - requires no empty fields. B7-E7 assume those fields are the same as the values in row 6 How do we fix that? Copy the data from row 6. Now you are in Normal Form 1 But you need a key for the database? Solution make a key from 2 fields that are always different. Which fields2? Step 2 - Now you are in normal form1 - how do you get to normal form 2? Some fields depend on only part of the key. To fix, create a new table for those that rely on part 1 and a new table for those that rely on part 2 And a table for those that rely on both parts 1 and 2 Step 3 - Now you are in normal form 2 - how do you get to normal form 3? Some fields do rely on their key, as required. But some fields rely on the key indirectly, by going thru another field. E.g. if you store both name and phone number, the phone number depends on the name which in turn depends on the key. Move out these fields to another table. Now you are completely normalized

Answer & Explanation Solved by verified expert

See Answer