Question

Suppose you are given the table:

EMPLOYEE_DEPARTMENT (EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName, BudgetCode)

You want to create an updated database and normalize the table

into the two tables:

DEPARTMENT (DepartmentName, BudgetCode)

EMPLOYEE (EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName)

Show SQL statements for creating DEPARTMENT and EMPLOYEE.

Show SQL filling the EMPLOYEE and DEPARTMENT tables with data from EMPLOYEE_DEPARTMENT.

Answer 1

creating DEPARTMENT and EMPLOYEE

/*Create DEPARTMENT with DepartmentName as primary key and use it as foreign key in EMPLOYEE to link the two tables*/

CREATE TABLE DEPARTMENT (

DepartmentName varchar(255),

BudgetCode varchar(255),

PRIMARY KEY (DepartmentName)

);

CREATE TABLE EMPLOYEE (

EmployeeNumber int,

EmployeeLastName varchar(255),

EmployeeFirstName varchar(255),

Email varchar(255),

DepartmentName varchar(255),

FOREIGN KEY (DepartmentName) REFERENCES DEPARTMENT(DepartmentName)

);

filling the EMPLOYEE and DEPARTMENT tables -

INSERT INTO DEPARTMENT (DepartmentName, BudgetCode)
SELECT DepartmentName, BudgetCode FROM EMPLOYEE_DEPARTMENT ;

INSERT INTO EMPLOYEE (EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName)
SELECT EmployeeNumber, EmployeeLastName, EmployeeFirstName, Email, DepartmentName FROM EMPLOYEE_DEPARTMENT ;