Database Design and Implementation

This document provides an overview of database design and implementation. It covers topics such as RDBMS vs File Based System, hierarchical vs network vs relational database, top-down vs bottom-up approach, and more. It also includes a SQL script for creating the database and tables. If you need help with your assignments or essays on this topic, Desklib is here to assist you.

Running head: DATABASE DESIGN AND IMPLEMENTATION
Database Design and Implementation
Name of the Student
Name of the University
Author’s note:

1DATABASE DESIGN AND IMPLEMENTATION
Table of Contents
Task 1: Understanding of database..................................................................................................2
1.1 Difference between RDBMS and File Based System:..........................................................2
1.2 Hierarchical, Network and Relational Database:...................................................................3
1.3 Top-Down and Bottom-Up approach:...................................................................................6
Task 2: Conceptual Modelling.........................................................................................................7
Task 3: Logical Modelling...............................................................................................................7
3.1 Normalization:.......................................................................................................................7
3.2 Database Model:..................................................................................................................13
Task 4: SQL Script........................................................................................................................13
4.1 Create Database:..................................................................................................................13
4.2 Create Tables:......................................................................................................................14
4.3 Insert Data:...........................................................................................................................16
Bibliography:.................................................................................................................................18

2DATABASE DESIGN AND IMPLEMENTATION
Task 1: Understanding of database
1.1 Difference between RDBMS and File Based System:
Difference Factor RDBMS File System
Definition The relational database
management system is a set of
interconnected data and various
functions for processing those
data.
The file based system can be
referred to the abstraction for
retrieving, storing, handling and
modifying a collection of files.
Data Redundancy The RDBMS systems provide
strong mechanisms for handling
redundancy. The database
provides referential integrity
constraints for handling the data
redundancy (Connolly and Beg
2015).
The file base system can have
redundancy as the user will be in-
charge of defining and
implementing the files needed to
run an application. There is no
specific process for handling
redundancy.
Sharing Data The data sharing mechanism of
file based system is extremely
complex. It limits the capability of
a system in modern day IT.
The RDBMS has took the
opportunity of data sharing and
implemented a centralized system
architecture which is suitable for
data sharing (Vangoor, Tarasov and
Zadok 2017).
Data Consistency Redundant data goes through very
complex update processes. Taken
As the amount of redundancy is
less in relational database, the data

3DATABASE DESIGN AND IMPLEMENTATION
as an example, if the application
wants to change the name of the
user then it has to track every
place where name is stored and
change the name every time.
consistency is extremely high.
Atomicity The atomicity refers to the
situation where a transaction is
either complete or incomplete. The
file based system does not follow
the rules of atomicity (Connolly
and Beg 2015).
The RDMBS strongly follows the
concept of atomicity. In terms of
RDBMS, data is only changed
when the transaction is complete. If
the transaction gets interrupted then
data will be restored.
Concurrent access
anomalies
The file based systems are not
capable of providing support for
preventing anomalies that can be
caused by concurrent access.
In order to handle the concurrency,
the RDBMS is highly
recommended (Liao et al. 2018).
1.2 Hierarchical, Network and Relational Database:
Hierarchical Network Relational
Advantages This data model
promote data sharing
feature
Supports parent child
relationship (Niazi et
The model’s concepts
are simple enough to
be equal to
hierarchical model
The network model
The relational model
promotes
independency in
terms of structure by
allowing creating

4DATABASE DESIGN AND IMPLEMENTATION
al. 2017)
It provides database
security and it is
enforced by the
database system
Data integrity is
promoted
Efficiently supports
one to many
relationship
can support many to
many relationship
easily
The access to data is
much more effective
and efficient in
comparison to file
based system and
hierarchical model
(Agrawal et al. 2015)
The data integrity is
promoted by the Data
owner/member
relationship
Conformance to
standards is not
available
The network model
supports DDL and
DML language
independent tables
The SQL is the base
of the Ad Hoc query
Disadvantages The database
administrator will
require good
The network based
systems are
extremely complex,
The software and
hardware support is
extensively high for

5DATABASE DESIGN AND IMPLEMENTATION
understanding of data
storage
characteristics as
database
implementation is
complex
Application
development and
management is
complex due to
navigation
All application
programs are required
to be altered if any
changes are to be
done in structure
The database model
does not support
many to many
relation and it limits
the implementation
This database model
does not have any
thus reduces the
efficiency
Application
development and
management is
complex due to
navigation
All application
programs are required
to be altered if any
changes are to be
done in structure
(Elmasri and Navathe
2017)
relational database
The developers must
have a good
understanding of
inner working of the
database system to
implement one

