| 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 : /nginx/html/JimMartinson/CST1600/_Archive/Notes/ |
Upload File : |
CST1600_wk08_20180227_part1 View lecture part 1
CST1600_wk08_20180227_part2 View lecture part 2
CST1600_wk08_20180227_part3 View lecture part 3 (Lab 5)
* = primary key (unique identifier)
@ = foreign key (points to a primary key or unique identifier in another table)
^ - unique field.
employee(EmployeeNo*, HomeMobilePhone, Name)
And sample data: 1001, 103-391-7811;676-683-6301, Jim Martinson
1002, 447-149-5334;156-953-7528, Jane Doe
.
Example 1:
1NF:
employee(EmployeeNo*, HomeMobilePhone*, Name)
And sample data: 1001, 103-391-7811, Jim Martinson
1001, 676-683-6301, Jim Martinson
1002, 447-149-5334, Jane Doe
1002, 156-953-7528, Jane Doe
2NF:
employee(EmployeeNo*, HomeMobilePhone*, Name)
And sample data: 1001, 103-391-7811, Jim Martinson
1001, 676-683-6301, Jim Martinson
1002, 447-149-5334, Jane Doe
1002, 156-953-7528, Jane Doe
employee(EmployeeNo*, Name)
employeephone(EmployeeNo*@, Phone*, Type)
3NF:
Done
Example 2:
UNF:
employee(EmployeeNo*, HomePhone, MobilePhone)
And sample data: 1001, 103-391-7811, 676-683-6301
1002, 447-149-5334, 156-953-7528
1NF:
employee(EmployeeNo*, Phone*)
And sample data: 1001, 103-391-7811
1001, 676-683-6301
1002, 447-149-5334
1002, 156-953-7528
Example 3:
Dept (DeptCode*, DeptName)
CST, Computer Systems Technology
AIT, Artificial Intelligence Technology
MMDT, Multimedia Design Technology
ENGL, English
Dept (DeptId*, DeptCode, DeptName)
1, CST, Computer Systems Technology
2, AIT, Artificial Intelligence Technology - NOW OBSOLETE
2, MMDT, Multimedia Design Technology
4, ENGL, English
5, AIT, Artificial Insemination Technology
Dept (DeptId^, DeptCode*, DeptName*, IsObsolete)
1, CST, Computer Systems Technology
2, AIT, Artificial Intelligence Technology - NOW OBSOLETE
2, MMDT, Multimedia Design Technology
4, ENGL, English
5, AIT, Artificial Insemination Technology
Too much normalization example:
student ( studentNo*, FirstName, MiddleName, LastName, email, phone, image)
SELECT * FROM `student';
student ( studentNo*, FirstName)
studentmiddlename ( studentNo*@, MiddleName)
studentlastname ( studentNo*@, LastName)
studentemail ( studentNo*@, email)
studentphone ( studentNo*@, phone)
studentimage ( studentNo*@, image)
SELECT student.studentNo
, FirstName
, IFNULL(MiddleName,"")
, IFNULL(LastName,"")
, email
, phone
, image
FROM `student'
LEFT JOIN `studentmiddlename` ON studentmiddlename.studentNo = student.studentNo
LEFT JOIN `studentlastname` ON studentlastname.studentNo = student.studentNo
LEFT JOIN `studentemail` ON studentemail.studentNo = student.studentNo
LEFT JOIN `studentphone` ON studentphone.studentNo = student.studentNo
LEFT JOIN `studentimage` ON studentimage.studentNo = student.studentNo;