The application scenario: CQ Capital Partner (CQCP)
Central Queensland Capital Partners (CQCP) is a leading investment management company in Australia with the headquarter office located in Rockhmapton, Queensland. It was founded by a group of professional funding managers and foreign currency analysts. Specialized in foreign currency exchange (FX) trading in Australian financial market, it provides investment in FX & consultant services to institutional investors and private clients. With the growth of its business, CQCP needs a relational database to keep track of all employees, their clients, analysts reviews for foreign currencies, portfolio managed, as well as the currency trade history.
The employees of CQCP mainly consist of analysts, portfolio managers and administrative staff. Each employee is given a job title. The duty of the analysts is regularly to review for various currencies that are traded in Australian financial market and make recommendations based on their research, including a comprehensive fundamental analysis and a thorough technical analysis. Each analyst is recorded with his/her ID, full name, address, phone number (including mobile phone) and expertise area. The recommendation could be buy, sell, or hold, with a brief reason. Of course the exchange rate of a currency against Australian dollar in market could vary significantly at any time. This is because the financial market is a highly complicated place where numerous factors like the concurrency of investment and speculation, macroeconomics performance of a country even a geo-political event all impact the actual exchange rate. So the recommendation should be time-sensitive. This means that a buy recommendation for a particular currency at a time could be downgraded as sell recommendation at other times. The currency can be described by its code, name, current interest rate and the rating information. The rating is normally conducted by an internationally recognized credit-rating agency like Standard & Poor, Moody and Fitch. In Australian FX trading market, a 3-letter code can uniquely identify a currency. As an example, the currency of US dollar has the code USD, with interest rate 1.0% set by the Federal Reserve Bank, and the rating is AA- from Standard & Poor. A currency may receive multiple ratings from different agencies. Appendix A lists some major currencies traded in Australian FX trading market.
The personal information (address, phone and qualification) of managers and administrative staff is also required to be recorded into the database. Each manager may manage multiple portfolios. Each portfolio has its own ID. Conceptually a portfolio can be regarded as equivalent to an account. However a portfolio should be only associated to one client. In addition, the portfolio could contain the data like available cash. Although CQCP provides service for all aspects of the wealth management, the database to be developed in this case study only concerns its core business foreign currency trading. When a manager decides to make a trade, he/she will trade the currency for a particular portfolio. In other words, the managers trade the currencies on behalf of clients rather than for themselves. The aim of trading is to try to make a profit when the managers buy and sell foreign currencies. A trade or a deal is defined as using Australian dollar to buy other currencies or other currencies you hold are sold in terms of Australian dollar, through an online trading platform. The database will record the trade details like amount or quantity traded, trade mode (buy or sell), price or exchange rate per currency unit (for example, currently the exchange rate of US dollar to Australian dollar is 1.28 approximately, that means 1 US dollar is sold/bought with 1.28 Australian dollar), and transaction date. The client normally can be categorized into three types: individual, trustee and corporate investor. Their IDs, names, addresses, and contact phones should be recorded. In addition, the Tax File Number (TFN) of an individual client is required. For a trustee client, a corresponding trust account is required to nominate; whereas for a corporate client, an Australian Business Number (ABN) is an essential data item.
Appendix A
Some major currencies traded in Australian market
Code Name Interest rate Rating
USD US Dollar 1.0% AA- from Standard & Poor (June 2014)
EUR Euro 0 ABA from Moody (Aug.2014)
JPY Japanese Yen 0 AA- from Standard &Poor (Dec.2014)
CNY Chinese Yuan 5.5% Aaa from Fitch (Nov.2014)
GBP Great Britain Pound 2% Aab from Fitch (Nov.2014)
CAD Canadian Dollar 2.5% Aab from Fitch (Nov.2014)
NZD New Zealand Dollar 2.0% Aab from Fitch (Oct.2014)
HKD Hong Kong Dollar 4.5% Aaa from Fitch (Oct.2014)
ZAR South African Rand 6% Bbb from Fitch (Oct.2014)
DKK Danish Kroner 3% Aab from Fitch (Oct.2014)
Note: (1) The interest rate and the rating result (symbol) are assumed for modelling purpose only.
(2) The above specification may not be presented every detail because one of your tasks for this assignment is to do your own analysis and research based on the application scenario description and the common knowledge.
Entity-relationship data model
You are required to develop an ER model to represent the information requirements for the application scenario (CQCP) as described on last two pages.
Your ER model must:
Some business rules or other aspects of the case study may not be clear to you when you read the case study. If this is the case, then you should either approach your lecturer or tutor for clarification, or you may simply make an assumption and then develop your ER model accordingly. For example, the case study might not mention all relevant participation information (also called minimum cardinalities). If so, you may make an assumption about what the minimum cardinalities might reasonably be, and then show these in your ER model accordingly. You should justify each assumption in terms of the business, for example: it is assumed that each customer must have at least one order because it is assumed that the business does not record customer details until the customer places an order.
To get yourself started, ask yourself, 'If I were running this business, what things would I need to keep a list of?' Write those things down. For each thing, what information would you need to record about it?
Entity and attribute definitions
You are required to write brief definitions for the entities and attributes in your model. For each entity and attribute in your model, write a sentence to describe the meaning of that entity or attribute. You should refer to the sections Naming and Defining Entity Types and Naming and Defining Attributes in the textbook. For example:
STUDENT: A Student is a person who has enrolled in one or more programs at the university. This includes Start Uni Now high school students but does not include prospective students.
Student No: Uniquely identifies each student.
Student Name: The full name of the student.
Date of Birth: The student’s date of birth.
…
PROGRAM: A Program is a program of study available at the university.
Program code: Uniquely identifies each program
Program name: The full name of the program.
…