6DATABASE DESIGN AND IMPLEMENTATION
DDL or DML
language support
The standards are not
available in good
amount
1.3 Top-Down and Bottom-Up approach:
Top-Down Bottom-Up
Strength This approach is more coherent.
It has less amount of redundancy.
The bottom-up approach can be
extremely efficient
This approach is more suitable for
implementing real world
situations
The development can be scalable
(Ochsner, Hug and Galleron 2017)
Weakness Limits the way of implementing a
database (Huang et al. 2017)
The requirements for implementing
the database can be extremely
complex
This approach can be incoherent
The amount of data redundancy is
high

7DATABASE DESIGN AND IMPLEMENTATION
The requirements can be difficult
to implement in terms of design
and development
Based on the information illustrated above, the top-down approach can be the best way of
designing and implementing the database. The database is relatively simple and small in scale.
As the requirements are clear and concise, the to-down approach would the most suitable
solution.
Task 2: Conceptual Modelling
Figure 1: Conceptual Model of Auto Seller Database
(Source: Created by Author)
Task 3: Logical Modelling
3.1 Normalization:
1st Normal Form:

8DATABASE DESIGN AND IMPLEMENTATION
buyer_id car_number purchase_date sold_date seller_id from_date to_date
1 2 2019-02-02 2019-02-
02
1 2019-02-
02
Present
1 2019-03-02 2019-03-
02
2 2019-02-
02
Present
2 3 2019-02-02 2019-02-
02
1 2019-02-
02
Present
4 2019-03-02 2019-03-
02
2 2019-02-
02
Present
The above table shows that the table have multivalued attributes. The following table has
been represented after converting the multivalued attributes into atomic attributes.
buyer_id car_number purchase_date sold_date seller_id from_date to_date
1 2 2019-02-02 2019-02-
02
1 2019-02-
02
Present
1 1 2019-03-02 2019-03-
02
2 2019-02-
02
Present
2 3 2019-02-02 2019-02-
02
1 2019-02-
02
Present
2 4 2019-03-02 2019-03-
02
2 2019-02-
02
Present

9DATABASE DESIGN AND IMPLEMENTATION
2nd Normal Form: The above table is in the first normal form. To convert it into second
normal form, the partial dependencies are removed from the tables. The dependencies are
showed in the following section.
car_number  make, model, color, price, year, mileage, body_type, fuel_type,
engine_size, fuel_consumption, CO2_emission
seller_id  last_name, first_name, address, contact_number
buyer_id  first_name, last_name, address, contact_number, from_date, to_date
sales_number  car_number, seller_id, buyer_id, purchase_date, sold_date, sold_price
Now if the partial dependencies are removed then the following tables are created.
buyer_
id
first_na
me
last_na
me
address contact_num
ber
car_num
ber
from_da
te
to_da
te
1 'Archie' 'Kelley' '17 Wallis
Street,
TAMARA
MA NSW
2026'
'(02) 9540
4908'
2 '2019-
01-01'
'2019-
02-01'
2 'Tristan' 'Waddell
'
'22 Friar
John Way,
MYARA
WA 6207'
'(08) 9473
9988'
1 '2019-
01-01'
'2019-
02-02'
3 'Lincoln' 'Jerome' '20 Wagga
Road, SAN
'(02) 6139 3 '2019- '2019-

10DATABASE DESIGN AND IMPLEMENTATION
ISIDORE
NSW 2650'
4305' 01-01' 02-03'
seller_id first_name last_name address contact_numbe
r
1 'Housley' 'Daniel' '64 Derry
Street,
CLEAR
MOUNTAIN
QLD 4500'
'[phone]'
2 'Krichauff' 'Joshua' '42 Kerma
Crescent,
OAKY PARK
NSW 2790'
'[phone]'
3 'Bradley' 'Tyson' '19
Rockhampton
Qld,
MILMAN
QLD 4702'
'[phone]'
car_numbe
r
make model color price year mileag
e
body_type
1 'alfa- 'Sports' 'red' '150000.00' 2018 10 'convertible'

11DATABASE DESIGN AND IMPLEMENTATION
romero'
2 'mercedes-
benz'
's102' 'royal blue' '250000.00' 2017 7 'wagon'
3 'alfa-
romero'
'SUV' 'red' '150000.00' 2016 12 'convertible'
sales_number car_number seller_i
d
seller_i
d
purchase_date sold_date sold_price
1 1 2 2 '2019-02-01' '2019-02-
01'
'1200.00'
2 2 1 1 '2019-02-02' '2019-02-
02'
'2200.00'
3 3 3 3 '2019-02-03' '2019-02-
03'
'3200.00'
3rd Normal Form: In order to convert the table into third normal form, the transitive
dependency needs to be eliminated. The dependency is shown in the following section.
buyer_id  car_number
car_number ! buyer_id
car_number  from_date, to_date
buyer_id first_name last_name address contact_number
1 'Archie' 'Kelley' '17 Wallis Street, '[phone]'

