COMP9120 Database Management Systems
Assignment 1: Conceptual Modelling
Group assignment (10%) Introduction
This assignment intends to provide you with experience in conceptual modelling. You are given the domain description for the iSuper Investing System. Your task is to create an entity relationship diagram that can capture all the concepts and requirements conveyed in that description.
This is a group assignment, and you should ensure that you are enrolled in your assignment group on Canvas.
Please also keep an eye on your email and any announcements that may be made on Canvas.
Submission Details
For this assignment, you are required to submit your conceptual model in the form of an E-R diagram using the lecture notation, formatted as a PDF document. You can annotate your diagram with comments. You should submit your solution on Canvas by 5pm, Friday, 05/04/2019 (end of Week 6).
iSuper Investing System
The electronic investing system is called, iSuper that manages retirement account like superannuation, has assigned you the task of developing a new conceptual model, described in an E-R diagram, to capture all the concepts and requirements described in below.
The database system would allow future retirees to buy and sell shares of mutual funds (stocks, bonds, or both). The database system you will design should include information about the mutual funds, customers, their investments, and transactions¡¯ histories.
Mutual Funds: The system offers a variety of mutual funds of which the basic ones are money market, real-estate, short-term-bonds, long-term-bonds, balance-bonds-stocks, social-responsibility-bonds-stocks, general-stocks, aggressive-stocks, and international-markets-stock. Each mutual fund has a name (e.g. money-market), is identified by its symbol and belongs to one or more categories: fixed, bonds, stocks, and mixed. For example, money-market and real-estate are fixed. However, balance-bonds-stocks and social- responsibility-bonds-stocks are mixed. The database also maintains a description of each fund. Information about the date when the fund was created and the total number of shares of this fund is also maintained. The database keeps track of the closing price of each mutual fund, that is, the price of the mutual fund at the end of the previous day that is given by a date. This price is used in today’s purchases, exchanged/traded shares and calculation of investment.
Customer data: For every customer, we need to store their name, address, email, a unique login name, a password and a balance. Balance is the amount of money not yet invested in any mutual fund. We would also like to keep track of the customer’s allocation preferences based on which assets are allocated. This allocation is used whenever a new amount is deposited in the iSuper account: it is invested in shares based on the allocation preferences expressed as percentages. A customer must specify at least one preference. Many customers may specify the same mutual fund as their preference. A customer also owns a number of shares for a particular mutual fund represents an association between the customer and mutual fund. Customers are allowed one single monthly change to their allocation preference and it takes effect immediately. This means that only one single update for a particular customer is allowed for the current month. The single monthly change is to be maintained and enforced at all times. Customers are free to distribute their allocation in any way they want as long as the total of allocations adds up to exactly 100%.
1
School of Computer Science
COMP9120 Assignment 1
Administrator: For every administrator, we need to store their name, address, email, a unique login name and a password.
Investment transactions: For a number of reasons (audit, statistics, analysis, etc.), we would like to keep track of the history of trading transactions. Transactions are identified by transaction ID. A transaction is either a deposit transaction or an investment transaction. A deposit transaction is for customers depositing an amount of money into their accounts. An investment transaction is for customers selling or buying mutual funds, which is indicated by action attribute in their portfolio action relationship. The portfolio action is associated with the number of shares involved, (price per) share and (total) amount of the action.
Marking
This assignment is worth 10% of your final grade for the unit of study. Your group¡¯s submission will be marked according to the attached rubric (see last section of this assignment description).
Group member participation
If members of your group do not contribute sufficiently you should alert your tutor as soon as possible.
The course instructor has the discretion to scale the group¡¯s mark for each member as follows:
Level of contribution
Proportion of final grade received
No participation.
0%
Full understanding of the submitted work.
50%
Minor contributor to the group¡¯s submission.
75%
Major contributor to the group¡¯s submission.
100%
Marking Rubric
Your submissions will be marked according to the following rubric, with a maximum possible score of 10 points.
Novice (0 pts)
Competent (1.5 pt)
Proficient (2.5 pts)
Notation
Big mistakes in the usage of E-R notation
Good usage of E-R notation with a few mistakes
Proficient usage of the E-R notation
Core Model
Less than competent model of the given scenario
Some entities, relationships, or attributes cannot be correctly captured by the model
The core model was very well designed, and all the main entities, relationships and attributes can be correctly captured by the model
Constraints
No constraints captured at all
Some constraints (key / total participation constraints on relationship types etc) are included in the model, but either incorrectly or incomplete
All appropriate constraints are modelled correctly
Design Specialities
No design specialities used
At least one ISA, weak entity or aggregation is used, but either incorrectly or incomplete
All appropriate design specialities are used correctly
2