ER Diagram for the Proposed Solutions


ER Diagram Design for Practice Firm


The Diagram depicts the key tables and how the entire system could work with dependencies between the tables and the relational data connections.



Relational Data Structures

Table Name Attributes Field Type Constraint
Structure Info


Structure_ID Number Primary Key
Structure Name Text     
Sector Information


Sector_ID Number  
Sector Name Text  


Service_ID     Number Primary Key
Service Name Text  
Client Information


Customer_ID Number Primary Key 
First Name Text  
Last Name Text  
Address-1 Text  
Address-2 Text  
City Text  
State    Text  
ZIP Number  
Contact Text  
Phone Number  
ABN Number  
TFN Number  
Structure_ID Number Foreign Key
Sector_ID Number Foreign Key
Service ID Number Foreign Key
Current Rate Card Rate_ID Number Primary Key
Employee _ID Text Foreign Key
Sector_ID Text Foreign Key
Service_ ID Text Foreign Key
Structure_ID Text Foreign Key
Current Rate Number  
DOM Date  
Previous Rate Card Prate_ID Number Primary Key
Rate_ID Number Foreign Key
DOM Text     
HR Master


Employee_ID Number Primary Key 
Employee Name        Text  
Employee Type          Text  
Designation Text  
DOJ    Double  
DOR Number  
Qualification Number  
CStatus Text  
Client Services Form


Appointment_ID Number Primary Key 
Customer_ID Number Foreign Key
Service_ID Text Foreign Key
Date of Start Date  
Time of Start Number  
Start Time Time  
End Time Time  
Charge           Double  
Billing Values


Billing_ID Number Primary Key
Customer_ID Number Foreign Key
Date From Date  
Date To Date  
Total Charge Double  
Employee_ID Number Foreign Key
Appointment_ID Number Foreign Key


Dependency Diagram


The dependencies are developed according to 3NF levels

Relational Schema:

Structure Info (Structure_ID, Structure Name)
SECTOR Information (Sector_ID, Sector Name)
SERVICE (Service_ID, Service Name)
Client Information (Customer_ID, First Name, Last Name, Add-1,Add-2,  City, State, Zip code, Contact, Phone, ABN, TFN, Structure _ID, Sector _ID, Service_ID)
HR Master (Employee_ID, First Name, Last Name, Designation, Qualification, Service_ID, Qualification, DoJ, DoR, Cstatus)

Current Rate Card (Rate_ID, DoM, Employee_ID, Rate, Service_ID)

Previous Rate Card(PRate_ID, Previous Rate, Employee_ID)
Client Services Form (Appointment_ID, Customer_ID, Client Name, Service_ID, Structure_ID, Date of Start, Date of End, Start Time, End Time, Employee_ID,  Charge)
Billing Values (Billing_ID, Date From, Date To, Date, Total Charge, Employee_ID, Customer_ID, Appointment_ID)


Assumptions taken in to Consideration


In the given case context scenario, there are various factors that have been detailed pertaining to the existing system, and there are certain issues that has been detailed for which the solution is potentially required to develop the business in an extended manner.

Keeping in purview the need of the system, the following assumptions has been taken in to consideration.

1) The validations and the rules for the integration of the data and the records shall be taken up during the code development

2) The types and the characters of the fields has not been detailed in this report, assuming the factor, that the process of assigning the characters and the length is standardized and is laid in the specifications and hence is not assigned in specific in this document.

3) The table names has been created on own ideas as the case context does not define any specific inputs on the case scenario.

3NF and the relations

The 3NF is a very essential attribute that has to be considered in an effective manner which could reduce the redundancy in the system, but in the given case scenario, when the solutions are proposed, for few tables, the 3NF is not adhered to, keeping in view the limited records that could take place in each table, and the complexities that could arise in terms of increasing the integration of the data between various tables.

The tables where the 3NF is not adapted are the “Previous Rate Card”, “Current Rate Card”, “Client Services Form”. Keeping in purview the information solicitation that is required for the tables, the fields has been taken in to inclusion in the table for records.



Beal, V. (n.d.). Entity-relationship model (diagram). Retrieved Jan 03, 2015, from Webpodia:

(n.d.). Normalisation to 3NF. (2007). Convert to Third Normal Form (3NF). Retrieved Dec 27, 2014, from


Contact Information:


Academic Avenue


Skype Id: hari.reddyc

Phone: +91-9502542081(IND)  (Whats App, Viber)

phone: +1-2089086040 (US)

Thanks for visting

One thought on “ER Diagram for the Proposed Solutions

Leave a Reply

Your email address will not be published. Required fields are marked *