Introduction
This document provides a well-detailed entity-relationship model (ERD) that is appropriate for New CQ Asset Management’s (NCQAM) relational database. Additionally, a list of business rules at NCQAM is given, assumptions underlying the ERD are stated and definitions are given for each attribute and entity. Entity-relationship drawing is one of the processes that make up the design stage of a relational database. An ERD shows the entities that make up a relational database, their attributes and the relationships between them (About.com).
Entity-relationship diagram
For the NCQAM’s relational database, the appropriate ER model is shown figure 1 below.
Business rules
NCQAM’s relational database is to capture the company’s business rules so that it enhances data integrity. Business rules clearly define how work is done in a company. A careful study of NCQAM’s case study reveals the business rules currently in place at NCQAM. One of the business rules is that for each analyst there should be an ID, full name, phone number and expertise area. Another business rule is that analysts make recommendations after research processes that include comprehensive fundamental and thorough technical analysis. In addition, the recommendations can only be buy, long-term buy, sell, and hold. At NCQAM it is a business rule that each recommendation should be recorded alongside the date it was made. For each stock NSQAM is dealing in, the rule is that there should be a share code, a company name, a stock section, dividend yield and the year it was listed. In addition, the share code is a three-letter code. For example, the share code for BHP Billiton Company is BHP.
Another business rule is that for each of NCQAM’s managers there should be an ID, name, phone and address. Additionally, a manager can manage more than one portfolio and he /she can only trade stocks on behalf of clients and not for him/her. A single portfolio can be associated with one client only. Details of each portfolio like available cash should be recorded. For each trade, the share quantity traded, share price and transaction date should be recorded. Finally, it is a rule that for each of NCQAM’s clients that there is an ID, name, address, contact phone and tax file numbers (TFN).
Assumptions underlying the ER model
A number of assumptions underlie the entity-relationship model above. Considering that variations exist between the stocks NCQAM is dealing in it is assumed that at a given instant a recommendation can only be made for a single stock. This assumption forms the basis of two other assumptions. The first is that stock review and research is done for one stock at a time. The second is that at a given instance an analyst is reviewing stock for one company only. Since it is a business rule at NCQAM that a single portfolio be associated with only one client it is thus another assumption that a portfolio can only contain one or multiple kind of stocks, that is, stock for one or more companies. Form this assumption it can be further assumed that a NCQAM client cannot have multiple portfolios. Since recommendations can be buy, long-term buy, sell, and hold it is assumed that NCQAM’s transactions on behalf of a client are both buy and sell.
Entity and attribute definitions
The entity and attribute definitions for the above ER model are shown in Appendix A.
Works Cited
“Entity-relationship diagram.” About.com. n.p. (2011) Web.