QAC020C155H: AutoSeller RDBMS Database Design and Development

This project focuses on the design and development of a relational database management system (RDBMS) for AutoSeller, an online startup for car buyers and sellers. The report outlines the importance of databases and the differences between RDBMS and file-based systems. It covers the conceptual and logical modeling of the database, including entity relationship diagrams (ERD) and normalization techniques to reduce data redundancy. The database design includes tables for cars, buyers, sellers, and purchase transactions, with appropriate attributes and data types. The report also details the SQL implementation, including DDL and DML commands for creating tables and manipulating data. The project uses MySQL to implement the database and provides SQL queries for data population and manipulation. The final database design aims to meet AutoSeller's requirements for managing car, buyer, and seller information efficiently.

Running head: DATABASE DESIGN AND DEVELOPMENT
Database Development and Design
Name of the Student:
Name of the University:
Author Note

1
DATABASE DESIGN AND DEVELOPMENT
Introduction
The report is going to discuss about the importance of databases. Database are
important for maintaining every organizations details. AutoSeller is an online start up
organization that aims at developing an effective RDMS for the growth of business. Every
organization and online startup have their own database behind the search engine. The
database developed for the AutoSeller needs to have proper information regarding the cars,
the buyers and details of sellers. While implementing it becomes important to have proper
information system that will help in development of the database. The report will discuss
about the difference between RDBMS and file based system.
Understanding of database
The case scenario describes the need of RDBMS (Relational Database Management
System) for managing the details of customers and cars in Autoseller startup business. The
university has asked to develop a database that will help to meet all the requirements of this
AutoSeller online startup. In the given case study the details that are needed to be organized
includes car details, details of buyers and details regarding sellers. This details needs to be
stored in a proper organized form. The car detail contains information regarding model, color,
and price, year of launch, mileage, body type, and size of the engine and CO2 emissions. The
buyer’s database should have the ability to store information regarding buyers full name,
address, contact details and buying history. The seller’s database will store the information
regarding the seller’s full name, contact details, selling history. The developed database needs
to have proper access so that they can run the sheets regarding the selling details.
Database is basically referred to a container that is used for the purpose of storing
organized data. This data are stored in the form of data sheets. This will help in storing each

2
DATABASE DESIGN AND DEVELOPMENT
data regarding the car and buyers. This also helps in restoring the data once they are lost or
allows the user tp modify the data easily.
In order to work along with this huge number of data it is important to have a proper
search engine that will facilitate the working of the system. This search engine is known as
database management system or DBMS (Database Management System). This is basically a
combination of hardware and computer software and information system that is used for
handling the data and manages the database in a systematic way. This makes the system
easily accessible for the users.
However there are different ways in which data can be stored after it is being
modified. Apart from this the concept of database model is used for defining the logical
structure of information. This helps in defining the relationship between different parts of
data. The most examined and useful databases are describe below:
File management system are referred to as the predecessor for DBMS (Database
Management System). This is a system that allows accessing single fielin at a particular
instance of time. With the use of this approach use can creates and implement the files that re
needed for running the specific programs.
For example, in the given scenario it becomes important to appoint different users for
maintaining the details regarding customer, seller and buyer separately. One user will be
responsible for maintaining the data regarding cars and another one is responsible for
maintaining the details regarding the seller and the information regarding seller. However
after analyzing the situation it can be stated that data sharing becomes very complex.
Sometimes it is also observed that same data is stored at different places thus leading to
wastage of space. Apart from this it becomes very difficult to change or make any
modification in case of data redundancy. After analyzing the given scenario it can be stated

