Data Modeling & SQL Language Project: Database Design & Queries

This project focuses on data modeling and SQL language, encompassing the design and implementation of a database for a specified case scenario. Task 1 introduces and compares different database models, contrasting top-down and bottom-up approaches, as well as RDBMS and file-based systems. Task 2 involves designing entities for the case scenario, accompanied by a conceptual diagram. Task 3 employs normalization techniques to optimize data tables and records. Task 4 demonstrates SQL queries, including DDL and DML commands, for a database named 'data modeling,' complete with screenshots of the results. The project culminates in a successfully designed database, showcasing practical application of data modeling principles and SQL proficiency.

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

 It offers a multipurpose platform such that multiple users can visit and access their data
or information.
Disadvantage of RDBMS
 It requires more human technical effort, the meaning is that technically skilled or expert
is required.
 The complexity of hardware is there & expensive also.
Limitation of RDMS
 High Complexity
 Limits are having when you use data size
 Highly cost than file-based system
Figure 1: RDBMS
Advantages of File System
7

 We can easily take the back up of information or data at a very high speed of backing up.
 It very eases for making changes into the database or files with a feature of remote
accessibility.
 The efficiency of receiving information or data is very high.
Disadvantages of File System
 There is a redundancy of information or data is there with the inconsistency of it.
 It is having widely facing problems that are atomicity and accessing data continuously &
concurrently.
 Security & integration of data or information is also one of the major issues in this file
based system.
Limitation of File System
 Less Security Aspects
 Less Redundancy
 Less Inconsistency
Task 1.2
Advantages of Hierarchical System
 It very easy to handles a large amount of data.
 It has integrated information with completely independent information.
 Data is secured and simply modified information is there.
Disadvantages of Hierarchical System
 It is hard for implementation or applying this database and managing database.
 It is lacking by some industry standards and having complexity related to application
programming interface (Myreadingroom.co.in., 2019).
Advantages of Network Model
 It is very easy for designing this database as same as the hierarchical database.
 Accessibility of the database is there.
8

 It has more capability for handling & managing information or data with more than one
relationship between the entities.
Disadvantages of Network Model
 This model is having a large amount of complexity because it is having pointers for
managing data by pointers.
 CRUD operation required a big amount of shuffling of pointers (Arora K., 2014).
Figure 2: Network Model
Advantages of the Relational Model
 It is used for decreasing the redundancy or repetition and replication of information or
data from the database.
 It offers a multipurpose platform such that multiple users can visit and access their data
or information.
Disadvantages of the Relational Model
 It requires technical skilled or expert is required.
 The complexity of hardware is there & expensive also.
9

Figure 3: Relational Database
Task 1.3
Top-down Approach
The top-down approach is also known as analysis with designing. In this, we have divided
entities into sub entities until and unless the requirement is not completed.
Strengths
 By using this approach, it will provide less amount of completion time for a project or
task.
 It has designing techniques which are faster than others.
Weakness
 It is required that knowledge or technical person can apply this concept.
 Another major issue is that participation of task or user is very less.
Bottom down Approach
It is also known as the process of synthesis with designing. In this, first of all, information is
gathered and after that construction process take place with entities. After the completion of this,
the relationship takes place.
Strengths
 Giving straight & direct solution and answers with accuracy.
10

 It very eases for using this.
 For this, no highly or advance technical person is required.
Weakness
 Cost may be very high.
 Lower efficiency with less effectiveness due to less control of management (Andrew
Odendaal, 2017).
I would prefer a top-down approach for this case scenario for providing better databases
management with a good amount of efficiency and effectiveness of database managing
operation.
11

Task 2
Task 2.1
At below, I have mentioned entity name with their attribute as well as their data type
respectively. For this, case scenario I have used 6 entities and these are:
 Buyers
 PurchasedCars
 Cars
 SoldCars
 Sellers
 MethodOfPayment
Buyers
Table 2: Buyers
S.N. Name of Attribute for Entity Data Type of Attribute Constraints (if any)
1. BuyerId Integer Primary Key
2. BuyerName Varchar NOT NULL
3. BuyerContact Varchar NOT NULL
4. BuyerResidency Varchar NOT NULL
PurchasedCars
Table 3: PurchasedCars
S.N. Name of Attribute for Entity Data Type of Attribute Constraints (if any)
1. PurchaseId Integer Primary Key
2. PurchaseDate Date NOT NULL
3. BuyerId Integer Foreign Key
4. CarId Integer Foreign Key
5. PaymentId Integer Foreign Key
6. Price Varchar Foreign Key
12