Data Modelling: Conceptual, Logical and Physical Models

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.