3
DATABASE DESIGN AND DEVELOPMENT
that it become every difficult to change the details of customer. In case the user wants to
update the customer details in one table than it becomes important to change the address at
every place wherever the data is stored. In case the data remains unchanged in some place
then the situation will become inconsistence and will effect the whole database.
In the file system stored within the database it is observed that the data are stored in
various other formats without any proper standards and can be operated on different
programs. This situation will lead to data isolation and will become very difficult to track a
specific data item. Apart from this a major concern is that less security provided to the
system.
Apart from this it is important to ensure that the value of data in the database is
satisfying certain rules. For example in the scenario, one car can be purchased only for one
time. In case the value is not satisfying this constraint than it is important to avoid storing this
data within the system. However there is no specific file system available for checking the
procedures with the use of constraints.
In addition to this the database management system allows the user to access the
multiple files or table at a single instance of time. In this type of database only single
repository data is maintained. Centralized system uses standard format for sharing the data as
there are no less data redundancy and data remains consistent.
In addition to this if the user wants to search a particular item it becomes easily
available as the system is user friendly and provides the operation of being available all the
time. Apart from this DBMS also offers high level security with the help of passwords,
fingerprint matching, voice detection, face and passwords.
The network model is used for many-many relationship model. This database records
are connected to the parent file. The network model includes preventive maintenance into

4
DATABASE DESIGN AND DEVELOPMENT
rigid pavement and flexible pavement. The rigid pavement is further divided into spall repair
and joint seal. This network model further joint seal into silicone sealant. Flexible pavement
is used for crack seal and patching. Asphalt sealant is connected with patching and joint seal.
Relational database management system is used for defining the basic data structure with the
relational model. The information related to the database are represented with the use of rows
and columns. However there are two approaches that is used in software development
process. This includes breaking the problems in small pieces and adding more details to each
sub level. This also offers reusability of code that offers bottom up approach.
Conceptual model
Based on the scenarios the possible entities identified for the scenario are:
 Cars
 Buyers
 Sellers
 Purchase
Logical Modelling
Normalization

5
DATABASE DESIGN AND DEVELOPMENT
Normalization is used for designing the database and organizing the tables in a way
that helps in reducing the dependency of data and data redundancy. This helps in dividing
larger tables into smaller tables and linking those using relationships. Normalization is a
technique that is used for the purpose of relating the entities. This helps in linking all the
entities together with an appropriate relationship. In order to achieve 1NF in columns it
becomes important to ensure that proper rules are followed that includes describing each
entity based on the attributes on which the data needs to be atomized. His are also defined
with the help of primary key and foreign key. This are used for the purpose of uniquely
identifying the tables. Primary key is used for the purpose of uniquely identifying the table
values and will not allow to enter NULL values. The foreign key are used to connect the
tables by referencing to primarily identifying the table. Apart from this details there are
needed to be 1NF and creating separate table with 2NF needs to be identified properly. This
is also important to relate to others tables by foreign key. This is important to create a proper
3NF column that will ensure that all information displayed in 2NF table has no transitive
functional dependencies with other entities. The entities identified are already normalized.
The tables for the database after normalization is provided below:
Car (CarID (pk), make, model, colour, price, year, mileage, bodyType, fuelType, engineSize,
consumption, CO2E)
Seller (SellerID (pk), fullName, address, contact)
Buyer (BuyerID (pk), fullName, address, contact)
Purchase (PurchaseID (pk), soldPrice, soldDate, BuyerID (fk), purchasedDate, CarID (fk),
SellerID (fk))
ERD with the attributes list is provided below:

6
DATABASE DESIGN AND DEVELOPMENT
ERD with the data types is provided below:

7
DATABASE DESIGN AND DEVELOPMENT
ERD generated in MySQL is provided below:

8
DATABASE DESIGN AND DEVELOPMENT
SQL and database implementation
The SQL or structured query language has been used for the interaction with the
database. Two type of SQL language has been used here and they are DDL and DML. The
DDL used in this report are the create and alter table queries. The main DML considered here
in this database are the insert queries have been used here for the data population in the
database tables. DML (Data Manipulation Language) manipulates data that has an existing
definition. DDL (Data Definition Language) defines the structure of data.
Important DDL Commands are:
1) Create
2) Alter
3) Drop
4) Truncate
5) Rename
Important DML Commands are:

9
DATABASE DESIGN AND DEVELOPMENT
1) Select
2) Update
3) SET
4) Join operations
Sql Tables
Buyer
Car
Purchase

10
DATABASE DESIGN AND DEVELOPMENT
Seller
Sql Data
Buyer
Car

11
DATABASE DESIGN AND DEVELOPMENT
Seller
Purchase