AutoSeller Database: Relational Database Design and Implementation

Desklib provides past papers and solved assignments for students. This report details the design and implementation of an AutoSeller database.

Contents
Introduction................................................................................................................................2
TASK 1......................................................................................................................................3
1..............................................................................................................................................3
2..............................................................................................................................................4
3..............................................................................................................................................7
TASK 2......................................................................................................................................9
TASK 3....................................................................................................................................13
TASK 4....................................................................................................................................19
Conclusion................................................................................................................................24

List of Figures
Figure 1: Hierarchical Database Example (Source: Tutorials et al., 2019)............................................6
Figure 2: Network model example (Source Studytonight.com, 2019)...................................................7
Figure 3: Relational Database Model Example (Source: Studytonight.com, 2019)...............................8
Figure 4: Conceptual Diagram.............................................................................................................13
Figure 5 Entity Relationship Diagram.................................................................................................17
Figure 6 UML Diagram.......................................................................................................................18
Figure 7 PhpMyadmin database design...............................................................................................19
Figure 8 Buyer Table...........................................................................................................................20
Figure 9 Cars Table.............................................................................................................................20
Figure 10 CarsPurchased Table...........................................................................................................20
Figure 11 Sold Cars Table...................................................................................................................20
Figure 12 Seller Table.........................................................................................................................21
Figure 13 MethodOfPayment Table....................................................................................................21
Figure 14 Database table of AutoSellers..............................................................................................21
Figure 15 Foreign key Queries............................................................................................................22
Figure 16 Execution of Foreign Key...................................................................................................22
Figure 17 Data of Buyer table.............................................................................................................23
Figure 18 Cars Table Insertion............................................................................................................23
Figure 19 Data of Cars Purchased.......................................................................................................23
Figure 20 Data of Seller Table.............................................................................................................24
Figure 21 Data of Sold Cars................................................................................................................24
Figure 22 Data of Method of Payment.................................................................................................24

Introduction
In this report file database is being described and various logical and relational diagram is
being generated according to it. ER diagram is being built to give logical view and use case
diagram should be made. Data base is being created in phpmyadmin and various tables is
being generated according to it. Data should be enter in database and relevant data is being
inserted in database and screenshots should be added in this file.

TASK 1
1.
RDBMS V/S File Based System
File based system means the system which is used to stored files in a hard-disk or secondary
devices in computers. It is installed at the same time when Operating system is installed in a
computer. File based system can be of two types: FAT (File Allocation Table) and NTFS
format. File based system follow tree hierarchy which means files are stored in tree format
like parent then its child represented as sub-directory and so on. Relational Database System
(RDBMS) is based on relational data model which contains data in form of table. It is
completely dependent on relational model.
Difference between RDMBS and File Based System.
Table 1 Difference between RDBMS and File based System
Parameters Relational Database System File Based System
Storage It stores data in table format Stores data as file
No. of Users It supports multiple users Single user is supported
Type of
Program
RDMS systems are used for
relationship management among
tables
File Based System is used for
management of database in secondary
storage device or in computer
network
Normalisatio
n
It supports Normalisation Normalisation is not supported
Data
Redundancy
Data redundancy is not allowed due
to keys and indexes
Data redundancy is common
Security Multiple level of security is
followed
No Security

Comparison between Advantages, dis-advantages and limitations of RDBMS and File
Based System
Table 2 Comparison regarding various Parameters
RDBMS File Based System
Advantages Data Retrieval in RDBMS is very quick Data Retrieval is very easy in
file based system
Maintenance of data is very easy in
Relational Database System
Data sharing is very easy in file
based system
In RDBMS security is in multiple levels
and it is one of the secure database
Backup of data is easy in file
based system
Dis-
Advantages
Maintaining data is costly in RDBMS Data in file based system is less
secure
Complexity to manage data is high because
of using query languages
Inconsistent data due to no use
of keys and indexes
Limit is set in field size Redundancy is high
Limitations Limits are on assigning field Security is one of the main
limitation
Expensive while compared with file based
system
Redundant or repetition of data
High Complexity Data inconsistency
2.
Hierarchical Database Model
In hierarchical database model, data is being arranged in a tree structure. In tree data structure
root is at top level and from that there can be many children and grandchildren as well.
Hierarchical model same structure. Main limitation of hierarchical model is that it can only

