Entity-Relationship And Relational Database Modelling Techniques For Client Billing System Development In CQR Accounting Practice
Project Specification
To give you practical experience in using Entity-Relationship and Relational Database modelling techniques
Project 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.
Attached as PDF document.
- 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)
);
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.
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