HS2021 Database Design and SQL Queries Individual Assignment

This document presents a comprehensive solution to a database design assignment, addressing the creation, population, and manipulation of a relational database. The assignment begins with an introduction to database concepts, emphasizing the role of Entity Relationship Diagrams (ERDs) in structuring data and establishing relationships between entities. Section 1 focuses on interpreting and describing the relationships depicted in a provided ERD. Section 2 delves into SQL queries, demonstrating how to retrieve data, perform calculations, and filter results based on specific criteria. Section 3 highlights potential errors encountered during database creation, such as primary key and foreign key violations, and provides solutions. Section 4 presents more complex SQL queries involving joins, subqueries, and data aggregation, showcasing advanced database manipulation techniques. Section 5 continues with advanced queries using left outer joins, and aggregate functions. Finally, the report concludes with a summary of the key concepts and techniques covered in the assignment. The solution includes the creation of tables, inserting data, and executing a variety of SQL queries to extract, filter, and transform the data, with the outputs of the queries included.

Running head: DATABASE DESIGN
Database Design
Name of the Student
Name of the University
Author Note

1
DATABASE DESIGN
Table of Contents
Introduction................................................................................................................................2
Section 1.....................................................................................................................................2
Section 2.....................................................................................................................................7
Queries...................................................................................................................................7
Section 3...................................................................................................................................11
Section 4...................................................................................................................................13
Section 5...................................................................................................................................21
Queries.................................................................................................................................21
Conclusion................................................................................................................................28
References................................................................................................................................29

2
DATABASE DESIGN
Introduction
A database helps a lot in running a business system by maintaining the information of
the whole system. The database is a collection of various data which is interrelated to each
other (Schloming 2013). The data are store into the tables called entities and every table is
consist of attributes or columns. These stored data is later retrieved, deleted or updates using
the database management system (Harger, Jones and Seabolt 2013). For developing a
database an Entity Relationship Diagram is generally used. An ERD is basically diagram that
shows the relationships between the entities of the database (Thalheim 2013). Later the
Database is developed according to the ERD to organise the relationships, cardinality,
integrity and optionality of the data (Kruse and Wells 2016). This report discusses with the
provided ERD and its description. In later section of the report a database is created,
populated and manipulated using SQL queries.
Section 1
Relationship
ERD is a data modelling technique to develop a software system using some diagrams
and notations (Coronel and Morris 2016). The ERD provides the database a structure,
abstraction and optimization. The interesting fact of the ERD is that it can be directly
transformed into a Database schema (Al-Masree 2015). As it is known that schema or ERD is
consist of mainly attributes, entities and relationship between them. For linking tables, the
concept of primary key and foreign key is required where the foreign key of any table
references the column value of the parent table where the column is defined as primary key
(Roberts, Stanfill and Studer 2019). In this ERD, the relationships can be seen through the
keys and concept of strong entity is also seen. During the creation of the table, it is
considered to assign the primary and foreign keys to the respective tables appropriately. Also

3
DATABASE DESIGN
the use of constraints (check, foreign key) is also introduced (Lano and Kolahdouz-Rahimi
2013). The relationship between the entities can be described as follows:
 One customer can have zero or many shoporders.
 Each shoporder is associated with one and only one customer.
 Each shoporder is assigned by one and only one salesperson.
 One sales person can place zero or many shoporders.
 One order in shoporder is related to zero or many orderlines.
 One orderline is associated with one and only one shoporder.
 Each orderline is associated with one and only one product.
 One product can be associated with zero or more orderlines.
 One product can be allocated in zero or many times.
 One allocation is related to one and only one product.
 One manager can handle zero or more allocations.
 One allocation is associated with one and only one manager.
 One allocation can have zero or more qualitycheck.
 One quality check is associated with one and only one allocation.
Check constraint error Customer Table
insert into customer values (8, 'Milton','Hastings','X','3 Blue St, Kew');
Check constraint error Product Table
insert into product values (40, 'Cupboard',-99);
insert into product values (41, 'Bookcase',8765);

4
DATABASE DESIGN
FK Error Shoporder Table
insert into shoporder values(66,null,5,29);
insert into shoporder values(67,null,9,26);
FK errors Orderline Table
insert into orderline values(2,2,200,49,40,1,23);
insert into orderline values(10,10,175,52,36,1,23);
List rows in all Tables
select * from product;
select * from salesperson;

5
DATABASE DESIGN
select * from customer;
select * from shoporder;

6
DATABASE DESIGN
select * from orderline;

