Entity Relationship Diagram and Use Case Diagram for BuyMe Trading System

1. Show Current Price Of Apple For All Exchanges

2. Show Maximum Price Of a Share In Usa Market 

3. Show Minimum Price For Apple For All Data (From All Exchanges) 

4. Find Broker Who Has Maximum Number Of Appointments 

5. Find Customer Which Booked The Maximum Number Of Appointments 

6. Show All Uk Stocks Having Price Higher Than Average For Asian Market Today 

7. Show All Stocks Having Price Higher Than Average For Usa Market Today 

8. Update The Price For All Apple Shares (Assuming There Is a Price Change To 1000$) 

9. Show Average Price Of Asian Shares Which Are Higher Than Average Of European Shares For Gold Brokers 

10. Find An Asian Stock Having Price Which Is Higher Than Average Of Asian And European Stock But Less Than Average Of Usa Stock.

Identification of the entities are most important part of the design and analysis of a Database system. In the current scenario, BuyMe is a stock and share trading agency having headquarters in London. The entities are inspired from the real world objects (Al-Btoush 2015). The identified objects for the BuyMe are Customers, Appointments, Receptionist, Brokers, Shares, Market and Stock Exchange.

Customers: Customer details are stored such as Name, Address, City, Email and Contact. Each record is identified by CustID.

Appointments:  Customer Appointment details are stored such as, Appointment date, Broker, Receptionist, customer, market, and shares. Each Record is identified by Appointment No.

Receptionist: Stores receptionist details along with Name, email, contact, and identified by Receptionist ID.

Brokers: Stores Broker details along with Name, email, contact, and identified by Broker ID.

Shares: Shares details such as, share code, company name, price, last updated date of the price and market name is recorded.

Market: Market ID and Market name is stored.

Stock Exchange: It stores the Stock Exchange name, location city and market it belongs to. It is recognized by Stock Exchange ID.

The objects (Entities) are used to represent the Entity Relationship Diagram for the BuyMe Database System. It show the logical structure of the intended database of BuyMe. The Diagram has been developed using the business rules of the company. The processes are determined on the basis of the business functions of the company to achieve their goals (Kiedrowicz et al. 2015). The business rules (processes) for the Entity relationship Diagram has been given below:

1. Each customer can book zero or many appointments to the brokers.
2. Each Appointment can be associated with one and only one customer.
3. Each Receptionist can book one or more appointments for the customers.
4. Each Appointment is associated to one and only one receptionist.
5. Each Broker can have one or more Appointments.
6. Each Appointment can be associated to one and only one broker.
7. Each Appointment can be related to zero or only one share of a particular market.
8. Each Share of a particular market can be associated to the one or many Appointments.
9. Each share can be related to one or many markets.
10. Each market have one or many share companies.
11. Each market manages one or many stock exchanges.

Each stock exchange is related to one and only one market.

Unified Modeling Language

Figure 1: Entity Relationship Diagram of BuyMe

Use case is identification is done on the basis of the business process of the organization. The business process can be done by the actors in the system (Jacobson, Spence and Kerr 2016). The identified use case and their description is given below:

Book Appointment: The Appointment between the customer and broker is booked in the system for taking advisory up on the stock buying.

Buy Shares: The shares is bought by the customers after their appointments.

Observe Stock: The stock levels and their profit/gain is observed by the CTO of the company.

Solve Query: The queries and concerns raised by the customers are solved by the broker.

Assist Customer: The assistance is provided to the customer for advices and share buying by the broker.

Make Payment: Customer makes the payment of the share which is bought.

Make Query: Customer can make queries and raise their concerns to the brokers.

Identification of the actors in the system is a part of the system design in Unified Modelling Language. The Actors the individuals or group are individuals who perform tasks in the system (Langer et al. 2016). The identified Actors for the BuyMe system are described below:

Customer: Customer is responsible for booking appointments, buying shares, making payments and raising queries in the system.

Receptionist: Receptionist has the role for booking the appointments of the customers with the broker.

CTO: CTO of the company has the responsibility of maintaining a keen eye on the profit/loss of the company and ups/downs in the stock levels.

Broker: Broker is responsible for attending appointments for the customers, solving their concerns and assist them to buy shares in the system.

Use case diagram represents the business processes as use case, actors of the system and relationship between them. It clearly defines which actors perform what functionalities in the system (Langer et al. 2014). The Use case diagram for the BuyMe trading system is given below:

Figure 2: Use case diagram of BuyMe

Normalization is a method of simplifying the database structure and tables. It helps in maintaining the redundancy, integrity and consistency in the database. Normalization is done step by step and it has five normal forms (Eessaar 2016). First Normal Form, second Normal Form, Third Normal Form, 3.5 Normal form and BCNF (Boyce-Codd Normal Form). For the BuyMe ERD, Normalization has been done up to third normal form which is discussed below.

According to the first normal form, the table should not have repetitive groups and there should only be atomic values in the relation (Jukic, Vrbsky and Nestorov 2014). In BuyMe ERD, all the relations have atomic values and no grouping is found hence, the table is in first normal form.

According to the second normal form, the relations should be in the first normal form and all the non-key attributes should depend on the key attribute of the table (Coronel and Morris 2016). For example, relation customer name, city, email, address and contact can be identified by the CustID which is a primary key. Hence, all the attributes depend on the primary key of the table which makes the relation in second normal form.

According to the third normal form the relations should be in second normal form and there should not be any transitive functional dependency between the attributes of the relation (Soulé 2014). For example market and shares are transitively functionally depended. To normalize this relation another relation is created which stores the MarketID and Market name separately. Though one market can have multiple shares and one share can be present at multiple markets the relation is resolved by using composite primary key in the shares relation.

References

Al-Btoush, A.A.S., 2015. Extracting Entity Relationship Diagram (ERD) from English Sentences. International Journal of Database Theory and Application, 8(2), pp.35-244.

Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.

Eessaar, E., 2016. The database normalization theory and the theory of normalized systems: finding a common ground. Baltic Journal of Modern Computing, 4(1), p.5.

Jacobson, I., Spence, I. and Kerr, B., 2016. Use-Case 2.0. Queue, 14(1), pp.94-123.

Jukic, N., Vrbsky, S. and Nestorov, S., 2014. Database systems: Introduction to databases and data warehouses (p. 400). Pearson.

Kiedrowicz, M., Waszkowski, R., Kubiak, B.F. and MaÅ›lankowski, J., 2015. Business rules automation standards in business process management systems. Information Management in Practice, pp.187-200.

Langer, P., Mayerhofer, T., Wimmer, M. and Kappel, G., 2014. On the usage of UML: Initial results of analyzing open UML models. Modellierung 2014.

Soulé, R., 2014. Functional dependencies and finding a minimal cover. línea]. Available: http://www. inf. usi. ch/faculty/soule/teaching/2014-spring/cover. pdf.[Último acceso: 2017]