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|
|Service ID||Number||Foreign Key|
|Current Rate Card||Rate_ID||Number||Primary Key|
|Employee _ID||Text||Foreign Key|
|Service_ ID||Text||Foreign Key|
|Previous Rate Card||Prate_ID||Number||Primary Key|
|Client Services Form
|Date of Start||Date|
|Time of Start||Number|
The dependencies are developed according to 3NF levels
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)
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.
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: http://www.webopedia.com/TERM/E/entity_relationship_diagram.html
(n.d.). Normalisation to 3NF. http://www.cs.nott.ac.uk/~nza/G51DBS/dbs11.pdf.
Sqa.org. (2007). Convert to Third Normal Form (3NF). Retrieved Dec 27, 2014, from www.sqa.org.uk: http://www.ccs.neu.edu/home/mirek/classes/2010-F-CS3200/Slides/Ch2_ER.pdf
Skype Id: hari.reddyc
Phone: +91-9502542081(IND) (Whats App, Viber)
phone: +1-2089086040 (US)