Data Modeling & SQL Language

Data Modelling & SQL Language

Student ID Number
(Do not include student
name as anonymous
marking is implemented)
Programme Title
Module Title
Module Code (listed on
Moodle and in LTAFP)
Module Convenor
Coursework Title
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
2

Table of Contents
Introduction......................................................................................................................................5
Task 1...............................................................................................................................................6
Task 1.1........................................................................................................................................6
Task 1.2........................................................................................................................................8
Task 1.3......................................................................................................................................10
Task 2.............................................................................................................................................12
Task 2.1......................................................................................................................................12
Task 2.2......................................................................................................................................14
Task 3.............................................................................................................................................15
Task 3.1......................................................................................................................................15
Task 3.2......................................................................................................................................17
Task 3.3......................................................................................................................................18
Task 4.............................................................................................................................................19
Task 4.1......................................................................................................................................19
Task 4.2......................................................................................................................................23
Conclusion.....................................................................................................................................27
References......................................................................................................................................28
Appendix........................................................................................................................................29
Table of Figures
Figure 1: RDBMS............................................................................................................................7
Figure 2: Network Model................................................................................................................9
Figure 3: Relational Database........................................................................................................10
Figure 4: Conceptual Diagram for Case Scenario.........................................................................14
Figure 5: ERD Diagram.................................................................................................................17
Figure 6: UML Diagram................................................................................................................18
Figure 7 phpmyadmin ERD...........................................................................................................19
Figure 8: Buyers Table..................................................................................................................20
Figure 9: Purchased Cars...............................................................................................................20
Figure 10: Cars..............................................................................................................................20
Figure 11: SoldCars.......................................................................................................................21
Figure 12: Sellers...........................................................................................................................21
Figure 13: MethodOfPayment.......................................................................................................22
Figure 14: Foreign Key SQL Queries............................................................................................22
3

Figure 15: DDL Inserting Command.............................................................................................23
Figure 16: DDL inserting data into database.................................................................................23
Figure 17: List of Entities..............................................................................................................23
Figure 18: Buyers..........................................................................................................................24
Figure 19: PurchasedCars..............................................................................................................25
Figure 20: Cars..............................................................................................................................25
Figure 21: SoldCars.......................................................................................................................26
Figure 22: Sellers...........................................................................................................................26
Figure 23: MethodOfPayment.......................................................................................................27
4

Introduction
Data modeling is defined as performing operations on database. This assignment is containing
designing of database for given case scenario. In Task 1, it is having introduction & comparison
between different database models by distinguishing top down approach & bottom down
approach and RDBMS & File based System. At task 2, it is having desiging of entities for case
scenario with conceptual diagram also. In task 3, it is having normalization technique for getting
better results for data tables and records. At task 4, it is having SQL queries which DDL and
DML commands for a database named as data modeling with a screenshot of the outcome.
5

Task 1
Task 1.1
Database
The database is defined as a collection of data or information which are well maintained and well
designed. This should be accessed by using an electronic medium that is a computer system.
Difference between RDBMS & File Management System
Table 1: Difference between RDBMS & File Management System
Attribute RDBMS File-Based System
Repetition of
Data
Redundancy of information is
very inferior.
This file system is having more
redundancy of information.
Security It is having high security with
different highly configured
security aspects.
It has fewer security aspects and
constraints are having very less security.
Inconsistency
of Data
Lower High
Centralization Yes No
Storing Data Structured Data by illustrating
constraints with their
relationship which are
interconnected.
Unstructured File (Data Pediaa.Com.
2018).
Advantages of RDBMS
 It is having the main aim for decreasing the redundancy or repetition and replication of
information or data from the database.
 It is offering a good level of physical data which are independence with completely
integrated data or information.
6