Data Modelling: Conceptual, Logical and Physical Models

This article discusses the importance of database design and normalization in data modelling. It covers conceptual, logical and physical models with examples and SQL scripts for creating tables. The article also explains the benefits of normalization and denormalization in database design.

DATA MODELLING 1
Data Modelling
Student Name
Institutional Affiliation

DATA MODELLING 2
Table of Contents
Introduction.................................................................................................................................................3
Task 1: Conceptual Model...........................................................................................................................3
Entities and Attributes.............................................................................................................................3
Conceptual Data Model...........................................................................................................................4
Task 2: Logical Model.................................................................................................................................5
Database Normalization..........................................................................................................................5
UNF......................................................................................................................................................5
1NF......................................................................................................................................................6
2NF......................................................................................................................................................6
3NF......................................................................................................................................................7
ER Diagram.............................................................................................................................................9
Mapping ERD........................................................................................................................................10
Task 3: physical Model.............................................................................................................................10
SQL Scripts for Creating Tables............................................................................................................10
Insert Statements...................................................................................................................................12
Conclusion.................................................................................................................................................15
Reference List...........................................................................................................................................16

DATA MODELLING 3
Introduction
Database design is a very crucial process and requires in-depth analysis of the business
requirements. There are several factors that should be focused on while designing a database
such as data integrity, security, speed of access, data redundancy among other factors (Sul, Yeom
and Jung, 2018). The database holds crucial information that the businesses use to execute its
daily operations and in making vital decisions. Therefore, the database should be kept secured at
all time to ensure that there is no unauthorized access or modification of any kind (Galatescu,
Greceanu and Nicolau, 2011).
Task 1: Conceptual Model
Entities and Attributes
Customer
-CustomerID
-CustomerName
-CustomerAddress
-PhoneNumber
Employee
-EmployeeID
-EmployeeName
-EmployeeAddress
-EmployeeJobTitle
-EmployeeQualification
Order
-OrderID
-OrderDate
-OrderQuantity
-Products

DATA MODELLING 4
Product
-ProductID
-ProductName
-ProductPrice
-ProductQuantity
The assumptions made the address and the name of the business have been prefixed with
Customer in customer table to maintain unique name attributes. The total price that should be
paid by the customer is calculated by multiplying product price and quantity.
Conceptual Data Model
Figure 1: Conceptual Data Model
Assumptions
The existence of an entity is independent of other entities. Child entity primary key is not
contained in parent entity. the existence of child entity is dependent on the parent entity.

DATA MODELLING 5
Task 2: Logical Model
Database Normalization
Normalization is a database technique of reducing data redundancies and is used when
redesigning and designing a database (Leong, 2011). It is a set of guidelines and processes that is
used to that is reduce data redundancy when designing a database. Normal forms are the actual
normalization guidelines. A database that is not normalized increases disk usage, reduces
database efficiency, affects data integrity, security threats, and reduces speed of processing
queries.
UNF
This type of normal form contains all the data in one table and is repeated in other subsequent
tables.

DATA MODELLING 6
Figure 2: UNF
1NF
The goal of 1NF is to break down the base data into tables or logical units. After designing each
table, a primary key is assigned to all of them.
Figure 3: 1NF
First normal form has been achieved by breaking the data down into logical units containing
related information as shown in figure 3 above. Each table is assigned a primary key to ensure
that there is no repetition in any of the group (Demba, 2013). We now have smaller and more
manageable tables instead of one big table.
2NF
The goal of the second normal form is to further extract data that is partially dependent on the
primary key to create another table. Figure 4 below, shows 2NF.

DATA MODELLING 7
Figure 4: 2NF
First normal form is used to derive second normal form according to the figure above by further
breaking down the tables in 1NF to more specific logical units.
3NF
The objective of third normal form is to is to extract data that does not depend on the primary
key in the tables as shown in figure 5.

DATA MODELLING 8
Figure 5: 3NF
In this case we took the employee table and further broke it down to get two more tables one
containing position description and the other containing and another containing payment
information. In this case, position details do not need to reside in employee table. ‘
Normalization offers many advantages to a database including minimized data redundancy, more
flexible design of database, greater organization of database, better security handling in the
database, and improve data consistency within the database (Duggal, Srivastav and Kaur, 2014).