form one to many relationships only, other relationships can’t be followed by hierarchical
model.
Figure 1: Hierarchical Database Example (Source: Tutorials et al., 2019)
Advantages of Hierarchical model
 Insertion and deletion of data in database is easy
 Relations can automatically be defined in data model
 Retrieval of information or data is fast
Dis-Advantages of Hierarchical model
 Only one to many relationship can be occur
 Communication barrier could be occurred due to various levels in hierarchical model
 Changes can be effected in whole application
Network Database Model
On failing of requirement by hierarchical model, network model is being developed. Objects
and relationship are easy to manage in network database model. Lattice structure can be
formed using multiple parent and child can be allowed to each record.

Figure 2: Network model example (Source Studytonight.com, 2019)
Advantages of Network model
 Easy understandable and working is easy
 Easy accessible of data
 Data Integration
Dis-Advantages
 Quite high complexity
 Structural dependence
Relational Database Model
Data organisation is done in two dimensional table and by storing common thing relationship
can be maintained. E.F Codd has introduced this model in 1970. Since then this model is
most widely used. Basic structure of relational model is table. Thus, Tables are also called as
Relations in this model.

Figure 3: Relational Database Model Example (Source: Studytonight.com, 2019)
Advantages of Relational database model
 Retrieval of data is easy
 Complexity is less
 Scalability is easy
Dis-Advantages
 Expensive
 Problems while entering data
3.
Top-Down Approach
Design by analysis method is also known as Top-Down approach. Analysis is done until the
requirements get fulfil. Criteria should be accomplished to proceed further in top down
approach.
Strengths
 These approaches are used in those projects which have limited amount of time.
 Faster decision taking ability is another strength of this approach
Weakness
 Expertise knowledge is necessary to proceed further in this approach

 Participation is low in this approach
Bottom-Up Approach
Design by synthesis is also called as Bottom-Up Approach. Information is collected when
entities constructions begins. Relationships are defined after making up of entities.
Strengths
 Best and exact solutions is provided
 It does not require expertise knowledge
Weakness
 Cost can be incremented while using this approach
 Management control is low so it may reduce the effectiveness and can result in low
efficiency
Top-Down approach is being used in this task to make database design more effective and
efficient.

TASK 2
Entities designed are:
1. BUYER
ATTRIBUTES DATATYPE
BUYER_ID Int
BUYER_NAME Varchar
BUYER_CON Int
BUYER_ADD Varchar
2. CARSPURCHASED
ATTRIBUTES DATATYPE
PURCHASE_ID Int
PURCHASE_DATE Date
BUYER_ID Int
CAR_ID Int
PAYMENT_ID Int
PRICE Int
3. CARS
ATTRIBUTES DATATYPE
CAR_ID Int
MODEL_NO Varchar
COLOR Varchar
PRICE Int
YEAR Date
MAKE Varchar

MILEAGE Int
BODYTYPE Varchar
FUELTYPE Varchar
ENGINESIZE Int
FUELCONSUMPTION Int
CO2EMISSIONS Int
4. SOLDCARS
ATTRIBUTES DATATYPE
SOLD_ID Int
SOLD_DATE Date
SELLER_ID Int
SOLD_PRICE Int
BUYER_ID Int
5. SELLER
ATTRIBUTES DATATYPE
SELLER_ID Int
SELLER_NAME Varchar
SELLER_ADD Varchar
SELLER_CON Int
6. METHODOFPAYMENT
ATTRIBUTES DATATYPE
PAYMENT_ID Int
INTEREST_RATE Int
AMOUNT_APPLIED Int
METHOD Varchar