12DATABASE DESIGN AND IMPLEMENTATION
TAMARAMA
NSW 2026'
2 'Tristan' 'Waddell' '22 Friar John Way,
MYARA WA 6207'
'[phone]'
3 'Lincoln' 'Jerome' '20 Wagga Road,
SAN ISIDORE
NSW 2650'
'[phone]'
car_numbe
r
buyer_id from_date to_date
2 1 '2019-01-01' '2019-02-01'
1 2 '2019-01-01' '2019-02-02'
3 3 '2019-01-01' '2019-02-03'

13DATABASE DESIGN AND IMPLEMENTATION
3.2 Database Model:
Figure 2: Logical Model of Auto Seller Database
(Source: Created by Author)
Task 4: SQL Script
4.1 Create Database:
CREATE DATABASE IF NOT EXISTS autoseller;
USE autoseller;

14DATABASE DESIGN AND IMPLEMENTATION
4.2 Create Tables:
CREATE TABLE tbl_buyer (
buyerID int(11) NOT NULL,
first_name varchar(150) NOT NULL,
last_name varchar(150) NOT NULL,
address varchar(150) NOT NULL,
contact_number varchar(150) NOT NULL
);
CREATE TABLE tbl_car (
car_number int(11) NOT NULL,
make varchar(150) NOT NULL,
model varchar(150) NOT NULL,
color varchar(150) NOT NULL,
price decimal(10,2) NOT NULL,
year year(4) NOT NULL,
mileage int(11) NOT NULL,
body_type varchar(150) NOT NULL,
fuel_type varchar(150) NOT NULL,
engine_size varchar(150) NOT NULL,
fuel_consumption varchar(150) NOT NULL,
CO2_emission varchar(150) NOT NULL
);
CREATE TABLE tbl_car_owner (
car_number int(11) NOT NULL,
buyer_id int(11) NOT NULL,
from_date date NOT NULL,
to_date date NOT NULL
);
CREATE TABLE tbl_sales_invoice (

15DATABASE DESIGN AND IMPLEMENTATION
sales_number int(11) NOT NULL,
car_number int(11) NOT NULL,
seller_id int(11) NOT NULL,
buyer_id int(11) NOT NULL,
purchase_date date NOT NULL,
sold_date date NOT NULL,
sold_price decimal(10,2) NOT NULL
);
CREATE TABLE tbl_seller (
seller_id int(11) NOT NULL,
last_name varchar(150) NOT NULL,
first_name varchar(150) NOT NULL,
address varchar(150) NOT NULL,
contact_number varchar(150) NOT NULL
);
ALTER TABLE tbl_buyer
ADD PRIMARY KEY (buyerID);
ALTER TABLE tbl_car
ADD PRIMARY KEY (car_number);
ALTER TABLE tbl_car_owner
ADD PRIMARY KEY (car_number,buyer_id),
ADD KEY buyer_id (buyer_id);
ALTER TABLE tbl_sales_invoice
ADD PRIMARY KEY (sales_number),
ADD KEY buyer_id (buyer_id),
ADD KEY car_number (car_number),
ADD KEY seller_id (seller_id);

16DATABASE DESIGN AND IMPLEMENTATION
ALTER TABLE tbl_seller
ADD PRIMARY KEY (seller_id);
ALTER TABLE tbl_car_owner
ADD CONSTRAINT tbl_car_owner_ibfk_1 FOREIGN KEY (buyer_id)
REFERENCES tbl_buyer (buyerID),
ADD CONSTRAINT tbl_car_owner_ibfk_2 FOREIGN KEY (car_number)
REFERENCES tbl_car (car_number);
ALTER TABLE tbl_sales_invoice
ADD CONSTRAINT tbl_sales_invoice_ibfk_1 FOREIGN KEY (buyer_id)
REFERENCES tbl_buyer (buyerID),
ADD CONSTRAINT tbl_sales_invoice_ibfk_2 FOREIGN KEY (car_number)
REFERENCES tbl_car (car_number),
ADD CONSTRAINT tbl_sales_invoice_ibfk_3 FOREIGN KEY (seller_id)
REFERENCES tbl_seller (seller_id);
4.3 Insert Data:
INSERT INTO tbl_buyer (buyerID, first_name, last_name, address, contact_number)
VALUES
(1, 'Archie', 'Kelley', '17 Wallis Street, TAMARAMA NSW 2026', '[phone]'),
(2, 'Tristan', 'Waddell', '22 Friar John Way, MYARA WA 6207', '[phone]'),
(3, 'Lincoln', 'Jerome', '20 Wagga Road, SAN ISIDORE NSW 2650', '[phone]');
INSERT INTO tbl_car (car_number, make, model, color, price, year, mileage,
body_type, fuel_type, engine_size, fuel_consumption, CO2_emission) VALUES
(1, 'alfa-romero', 'Sports', 'red', '150000.00', 2018, 10, 'convertible', 'gas', '80 ',
'mpfi', '.3'),
(2, 'mercedes-benz', 's102', 'royal blue', '250000.00', 2017, 7, 'wagon', 'diesel', '183', 'idi',
'.25'),
(3, 'alfa-romero', 'SUV', 'red', '150000.00', 2016, 12, 'convertible', 'Diesel', '90 ',
'mpfi', '.5');

