TASK (EmployeeID, EmpLastName, EmpFirstName, Phone, OfficeNumber, ProjectName, S

Question

TASK (EmployeeID, EmpLastName, EmpFirstName, Phone, OfficeNumber, ProjectName, Sponsor, WorkDate, HoursWorked)

A. Write SQL statements to display the values of any rows that violate these functional dependencies.

B. If no data violate these functional dependencies, can we assume that they are valid? Why or why not?

C. Assume that these functional dependencies are true and that the data have been corrected, as necessary, to reflect them. Write all SQL statements necessary to redesign this table into a set of tables in BCNF and 4NF. Assume that the table has data values that must be appropriately transformed to the new design.

Explanation / Answer

A. Primary key can be EmpId and ProjectName, but LastName and FirstName only depends on EmpId so this is violating 2nd normal form and duplicate data can be there.

select EmployeeID from TASK where EmployeeId=10, you get multiple rows that is violation.

B. Yes data can be valid, it will be duplicate but no bad data will be there.

C.

create Emp(EmpId int primary key,EmpLastName varchar(50),EmpFirstName varchar(50),Phone int,OffNum int)

insert into Emp
select EmployeeID, EmpLastName, EmpFirstName, Phone, OfficeNumber
from TASK
group by EmployeeID, EmpLastName, EmpFirstName, Phone, OfficeNumber

alter table TASK drop column EmployeeLastName

alter table TASK drop column EmployeeFirstName
alter table TASK drop column Phone
alter table TASK drop column OfficeNumber

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.