| Server IP : 134.29.175.74 / Your IP : 216.73.216.160 Web Server : nginx/1.10.2 System : Windows NT CST-WEBSERVER 10.0 build 19045 (Windows 10) i586 User : Administrator ( 0) PHP Version : 7.1.0 Disable Function : NONE MySQL : OFF | cURL : ON | WGET : OFF | Perl : OFF | Python : OFF | Sudo : OFF | Pkexec : OFF Directory : C:/nginx/html/JimMartinson/CST1600/Resources/Notes/Archive/ |
Upload File : |
Three examples that are not 1NF and a solution:
1: This is not 1NF because you have multiple email addresses stored in one field. Non-atomic data stored in a field.
user (id*, name, email)
id name email
1 jim jim@jimmartinson.com,jim.martinson@ridgewater.edu
2: This is not 1NF because you have repeating fields used to store multiple email addresses. This makes queries complex and allows for redundant data.
user (id* name, email1, email3)
id name email1 email2 email3 ( Need to modify the database to add a 3rd email address )
1 jim jim@jimmartinson.com jim.martinson@ridgewater.edu
3: This is not 1NF because you have repeating tables used to store multiple email addresses. This makes queries complex and allows for redundant data.
user (id*, name)
id name
1 jim
email1 (id*, email)
id email
1 jim@jimmartinson.com
email2 (id*, email)
id email
1 jim.martinson@ridgewater.edu
email3 ( Need to modify the database to add a 3rd email address )
id email
solution: Move email address to its own table and setup a one-to-many relationship.
user (id*, name)
id name
1 jim
useremail (id*@, email*, primary)
id email primary
1 jim@jimmartinson.com 0
1 jim.martinson@ridgewater.edu 1
Symbol key:
* = primary key
@ = foreign key
^ = unique field
An example from Database-Design-2nd-Edition:
UNF:
Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade
1 jim CP CST1600 Database Design 1 jim 327 NULL
1 jim CST CST1500 Hardware Diagnostics 1 jim 327 NULL
1NF ( I found UNF to be in !NF and consider this a 2NF first draft):
Student (StudentNo*, StudentName, Major)
StudentNo*, StudentName, Major
1 jim CP
StudentCourse (StudentNo*@, CourseNo*, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
StudentNo*@, CourseNo*, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade
1 CST1600 Database Design 1 jim 327 NULL
1 CST1500 Hardware Diagnostics 1 jim 327 NULL
2NF:
Student (StudentNo*, StudentName, Major)
StudentNo*, StudentName, Major
1 jim CP
CourseGrade (StudentNo*@, CourseNo*, Grade)
StudentNo*@, CourseNo*, Grade
1 CST1600 NULL
1 CST1500 NULL
CourseInstructor (CourseNo*, CourseName, InstructorNo, InstructorName, InstructorLocation)
CourseNo*, CourseName, InstructorNo, InstructorName, InstructorLocation
CST1600 Database Design 1 jim 327
CST1500 Hardware Diagnostics 1 jim 327
3NF:
Student (StudentNo*, StudentName, Major)
StudentNo*, StudentName, Major
1 jim CP
CourseGrade (StudentNo*@, CourseNo*, Grade)
StudentNo*@, CourseNo*, Grade
1 CST1600 NULL
1 CST1500 NULL
Course (CourseNo*, CourseName, InstructorNo)
CourseNo*, CourseName, InstructorNo
CST1600 Database Design 1
CST1500 Hardware Diagnostics 1
Instructor (InstructorNo, InstructorName, InstructorLocation)
InstructorNo, InstructorName, InstructorLocation
1 jim 327
I usually look at the data and look for entities (tables). What are the real-world things I am keeting data about?
Student_Grade_Report (StudentNo, StudentName, Major, CourseNo, CourseName, InstructorNo, InstructorName, InstructorLocation, Grade)
student
course
instructor
student_course