The Tables:

create database cp363; use cp363;

CREATE TABLE organizations ( organization_id INT PRIMARY KEY AUTO_INCREMENT, org_date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, org_name VARCHAR(80) NOT NULL UNIQUE, org_address TEXT, org_desc TEXT, org_netWorth DECIMAL(10 , 2 ), payment_cycle DATE NOT NULL );

CREATE TABLE project( project_id INT PRIMARY KEY AUTO_INCREMENT, prj_date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, prj_name VARCHAR(50) NOT NULL, prj_desc TEXT NOT NULL, KPI_goal INT NOT NULL CHECK (KPI_goal >= 0 AND KPI_goal

CREATE TABLE department( department_id INT PRIMARY KEY AUTO_INCREMENT, dep_date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, dept_name VARCHAR(50) NOT NULL UNIQUE, dept_desc TEXT NOT NULL, dept_budget DECIMAL(10,2) NOT NULL CHECK (dept_budget >= 0) );

CREATE TABLE bank ( bank_id INT PRIMARY KEY AUTO_INCREMENT, bnk_date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, institute_number VARCHAR(50) NOT NULL UNIQUE, transit_number VARCHAR(50) NOT NULL UNIQUE, account_number VARCHAR(50) NOT NULL UNIQUE );

CREATE TABLE employee( employee_id INT PRIMARY KEY AUTO_INCREMENT, emp_date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, emp_firstName VARCHAR(50) NOT NULL, emp_lastName VARCHAR(50) NOT NULL, emp_address TEXT NOT NULL, emp_phone BIGINT NOT NULL, emp_username VARCHAR(50) NOT NULL UNIQUE, emp_email VARCHAR(50) NOT NULL UNIQUE, emp_password VARCHAR(50) NOT NULL, -- Disjoint constraint emp_type ENUM('full_time', 'part_time') NOT NULL, #employee can either be part time or full time emp_hourlyWage DECIMAL(10,2) DEFAULT NULL, emp_salary DECIMAL(10,2) DEFAULT NULL, CHECK( # if FULL-TIME then salary is NOT null and hourly rate is null (emp_type = 'full_time' AND emp_salary IS NOT NULL AND emp_hourlyWage IS NULL) OR # if PART-TIME then salary is null and hourly rate is NOT null (emp_type = 'part_time' AND emp_salary IS NULL AND emp_hourlyWage IS NOT NULL) ), -- Foreign Keys organization_id INT NOT NULL, department_id INT NOT NULL, # Department must be created before a employee is added to the system bank_id INT DEFAULT NULL, # for instance, can be NULL for employees still in onboarding FOREIGN KEY (organization_id) REFERENCES organizations(organization_id) ON DELETE CASCADE, FOREIGN KEY (department_id) REFERENCES department(department_id), FOREIGN KEY (bank_id) REFERENCES bank(bank_id) );

ALTER TABLE department ADD manager_id INT DEFAULT NULL; # Manager can be added after the employees are created ALTER TABLE department ADD FOREIGN KEY (manager_id) REFERENCES Employee(employee_id) ON DELETE SET NULL;

CREATE TABLE performance( performance_id INT PRIMARY KEY AUTO_INCREMENT, perf_date_created TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, perf_notes TEXT NOT NULL, date_Achieved DATE NOT NULL, KPI_Achieved INT NOT NULL CHECK (KPI_Achieved >= 0 AND KPI_Achieved

# Foreign Keys appraiser_id INT NOT NULL, appraised_id INT NOT NULL, CONSTRAINT chk_appraiser CHECK (appraised_id!=appraiser_id), # constraint to ensure that the appraiser and appraised are not identical project_id INT NOT NULL,

FOREIGN KEY (appraiser_id) REFERENCES employee(employee_id) ON DELETE CASCADE, FOREIGN KEY (appraised_id) REFERENCES employee(employee_id) ON DELETE CASCADE, FOREIGN KEY (project_id) REFERENCES project(project_id) ON DELETE CASCADE );

CREATE TABLE transactions( transaction_id INT PRIMARY KEY AUTO_INCREMENT, transaction_date DATE NOT NULL, # initial wage wage DECIMAL(10,2) CHECK (wage>=0), # deductions EI_pay DECIMAL(10,2), vacation_pay DECIMAL(10,2), # additions bonus_pay DECIMAL(10,2) CHECK (bonus_pay >= 0), overtime_pay DECIMAL(10,2) CHECK (overtime_pay>=0), # net after calculations (automatic?) net_pay DECIMAL(10,2) NOT NULL CHECK (net_pay >= 0),

# Foreign Keys employee_id INT NOT NULL, bank_id INT NOT NULL, FOREIGN KEY (employee_id) REFERENCES employee(employee_id) ON DELETE CASCADE, FOREIGN KEY (bank_id) REFERENCES bank(bank_id) ON DELETE CASCADE );

should normalize all tables to be in 3NF or BCNF. Then you should create dummy data, test and validate your application and try to optimize it. For assignment 6 you should only Functional Dependencies: You should show all the functional dependencies in all the tables in your database