Entity Relation Diagram for High Frequency Investments

This report contains the entity relationship diagram for High Frequency Investments, a leading investment management company in Australia. It explains the primary key, foreign key and multivalued key in the diagram and justifies the relationships between entities. It also includes logical design and references.

Running head: ENTITY RELATION DIAGRAM
Entity Relation Diagram
Name of Student-
Name of University-
Author’s Note-

1ENTITY RELATION DIAGRAM
1. Introduction
HFI (High Frequency Investments) is company in Australia which is considered as a
leading investment management company. The HFI is founded by managers and also security
analysts. In this report, the methods and procedures that are carried out are shown with an entity
relationship diagram. This report also contain the details of all the entity and attributes that are
involved in the ERD diagram of the company. The ERD shows the primary key, foreign key and
the multivalued key in the diagram. The explanation of all the entity and attributes are explained
and some assumptions are made and justification is provided for the procedures of the company.

2ENTITY RELATION DIAGRAM
2. Entity Relation Diagram of High Frequency Investments
Figure: ERD of High Frequency Investments

3ENTITY RELATION DIAGRAM
3. Assumptions
EMPLOYEE: The employee table consist of all the details of the employees who works
in HFI (Human Frequency Investments). There are mainly two types of employees who works in
HFI.
Employee_ID: There is a unique employee id for all the employees who work in HFI. All
the employees of the company have a unique identification number which is considered as the
primary key of the table EMPLOYEE.
Full Name: This denotes the full name of employee.
Address: The address of the employees are stored in the data base in this address table.
Phone_Number: The phone number is stored in this attribute. This is a multivalued
attribute because a particular employee may have more than one contact number.
Designation: An employee have some roles in the company. There are mainly two types
of designation in this company, Security analysts and Portfolio Managers. All the employees are
divided in these two categories.
Salary: The salary of all employees are mentioned in the salary attribute.
Date of Joining: The date of joining on which the employee has joined is saved in the
database in this field.
Portfolio Managers: This entity is about the employees having designation of portfolio
managers. The attributes of this entity are described in the section given below:
Manager_id: This is the primary key of this table. Each of the managers gets a unique id.
Employee_id: This attribute will act as foreign key in this entity.

4ENTITY RELATION DIAGRAM
Full name: This is the full name of the employee.
Expert Area: This denotes the area where an employee is expert in. There are some
particular fields in which an employee works the best. This attribute explains the area.
Phone number: Will contain the phone number of the employee.
Research Analyst: This entity is about the employees having designation of portfolio
managers. The attributes of this entity are described in the section given below:
Analyst_id: This is the primary key of this table. Each of the managers gets a unique id.
Employee_id: This attribute will act as foreign key in this entity.
Full name: This is the full name of the employee.
Expert Area: This denotes the area where an employee is expert in. There are some
particular fields in which an employee works the best. This attribute explains the area.
Phone number: Will contain the phone number of the employee.
PORTFOLIO: The portfolio is a table in which all the details of the stock, client are
saved in the database.
P_ID: This is a unique portfolio ID which is considered as a unique key in
PORTFOLIO table. All the transactions that takes place in the company has a unique
identification number.
Stock_ID: Particular P_ID has a particular Stock_ID which is connected with the
transaction.

5ENTITY RELATION DIAGRAM
Client_ID: The Client_ID is also mentioned along with the Stock_ID for the transaction
in the PORTFOLIO table.
Trade_Type: The type of trade, i.e. long term trade, buy, sell is mentioned in this
attribute.
Transaction_Date: The date of the transaction is recorded in this attribute.
Quantity_Traded: The number of items that are traded are noted in the database by the
quantity_traded attribute.
Available_cash: The cash that is available with the company after the buying and selling
of all the items are noted in available cash.
Price_per_unit: The price of per unit is mentioned in this attribute.
STOCK: The stock that is available with HFI (Human Frequency Investments) is all
mentioned in this table. Only the managers manages the stock table as all the employees cannot
be given the responsibility to handle the stock of the company.
Stock_ID: All the items in stock have a particular stock id which is denoted in this
attribute. The Stock table has Stock_ID as the primary key.
Company_Name: The name of the company is mentioned in this attribute with
which the items is bought from or sold to.
Recommendation: A particular item is sold or bought is mentioned in the
recommendation section.
Code: Code is a three letter word which uniquely identifies an ordinary stock. As
an example, the stock share of the Rio Tinto has the code RIO.

