Database Design

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

DATABASE DESIGN
1
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

DATABASE DESIGN
2
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

DATABASE DESIGN
3
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);

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

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