Relational Database and its Working

1
Submission Coversheet (All Programmes)
Student ID Number
(Do not include student
name as anonymous
marking is implemented)
COR17454166
Programme Title BSc Computing Technologies (Day)
Module Title Data Modelling & SQL Language
Module Code (listed on
Moodle and in LTAFP) QAC020C155A
Module Convener Sharjeel Aslam
Coursework Title UShop Assignment
Academic Declaration:
Students are reminded that the electronic copy of their essay may be checked, at
any point during their degree, with Turnitin or other plagiarism detection software
for plagiarised material.
Word Count Date
Submitted
COR17454166

2
Contents
1. Conceptual model..................................................................................................3
1.1. Entities.............................................................................................................3
1.2. Crow’s Foot Notation.......................................................................................3
2. Logical Modelling...................................................................................................3
2.1. Normalization...................................................................................................3
2.2. ERD.................................................................................................................5
2.3. ERD model......................................................................................................6
3. Physical Model.......................................................................................................6
3.1. DDL.................................................................................................................7
3.2. DML...............................................................................................................12
4. References...........................................................................................................15
COR17454166

3
The report aims at describing the relational database and its working. Relational
database is referred to a location that is used for the purpose of storing and
retrieving the data. The database contains entities and attributes. Entities are
basically that holds the data and attributes are referred to the content that is stored.
Relational database management system can be stated as a collection of programs
that has the capability of creating, updating and providing interaction with the IT
teams. SQL is referred to as the most common used relational database
management system.
1. Conceptual model
Conceptual data modelling is the chart that contains semantics for system
data. This helps in storing data and attributes related to the requirements. Basically a
conceptual data model is the one with abstract level data model. Information is
referred to the platform that helps in ensuring better implementation of the
information. This ensures that other information are eliminated by the data model.
1.1. Entities
The major entities identified for the system are described below:
Customers, Address, Qualification, Employees, Orders, Quantity, Products, Price,
Salary
1.2. Crow’s Foot Notation
2. Logical Modelling
Logical data modelling is basically used as a link that are being used between
the gaps of ERD and physical data model. The logical relationships between the
data of an organization is represented in the logical data model. This helps in
representing data architecture and organization in a graphical way. The data are
stored within the database management system with the help of logical data
modelling.
COR17454166

4
2.1. Normalization
UNF 1NF 2NF 3NF Entities
CustomerID CustomerID
(PK)
CustomerID
(PK)
CustomerID
(PK)
Custom
er
CustomerName CustomerName CustomerName CustomerName
CustomerAddre
ss
CustomerAddre
ss
CustomerAddre
ss
AddressID (FK)
CustomerPhone
Number
CustomerPhone
Number
CustomerPhone
Number
CustomerPhone
Number
EmployeeID EmployeeID
(PK)
EmployeeID
(PK)
EmployeeID
(PK)
Employ
ee
EmployeeName EmployeeName EmployeeName EmployeeName
EmployeeAddre
ss
EmployeeAddre
ss
EmployeeAddre
ss
AddressID (FK)
EmployeeSalary EmployeeSalary EmployeeSalary EmployeeSalary
EmployeeJobTit
le
EmployeeJobTit
le
EmployeeJobTit
le
EmployeeJobTit
le
EmployeeQualifi
cation
EmployeeQualifi
cation
EmployeeQualifi
cation
QualificationID
(FK)
AddressID (PK) AddressID (PK) Address
AddressName AddressName
QualificationID
(PK)
QualificationID
(PK)
Qulaific
ation
QualificationNa
me
QualificationNa
me
OrderID OrderID (PK) OrderID (PK) OrderID (PK) Orders
OrderDate OrderDate OrderDate OrderDate
OrderQuantity OrderQuantity OrderQuantity OrderQuantity
ProductsOrdere
d
ProductsOrdere
d
ProductsOrdere
d
ProductsID (FK)
EmployeeName EmployeeID
(FK)
CustomerName CustomerID
(FK)
ProductID ProductID (PK) ProductID (PK) ProductID (PK) Product
sProductName ProductName ProductName ProductName
ProductPrice ProductPrice ProductPrice ProductPrice
ProductQuantity ProductQuantity ProductQuantity ProductQuantity
COR17454166