Description
General Instructions:
1. Please ensure your answers are well-formatted and easy to read: Lines connecting
tables are obvious, and for each relationship, the cardinality ratios and their directions
are specified (whether they are one-to-many, one-to-one, or many-to-many).
2. It is highly recommended that you model the ER/EER diagram using the tools
provided in class.
3. It is preferable to use the naming conventions that have business acumen and are
self-explanatory. For example, use SIN for the SIN number, clientID for the Client
ID and so on. It should be easy for anyone reading the diagram to understand.
Model Requirements:
1. Clients are individual investors who buy Financial Securities (FS) as investments.
They do this through financial advisers.
2. Clients are identified by a SIN, and their names, addresses, and ages must be recorded.
3. Financial advisers are professional investment agents who contact clients and recommend FS for them to buy.
4. Financial advisers are identified by a SIN. For each adviser, the name, specialty, and
years of experience must be recorded.
5. Every client has a primary financial adviser. Every adviser has at least one client.
6. Financial Security Issuers (FSI) are companies that are listed in the exchange and
offer FS for investors to buy. Examples of FSI are IBM, Microsoft, TELUS, etc.
7. Each financial security issuer is identified by name and has a phone number.
8. For each financial security, the trade name and symbol must be recorded.
9. Each security is sold by a given issuer, and the trade name identifies a security
uniquely from among the products of that issuer. If an issuer is deleted, you need
not keep track of its products any longer.
10. An exchange is a marketplace in which securities, commodities, derivatives and other
financial instruments are traded.
1
11. Each exchange has a name, address, and phone number.
12. Each exchange lists several securities and has a price and currency for each. A security
could be listed at several exchanges, and the price could vary from one exchange to
another.
13. Financial advisers recommend securities for clients. An adviser could recommend
one or more securities for several clients, and a client could obtain recommendations
from several advisers.
14. Each recommendation has a date and a quantity associated with it. You can assume
that, if an adviser recommends the same security for the same client more than once,
only the last such recommendation needs to be stored.
15. Issuers have long-term contracts with exchanges. An issuer can contract with several
exchanges, and an exchange can contract with several issuers. For each contract, you
have to store a start date, an end date, and the text of the contract.
Definitions: Here are links for more details about the terminology used for this assignment:
1. Financial Securities: http://www.investopedia.com/terms/s/security.asp
2. Listed Security: http://www.investopedia.com/terms/l/listedsecurity.asp
3. Financial Security Issuer: http://www.investopedia.com/terms/i/issuer.asp
4. Trade Name: https://en.wikipedia.org/wiki/Trade_name
5. Financial Advisor: http://www.investopedia.com/terms/f/financial-adviser.
asp
6. Exchange: http://www.investopedia.com/terms/e/exchange.asp
7. Ticker Symbol: http://www.investopedia.com/terms/t/tickersymbol.asp
Submissions:
1. SUBMIT The Entity Relationship data model in MWB format along with a screenshot either in JPG or PNG format, after adding at least three records to each table.
The logical model should include the tables, attributes, keys, mandatory role constraints, and referential integrity constraints.
2. SUBMIT The output script in SQL format or text-based file (e.g. TXT or DOCX),
after forward engineer the designed ER-model. The script should include the structure of the tables in addition to the data stored in them.
2