BSc Computing: Data Modelling & SQL Language UShop Assignment

This report details the creation of a relational database, covering conceptual, logical, and physical models. The conceptual model identifies key entities like Customers, Addresses, and Products. The logical model employs normalization (UNF, 1NF, 2NF, 3NF) and ERD to represent data relationships. The physical model includes DDL scripts for table creation and constraints, along with DML statements for data manipulation. The report includes crow’s foot notation, ERD diagrams, and SQL code snippets to illustrate the database design and implementation process. References to external sources and lecture materials are also provided.

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

5
2.2. ERD
COR17454166

6
2.3. ERD model
3. Physical Model
The physical model is used for the purpose of representing the way data is
stored within the computer memory. This also describes how data is stored within
the computer memory and how this data are retrieved from the memory for
performing the functions. Every table and the constraints related to this are explained
in physical data model. In addition to this physical data model also defines all the
logical database components related to the databases. The services provided by the
existing database are also explained at the time of building a database.
COR17454166

7
3.1. DDL
COR17454166

8
COR17454166

9
COR17454166

10
-- Indexes for table `address`
--
ALTER TABLE `address`
ADD PRIMARY KEY (`AddressID`);
--
-- Indexes for table `customer`
--
ALTER TABLE `customer`
ADD PRIMARY KEY (`CustomerID`),
ADD KEY `AddressID` (`AddressID`);
--
-- Indexes for table `employee`
--
ALTER TABLE `employee`
ADD PRIMARY KEY (`EmployeeID`),
ADD KEY `AddressID` (`AddressID`),
ADD KEY `QualificationID` (`QualificationID`);
--
-- Indexes for table `order`
--
ALTER TABLE `order`
COR17454166

11
ADD PRIMARY KEY (`OrderID`),
ADD KEY `CustomerID` (`CustomerID`),
ADD KEY `EmployeeID` (`EmployeeID`),
ADD KEY `ProductID` (`ProductID`);
--
-- Indexes for table `product`
--
ALTER TABLE `product`
ADD PRIMARY KEY (`ProductID`);
--
-- Indexes for table `qualification`
--
ALTER TABLE `qualification`
ADD PRIMARY KEY (`QualificationID`);
--
-- Constraints for dumped tables
--
--
-- Constraints for table `customer`
--
ALTER TABLE `customer`
ADD CONSTRAINT `customer_ibfk_1` FOREIGN KEY (`AddressID`)
REFERENCES `address` (`AddressID`);
--
-- Constraints for table `employee`
--
ALTER TABLE `employee`
COR17454166

12
ADD CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`AddressID`)
REFERENCES `address` (`AddressID`),
ADD CONSTRAINT `employee_ibfk_2` FOREIGN KEY (`QualificationID`)
REFERENCES `qualification` (`QualificationID`);
--
-- Constraints for table `order`
--
ALTER TABLE `order`
ADD CONSTRAINT `order_ibfk_1` FOREIGN KEY (`CustomerID`) REFERENCES
`customer` (`CustomerID`),
ADD CONSTRAINT `order_ibfk_2` FOREIGN KEY (`EmployeeID`) REFERENCES
`employee` (`EmployeeID`),
ADD CONSTRAINT `order_ibfk_3` FOREIGN KEY (`ProductID`) REFERENCES
`product` (`ProductID`);
3.2. DML
COR17454166

13
COR17454166

14
COR17454166

15
4. References
Forta, Ben (2012) SQL Crash Course (12 th Edition)SAMS. ISBN: 0672327120
(Accessed: 08/04/2018).
Howe, David(2001) Data Analysis for Database Design (3rd Edition) Butterworth-
Heinemann. Available
at:https://www.vlebooks.com/vleweb/Product/Index/33899 (Accessed:
01/07/2018)
https://en.wikipedia.org (Accessed:01/07/2018)
https://www.tutorialspoint.com/sql/sql-rdbms-concepts.htm (Accessed: 02/07/2018)
Lecture slides presented on Moodle Page. Available at:
https://partnerships.moodle.roehampton.ac.uk(Accessed: 01/07/2018)
Ullman, Jeffrey D (2013) A first course in database systems , Pearson, Available at
https://www.vlebooks.com/vleweb/Product/Index/437632 (Accessed:
01/07/2018)
COR17454166