Computer Science ER Diagram

Question: To give you practical experience in using Entity-Relationship and Relational Database modelling techniquesProject Specification: Alan Counting Edward Quals and Peter Rofit are in partnership in an accounting practice CQR. tHE practice specialises in taxation auditing and financial advice for small to medium sized clients. They started the pracitce in 2005 and it has grown to now employ 25 people . These employees include other accountants and office staff.Alan, Edward and Peter believe the practice is headed for a period of significant growth and have come to you to help them redevelop their client billing system so that it copes with their workflows and supports future growth opportunities. Answer: ER diagram Attached as PDF document. Relational DataStructures It contains the different structures provided by CQR to the clients CQRStructureTypes( Structure varchar(50), Description varchar(200) primaryKey(Structure) ); It contains the different sectors provided by CQR to group the clents CQRSectorTypes( Sector varchar(50), Description varchar(200) primaryKey(Sector) ); It contains the different services provided by CQR to the clients CQRServicesTypes( Service varchar(50), Description varchar(200) primaryKey(Service) ); ParentClientID is just to have link for individuals who are part of any private client CQRClientsInfo( ClientID varchar(10), Name varchar(20), Address varchar(100), ContactNumber varchar(20), ABN varchar(20), TFN varchar(20), Structure varchar(50), Sector varchar(50), Service varchar(50), ParentClientID varchar(10) primaryKey(ClientID, Sector, Service) foreignKey(Structure) referenced by CQRStructureTypes(Structure) foreignKey(Service) referenced by CQRStructureTypes(Service) foreignKey(Sector) referenced by CQRStructureTypes(Sector) It contains information about different domains CQR supports CQREmploymentDomains( EmploymentDomain varchar(20), Description varchar(200) primaryKey(EmploymentDomain); ); It contains information about different employee types CQR supports CQREmpTypes( EmpType varchar(20), Description varchar(200) primaryKey(EmpType); ); It contains emploees information CQREmployeesIn EmpID varchar(10), EmploymentDomain varchar(20), EmpType varchar(10), BillingRate float, SupervisorID varchar(10) primaryKey(EmpID, EmploymentDomain); foreignKey(EmploymentDomain) references to CQREmploymentDomains(EmploymentDomain); foreignKey(EmpType) references to CQREmpTypes(EmpType); ) It contins the employee hour rates history CQREmployeeHourRatesHostory( EmpID varchar(10), StartDate Date, EndDate Date, BillingRate float foreignKey(EmpID) references to CQREmployeesInfo(EmpID) ); It contains the work categories those can be done to client CQRWorkCategories( WorkCategory varchar(20), Charge flaot primaryKey(WorkCategory)); It contians the Client billing information CQRClientBilling( ClientID varchar(10), EmpID varchar(10), WorkCategory varchar(20), StartTime Date, EndTime Date, Bill float primaryKey(ClientID, EmpID, WorkCategory) foreignKey(ClientID) references to CQRClientsInfo(ClientID) foreignKey(EmpID) references to CQREmployeesInfo(EmpID) ); Normalisation of relations which identifies a. Many clients can be mapped to same strucure type CQRClientsInfo ----many-one------ CQRStructureTypes b. same clients can be mapped many sectors and many clients mapped to many secotrs CQRClientsInfo ----many-many------ CQRSecotrsTypes c. same clients can be mapped many services and many clients mapped to many services CQRClientsInfo ----many-many------ CQRServiceTypes d. same employees mapped to many domains and many employees mapped to many domains CQREmployeesInfo ----many-many------ CQREmploymentDomains e Many employees mapped to one employee type CQREmployeesInfo ----one-many------ CQREmpTypes f. One client mapped to many works and many clients mapped one work. CQRClientBilling ----many-many------ CQRWorkCategories g. CQRClientsInfo is not in 1NF as address field is not atomic CQRClientsInfo key (ClientID, Sector, Service), rest of the columns depending on ClientID which is part of the key. It is not in 2NF. CQREmployeesInfo key (EmpID, EmploymentDomain), rest of the columns depending on EmpID which is part of the key. It is not in 2NF. CQRClientBilling key (ClientID, EmpID, WorkCategory), rest of the columns depending on ClientID which is part of the key, it is not in 2NF. Rest all other relations maintained in 3NF. Relational schema corrected to 3NF: CQRStructureTypes(Structure, Description, primaryKey(Structure)); CQRSectorTypes(Sector, Description, primaryKey(Sector)); CQRServicesTypes(Service, Description, primaryKey(Service)); CQRClientsInfo(ClientID, Name, StreetName, City, Country, ContactNumber, ABN, TFN, Structure, ParentClientID, primaryKey(ClientID), foreignKey(Structure) referenced by CQRStructureTypes(Structure)); CQRClientsSectors(ClientID, Sector, primaryKey(ClientID, Sector), foreignKey(Sector) referenced by CQRSectorsTypes(Sector)); CQRClientsServices(ClientID, Service, primaryKey(ClientID, Service), foreignKey(Service) referenced by CQRServicessTypes(Service)); CQREmployeeHourRatesHostory(EmpID, StartDate, EndDate, BillingRate, foreignKey(EmpID) references to CQREmployeesInfo(EmpID)); CQRWorkCategories(WorkCategory, Charge, primaryKey(WorkCategory)); CQRClientBilling(ClientID, StartTime, EndTime, primaryKey(ClientID)); CQRClientWorkCategories(ClientID, WorkCategory primaryKey(ClientID) foreignKey(WorkCategory) references to CQRWorkCategories(WorkCategory)) CQRClientEmployees(ClientID, EmpID primaryKey(ClientID) foreignKey(EmpID) references to CQREmployeesInfo(EmpID)); References Studytonight, 2014, DataBase Management System Vangie Beal, 2014, Normalization