程序代写代做代考 SQL Microsoft Word – Worked Example – Normalisation
Microsoft Word – Worked Example – Normalisation
Page 1 of 4
Table of Contents
Worksheet – Normalisation ………………………………………………………2
Transformation to First Normal Form ………………………………………2
Transformation into Second Normal Form ………………………………….3
Normalisation – Second Normal Form – Answers ……………………………4
Page 2 of 4
Worksheet – Normalisation
Transformation to First Normal Form
1. Identify the primary key for the following non-normalised table
Student
Name
Phone
Number
Exam Exam
Mark
Pass
Mark
Grade Exam1 Exam2
Joe Bloggs 02083382991 French
Japanese
59 63 50
50
C B
Stuart
Pierce
0774828382 French
English
28 55 45
50
F C
Janet Ellis 02299382111 English
Maths
66 83 50
50
B A
Leo
Zimmerman
02084349291 Maths
French
95 79 50
50
A A
2. Update: From the table, can you determine which grade relates to which
exam? What happens when you try to update Stuart Pierce’s grade for English
(write the SQL to do this)?
3. Insert: In this table, what would happen if the students had taken three
exams rather than two?
4. Delete: What happens if we don’t yet know the second exam taken by Janet
Ellis and we need to delete the Maths exam and its related marks (write the
SQL to do this)?
5. To overcome this problem, transform the data into first normal form. Assume
that the left hand Grade column refers to the left exam column and so on.
Page 3 of 4
Transformation into Second Normal Form
Student
Name
Phone
Number
Exam Exam
Mark
Pass
Mark
Grade
Joe Bloggs 02083382991 French 59 45 C
Stuart
Pierce
0774828382 French 28 45 F
Janet Ellis 02299382111 English 66 50 B
Leo
Zimmerman
02084349291 Maths 95 50 A
Joe Bloggs 02083382991 Japanese 63 50 B
Stuart
Pierce
0774828382 English 55 50 C
Janet Ellis 02299382111 Maths 83 50 A
Leo
Zimmerman
02084349291 French 79 45 A
1. What is the primary key for this table, which is in first normal form?
2. Update: What happens if we need to modify the contact phone number for Joe
Bloggs?
3. Insert: How do we add pass mark information for an exam which no students
have yet taken?
4. Delete: What happens if you delete information about the exams taken by Joe
Bloggs?
5. To overcome these problems, transform the above data from First Normal
Form into Second Normal Form.
Page 4 of 4
Normalisation – Second Normal Form – Answers
STUDENT EXAM
Student
Name
Phone No. Exam Name Pass Mark
Joe Bloggs 02083382991 French 45
Stuart Pierce 0774828382 English 50
Janet Ellis 02299382111 Japanese 50
Leo
Zimmerman
02084349291 Maths 50
STUDENT_EXAM
Student
Name
Exam
Name
Exam
Mark
Grade
Joe Bloggs French 59 C
Stuart
Pierce
French 28 F
Janet Ellis English 66 B
Leo
Zimmerman
Maths 95 A
Joe Bloggs Japanese 63 B
Stuart
Pierce
English 55 C
Janet Ellis Maths 83 A
Leo
Zimmerman
French 79 A
