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

DATABASE DESIGN AND USE1
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

DATABASE DESIGN AND USE2
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.

DATABASE DESIGN AND USE3
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');

DATABASE DESIGN AND USE4
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

DATABASE DESIGN AND USE5
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;

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

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