HS2021 Individual Assignment: Database Design and Use Solution

This document presents a comprehensive solution to a database design and use assignment. It begins with an introduction to database management systems and ERDs, explaining their roles in database creation and management. The solution includes an analysis of a provided ERD, detailing entities, attributes, and relationships. The core of the assignment involves creating, populating, and manipulating a database using SQL scripts. These scripts cover various sections, including constraint checks, data insertion, and a wide range of SQL queries. The queries address different aspects of data retrieval, including counting orders, filtering by gender, sorting results, and joining multiple tables to extract specific information. Additional tables are also included with the relevant scripts and queries. The solution concludes with a summary of the work and a reference list.

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;