What is a database management system? -

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');

12DATABASE SYSTEMS
StockExchange Table
insert into stockexchange values ('SE101','New York','New
York','M101');
insert into stockexchange values ('SE102','Nasdaq','New
York','M101');
insert into stockexchange values
('SE103','London','London','M102');
insert into stockexchange values
('SE104','Tokyo','Tokyo','M103');
insert into stockexchange values
('SE105','Sanghai','Sanghai','M103');
Shares table
insert into shares values ('SBUX','Starbucks
Corporation',45.85,To_date('2020/03/19',
'yyyy/mm/dd'),'M101');
Insert Into Shares Values ('NXPI','NXP
Semicondutors',78.25,To_Date('2020/03/19',
'yyyy/mm/dd'),'M101');
Insert Into Shares Values
('FB','Facebook',90.25,To_Date('2020/03/19',
'yyyy/mm/dd'),'M101');
Insert Into Shares Values ('SFIX','Stitch
Fix',38.36,To_Date('2020/03/19', 'yyyy/mm/dd'),'M101');
Insert Into Shares Values ('JNJ','Johnson and
Johnson',85.36,To_Date('2020/03/19',
'yyyy/mm/dd'),'M101');
Insert Into Shares Values ('BRK','Birkshire
Hathaway',78.25,To_Date('2020/03/19',
'yyyy/mm/dd'),'M101');
Insert Into Shares Values
('AAPL','Apple',92.65,To_Date('2020/03/19',
'yyyy/mm/dd'),'M101');
Insert Into Shares Values ('NVS','NVS Novartis
NG',78.25,To_Date('2020/03/19', 'yyyy/mm/dd'),'M102');

13DATABASE SYSTEMS
Insert Into Shares Values
('HSBC','HSBC',54.25,To_Date('2020/03/19',
'yyyy/mm/dd'),'M102');
Insert Into Shares Values
('UL','Unilever',69.65,To_Date('2020/03/19',
'yyyy/mm/dd'),'M102');
Insert Into Shares Values ('ToT','Total
SA',85.35,To_Date('2020/03/19', 'yyyy/mm/dd'),'M102');
Insert Into Shares Values ('SAP','SAP
SE',78.35,To_Date('2020/03/19', 'yyyy/mm/dd'),'M102');
Insert Into Shares Values ('BP','BP
PLC',69.35,To_Date('2020/03/19', 'yyyy/mm/dd'),'M102');
Insert Into Shares Values
('AAPL','Apple',89.25,To_Date('2020/03/19',
'yyyy/mm/dd'),'M102');
Insert Into Shares Values
('VoD','Vodafone',58.35,To_Date('2020/03/19',
'yyyy/mm/dd'),'M102');
Insert Into Shares Values
('BABA','AliBaba',65.35,To_Date('2020/03/19',
'yyyy/mm/dd'),'M103');
Insert Into Shares Values ('TM','Toyota
Motors',93.25,To_Date('2020/03/19', 'yyyy/mm/dd'),'M103');
Insert Into Shares Values
('BIDU','BAIDU',45.35,To_Date('2020/03/19',
'yyyy/mm/dd'),'M103');
Insert Into Shares Values
('AAPL','Apple',85.65,To_Date('2020/03/19',
'yyyy/mm/dd'),'M103');
Appointment Table
insert into appointment values (101,To_Date('2020/03/01',
'yyyy/mm/dd'),'C101','B101','R101','AAPL','M101');
insert into appointment values (102,To_Date('2020/03/02',
'yyyy/mm/dd'),'C101','B101','R103','SBUX','M101');
insert into appointment values (103,To_Date('2020/03/01',
'yyyy/mm/dd'),'C102','B101','R102','FB','M101');

14DATABASE SYSTEMS
insert into appointment values (104,To_Date('2020/03/02',
'yyyy/mm/dd'),'C102','B101','R104','JNJ','M101');
insert into appointment values (105,To_Date('2020/03/02',
'yyyy/mm/dd'),'C103','B101','R105','BRK','M101');
insert into appointment values (106,To_Date('2020/03/03',
'yyyy/mm/dd'),'C103','B101','R101','AAPL','M102');
insert into appointment values (107,To_Date('2020/03/04',
'yyyy/mm/dd'),'C104','B101','R102','AAPL','M102');
insert into appointment values (108,To_Date('2020/03/04',
'yyyy/mm/dd'),'C104','B101','R103','VoD','M102');
insert into appointment values (109,To_Date('2020/03/04',
'yyyy/mm/dd'),'C105','B101','R104','UL','M102');
insert into appointment values (110,To_Date('2020/03/04',
'yyyy/mm/dd'),'C105','B102','R105','SAP','M102');
insert into appointment values (111,To_Date('2020/03/05',
'yyyy/mm/dd'),'C106','B102','R104','TM','M103');
insert into appointment values (112,To_Date('2020/03/05',
'yyyy/mm/dd'),'C106','B102','R103','BABA','M103');
insert into appointment values (113,To_Date('2020/03/06',
'yyyy/mm/dd'),'C107','B102','R102','BIDU','M103');
insert into appointment values (114,To_Date('2020/03/06',
'yyyy/mm/dd'),'C101','B103','R101','AAPL','M103');
SQL Queries
Query 1
select companyname, price from shares where companyname =
'Apple';
Query 2

15DATABASE SYSTEMS
select s.companyname, s.price, m.name from market m,
shares s
where m.marketid=s.marketid and m.name='USA' and s.price =
(select max(price) from (select s.companyname, s.price,
m.name from market m, shares s
where m.marketid=s.marketid and m.name='USA'));
Query 3
select companyname, price from shares where
companyname='Apple' and price =
(select min(price) from shares where companyname='Apple');
Query 4
select b.name, count(a.appointmentno) as TotalAppoinment
from brokers b, appointment a where a.brokerid=b.brokerid
group by b.name having count(a.appointmentno)=(select
max(t.a) from (select b.name, count(a.appointmentno) as a
from brokers b, appointment a where a.brokerid=b.brokerid
group by b.name) t);

16DATABASE SYSTEMS
Query 5
select c.name, count(a.appointmentno) as TotalAppoinment
from customer c , appointment a where a.custid=c.custid
group by c.name having count(a.appointmentno)=(select
max(t.a) from (select c.name, count(a.appointmentno) as a
from customer c ,
appointment a where a.custid=c.custid group by c.name) t);
Query 6
select s.companyname, s.price, m.name from market m,
shares s
where m.marketid=s.marketid and m.name='Europe' and
s.price>(select avg(s.price) from market m, shares s
where m.marketid=s.marketid and m.name='Asia');

17DATABASE SYSTEMS
Query 7
select s.companyname, s.price, m.name from market m,
shares s
where m.marketid=s.marketid and s.price>(select
avg(s.price) from market m, shares s
where m.marketid=s.marketid and m.name='USA');
Query 8
update shares
set price = 1000
where companyname='Apple';

18DATABASE SYSTEMS
Query 9
select b.name, avg(s.price) as average from brokers b,
appointment a,
shares s, market m where a.brokerid=b.brokerid and
a.sharecode=s.sharecode and
s.marketid=m.marketid group by b.name having
avg(s.price)>(select avg(s.price) from market m, shares s
where m.marketid=s.marketid and m.name='Europe');
Query 10
select s.companyname, s.price from market m, shares s
where s.marketid=m.marketid and s.price > (select
avg(price) from market m, shares s
where m.marketid=s.marketid and m.name='Europe') or
s.price > (select avg(price) from market m, shares s
where m.marketid=s.marketid and m.name='Asia') and s.price
< (select avg(price) from market m, shares s
where m.marketid=s.marketid and m.name='USA');

19DATABASE SYSTEMS
Procedures or Triggers
/*create customer_histtory table*/
create table customer_history (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);
/*Trigger*/
CREATE OR REPLACE TRIGGER customer_history
AFTER DELETE
ON customer
FOR EACH ROW
DECLARE
system varchar2(10);
BEGIN
SELECT user INTO system FROM dual;
INSERT INTO customer_history
VALUES(:OLD.CUSTID, :OLD.NAME,:OLD.ADDRESS,:OLD.CITY,
:OLD.EMAIL,:OLD.CONTACT);END;
/*insert and delete to test Trigger*/
insert into customer values('C108', 'Paul Mark', 'New
Town','New York','[e-mail]',7867667676);
delete from customer where custid='C108';
select * from customer_history;

20DATABASE SYSTEMS

Gantt chart
ID Task Nam e Duration Start Finish Predecessors
1 Project Sum mary 2 days Fri 3/20/20 Sat 3/21/20
2 Design Phase 2 days Fri 3/20/20 Sat 3/21/20
3 Case Study Analysis 4 hrs Fri 3/20/20 Fri 3/20/20
4 Entity relationship Diagram 4 hrs Fri 3/20/20 Fri 3/20/20 3
5 Use Case Idenfication 4 hrs Fri 3/20/20 Fri 3/20/20 3
6 Identification of Actor and
their Role
3 hrs Fri 3/20/20 Fri 3/20/20 3
7 Use case diagram 8 hrs Fri 3/20/20 Fri 3/20/20 5,6
8 Tables and Normalization 4 hrs Fri 3/20/20 Fri 3/20/20 4,7
9 End Design Milestone 0 days Fri 3/20/20 Sat 3/21/20 8
10 Implementation Phase 1 day Sat 3/21/20 Sat 3/21/20
11 Table Creation 8 hrs Sat 3/21/20 Sat 3/21/20 9
12 Table Population 8 hrs Sat 3/21/20 Sat 3/21/20 11
13 Trigger Implementation 2 hrs Sat 3/21/20 Sat 3/21/20 11
14 Query Implementation 8 hrs Sat 3/21/20 Sat 3/21/20 13,11,12
15 End Implementation Milestone 0 days Sat 3/21/20 Sat 3/21/20 14
Mem ber 1,Member 3
Mem ber 1
Mem ber 3
Mem ber 3
Mem ber 3
Mem ber 2
3/20
Mem ber 2
Mem ber 2,Member 4
Mem ber 4
Mem ber 2
3/21
6 AM 12 PM 6 PM 12 AM 6 AM 12 PM 6 PM 12 AM 6 AM 12 PM 6 PM 12 AM 6 AM 12 PM 6 PM
Thu Mar 19 Fri Mar 20 Sat Mar 21 Sun Mar 22
Figure 3: Gantt chart Showing Milestones
Source: create by author

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]