DATA MODELLING 9
ER Diagram
Figure 6: UShop ERD
Denormalization is a technique of optimizing the database when redundant data to some tables.
This will help the business to evade costly process of joins in relational database. However, this
does not mean that normalization is not important as it is and denormalization is only required in
specific cases. Denormalization is normally carried after normalization has been done. The
assumption made with denormalization is that the users are comfortable with putting some more
effort while updating the database and few redundancies (Hutcheson, 2012). Some of the
benefits of denormalization include increased speed of data retrieval because of reduced fewer
joins and more simpler queries for retrieving data. However, it denormalization has some
setbacks including inserts and updates are costlier, increases data inconsistency, more storage is

DATA MODELLING 10
required is needed because of data redundancy, and makes inserts and updates more difficult to
write.
In case Ushop System requires expansion/ scalability, denormalized and normalized database
elements can be employed. Denormalization is carried out to free relations collection from
unnecessary updates, inserts, and deletion dependencies (Sharmila and Anusya, 2012). It also
minimizes the need to restructure capturing of relationships when new data types are introduced
into the database, ensure that users are informed well on relational model, and make neutral
relations to query statistics.
Mapping ERD
Task 3: physical Model
SQL Scripts for Creating Tables
CREATE TABLE `customers` (
`CustomerID` int(255) NOT NULL,
`CustomerName` varchar(123) NOT NULL,
`Address` varchar(255) NOT NULL,
`City` varchar(255) NOT NULL,
`Region` varchar(255) NOT NULL,
`PostalCode` varchar(255) NOT NULL,
`Country` varchar(255) NOT NULL,
`Phone` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

DATA MODELLING 11
CREATE TABLE `employees` (
`EmployeeID` int(255) NOT NULL,
`LastName` varchar(123) NOT NULL,
`FirstName` varchar(123) NOT NULL,
`Title` varchar(123) NOT NULL,
`Titleofcourtesy` varchar(123) NOT NULL,
`HireDate` datetime NOT NULL,
`Address` varchar(123) NOT NULL,
`City` varchar(123) NOT NULL,
`Region` varchar(123) NOT NULL,
`PostalCode` varchar(123) NOT NULL,
`Country` varchar(123) NOT NULL,
`Reportsto` varchar(255) NOT NULL,
`Photo` image NOT NULL,
`Qualification` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `order` (
`OrderID` int(255) NOT NULL,
`CustomerID` int(255) NOT NULL,
`EmployeeID` int(255) NOT NULL,
`OrderDate` datetime,
`RequiredDate` datetime,

DATA MODELLING 12
`ShippedDate` datetime,
`OrderQuantity` varchar(255) NOT NULL,
`Products` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `product` (
`ProductID` int(255) NOT NULL,
`ProductName` varchar(123) NOT NULL,
`ProductPrice` varchar(255) NOT NULL,
`ProductQuantity` int(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
ALTER TABLE `customer`
ADD PRIMARY KEY (`CustomerID`);
ALTER TABLE `employee`
ADD PRIMARY KEY (`EmployeeID`);
ALTER TABLE `order`
ADD PRIMARY KEY (`OrderID`);
ALTER TABLE `product`
ADD PRIMARY KEY (`ProductID`);
Insert Statements
INSERT INTO `customers` (`CustomerId`, `CustomerName`, `Address`, `City`, `Region`,
`PostalCode`, `Country`, `Phone`) VALUES

DATA MODELLING 13
(1, 'Abdirahman Ali Abdi', '001-Riverside', 'London', 'East London', '001', 'UK', '[phone]'),
(2, 'Janan Ali British', '002-Kingsway', 'Manchester', 'Central', '002', 'UK', '+2185255'),
(3, 'Edwin King', '003-Corner', 'Liverpool', 'South', '003', 'UK', '[phone]'),
(4, 'Joe Doe', '004-ChurchRoad', 'Everton', 'North', '004', 'UK', '[phone]'),
(5, 'Ali Abdi', '005-Kicks', 'Derby', 'West', '005', 'UK', '[phone]');
Table 1: Customer Table
INSERT INTO `Employees` (`EmployeeId`, `LastName`,`FirstName`,
`Title`,`Titleofcourtesy`,`HireDate`,`Address`, `City`, `Region`, `PostalCode`, `Country`,
`Photo`,`ReportsTo`,`Qualification`) VALUES
(1, 'Doe', 'John', 'Chef', 'Mr.', '2014-08-17', '150 Street1',
'London','Central','001','UK','85296374','ChiefChef','Diploma'),
(2, 'Queen', 'Mary', 'Sales', 'Miss', '2015-01-19', '150 Street1',
'London','Central','001','UK','85296374','Sales Manager','Degree'),
(3, 'Toet', 'King', 'Accountant', 'Mr.', '2014-05-20', '150 Street1',
'London','Central','001','UK','85296374','Chief Accountant','Degree'),
(4, 'Sang', 'Ed', 'Chef', 'Mr.', '2014-10-13', '150 Street1',
'London','Central','001','UK','85296374','ChiefChef','Diploma'),
(5, 'Michaels', 'June', 'Chef', 'Miss', '2016-07-19', '150 Street1',
'London','Central','001','UK','85296374','ChiefChef','Diploma');

DATA MODELLING 14
Table 2: Employee Table
INSERT INTO `order` (`OrderId`, `CustomerID`,`EmployeeID`,`OrderDate`,`RequiredDate`,
`ShippedDate`, `OrderQuantity`, `Products`) VALUES
(1, '2', '4', '2018-09-11', '2018-09-11', '2018-09-11', '3', 'Pizza'),
(1, '1', '5', '2018-09-10', '2018-09-10', '2018-09-10', '1', 'Phone'),
(1, '4', '5', '2018-09-09', '2018-09-09', '2018-09-09', '1', 'Laptop'),
(1, '3', '1', '2018-09-11', '2018-09-11', '2018-09-11', '2', 'Burger'),
(1, '5', '3', '2018-09-11', '2018-09-11', '2018-09-11', '10', 'Pillow');
Table 3: Order Table
INSERT INTO `product` (`ProductID`, `ProductName`,`ProductPrice`,`ProductQuantity`)
VALUES
(1, 'Computers', '500', '200'),
(2, 'Phones', '140', '157'),
(3, 'Pizza', '35', '300'),
(4, 'Burger', '20', '274'),
(5, 'Pillows', '35', '91');

DATA MODELLING 15
Table 4: Product Table
Conclusion
The database holds crucial information that the businesses use to execute its daily operations and
in making vital decisions as such it should be kept secured at all times. Normalization is a
database technique of reducing data redundancies and is used when redesigning and designing a
database. It is a set of guidelines and processes that is used to that is reduce data redundancy
when designing a database. Normalization offers many advantages to a database including
minimized data redundancy, more flexible design of database, greater organization of database,
better security handling in the database, and improve data consistency within the database.
Denormalization is normally carried after normalization has been done. The assumption made
with denormalization is that the users are comfortable with putting some more effort while
updating the database and few redundancies.

DATA MODELLING 16
Reference List
Demba, M. (2013). Algorithm for Relational Database Normalization Up to 3NF. International
Journal of Database Management Systems, 5(3), pp.39-51.
Duggal, K., Srivastav, A. and Kaur, S. (2014). Gamified Approach to Database
Normalization. International Journal of Computer Applications, 93(4), pp.47-53.
Galatescu, A., Greceanu, T. and Nicolau, D. (2011). Using a semantically enhanced database for
business service and process modelling and integration. International Journal of Intelligent
Information and Database Systems, 5(5), p.468.
Hutcheson, G. (2012). Missing Data: data replacement and imputation. Journal of Modelling in
Management, 7(2).
Leong, L. (2011). The Issues And Solutions Of Integrating DBMS To A Multi-DBMS. Review
of Business Information Systems (RBIS), 7(2), p.73.
Sharmila, S. and Anusya, A. (2012). Database Design for Scheduling System using
Normalization and SQL. Paripex - Indian Journal Of Research, 3(7), pp.1-4.
Sul, W., Yeom, H. and Jung, H. (2018). Towards Sustainable High-Performance Transaction
Processing in Cloud-based DBMS. Cluster Computing.