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 multiple employee, according to my assumption we should have one three or four entities, and every employee , 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:
Table NameAttributeAttribute DescriptionData TypeData FormatRangeMandatoryPrimary Kay/Foreign KeyForeignKey Reference TableProjectProjNumUnique Identifier of Projectint999YPKProjNameName of ProjectChar(40)AaaaaaYAssignAssignIDUnique Identifier of assignint999YPKProjNumProject identification numberint999YFKProjectEmpNumEmployee identification numberint999YFKEmployeeHoursHours work by employeeDecimal99.9YEmployeeEmpNumUnique Identifier of Employeeint999YPKEmpNameEmployee nameChar(40)AaaaaYJobClassJob class of employeeChar(40)AaaaaYFKJobJobJobClassUnique Identifier of jobChar(40)AaaaaYPKChargeHourCharge per hourDecimal$999.9YFunctional Dependence:When one attribute uniquely determines another attribute in the table is called functional dependency. In the given case study Project number is determine the project name in the project table.Normalisation:Normalisation is a process of assigning the attributes into the entities, it reduces the data redundancy, and normalisation helps to eliminate the data anomalies.
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
ER Diagram, Relational Data logical model, Data dictionary, Oracle SQL Codelg...
|12
|1599
|46
Database Design and Development Case | Assignment 1lg...
|13
|2159
|29
BIT358: Advanced Database Assignmentlg...
|10
|1155
|310
Data Model, Normalisation, DDL and DML for Spock Bank Databaselg...