2015.09.10 / 11

Learn about Entity Relationship Diagram

We have to create an ERD diagram for our new project which is used for Enterprise Resource Planning System. There we have to have a sharp knowledge on ERD concepts because it's going to be a business analyst role with me. So I learn about them from the beginning.

According to my point of view, it will be more helpful for DBMS learners from the beginning. I used (http://www.tutorialspoint.com/dbms/index.htm) for leaning about DBMS concepts with ERD diagram concepts also.

After improving my knowledge on ERD diagrams, I created the ERD diagram for our project by using Visual Paradigm tool. There is a free trial version for 30 days and by using it we draw the ERD after drawing the finalized rough diagram in a excel sheet. Actually it is more easy to draw the ERD diagram roughly at the beginning and we have improve it day by day or time to time when we need changes on ERD. After finalizing the ERD diagram with regard to our customer needs, we can draw the finalized ERD by using an tool like this.

ERD drawing Tool - (http://www.visual-paradigm.com/)

After drawing the finalized ERD, I was asked to create a report that would be nicely describe the ERD diagram. The description would have a technical description and also non technical description what is it in the system. A part of description would be as follows.

ERD Diagram for ERP Package

The following is the proposed ERD diagram for ERP package which is cloud based system. There we can see 10 tables. The detailed review about proposed ERD will be mentioned in the below.

Table name	Description	Attributes	Description	Relationship
tblCOA	This is the table for Chart of Accounts.	·         Fld_COAId ·         Fld_CateLable ·         Fld_NameLable	·         Fld_COAId is the primary key of the table. ·         Variable characters of all attributes are varchar.	·         Fld_COAId is Composite key of tblComCOA. ·         Fld_COAId is foreign key of tblglname.
tblComCOA	This is the table for company chart of accounts.	·         Fld_COAId ·         Fld_COMID ·         Fld_CateLAble ·         Fld_NameLable ·         Fld_NotAllowMinus ·         Fld_Filter	·         Fld_COAID & Fld_COMID are composite key. ·         Variable characters of all attributes instead of fld_NotAllowMinus are varchar. ·         Fld_NotAllowMinus character is from decimal.	·         Fld_COMID is Primary key of tbl_COM. ·         Fld_COAID is the foreign key of tblglname.
tblCOM	This is the table for company details.	·         Fld_COMId ·         Fld_RegNo ·         Fld_MonthEndDate ·         Fld_YearEndDate ·         Fld_ComNameLable ·         Fld_AddressLable	·         All the variable characters are varchar without fld_MonthEndDate & YearEndDate. ·         Fld_COMId is the primary key.	Fld_ComId is the foreign key of tblComCOA.
tblLable	This is the table for Labeling.	·         Fld_LableId ·         Fld_Name1 ·         Fld_name2 ·         Fld_Name3	·         Fld_LableId is the primary key. ·         All the variable characters are from varchar.
Tblglname	This is the table for categories.	·         Fld_GLNameID ·         Fld_COAId ·         Fld_GLNameLable ·         Fld_ComNameLAble ·         Fld_COACateLable ·         Fld_COANameLable ·         Fld_Filter	·         Fld_GLNameID is the primary key. ·          All the variable characters are from varchar for all attributes.	·         Fld_COAId is a foreign key. ·         Fld_GLNameID is a foreign key od tblglaccount.
tblBranch	This table is for branch details.	·         Fld_BRID ·         Fld_BRNAmeLAble ·         Fld_BRAddressLable	·         Fld_BRID is the primary key of the table. ·         All the variable characters are from varchar for all attributes.	·         Fld_BRID is a foreign key for tblglaccount.
tblTraLable	This table is for transaction labeling.	·         Fld_TransactionID ·         Fld_LineNo ·         Fld_Name1 ·         Fld_name2 ·         Fld_Name3	·         Fld_TransactionID is the primary key. ·         All the variable characters are from varchar instead of fld_LineNo. ·         Fld_LineNo is from the variable character of float.	·         Fld_Transaction ID is a composite key for tbltransactions.
Tbl_Member	This is the table for insert member details	·         Fld_MemNo ·         Fld_TypeId ·         Fld_NameLable ·         Fld_InitialLable ·         Fld_TitleLable ·         Fld_AddressLable ·         Fld_DOB ·         Fld_HomeNo ·         Fld_JoinDate ·         Fld_Active	·         Fld_MemNo is the primary key of the table. ·         All the variable characters of attributes are from varchar without fld_DOB and fle_JoinDate.	·         Fld_MemNo is the foreign key of tblpersonalaccount.
tblTransactions	This table is for inserting transaction details	·         Fld_TransNo ·         Fld_TransYear ·         Fld_AutoNo ·         Fld_AccountNo ·         Fld_CusNo ·         Fld_GLAccountID ·         Fld_BrID ·         Fld_TrDate ·         Fld_UpdDate ·         Fld_UpdTime ·         Fld_Debit ·         Fld_Credit ·         Fld_Balance ·         Fld_Interest ·         Fld_Penalty ·         Fld_Cheq ·         Fld_CheqDate ·         Fld_TrType ·         Fld_PbLine ·         Fld_PbPage ·         Fld_Print ·         Fld_Upd ·         Fld_UserId ·         Fld_CashbookID ·         Fld_PbNo ·         Fld_BatchMode ·         Fld_NoteLable ·         Fld_NameLable ·         Fld_addressLable	·         Fld_TransnNo & fld_TransYear is the composite key of the table. ·         Fld_AutoNo is from float character. ·         Fld_TrDate, fld_UpdDate, fld_UpdTime & fld_CheqDate are from Time Stamp variable character. ·         Fld_Debit,  Fld_Credit , Fld_Balance   , Fld_Interest , Fld_Penalty, fld_PbLine & fld_PbPage, fld_PbNo & fld_BatchMode are from the variable character of double. ·         All the other attributes are from varchar variable character.	·         Fld_TransNo is the foreign key of fld_TransactionID of tbl_TraLable. ·         Fld_AccountNo is the foreign key of tblpresonalaccount table.
Tblpersonalaccount	This table is for inserting personal account details.	·         Fld_AccountNo ·         Fld_GLAccountID ·         Fld_MemNo ·         Fld_BrID ·         Fld_InteID ·         Fld_PenID ·         Fld_FundID ·         Fld_OpenDate ·         Fld_Time ·         Fld_DueDate ·         Fld_Approved ·         Fld_Capital ·         Fld_Balance ·         Fld_Hold ·         Fld_MinBalance ·         Fld_Period ·         Fld_InteRate ·         Fld_PenRate ·         Fld_FundRate ·         Fld_Interst ·         Fld_Penalty ·         Fld_Fund ·         Fld_Acc_Type ·         Ald_Acc_Type2 ·         Fld_InteType ·         Fld_PenType ·         Fld_PassType ·         Fld_installment ·         Fld_DueInte ·         Fld_PayDate ·         Fld_PBType ·         Fld_PBLine ·         Fld_PBPage ·         Fld_Renew ·         Fld_Act ·         Fld_Note ·         Fld_StdOrders ·         Fld_TraAccount ·         Fld_DedAmount ·         Fld_ReneType ·         Fld_OpeDeb ·         Fld_OpeCre ·         Fld_Filter ·         Fld_Name Lable ·         Fld_InitialLable ·         Fld_TitleLable ·         Fld_AddressLable.	·         Fld_AccountNo is the primary key. ·         Fld_OpenDate, Fld_Time, Fld_DueDate, fld_PayDate are from timestamp character. ·         Fld_Approved, Fld_Capital, Fld_Balance, Fld_Hold, Fld_MinBalance, Fld_Period, Fld_InteRate, Fld_PenRate, Fld_FundRate, Fld_Interst, Fld_Penalty, Fld_Fund, fld_Installment, fld_DueInte, fld_PBLine, fle_PBPage, fld_DedAmount, Fld_OpenDeb, fld_OpenCre, are from  the variable character of double. ·         All the other attributes are from varchar character.	·         Fld_MemNo is a foreign key for tblmemeber table. ·         Fld_GLAccountID is a foreign key for tblglaccount. ·         Fld_AccountNumber is foreign key for tbltransactions.