6ENTITY RELATION DIAGRAM
Section: This attribute mentions the section of the stock that is cleared from the
stock.
Status: This attribute shows the status of the stock that is how much quantity of
the item is left over or if the stock is empty.
CLIENT: The client attribute gives the details of all the clients that are associated with
the company. There are three types of clients that are involved in the company.
Client_ID: This a unique identification number which is needed to identify each
client. This is a primary key in the Client table as it uniquely defines each client.
Name: The full name of the client is detailed in this attribute and is stored in the
table in database.
Address: The full address of the client is stored in the database using this attribute.
Address is required to have a contact with the client and know from where the client belongs to.
Phone_Number: All the phone numbers are to be stored in the Phone_Number
attribute. This attribute is considered as multivalued attribute because it may have more than one
value.
Client_Type: There is mainly three types of client associate with the company.
Individual clients, trustee clients and corporate investor clients are mainly associated with HFI (Human
Frequency Investments). Individual client needs to have a TFN (Tax File Numbers), the trustee
clients should have to be nominated and the corporate investors should have an ABN (Australian
Business Number).

7ENTITY RELATION DIAGRAM
TFN: Tax File Numbers are unique numbers that are needed for clients which are
individual.
4. Justification
All the work of HFI (High Frequency Investments) are justified according to the stated
entity relationship diagram for the company. The relationship that are shown in the ERD are
justified within the relationship of all the entities. The employees of HFI manages the portfolio
and the stock of the company. Portfolio has a relationship with stock. The client has a relation
with portfolio because the client manages to its account through the portfolio itself.

8ENTITY RELATION DIAGRAM
6. Logical Design
EMPLOYEE
Column name Data type Allow value
Employee_id int Not null
Full Name Varchar (50) Not null
Address Varchar (100) Not null
Phone_number Varchar (12) Not null
Designation Varchar (10) Not null
Expert Area Varchar (10) Not null
Salary int Not null
Date of joining date Not null

9ENTITY RELATION DIAGRAM
PORTFOLIO
Column name Data type Allow value
p_id int Not null
Stock_id int Not null
Client_id int Not null
trade_type Varchar (10) Not null
transaction_date date Not null
quantity_traded int Not null
available_cash int Not null
price_per_unit int Not null

10ENTITY RELATION DIAGRAM
CLIENT
Column name Data type Allow value
client_id int Not null
name Varchar (50) Not null
adddress Varchar (100) Not null
Phone_number Varchar (12) Not null
client_type Varchar (20) Not null
TFN int Not null

11ENTITY RELATION DIAGRAM
STOCK
Column name Data type Allow value
stock_id int Not null
company_name Varchar (50) Not null
recommendation Varchar (100) Not null
Code int Not null
section Varchar (20) Not null
status Varchar (20) Not null

12ENTITY RELATION DIAGRAM
6. References
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational database
schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., Motwani, R., Srivastava, U.
and Widom, J., 2016. Stream: The stanford data stream management system. In Data Stream
Management (pp. 317-336). Springer Berlin Heidelberg.
Byrne, B. and Shahzad Qureshi, Y., 2013. UML Class Diagram or Entity Relationship Diagram:
An Object Relational Impedance Mismatch. In Procs of 6th Int Conf of Education, Research,
and Innovation. International Association of Technology, Education and Development (IATED).
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.