| 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/20245/CST1600/41/15123653/Lab07/ |
Upload File : |
* (asterisk) to denote a primary key field. PKs are unique and do not need to be marked with the ^ character. @ (at sign) to denote a foreign key field. Append [table.field] if the table and field a foreign key points to is not obvious.Such as when the FK field name is not the same as the name of the field it relates to. E.G. The rerortsTo FK relates to the employeeNumber field in the employees table and would be written as: reportsTo@[employees.employeeNumber]. ^ (caret) to denote a unique field that is not a primary key. Business Logic: Each student may have more than one major. (no limit - which the UNF table does not allow) No student may have the same major more than once. No student may have the same phone number more than once. Note: The home number is listed first, followed by a semicolon(;), then the mobile number. Each major code is unique. Field meanings: Name is the student's full name. HomeMobilePhoneNumbers is the student's home phone number followed by the student's mobile phone number. Not all students have both numbers. studentID is a unique ID number assigned to the student. MajorCode1, MajorCode2, and MajorCode3 are the major codes for the student's majors. Not all students have three majors. UNF: student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorTitle1, MajorCode2, MajorTitle2, MajorCode3, MajorTitle3) Name HomeMobilePhoneNumbers studentID MajorCode1 MajorTitle1 MajorCode2 MajorTitle2 MajorCode3 MajorTitle3 Avram Hinton 103-391-7811;676-683-6301 34673656 NSA Network Systems Administration CSS Cyber Security Specialist CST Computer Support Technician Hayes Gibson 447-149-5334 83681840 CST Computer Support Technician Kristen Acosta ;972-658-1570 94086534 CP Computer Programmer MAD Mobile Application Development Colette Gibson 447-149-5334 96299266 NSA Network Systems Administration CSS Cyber Security Specialist Kylee Pugh 168-554-8071;362-673-1296 49622000 CSS Cyber Security Specialist Nissim Moreno 831-774-5128;676-683-6301 12755860 MAD Mobile Application Development WP Web Programming Lael Abbott 733-772-2949;298-846-3570 84228748 CST Computer Support Technician Ethan Gomez 484-285-4061;344-277-7057 40208573 CP Computer Programmer WP Web Programming Jena Sosa 814-888-1642;895-585-6779 99496218 CST Computer Support Technician John Smith 860-521-5683;418-765-5461 21479092 CST Computer Support Technician Amos McCarthy 435-975-9981;721-269-1301 25357066 CST Computer Support Technician NSA Network Systems Administration Caleb Madden 943-541-5201 98159249 CSS Cyber Security Specialist CP Computer Programmer Xerxes Hayes 877-544-4230; 83312932 WP Web Programming CP Computer Programmer Mira Gentry ;461-854-5007 42683650 MAD Mobile Application Development WP Web Programming CP Computer Programmer Harper Mcmahon 267-222-8035;530-888-7381 55593577 NSA Network Systems Administration CSS Cyber Security Specialist Bruce Maxwell 465-255-0860;217-387-8653 44107438 CP Computer Programmer John Smith ;945-553-7908 32388303 CSS Cyber Security Specialist Montana Dickson ;491-349-5614 53068679 MAD Mobile Application Development WP Web Programming Hayden Mcintyre 277-380-2418;785-939-8667 31441510 CST Computer Support Technician Hayes Knapp 779-778-5915;754-114-8609 78171647 WP Web Programming MAD Mobile Application Development Lucy Merritt ;294-399-5728 22255753 NSA Network Systems Administration CST Computer Support Technician Dorian Morris 780-424-6179;149-150-0084 15665209 WP Web Programming MAD Mobile Application Development CSS Cyber Security Specialist Halee Bryan 454-713-2195;622-825-8598 10063618 WP Web Programming Craig Warner 302-746-8454;147-544-8901 46483879 CST Computer Support Technician CST Computer Support Technician Lev Albert ;620-799-3350 52300353 WP Web Programming CP Computer Programmer CST Computer Support Technician 1NF: student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorTitle1, MajorCode2, MajorTitle2, MajorCode3, MajorTitle3) No primary key. HomeMobilePhoneNumbers has multiple values (non-atomic), Name has two values. MajorCode & MajorTitle have repeating fields. Craig Warner has same major twice.(Duplication of data) Create Tables: student(StudentID*, FirstName, LastName) studentPhone(StudentID*@, Phone*, Type) Major(MajorCode*,MajorTitle^) studentMajor(studentID*@,MajorCode*@) student(StudentID*, FirstName, LastName) studentID FirstName LastName 34673656 Avram Hinton 83681840 Hayes Gibson 94086534 Kristen Acosta 96299266 Colette Gibson 49622000 Kylee Pugh 12755860 Nissim Moreno 84228748 Lael Abbott 40208573 Ethan Gomez 99496218 Jena Sosa 21479092 John Smith 25357066 Amos McCarthy 98159249 Caleb Madden 83312932 Xerxes Hayes 42683650 Mira Gentry 55593577 Harper Mcmahon 44107438 Bruce Maxwell 32388303 John Smith 53068679 Montana Dickson 31441510 Hayden Mcintyre 78171647 Hayes Knapp 22255753 Lucy Merritt 15665209 Dorian Morris 10063618 Halee Bryan 46483879 Craig Warner 52300353 Lev Albert studentPhone(StudentID*@, Phone*, Type) studentID Phone, Type 34673656 103-391-7811 home 34673656 676-683-6301 mobile 83681840 447-149-5334 home 94086534 972-658-1570 mobile 96299266 447-149-5334 home 49622000 168-554-8071 home 49622000 362-673-1296 mobile 12755860 831-774-5128 home 12755860 676-683-6301 mobile 84228748 733-772-2949 home 84228748 298-846-3570 mobile 40208573 484-285-4061 home 40208573 344-277-7057 mobile 99496218 814-888-1642 home 99496218 895-585-6779 mobile 21479092 860-521-5683 home 21479092 418-765-5461 mobile 25357066 435-975-9981 home 25357066 721-269-1301 mobile 98159249 943-541-5201 home 83312932 877-544-4230 home 42683650 461-854-5007 mobile 55593577 267-222-8035 home 55593577 530-888-7381 mobile 44107438 465-255-0860 home 44107438 217-387-8653 mobile 32388303 945-553-7908 mobile 53068679 491-349-5614 mobile 31441510 277-380-2418 home 31441510 785-939-8667 mobile 78171647 779-778-5915 home 78171647 754-114-8609 mobile 22255753 294-399-5728 mobile 15665209 780-424-6179 home 15665209 149-150-0084 mobile 10063618 454-713-2195 home 10063618 622-825-8598 mobile 46483879 302-746-8454 home 46483879 147-544-8901 mobile 52300353 620-799-3350 mobile Major(MajorCode* MajorTitle^) MajorCode MajorTitle NSA Network Systems Administration CST Computer Support Technician CP Computer Programmer CSS Cyber Security Specialist MAD Mobile Application Development WP Web Programming studentMajor(studentID*@,MajorCode*@) studentID MajorCode 34673656 NSA 34673656 CSS 34673656 CST 83681840 CST 94086534 CP 94086534 MAD 96299266 NSA 96299266 CSS 49622000 CSS 12755860 MAD 12755860 WP 84228748 CST 40208573 CP 40208573 WP 99496218 CST 21479092 CST 25357066 CST 25357066 NSA 98159249 CSS 98159249 CP 83312932 WP 83312932 CP 42683650 MAD 42683650 WP 42683650 CP 55593577 NSA 55593577 CSS 44107438 CP 32388303 CSS 53068679 MAD 53068679 WP 31441510 CST 78171647 WP 78171647 MAD 22255753 NSA 22255753 CST 15665209 WP 15665209 MAD 15665209 CSS 10063618 WP 46483879 CST 52300353 WP 52300353 CP 52300353 CST 2NF: OK 3NF: OK Three data rows: student(StudentID*, FirstName, LastName) studentID FirstName LastName 34673656 Avram Hinton 83681840 Hayes Gibson 94086534 Kristen Acosta studentPhone(StudentID*@, Phone*, Type) studentID Phone Type 34673656 103-391-7811 home 34673656 676-683-6301 mobile 83681840 447-149-5334 home Major(MajorCode* MajorTitle^) MajorCode MajorTitle NSA Network Systems Administration CST Computer Support Technician CP Computer Programmer studentMajor(studentID*@,MajorCode*@) studentID MajorCode 34673656 NSA 34673656 CSS 34673656 CST