| 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/20243/CST1600/41/15510774/Lab07/ |
Upload File : |
UNF: student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3) ----------------------------------------------------- 1NF: Violations: - No primary key - HomeMobilePhoneNumbers holds more than 1 value per field - Name holds more than 1 value per field Create Fields/Tables: student(FirstName, LastName, HomeMobilePhoneNumber*, PhoneType, StudentID*, MajorCode1, MajorCode2, MajorCode3) Why: I created a FirstName and LastName field to split the name field into two fields. I also created a PhoneType field so that every added phone number can be labeled as the type of phone number. This takes away the need to have multiple numbers in one field or the excess use of nulls for phone numbers. I chose studentID and phoneNumber as the composite key because every studentID is unique and adding phoneNumber key allows each studentID to have more than one phoneNumber. Rename: student(FirstName, LastName, PhoneNumber*, Type, StudentID*, MajorCode1, MajorCode2, MajorCode3) Why: I renamed HomeMobilePhoneNumber to PhoneNumber because the Type field will specifiy the phone number type. ----------------------------------------------------- 2NF: Violations: - The MajorCodes, FirstName, and LastName aren't dependent on the phoneNumber - Type of phone number directly relates to PhoneNumber, not the StudentID Create Fields/Tables: student(StudentID*, FirstName, LastName, majorCode*) phones(StudentID*@, PhoneNumber*, Type) Why: I deleted the PhoneNumber field from the student table and added a phones table so that the phone number and type of phone directly relates to the table itself. I made StudentID and PhoneNumber the composite key so that each studentID can have more than one phone number. I also made StudentID and majorCode a composite key so each student can have more than one major. Rename: I joined and renamed majorCode1, majorCode2, and majorCode3 into just majorCode to reduce the number of nulls for students who don't have more than one major. ----------------------------------------------------- 3NF: Violations: - The first and last name fields are redundant if a student has more than one major. Create Fields/Tables: student(StudentID*, FirstName, LastName) studentMajor (StudentID*@, majorCode*@) phones(StudentID*@, PhoneNumber*, Type) majors(majorCode*) Why: I created a majors table because it is its own separate entity. This reduces the rundancy of first and last names by giving it its own table. Since it is the only field, it is the primary key. Also, the deletion of a student could have caused the deletion of a major. I also created a studentMajor table to connect the student table to the majors table. The StudentID and majorCode are the composite key so each studentID can have more than one majorCode. Rename: None ----------------------------------------------------- Three data rows: StudentID* FirstName LastName 34673656 Avram Hinton 83681840 Hayes Gibson 94086534 Kristen Acosta StudentID*@ majorCode*@ 34673656 NSA 34673656 CSS 34673656 CST StudentID*@ PhoneNumber* Type 34673656 103-391-7811 Home 34673656 676-683-6301 Mobile 83681840 447-149-5334 Home majorCode* NSA CSS CST