Case Study Project on Normalisation
Added on - 25 Sep 2019
Business Rule:Each employee has one job classificationMany employees have one classificationEach project needs to assign many employeesOne employee may assign to multiple projectSome employee may not assign to projectA employee can work in one job at a time but in a one job designation we have multipleemployee, according to my assumption we should have one three or four entities, and everyemployee , job designation and project must have one unique identification number and usingthis we can identify the employee, job and projects.Entities and Attributes:EntitiesAttributesProjectProjNumProjNameAssignAssignIDProjNumEmpNumHoursEmployeeEmpNumEmpNameJobClassJobJobClassChargeHourEntity Relationship Diagram:Data Dictionary:
TableNameAttributeAttributeDescriptionDataTypeDataFormatRangeMandatoryPrimaryKay/Foreign KeyForeignKeyReferenceTableProjectProjNumUniqueIdentifierof Projectint999YPKProjNameName ofProjectChar(40)AaaaaaYAssignAssignIDUniqueIdentifierof assignint999YPKProjNumProjectidentification numberint999YFKProjectEmpNumEmployeeidentification numberint999YFKEmployeeHoursHourswork byemployeeDecimal99.9YEmployeeEmpNumUniqueIdentifierofEmployeeint999YPKEmpNameEmployeenameChar(40)AaaaaYJobClassJob classofemployeeChar(40)AaaaaYFKJobJobJobClassUniqueIdentifierof jobChar(40)AaaaaYPKChargeHourChargeper hourDecimal$999.9YFunctional Dependence:When one attribute uniquely determines another attribute in the table is called functionaldependency. In the given case study Project number is determine the project name in theproject table.Normalisation:Normalisation is a process of assigning the attributes into the entities, it reduces the dataredundancy, and normalisation helps to eliminate the data anomalies.