7
DATABASE DESIGN
Section 2
Queries
1. select count(orderid) as totalorder from shoporder;
2. select count(shoporder.orderid) as femalesorders from shoporder, customer where
shoporder.custid=customer.custid and customer.cgender='F';
3. select count(shoporder.orderid) as totalorders, customer.cgender from shoporder,
customer where shoporder.custid=customer.custid group by customer.cgender;
4.
a. select shoporder.orderid, customer.custid, customer.cfirstname,
customer.csurname from customer,shoporder where
customer.custid=shoporder.custid and customer.cgender='F' order by
customer.custid;

8
DATABASE DESIGN
b. select shoporder.orderid, customer.custid, customer.cfirstname,
customer.csurname from customer,shoporder where
customer.custid=shoporder.custid and customer.cgender='F' order by
shoporder.orderid;
5.
a. 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;

9
DATABASE DESIGN
b. 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;

10
DATABASE DESIGN
c. 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;

11
DATABASE DESIGN
Section 3
PK Error Allocation Table
insert into allocation values (35,102);
insert into allocation values (36,101);
PK Error QualityCheck Table
insert into qualitycheck values(31, 101, 3, 3);
insert into qualitycheck values(32, 102, 1, 1);

12
DATABASE DESIGN
FK Error QualityCheck Table
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
select * from manager;
select * from allocation;

13
DATABASE DESIGN
select * from qualitycheck;
Section 4
4.1 select orderline.orderid, orderline.prodid,product.prodname, product.currentprice,
orderline.salesprice, (product.currentprice- orderline.salesprice)

14
DATABASE DESIGN
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. 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;

15
DATABASE DESIGN
b. 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;

16
DATABASE DESIGN
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 ;

17
DATABASE DESIGN
4.4
a. select orderline.orderid, orderline.custid, (customer.cfirstname ||' '||
customer.csurname) as customername, orderline.prodid, product.prodname from
orderline,customer,product where customer.custid=orderline.custid and
product.prodid=orderline.prodid order by custid;

18
DATABASE DESIGN
b. select orderline.orderid, orderline.custid, (customer.cfirstname ||' '||
customer.csurname) as customername,orderline.prodid, product.prodname from
orderline,customer,product where customer.custid=orderline.custid and
product.prodid=orderline.prodid order by prodid;

19
DATABASE DESIGN
c. select orderline.orderid, orderline.custid, (customer.cfirstname ||' '||
customer.csurname) as customername,orderline.prodid, product.prodname from
orderline,customer,product where customer.custid=orderline.custid and
product.prodid=orderline.prodid order by orderid;

20
DATABASE DESIGN
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;

21
DATABASE DESIGN
Section 5
Queries
5.1
a. select c.custid,c.csurname ,s.orderid from customer c left outer join shoporder s ON
c.custid = s.custid order by custid;
b. select c.custid,c.csurname ,s.orderid from customer c left outer join shoporder s ON
c.custid = s.custid order by orderid;

22
DATABASE DESIGN
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
a. select orderline.orderid, orderline.custid, customer.cfirstname, customer.csurname,
orderline.prodid, orderline.qtysold from customer,orderline,product where

23
DATABASE DESIGN
product.prodid=orderline.prodid and orderline.custid=customer.custid order by
orderid;
b. 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;

24
DATABASE DESIGN
c. 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;

25
DATABASE DESIGN
5.4
a. 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;
b. select prodid, prodname, sum(qtysold) as totalsold from (select product.prodid,

26
DATABASE DESIGN
product.prodname, orderline.qtysold from orderline, product where
product.prodid=orderline.prodid) group by (prodid,prodname) order by prodid;
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%';

27
DATABASE DESIGN
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;

28
DATABASE DESIGN
Conclusion
It is known that ERD is the first phase for executing the database in real where
according to the ERD the schema is developed generally. In this report, the given ERD is
transformed into the database structure successfully. The ERD helped in understanding the
relationship between the entities. Later in the report, the queries have been successfully
applied and the output has been attached as screenshots. The generated output is cross
checked with the tables manually to confirm that the generated output is real and accurate.

29
DATABASE DESIGN
References
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.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, &
management. Cengage Learning.
Harger, D.S., Jones, E. and Seabolt, E., International Business Machines Corp,
2013. Managing entities within a database. U.S. Patent 8,370,355.
Kruse, S.L.K. and Wells, M.G., 2016. Optionality of ERD Relationships: Project for the
Introduction to Database Course. In Proceedings of the EDSIG Conference ISSN (Vol. 2473,
p. 3857).
Lano, K. and Kolahdouz-Rahimi, S., 2013. Constraint-based specification of model
transformations. Journal of Systems and Software, 86(2), pp.412-436.
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.
Schloming, R.H., Red Hat Inc, 2013. System and method for retrieving data from a relational
database management system. U.S. Patent 8,612,468.
Thalheim, B., 2013. Entity-relationship modeling: foundations of database technology.
Springer Science & Business Media.