QAC020C155H Data Modelling And SQL Language

[NAME][STUDENT NUMBER]
DATA MODELLING & SQL
LANGUAGEDATABASE DESIGN AND REPORT

Table of Contents
Introduction................................................................................................................................2
Understanding the Database.......................................................................................................2
Conceptual modelling................................................................................................................3
Possible entities..............................................................................................................................................3
ER Model..........................................................................................................................................4
Logical model............................................................................................................................4
Normalization...................................................................................................................................4
Enhanced ERD..................................................................................................................................7
Relational schema.............................................................................................................................7
Physical model...........................................................................................................................8
Creation script...................................................................................................................................8
ER diagram in phpMyAdmin..........................................................................................................11
Table structure................................................................................................................................12
Insertion script................................................................................................................................12
Conclusion...............................................................................................................................16
References................................................................................................................................17
Figure 1: Conceptual data model...............................................................................................5
Figure 2: Normalization process................................................................................................7
Figure 3: Enhanced ERD...........................................................................................................8
Figure 4: Table structure in PhpMyAdmin..............................................................................12
1

Introduction
UShop is an online retail company and the business has grown immensely over the years. In
order to manage the data and information sets, the company is looking to Relational Database
Management System (RDBMS). The database needs to be developed for the organization and
there are certain parameters that must be met. The database must store the information of the
customers, products, employees, and orders associated with the company. There are specific
attributes associated with the four major categories of information. There are also certain
conditions that need to be met while developing the database. For instance, an employee of
UShop can server multiple orders at the same time while one order shall be served by a single
employee only. Similarly, an order may comprise of one or more products and one product
can be ordered in multiple orders. The development team will be able to adequately develop
and implement the database with the aid of the database details, such as normalization, entity
relationship diagrams, details of the attribute, and likewise.
Understanding the Database
A database is a container in which is used in order to store and organize the information sets.
There are manipulations that can be made to the database as per the user role that is assigned.
In order to implement all of these capabilities, it is required to implement a specialized engine
called a Database Management System (DBMS). The database engine is composed of the
software, hardware, and the information sets. There are different types of database
management systems that have been designed. File management system is one of the types of
database management systems in which file-based processing is carried out.
The database management system that is to be implemented for UShop is Relational
Database Management System (RDBMS). It is the database that is designed on the basis of
the relational model and was developed by E.F. Codd in the year 1970. The basic format
which may be used to explain the database is the tables that are made up of rows and
columns. The software development process can be broadly categorized and carried out
through two major approaches. One of these approaches is to break the problem in smaller
pieces and add further details to each sub-level (Yamamoto and Koizumi, 2013). It is termed
as the top-down or decomposition approach. The second approach is to develop the structure
using a defined coding process and to carry out the testing of each of these steps. It is termed
as the bottom-up approach. Code reusability is one of the prime benefits that are provided
with the use of this approach.
2

The implementation of RDBMS will offer several advantages to the organization, such as:
The table format that is used and supported by the RDBMSs is easy to use and
understand. The users of the database will be the employees of UShop and they will
get hold of the same in no time. It will lead to the enhancement of their respective
productivity and efficiency levels (Seo and Kim, 2011).
The database will allow multiple users to access the database at the same time. The
database will have transaction management facility implemented in it along with the
build-in locking mechanism. This will ensure that the reliability of the information is
maintained at every instance and there are no performance issues during simultaneous
database access by more than one user.
There will be several users that will be provided with the access to the database.
However, the access rights and permissions will vary from one user to the other. For
example, the rights of an operational staff members and the director of UShop will be
different. RDBMS will allow the database administrator to easily grant and control
the data access which will lead to enhanced security and protection of the data sets
(Hussain and Khan, 2014).
The database administrator will be able to easily handle and maintain the database
with the aid of automated maintenance tools. The backing up of the data sets will also
be done with ease which will enhance the security of the data sets.
Generic language as Structured Query Language (SQL) will be supported by the
database. The syntax used in the database will be simple and it will allow easy
execution of data handling and data storage operations.
Conceptual modelling
Possible entities
On the basis of given scenario, following entities are identified:
CITY
CUSTOMER
EMPLOYEE
ORDER
ORDER_PRODUCT
POTCODE
PRODUCT
QUALIFICATION
3