Database Design and Use Report 2022

Running head: DATABASE DESIGN AND USE
Database Design and Use
Name of the Student
Name of the University
Author Note

1DATABASE DESIGN AND USE
Table of Contents
Introduction................................................................................................................................2
ERD Description and relationship.............................................................................................2
Scripts: Section 1........................................................................................................................3
List rows in all tables.............................................................................................................5
Script: Section 2.........................................................................................................................8
Queries...................................................................................................................................8
Script: Section 3.......................................................................................................................12
Script: Section 4.......................................................................................................................15
Queries.................................................................................................................................15
Script: Section 5.......................................................................................................................22
Queries.................................................................................................................................22
Conclusion................................................................................................................................28
Reference..................................................................................................................................30

2DATABASE DESIGN AND USE
Introduction
A database management system is a type of system software that basically deals with
the creating databases and managing them (Elmasri and Navathe 2017). This report intends to
deal with database management system of a given business model. An ERD (Entity-
Relationship Diagram) is a modelling technique for developing any database (Al-Masree
2015). The diagram contains mainly three elements which are Entities, Attributes and
Relationship between them (Roberts, Stanfill and Studer 2019). The provided Entity
Relationship diagram is analysed and according to the entities and their relationship the
further database is created, populated and manipulated.
ERD Description and relationship
An ERD has some relationships between the entities of the system. The entities which
have primary keys are known as strong entity (Roy-Hubara et al. 2017). The entity without
primary key is a type of weak entity. Also, sometimes an entity can have more than one
primary key known as super-key (Dunn and Nel 2017). The relationships help in defining
business model or say business rules for any system management (Weske, 2019). The
database and ERD is described by defining the primary keys of the entities, the attributes and
their datatype (Behal, Chen and He 2019). The check constraints and foreign key constraints
are also defined as unique for the particular table (Connolly and Begg 2015). The following
descriptions are established in the diagram:
i. Every customer has customer id, firstname, surname, gender and billing address
recorded in the system.
ii. Every sales person has salesperson id, firstname, surname and their gender are also
stored.
iii. Every shoporder contains order id and delivery address for that order id.

3DATABASE DESIGN AND USE
iv. Table orderline is a strong entity and it contains the record of quantity sold, quantity
delivered and their selling price for a particular order.
v. Each product has product id, name and current price.
vi. One product can be related to many orders in orderline in multiple quantities.
vii. One order can have multiple quantity and price according to the quantity.
viii. Each customer can have multiple order in shoporder.
ix. Each salesperson can sell many orders in shoporder.
Figure 1: ERD
Scripts: Section 1
Check constraint error
insert into customer values (8, 'Milton','Hastings','X','3 Blue St, Kew');

4DATABASE DESIGN AND USE
Check constraint error
insert into product values (40, 'Cupboard’, -99);
insert into product values (41, 'Bookcase',8765);
FK error
customer 5 orders:
insert into shoporder values(66,null,5,29);
customer 9 orders:
insert into shoporder values(67,null,9,26);
FK errors
Order 49
insert into orderline values (2,2,200,49,40,1,23);
Order 52

5DATABASE DESIGN AND USE
insert into orderline values(10,10,175,52,36,1,23);
List rows in all tables
Customer
select * from customer order by custid;
Salesperson
select * from salesperson order by spid;

6DATABASE DESIGN AND USE
Product
select * from product order by prodid;
Shoporder
select * from shoporder order by orderid;

7DATABASE DESIGN AND USE
Orderline
select * from orderline order by orderid;

8DATABASE DESIGN AND USE
Script: Section 2
Queries
2.1.1 select count(orderid) as total_orders from shoporder;
2.1.2 select count(shoporder.orderid) as female_orders from shoporder, customer where
shoporder.custid=customer.custid and customer.cgender='F';
2.1.3 select count(shoporder.orderid) as orders, customer.cgender from shoporder, customer
where shoporder.custid=customer.custid group by customer.cgender;
2.1.4
i) List in ascending order id.

9DATABASE DESIGN AND USE
select shoporder.orderid, customer.custid, customer.cfirstname, customer.csurname from
shoporder, customer where shoporder.custid=customer.custid and customer.cgender='F'
order by shoporder.orderid;
ii) List in ascending customer id.
select shoporder.orderid, customer.custid, customer.cfirstname, customer.csurname from
shoporder, customer where shoporder.custid=customer.custid and customer.cgender='F'
order by customer.custid;
2.1.5
a. List in ascending product id.
select shoporder.orderid, customer.custid, customer.cfirstname, customer.csurname,
product.prodid, orderline.qtysold from shoporder, customer,product,orderline where
shoporder.custid=customer.custid and shoporder.custid=orderline.custid and
product.prodid=orderline.prodid order by product.prodid;

10DATABASE DESIGN AND USE
b. List in ascending customer id.
select shoporder.orderid, customer.custid, customer.cfirstname, customer.csurname,
product.prodid, orderline.qtysold from shoporder, customer,product,orderline
where shoporder.custid=customer.custid and shoporder.custid=orderline.custid and
product.prodid=orderline.prodid order by customer.custid;

11DATABASE DESIGN AND USE
c.List in ascending order id.
select shoporder.orderid, customer.custid, customer.cfirstname, customer.csurname,
product.prodid, orderline.qtysold from shoporder, customer,product,orderline
where shoporder.custid=customer.custid and shoporder.custid=orderline.custid and
product.prodid=orderline.prodid order by shoporder.orderid;

12DATABASE DESIGN AND USE
Script: Section 3
PK Error
insert into allocation values (35,102);
insert into allocation values (36,101);
PK Error
insert into qualitycheck values(31, 101, 3, 3);
insert into qualitycheck values(32, 102, 1, 1);

13DATABASE DESIGN AND USE
FK Error
insert into qualitycheck values(31, 101, 1, 2);
insert into qualitycheck values(34, 101, 1, 3);
insert into qualitycheck values(36, 103, 2, 2);
insert into qualitycheck values(31, 102, 3, 3);
insert into qualitycheck values(32, 101, 1, 1);
List all rows in additional Tables
Manager
select * from manager order by mgrid;
Allocation
select * from allocation order by prodid;

14DATABASE DESIGN AND USE
Qualitycheck
select * from qualitycheck order by prodid;

15DATABASE DESIGN AND USE
Script: Section 4
Queries
4.1 select orderline.orderid, orderline.prodid,product.prodname, product.currentprice,
orderline.salesprice, (product.currentprice- orderline.salesprice) as difference from orderline,
product where product.prodid=orderline.prodid and (product.currentprice-
orderline.salesprice)> (10*(product.currentprice/100)) order by difference;
4.2
a. in surname sequence:
select * from (select (csurname ||' '|| cfirstname) as name,'customer' as role from customer
union
select (msurname ||' '|| mfirstname)as name,'customer' as role from manager
union select (spsurname ||' '|| spfirstname) as name,'salesperson' as role from salesperson)
order by name;

16DATABASE DESIGN AND USE
b. firstname sequence:
select * from (select (cfirstname ||' '|| csurname) as name,'customer' as role from customer
union
select (mfirstname ||' '|| msurname)as name,'customer' as role from manager
union
select (spfirstname ||' '|| spsurname) as name,'salesperson' as role from salesperson) order by
name;

17DATABASE DESIGN AND USE
4.3 select shoporder.orderid, (NVL(shoporder.deliveryaddress, customer.billingaddress))as
address from shoporder,customer where customer.custid=shoporder.custid
union
select shoporder.orderid,(NVL(shoporder.deliveryaddress, customer.billingaddress)) as
address from customer, shoporder where customer.custid=shoporder.custid
order by orderid ;

18DATABASE DESIGN AND USE
4.4
Order id sequence:
select orderline.orderid, orderline.custid, (customer.cfirstname ||' '|| customer.csurname) as
customer,
orderline.prodid, product.prodname from orderline,customer,product where
customer.custid=orderline.custid
and product.prodid=orderline.prodid order by orderid;

19DATABASE DESIGN AND USE
Product id sequence
select orderline.orderid, orderline.custid, (customer.cfirstname ||' '|| customer.csurname) as
customer,
orderline.prodid, product.prodname from orderline,customer,product where
customer.custid=orderline.custid
and product.prodid=orderline.prodid order by prodid;

20DATABASE DESIGN AND USE
Customer id sequence
select orderline.orderid, orderline.custid, (customer.cfirstname ||' '|| customer.csurname) as
customer,
orderline.prodid, product.prodname from orderline,customer,product where
customer.custid=orderline.custid
and product.prodid=orderline.prodid order by custid;

21DATABASE DESIGN AND USE
4.5 select orderline.prodid, product.prodname, product.currentprice from product,orderline
where product.currentprice>(select avg(salesprice) from orderline) and
product.prodid=orderline.prodid order by product.currentprice;

22DATABASE DESIGN AND USE
Script: Section 5
Queries
5.1
Order id sequence
SELECT c.custid,c.csurname ,s.orderid
FROM customer c
LEFT OUTER JOIN shoporder s
ON c.custid = s.custid order by orderid;
Customer id sequence
SELECT c.custid,c.csurname ,s.orderid
FROM customer c

23DATABASE DESIGN AND USE
LEFT OUTER JOIN shoporder s
ON c.custid = s.custid order by custid;
5.2 select custid, cfirstname, count(orderid) from (SELECT c.custid,c.cfirstname, s.orderid
FROM customer c
LEFT OUTER JOIN shoporder s
ON c.custid = s.custid) group by (custid, cfirstname) order by custid;
5.3

24DATABASE DESIGN AND USE
Customer id sequence
select orderline.orderid, orderline.custid, customer.cfirstname,
customer.csurname,orderline.prodid, orderline.qtysold from customer,orderline,product
where product.prodid=orderline.prodid and orderline.custid=customer.custid order by custid;
Order id sequence
select orderline.orderid, orderline.custid, customer.cfirstname,
customer.csurname ,orderline.prodid, orderline.qtysold from customer,orderline,product
where product.prodid=orderline.prodid and orderline.custid=customer.custid order by
orderid;

25DATABASE DESIGN AND USE
Product id sequence
select orderline.orderid, orderline.custid, customer.cfirstname,
customer.csurname,orderline.prodid, orderline.qtysold from customer,orderline,product
where product.prodid=orderline.prodid and orderline.custid=customer.custid order by prodid;

26DATABASE DESIGN AND USE
5.4
Product id sequence
select prodid, prodname, sum(qtysold) as totalsold from (select product.prodid,
product.prodname, orderline.qtysold from orderline, product where
product.prodid=orderline.prodid) group by (prodid,prodname) order by prodid;
Total Sequence

27DATABASE DESIGN AND USE
select prodid, prodname, sum(qtysold) as totalsold from (select product.prodid,
product.prodname, orderline.qtysold from orderline, product
where product.prodid=orderline.prodid) group by (prodid,prodname) order by totalsold;
5.5 select * from (select prodid, prodname, sum(qtysold) as totalsold from (select
product.prodid, product.prodname, orderline.qtysold from orderline, product
where product.prodid=orderline.prodid) group by (prodid,prodname) order by totalsold)
where totalsold between 2 and 10;
5.6 select * from (select prodid, prodname, sum(qtysold) as totalsold from (select
product.prodid, product.prodname, orderline.qtysold from orderline, product
where product.prodid=orderline.prodid) group by (prodid,prodname) order by totalsold)
where totalsold>1 and prodname like '%Lamp%' or prodname like '%Table%';

28DATABASE DESIGN AND USE
5.7 select * from (select prodid, avg(score) as average from qualitycheck group by prodid)
where average>=2 order by average;
5.8 select * from (select prodid, avg(score) as average, count(orderid) from (SELECT
p.prodid ,o.orderid, q.score FROM product p inner JOIN orderline o
ON p.prodid = o.prodid inner join qualitycheck q
on
q.prodid=p.prodid) group by (prodid) order by prodid) where average>=2;
Conclusion
The development of the following given ERD is further developed and populated with
the given data according to the attributes. The diagram helped enough for understanding the

29DATABASE DESIGN AND USE
system and interconnection between the entities. All the queries have been successfully
applied and obtained. The screenshots of the output are also attached to their corresponding
queries. Hence, the queries have been generated the result successfully

30DATABASE DESIGN AND USE
Reference
Al-Masree, H.K., 2015. Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), pp.15-26.
Behal, A., Chen, Y. and He, B., International Business Machines Corp, 2019. Simplified
entity relationship model to access structure data. U.S. Patent Application 10/210,239.
Connolly, T. and Begg, C., 2015. Database systems. Pearson Education UK.
Dunn, C. and Nel, L.D., 2017. Entity Relationship Diagram Mapping.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Roberts, J., Stanfill, C.W. and Studer, S., Ab Initio Technology LLC, 2019. Mapping
attributes of keyed entities. U.S. Patent Application 10/191,862.
Roy-Hubara, N., Rokach, L., Shapira, B. and Shoval, P., 2017. Modeling graph database
schema. IT Professional, 19(6), pp.34-43.
Weske, M., 2019. Business Process Modelling Foundation. In Business Process
Management (pp. 71-122). Springer, Berlin, Heidelberg.