| 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/10970913/Lab07/ |
Upload File : |
UNF: student(Name, HomeMobilePhoneNumbers, StudentID, MajorCode1, MajorCode2, MajorCode3)
Note: It contains repeating fields: HomeMobilePhoneNumbers, MajorCode1, MajorCode2, MajorCode3. These represent arrays or lists of values in single columns, which violates the rule of 1NF where each column should contain atomic values.
-----------------------------------------------------------------------------------------------------
1NF: In order to normalize to 1NF, I address the repeating fields by creating separate tables for phone numbers and majors to ensure that each column contains atomic values only. And ensure that every table has a primary key.
Students Table:
Fields: StudentID (*), Name
Note: Split the original table to ensure atomicity; StudentID is the primary key.
PhoneNumbers Table:
Fields: PhoneNumberID (*), StudentID (@), PhoneNumber
Note: Created to remove repeating HomeMobilePhoneNumbers from the original table. PhoneNumberID is a newly created field to serve as a primary key.
Majors Table:
Fields: MajorID (*), StudentID (@), MajorCode
Note: Created to handle multiple majors (MajorCode1, MajorCode2, MajorCode3) by making MajorCode an atomic field. MajorID is a newly created field to serve as the primary key.
-----------------------------------------------------------------------------------------------------
CREATE TABLE Students (
StudentID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(255) NOT NULL
);
CREATE TABLE PhoneNumbers (
PhoneNumberID INT AUTO_INCREMENT PRIMARY KEY,
StudentID INT,
PhoneNumber VARCHAR(20),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
CREATE TABLE Majors (
MajorID INT AUTO_INCREMENT PRIMARY KEY,
StudentID INT,
MajorCode VARCHAR(10),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);
-----------------------------------------------------------------------------------------------------
2NF: Since my normalization from UNF to 1NF did not involve creating tables with composite primary keys, all tables created are inherently in 2NF. There are no partial dependencies within these tables because each table has only a single attribute as its primary key, and all other attributes are fully functionally dependent on it.
Students Table: This table is in 2NF because it has a single-column primary key, and all other columns are fully functionally dependent on the primary key.
PhoneNumbers Table: This table is in 2NF as well. It has a single-column primary key, and all non-key columns are fully dependent on the primary key. Although StudentID is a foreign key, there's no partial dependency because there's only one primary key.
Majors Table: The Majors table is in 2NF. It has a single-column primary key, with all non-key columns fully dependent on the primary key, ensuring no partial dependency exists.
-----------------------------------------------------------------------------------------------------
3NF: The goal of 3NF is to ensure that every non-prime attribute is directly dependent on the primary key.
Students Table: Consists of StudentID and Name. Since StudentID is the primary key and Name is directly dependent on StudentID, there are no transitive dependencies.
PhoneNumbers Table: Includes PhoneNumberID, StudentID, and PhoneNumber. PhoneNumberID is the primary key, and both StudentID and PhoneNumber are directly dependent on it. There's no attribute that is dependent on another non-prime attribute.
Majors Table: Contains MajorID, StudentID, and MajorCode. Like the PhoneNumbers table, MajorID is the primary key, and both StudentID and MajorCode are directly dependent on it.
Three data rows: