COIS20026 Database Development and Management (T1 2015) Assessment item 1- Entity Relationship Diagram (ERD) Due date: Midnight AEST Friday of Week 5 ASSESSMENT Weighting: 30% 1 Length: No fixed length Objectives This assessment item relates to the course learning outcomes numbers 1, 4 and 5 as stated on page 1 of the course profile.
Assessment task In this assignment, you are to implement the data modelling based on the following application scenario. 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, analyst's 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:
• show all necessary entities, attributes and relationships
• show unique identifiers
• show multi-valued attributes (also called repeating attributes), if any
• show participation and cardinality
• show associative entities, if appropriate
• use the notation described in the set text
• use consistent and appropriate naming for entities and attributes throughout (refer to Chapter 2 & 3 of set text). 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. ... What you should submit You should submit a Microsoft Word document containing:
• A copy of your entity-relationship model(s).
• A list of business rules that are apparent from the case study.
• A list of any assumptions that you made (include any clarifications from your tutor) with justifications.
• Your entity and attribute definitions. Your document should contain appropriate identifying information (your student number, name, course number, etc.) and should be appropriately formatted. For example, your document should contain a very brief introduction stating the purpose of the document, as well as headings for each section. Important points
• You must work individually on this assignment.
• The model solution to this assignment will be released approximately TWO (2) weeks after the due date.
• Assignments that are submitted after the release of the model solution will receive zero.
• You must state in your assignment (at the end under the heading Acknowledgements is a good place) all materials and sources you used. This may be as simple as 'This assignment was completed using only the COIS 20026 course materials' if that was all you used.
• Extension requests must be submitted online (for AIC students, contact your local campus administration staff) before the assignment due date. A penalty of 5% of the total mark per calendar day will apply for late submissions without an extension, or zero if submitted after the release of the model solution.
• Before submitting your assignment, review the marking criteria as shown on the marking guide (the guide will be available on the course website). Ensure that all the criteria are addressed in your submission.
• After submitting your assignment, please verify that your submission was successful, i.e. download your submitted files to ensure they are correct and uncorrupted. Assessment criteria COIS20026 Assignment 1 Marking Sheet Student number: Student name: Entity-relationship data model Available marks Marks awarded Overall structure: Is the set of entities and relationships between them sound?
•Will the overall design work?
•All necessary relationships are present?
•No extraneous or inappropriate entities?
•Unique identifiers are appropriate?
•Entities/relationships not apparent from the case study have been supported by appropriate assumptions?
•Entities follow consistent and appropriate naming standard? Cardinalities: No major cardinality errors?
•Maximum cardinalities are suitable for the business in question?
•Minimum cardinalities okay?
•Unusual cardinalities have been supported by an assumption?
•Naming convention is consistent? 1 Attributes: all necessary attributes are included? (3 marks)
•Attributes are in the right entities?
•Attributes follow consistent and appropriate naming standard?
•Extra or "nice to have" attributes are supported by an assumption? 1 Entity and attribute definitions
•Definitions follow the guidelines specified in the set text? Assumptions, clarifications & business rules
•Student has recognised where information is missing and assumptions/clarifications are required?
•Assumptions are reasonable for the business at hand?