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