Question

Construct an ER diagram for a database system that models data of the following situation. You are creating a database for a stock trading company. The company has clients and financial officers. Each client is either an individual or another legal entity (e.g. another company). A client has a name, social security number, address, contact information, the date that entered the database system, and also a ranking that the trading company keeps internally (high, med, low). Financial officers have a name, employee ID, social security number,annual salary, bonus, bonus. There are contracts that one client (or more clients together in one contract) have with the hedge fund. Each contract has a contract id, budget and task. For  One constraint is that each contract has exactly one financial officer that is responsible and at least one client. There can also be more than 1 financial officer in 1 location. There is one office location for hedge fund with address and phone number. Draw an ER diagram for the above description.

Answer 1

Cardinality specifies how many instances of an entity relate to one instance of another entity. Ordinality is also closely linked to cardinality. While cardinality specifies the occurrences of a relationship, ordinality describes the relationship as either mandatory or optional. In other words, cardinality specifies the maximum number of relationships and ordinality specifies the absolute minimum number of relationships.

Steps involved :

1. identify entities (client, fincial_officer, contract, hedge_fund)
2. identify attributes for each entity
   1. client:  name, social security number, address, contact information, the date that entered the database system, and also a ranking that the trading company keeps internally (high, med, low).
   2. finacial_officer: name, employee ID, social security number,annual salary, bonus
   3. contract: contract id, budget and task.
   4. hedge_fund(weak entity: i.e. can only be identifies by knowing a fincial office and a contract) : phone, address, location
3. identify relations and involving entities (contract-has-clients, contract-has-fincial_officer, contract-has-hedge_fund, hedge_fund-location_FO-finacial_officer)
4. identify cardinality constraints
   1. contract-has-clients: many to many as each client can have multiple contracts similarly a single contract can have multiple clients
   2. contract-has-fincial_officer: many to one as a single fincal_officier might be serving many contracts while a sinlgle contract can only have a single fincial_officer