| 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/Lab08/ |
Upload File : |
UNF: employeework (employeeId*, name, SSN, projectWork, spouseName, spouseSSN, spouseIsInsured)
Note: The projectWork field is not atomic because it includes multiple attributes: code, project name, date, and hours worked.
Note: There may be optional fields for spouseName, spouseSSN, and spouseIsInsured, which can be null if the employee is unmarried.
------------------------------------------------------------------------------------------------------
1NF: Employees Table:
Fields: employeeId (*), name, SSN
Note: Directly captures basic employee information. Each column is atomic.
Projects Table:
Fields: employeeId (@), projectCode, projectName, projectDate, hoursWorked
Note: Separates projectWork details into an atomic structure. Includes a foreign key employeeId to link to the Employees table.
Spouses Table:
Fields: employeeId (@), spouseName, spouseSSN, spouseIsInsured
Note: Optional information is captured separately. Every row is only added if the employee is married.
-- Creation of Employees Table
CREATE TABLE Employees (
employeeId INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
SSN VARCHAR(11) UNIQUE NOT NULL
);
-- Creation of Projects Table
CREATE TABLE Projects (
projectCode VARCHAR(10) NOT NULL,
employeeId INT NOT NULL,
projectName VARCHAR(255) NOT NULL,
projectDate DATE NOT NULL,
hoursWorked INT NOT NULL,
PRIMARY KEY (projectCode, projectDate, employeeId),
FOREIGN KEY (employeeId) REFERENCES Employees(employeeId)
);
-- Creation of Spouses Table
CREATE TABLE Spouses (
employeeId INT PRIMARY KEY,
spouseName VARCHAR(255),
spouseSSN VARCHAR(11),
spouseIsInsured BOOLEAN,
FOREIGN KEY (employeeId) REFERENCES Employees(employeeId)
);
Note: Creating separate tables for projects and spouses to handle multiple values and optional data correctly, Atomicity ensured by breaking down projectWork into separate columns.
------------------------------------------------------------------------------------------------------
2NF:
Employees Table: Already in 2NF because each non-key attribute is fully dependent on employeeId.
Projects Table: Already in 2NF. Though employeeId and projectDate could together act as a composite key for practical purposes, each non-key attribute depends on the whole of this hypothetical composite key.
Spouses Table: Already in 2NF. No partial dependencies; employeeId is the full primary key.
------------------------------------------------------------------------------------------------------
3NF:
Employees Table: In 3NF. There are no transitive dependencies between non-key attributes.
Projects Table: In 3NF. Attributes like projectName could theoretically be normalized further based on projectCode, but it's appropriate here given business logic constraints and clarity.
Spouses Table: In 3NF. No transitive dependencies; non-key attributes don't depend on each other but directly on employeeId.
Note: The data is fully normalized according to the requirements of 1NF, 2NF, and 3NF, ensuring atomicity, removing partial dependencies, and eliminating transitive dependencies. This setup helps in maintaining integrity and minimizing redundancy.
All data rows: