AutoSeller Database: RDBMS, SQL Queries, and Data Modeling Project

Desklib provides past papers and solved assignments for students. This project details the design and implementation of a relational database for AutoSeller.

Data Modeling and SQL Language

Table of Contents
Introduction......................................................................................................................................4
Task 1: Understanding Database.....................................................................................................5
1. Discuss the difference between RDBMS and file-based systems. Discussion should
include the advantages, disadvantages, and limitations of each..................................................5
2. Discuss the advantages and disadvantages of Hierarchical, network and relational
database models...........................................................................................................................7
3. Discuss the strengths and weaknesses of top-down and bottom-up approaches to
design/develop the database and justify the approach you will use for this task......................10
Task 2: Conceptual Modelling.......................................................................................................11
Task 3: Logical Modelling.............................................................................................................14
Normalization............................................................................................................................14
Task 4: SQL Queries and the database implementation................................................................18
Tables Created...........................................................................................................................18
Foreign Key Commands............................................................................................................19
References......................................................................................................................................22
Appendix........................................................................................................................................23
Table of Figures
Figure 1: Hierarchical Model...........................................................................................................6
Figure 3: Network Model................................................................................................................7
Figure 2: Relational Model..............................................................................................................8
Figure 4: Conceptual Modelling....................................................................................................12
Figure 5: ER Diagram....................................................................................................................14
Figure 6: UML Diagram................................................................................................................15
Figure 7: ERD Diagram by PHP_MyAdmin.................................................................................16
2

Figure 8: Buyer Table....................................................................................................................17
Figure 9: Car Table........................................................................................................................17
Figure 10: Car Purchased Table....................................................................................................17
Figure 11: Car Sold Table..............................................................................................................18
Figure 12: Payment Table..............................................................................................................18
Figure 13: Seller Table..................................................................................................................18
Figure 14: Database Tables of AutoSeller.....................................................................................18
Figure 15: Foreign Key of Car Purchased Table...........................................................................18
Figure 16: Foreign Key of Car Sold Table....................................................................................18
Figure 17: Buyer Values Inserted..................................................................................................19
Figure 18: Buyer Table..................................................................................................................19
Figure 19: Car Values Inserted......................................................................................................19
Figure 20: Car Table......................................................................................................................19
Figure 21: Car Purchased Values Inserted.....................................................................................19
Figure 22: Car Purchased Table....................................................................................................19
Figure 23: Car Sold Values Inserted..............................................................................................19
Figure 24: Car Sold Table..............................................................................................................19
Figure 25: Payment Values Inserted..............................................................................................20
Figure 26: Payment Table..............................................................................................................20
Figure 27: Seller Values Inserted..................................................................................................20
Figure 28: Seller Table..................................................................................................................20
List of Tables
Table 1: Car Table.........................................................................................................................10
Table 2: Buyer Table.....................................................................................................................10
Table 3: Seller Table......................................................................................................................10
3

Table 4: Payment Table.................................................................................................................11
Table 5: Car Purchased Table........................................................................................................11
Table 6: Car Sold Table.................................................................................................................11
Table 7: Normalization Table........................................................................................................13
4

Introduction
This assignment was designed for creating the relational database for the company named as
AutoSeller. It is the startup company of sellers and the buyers of car. As per the requirements,
the database related to them was created where they can store their required values of cars with
the details of the buyers and the sellers. In this below, each part must describe with the given
points. In the first part, difference between file system and the RDBMS must be discussed in
detail. Database models and the bottom-up and the top-down approaches were defined.
Conceptual model and logical model were also made. In the last part, SQL queries will be
detailed of the designed database with the requirements that were given in the brief.
5

Task 1: Understanding Database
1. Discuss the difference between RDBMS and file-based systems. Discussion
should include the advantages, disadvantages, and limitations of each.
RDBMS is abbreviated as Relational Database Management System which depends on relational
model. It is the collection of capabilities and programs which enables the IT team and has to
update, create and also used to interact with the relational database. Most of the commercial
RDBMS uses the SQL Language for accessing the database (Pop & Butoiu, 2015).
There are some advantages of RDBMS that were defined below:
o Data Structure: The tabular format was easy to understand by the manipulators and it also
makes it quite simpler for the users while using it. Data organization and data access were
arranged for using the natural structure of the data or information.
o Accessing network: RDBMS comprises as server domain and the software program that was
specifically designed for capturing the sent requests through network. It also facilitates the
conversations between database and database clients.
o Language: RDBMS supports the standard or the structured language that was correlated
with the SQL Language. SQL Language is necessary or the main part because the syntax of
the SQL was so simple or easy.
There are some disadvantages of RDBMS that were defined below:
o Cost: While setting u the system of the database was so high and it is the main disadvantage
of the relational databases. For assembling the relational database, the specially designed
software was used. Only for programmers, it was an easy task for updating the overall
information or the data elements while non-programmers it was quite difficult for them to
perform the related tasks.
o Managing Data or Information in Huge Volumes: To manage the data or the information,
it was quite complicated within the relational database that depended on the common
characteristics.
o Structured Limit: In the relational databases, each variable has its fixed length or limit
while designing databases. It is also the main disadvantage because this will lead to data loss.
6

7

File-Based System is also called a file management system. It was first attempted for organizing
and storing the information into the storage devices such as SSD, Hard Disk, CD-ROM, floppy
disk, USB key, and many more. It uses the indexing system for locating the information or the
data elements. Such management of the files makes the process possible and preserve the data
elements in a large amount. It also shares data between various computer programs (Simon,
2016).
There are some advantages of File-based System that were defined below:
o Backup: It was possible for backup the database that was stored into the system files. The
computer system will provide the functionalities for serving this purpose for backup the data
or files.
o Retrieving data: Computer-based system will provide the enhanced techniques for
retrieving the data that was stored in the files in an efficient and easy way.
There are some disadvantages of File-based System that were defined below:
o Data Redundancy: It was possible when duplicate information was present in more than one
file. Data redundancy resulted in memory wastage.
o Difficulty while accessing the data: Accessing the data was not so efficient and convenient
in processing the file management system.
8

2. Discuss the advantages and disadvantages of Hierarchical, network and
relational database models.
Hierarchical Model
Figure 1: Hierarchical Model
(Source: Studytonight, 2019)
Advantages of the hierarchical model are as follows:
o Simplicity: Naturally data has the relationship in the hierarchical form and has the
relationship in practical situations. Though, it was so easy to view the data in the arranged
manner.
o Database Integrity: Integration of database was promoted highly into this system because of
its integral parent and child structure (McNeish, et al, 2017).
Disadvantages of the hierarchical model are as follows:
o Implementation Complexity: Actual implementation for the hierarchical database was
depend on physical storage of information or data elements.
o Programming Complexity: Hierarchical database of the programming was complex
relatively because programmers must know physical paths of data items.
9

Network Model
Figure 2: Network Model
(Source: Studytonight, 2019)
Advantages of the Network Model are as follows:
o Conceptual Simplicity: It is same as hierarchical model, and it is conceptually easy and
simple to design.
o Data can be accessed easily: Accessing data was so flexible and easy as compared with
hierarchical model.
Disadvantages of the Network Model are as follows:
o System Complexity: Complete records were maintained with the use of pointers and the
whole structure of database was becoming so complex.
o Operational Anomalies: Updating, deleting, and inserting operation of such records were
required the adjustments of the pointers in the large numbers (Sorokin, et al, 2015).
10

Relational Model
Figure 3: Relational Model
(Source: Studytonight, 2019)
Advantages of the Relational model are as follows:
o Simplicity: Though network database model and hierarchical model are simple at conceptual
level, but relational model is even simpler at conceptual level.
o Implementation: This model supports data independence and design independence which
make it much easier than other models in maintenance, implementation, and usage (Khan, et
al, 2015).
Disadvantages of the Relational model are as follows:
o Lead to bad Design: There is no need to know about the physical database so no need to
know the complex things it tends to bad design of database.
o “Information Island” Phenomenon: This will create data inconsistency and data
redundancy which tends to create problem in data integration.
11

3. Discuss the strengths and weaknesses of top-down and bottom-up approaches to
design/develop the database and justify the approach you will use for this task.
Top-Down Approach: In Simple words, Top- Down approach divides the complex modules
into simpler modules. This approach analyzes the risks in project by predicting the impact of
operational failures. The top-down approach relies on historical data of database.
Advantages:
o Simple and not Data Intensive
o First Implementation is identity
Disadvantages:
o Limited Coverage
o High Cost
Bottom-up Approach:
The bottom-up approach is a type of information processing based on the incoming data. In this
approach, system is divided into sub-elements and then these elements will be linked together to
form a large specified system.
Advantages:
o Benefits in early stage
o Do not need to develop external adapters
Disadvantages:
o Cannot do immediate changes
o Driven by existing system
12