| 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/uploads/20255/CST1600/41/14904400/Lab07/ |
Upload File : |
UNF: student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3) Name HomeMobilePhoneNumbers studentID MajorCode1 MajorCode2 MajorCode3 Avram Hinton 103-391-7811;676-683-6301 34673656 NSA CSS CST Hayes Gibson 447-149-5334 83681840 CST Kristen Acosta ;972-658-1570 94086534 CP MAD ===1NF:A relation is said to be in 1NF (first normal form), if it doesn’t contain any multi-valued attribute. In other words you can say that a relation is in 1NF if each attribute contains only atomic(single) value only. -rip apart data set and create new tables: Students, StudentPhones, StudentMajors. -create table students Table: Students *studentID Name *34673656 Avram Hinton *83681840 Hayes Gibson *94086534 Kristen Acosta !Violation! -create table StudentPhones to fix multiple numbers in HomeMobilePhoneNumbers. WHY- Composite PK because Phone numbers can repeat especially if they live in the same household ie a homephone. Plus a student can have multiple phone numbers so they both should be primary. Table: StudentPhones *studentID *PhoneNumber *34673656 *103-391-7811 *34673656 *676-683-6301 *83681840 *447-149-5334 *94086534 *972-658-1570 ?Violation? -Note- not entirely sure just did it because there are multiple MajorCode fields WHY- similar to previous "WHY" StudentID is not enough because a student can have multiple Majors and Major is not good because multiple students can have it. -create table studentmajors Table: StudentMajors *studentID *MajorCode *34673656 *NSA *34673656 *CSS *34673656 *CST *83681840 *CST *94086534 *CP *94086534 *MAD Done 1NF ok ===2NF:Table is in 1NF (First normal form) No non-prime attribute is dependent on the proper subset of any candidate key of table. -Table: Students >Only one PK no candidate key and Name fully depends on it. >table OK< -Table: StudentPhones >only has a composite key >table OK< -Table: StudentMajors >only has a composite key >table OK< Done 2NF passed no changes. ===3NF:A table design is said to be in 3NF if both the following conditions hold: Table must be in 2NF Transitive functional dependency of non-prime attribute on any super key should be removed. -Table: Students >Name fully depends on StudentID >no Transitive functional dependency because no other non-prime attributes are present. >table OK< -Table: StudentPhones >their are no non-key attributes (cannot have Transitive dependency) >table OK< -Table: StudentMajors >On closer observation MajorCode does not belong here and should be in it's own table making it a foreign code. To add on if this table were larger Majors would probably include things like Department and full major name. -->create new table Table: Majors *MajorCode *NSA *CSS *CST *CP *MAD >passes previous NFs as it only has a Prime Key. -adjust Table: StudentMajors *studentID *MajorCode@[Major.MajorCode] *34673656 *NSA *34673656 *CSS *34673656 *CST *83681840 *CST *94086534 *CP *94086534 *MAD Done 3NF Three data rows: -Note I knida just cp the data as i already did them in previous steps \0/. Table: Students *studentID Name *34673656 Avram Hinton *83681840 Hayes Gibson *94086534 Kristen Acosta Table: StudentPhones *studentID *PhoneNumber *34673656 *103-391-7811 *34673656 *676-683-6301 *83681840 *447-149-5334 *94086534 *972-658-1570 Table: Majors *MajorCode *NSA *CSS *CST *CP *MAD Table: StudentMajors *studentID *MajorCode@[Major.MajorCode] *34673656 *NSA *34673656 *CSS *34673656 *CST *83681840 *CST *94086534 *CP *94086534 *MAD