17DATABASE DESIGN AND IMPLEMENTATION
INSERT INTO tbl_car_owner (car_number, buyer_id, from_date, to_date) VALUES
(1, 2, '2019-01-01', '2019-02-01'),
(2, 1, '2019-01-01', '2019-02-02'),
(3, 3, '2019-01-01', '2019-02-03');
INSERT INTO tbl_sales_invoice (sales_number, car_number, seller_id, buyer_id,
purchase_date, sold_date, sold_price) VALUES
(1, 1, 2, 2, '2019-02-01', '2019-02-01', '1200.00'),
(2, 2, 1, 1, '2019-02-02', '2019-02-02', '2200.00'),
(3, 3, 3, 3, '2019-02-03', '2019-02-03', '3200.00');
INSERT INTO tbl_seller (seller_id, last_name, first_name, address, contact_number)
VALUES
(1, 'Housley', 'Daniel', '64 Derry Street, CLEAR MOUNTAIN QLD 4500', '(07) 3663
1795'),
(2, 'Krichauff', 'Joshua', '42 Kerma Crescent, OAKY PARK NSW 2790', '(02) 4088
3888'),
(3, 'Bradley', 'Tyson', '19 Rockhampton Qld, MILMAN QLD 4702', '[phone]');

18DATABASE DESIGN AND IMPLEMENTATION
Bibliography:
Agrawal, S., Verma, J.P., Mahidhariya, B., Patel, N. and Patel, A., 2015. Survey on MongoDB:
An Open-Source Document Database. Database, 1(2), p.4.
Connolly, T.M. and Beg, C.E., 2015. Database systems: a practical approach to design,
implementation, and management.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Guion, A., Burton, K., Hodgkins, H., Morris Jr, D. and Wood, M.M.J., 2015, December.
Implementation of an interactive database interface utilizing HTML, PHP, JavaScript, and
MySQL in support of water quality assessments in the Northeastern North Carolina Pasquotank
Watershed. In AGU Fall Meeting Abstracts.
Huang, J., Mozafari, B., Schoenebeck, G. and Wenisch, T.F., 2017, May. A top-down approach
to achieving performance predictability in database systems. In Proceedings of the 2017 ACM
International Conference on Management of Data (pp. 745-758). ACM.
Liao, J., Cai, Z., Trahay, F. and Peng, X., 2018. Block Placement in Distributed File Systems
Based on Block Access Frequency. IEEE access, 6, pp.38411-38420.
Niazi, S., Ismail, M., Haridi, S., Dowling, J., Grohsschmiedt, S. and Ronström, M., 2017.
Hopsfs: Scaling hierarchical file system metadata using newsql databases. In 15th {USENIX}
Conference on File and Storage Technologies ({FAST} 17)(pp. 89-104).
Ochsner, M., Hug, S. and Galleron, I., 2017. The future of research assessment in the humanities:
bottom-up assessment procedures. Palgrave Communications, 3, p.17020.

19DATABASE DESIGN AND IMPLEMENTATION
Rabbani, T., 2016. Empirical Testing of the CySeMoL Tool for Cyber Security Assessment–
Case Study of Linux Server and MySQL.
Vangoor, B.K.R., Tarasov, V. and Zadok, E., 2017. To {FUSE} or Not to {FUSE}: Performance
of User-Space File Systems. In 15th {USENIX} Conference on File and Storage Technologies
({FAST} 17) (pp. 59-72).
Zhang, F., Ma, Z.M. and Cheng, J., 2016. Enhanced entity-relationship modeling with
description logic. Knowledge-Based Systems, 93, pp.12-32.
Zhang, L., Shi, L., Zhang, B., Zhao, L., Dong, Y., Liu, J., Lian, Z., Liang, L., Chen, W., Luo, X.
and Pei, S., 2017. Probabilistic Entity-Relationship Diagram: A correlation between functional
connectivity and spontaneous brain activity during resting state in major depressive
disorder. PloS one, 12(6), p.e0178386.