BuyMe Trading Agency: Database System Design and Implementation

This assignment focuses on the design and implementation of a database system for the BuyMe stock and share trading agency. It begins with the identification of entities such as Customers, Appointments, Brokers, and Shares, followed by the creation of an Entity Relationship Diagram (ERD) based on business rules. The assignment utilizes Unified Modeling Language (UML) to define use cases and actors within the system, represented through a use case diagram. Normalization is applied up to the third normal form to ensure data integrity. The implementation section includes the creation of tables for various entities, data population, and SQL queries to retrieve and manipulate data. The solution also includes procedures and a Gantt chart. The assignment provides a comprehensive overview of database design principles and practical implementation using SQL.

DATABASE SYSTEMS
Name of the Student
Name of the University
Author Note

1DATABASE SYSTEMS
Table of Contents
Entity and ERD................................................................................................................................2
Business Rules.............................................................................................................................3
Unified Modelling Language...........................................................................................................5
Use Cases.....................................................................................................................................5
Actors...........................................................................................................................................5
Use case Diagram........................................................................................................................6
Normalization..................................................................................................................................7
First Normal Form.......................................................................................................................7
Second Normal Form...................................................................................................................7
Third Normal Form......................................................................................................................7
Implementation................................................................................................................................8
Creation........................................................................................................................................8
Population..................................................................................................................................10
SQL Queries..............................................................................................................................14
Procedures or Triggers...............................................................................................................18
Gantt chart.....................................................................................................................................20
References......................................................................................................................................21

2DATABASE SYSTEMS
Entity and ERD
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

3DATABASE SYSTEMS
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:
Business Rules
i. Each customer can book zero or many appointments to the brokers.
ii. Each Appointment can be associated with one and only one customer.
iii. Each Receptionist can book one or more appointments for the customers.
iv. Each Appointment is associated to one and only one receptionist.
v. Each Broker can have one or more Appointments.
vi. Each Appointment can be associated to one and only one broker.
vii. Each Appointment can be related to zero or only one share of a particular market.
viii. Each Share of a particular market can be associated to the one or many Appointments.
ix. Each share can be related to one or many markets.
x. Each market have one or many share companies.
xi. Each market manages one or many stock exchanges.
xii. Each stock exchange is related to one and only one market.

4DATABASE SYSTEMS
Figure 1: Entity Relationship Diagram of BuyMe
Source: created by author

5DATABASE SYSTEMS
Unified Modelling Language
Use Cases
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.
Actors
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:

6DATABASE SYSTEMS
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
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:

7DATABASE SYSTEMS
Figure 2: Use case diagram of BuyMe
Source: created by author
Normalization
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.
First Normal Form
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.
Second 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.

8DATABASE SYSTEMS
Third 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.
Implementation
Creation
Customer Table
create table customer (custid varchar(5) primary key, name
varchar(40) not null ,
Address varchar(50) not null, City varchar(20) not null,
Email varchar(100) not null,
contact number(10) not null);
alter table customer
add constraint chkname check (regexp_like(name,'^[A-Za-
z''-]|[:space:]*$'));
alter table customer
add constraint chkemail check (email like '%@%');
Brokers Table
Create table brokers (BrokerID varchar(5) primary key,
name varchar(40) not null, Email varchar(100) not null,

9DATABASE SYSTEMS
contact number(10) not null, Blevel varchar(10));
alter table brokers
add constraint chknameb check (regexp_like(name,'^[A-Za-
z''-]|[:space:]*$'));
alter table brokers
add constraint chkemailb check (email like '%@%');
Receptionist Table
create table Receptionist(ReceptionistID varchar(5)
primary key, name varchar(40) not null, Email varchar(100)
not null unique,
contact number(10) not null unique);
alter table receptionist
add constraint chknamer check (regexp_like(name,'^[A-Za-
z''-]|[:space:]*$'));
alter table receptionist
add constraint chkemailr check (email like '%@%');
Market Table
create table market(MarketID varchar(5) primary key, name
varchar(30) not null);
StockExchange Table
create table StockExchange(StockExchangeID varchar(5)
primary key, name varchar(30) not null, city varchar(30),
MarketID varchar(5),
constraint fk_mk1 foreign key(marketid) references
market(marketid));

10DATABASE SYSTEMS
Shares table
create table shares (sharecode varchar(5), Companyname
varchar(30) not null, price number(6,2) not null,
Lastupdated date not null, marketid varchar(5) not null,
primary key(sharecode, marketid),
constraint fkmarket2 foreign key(marketid) references
market(marketid));
Appointment Table
create table Appointment (AppointmentNo number(5) primary
key, ApDate date not null, CustID varchar(5) not null,
BrokerID varchar(5) not null,
ReceptionistID varchar(5) not null, Sharecode varchar(5)
null, marketid varchar(5) null);
alter table Appointment
add constraint fkcust foreign key(custid) references
customer(custid);
alter table Appointment
add constraint fkbroker foreign key(brokerid) references
brokers(brokerid);
alter table Appointment
add constraint fkrec foreign key(Receptionistid)
references receptionist(receptionistid);
alter table Appointment
add constraint fkshare foreign key(sharecode, marketid)
references shares(sharecode, marketid);
Population
Customer Table
insert into customer values('C101', 'Paul Mark', 'New
Town','New York','[e-mail]',7867667676);
insert into customer values('C102', 'Joana Paul', '10th
street','New 1York','[e-mail]',88585685);

11DATABASE SYSTEMS
insert into customer values('C103', 'James Neo', 'kalp
road','Ohio','[e-mail]',858585822);
insert into customer values('C104', 'Sam Wilson', 'rowdon
street','London','[e-mail]',4492956256);
insert into customer values('C105', 'Kiara Mathew', 'fancy
Street','Tokyo','[e-mail]',87845478);
insert into customer values('C106', 'Susane Kick', 'george
street','Beijing','[e-mail]',795655622);
insert into customer values('C107', 'James Lannister',
'221b baker street','New
Jersey','[e-mail]',6497645265);
Brokers Table
insert into brokers values('B101', 'Rony
Patt','[e-mail]',876765877,'Gold');
insert into brokers values('B102', 'Lary
Saint','[e-mail]',546848445,'Silver');
insert into brokers values('B103', 'Rosina
Malfoy','[e-mail]',474478454,'Bronze');
Receptionist Table
insert into receptionist values('R101', 'Kate Marry',
'[e-mail]',5457876757 );
insert into receptionist values('R102', 'Jane kruth',
'[e-mail]',784944522);
insert into receptionist values('R103', 'Ruth Heather',
'[e-mail]',8785445323);
insert into receptionist values('R104', 'Ann Paul',
'[e-mail]',546454984);
insert into receptionist values('R105', 'Jake wilson',
'[e-mail]',548875454);
Market Table
insert into market values ('M101','USA');
insert into market values ('M102','Europe');
insert into market values ('M103','Asia');