Database Design and Implementation

This document provides an overview of database design and implementation. It covers topics such as the difference between RDBMS and file-based systems, advantages and disadvantages of RDBMS, limitations of RDBMS, advantages and disadvantages of file-based systems, advantages and disadvantages of hierarchical, network and relational database models, strengths and weaknesses of top-down and bottom-up approaches, conceptual modeling, logical modeling, and SQL script.

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
Difference between RDBMS and File-Based System ................................................................ 2
Advantage of RDBMS ................................................................................................................ 2
Disadvantages of RDBMS .......................................................................................................... 3
Limitation of RDBMS ................................................................................................................ 3
Advantage of File-Based System ................................................................................................ 3
Disadvantage of File-Based System ........................................................................................... 3
Limitation of File-Based System ................................................................................................ 4
Advantages of Hierarchical, network and relational database models ....................................... 4
Disadvantage of Hierarchical, network and relational database models .................................... 5
Strength of top-down and bottom-up approaches ....................................................................... 6
Weakness of top-down and bottom-up approaches .................................................................... 6
Task 2: Conceptual Modelling ........................................................................................................ 7
Task 3: Logical Modelling .............................................................................................................. 7
Task 4: SQL Script ......................................................................................................................... 9
Bibliography: ................................................................................................................................ 13

2DATABASE DESIGN AND IMPLEMENTATION
Task 1: Understanding of database
Difference between RDBMS and File-Based System
File system is easy to use system which is required for storing files which requires
minimum security and constraints. While database management system is taken into account when
the security constraints are very much high. In file-based system, the redundancy of data is more
in comparison to RDBMS (Arulraj and Pavlo 2017). Data inconsistency is there is more in file
system in comparison to RDBMS. In file-based system, centralization is tough while it is easy in
RDBMS. In file management system, user can locate the physical address while in RDBMS user
is unaware of physical address. In the file management system, the overall security is considered
to be low while it is high in RDBMS(Poerner and Winkelmann 2017). File management system
aims to store unstructured data like isolated data and entries or files. RDBMS aims to store
structured data which comes up with proper constraints and interrelation.
Advantage of RDBMS
There are certain number of advantages of RDBMS like
Control of Redundancy: In file-based system, each of the application comes up with own
private files which cannot be shared between the application (Elmasri and Navathe 2017). By
having centralized database issues of storage waste can be avoided.
Integrity can be enforced: Integrity of data in the database is all about accuracy like
inaccurate data which can be stored in the database.
Inconsistency can be avoided: The same kind of data is duplicated and changed at the
given site which cannot be propagated to another site.

3DATABASE DESIGN AND IMPLEMENTATION
Disadvantages of RDBMS
There are disadvantages of RDBMS like
Complexity: A good RDBMS makes use DBMS which is considered to be an extremely
piece of software.
Performance: File-based systems are generally written for some specific application like
invoicing so that the overall performance is good.
Cost of DBMS: There is huge amount of cost involved in DBMS which totally depends
on environment and overall functionality.
Limitation of RDBMS
There are certain number of limitation of RDBMS like
 RDBMS aims to provide definite processing of data.
 Huge amount of capital investment is required in hardware, software and training.
Advantage of File-Based System
There are certain advantages of File-based system like
 Back of data
 Retrieval of data.
 Remote access.
 Sharing.
Disadvantage of File-Based System
There are certain disadvantages of file-based system like

4DATABASE DESIGN AND IMPLEMENTATION
 Redundancy of data
 Issues in accessing data.
 Limited sharing of data.
 Issues related to security of data.
Limitation of File-Based System
There is certain limitation of file-based system like
 Separation and isolation of data.
 Duplication of data.
 Dependency on data.
 Incompatible with different file formats.
 Fixed queries of various application programs.
Advantages of Hierarchical, network and relational database models
Hierarchical model is not like the other two models as in this model data is stored in proper
hierarchy. At present this model does not exist but comes up with legal application (Laudon and
Laudon 2016). For example, an organization is made up of various departments and each of the
department has associated employees. To achieving the details, the user, need to transverse the
whole given tree.
Network model is the first model which is needed for addressing any kind of inefficient in
the hierarchical model (Deelman et al. 2015). In this model, the user needs to create network which
can provide data with each other.

5DATABASE DESIGN AND IMPLEMENTATION
Relational model is considered to be best model which is efficient and flexible in the
database model currently in use (Prout et al. 2015). There is various kind of advantages of
relational model which the reason behind the use of some popular database model used at present.
Disadvantage of Hierarchical, network and relational database models
Hierarchical data model comes up numerous disadvantages like
Redundancy: When the given data is stored in the flat file, there may be repetition of data
multiples and can be changed for the required data (Arasu et al. 2016). It does not require to make
any kind of changes in various places of flat file.
If the user requires to fetch any kind of data in this model, then an individual need to start
from the root of the model (Hababeh, Khalil and Khreishah 2015). Then after that, it is transverse
through the child for achieving the required model.
Network Data model comes up with various disadvantages like
 If there is any kind of need for making any kind of changes in the entities, then it does not
require to make any kind of changes in the database.
 It might be difficult for proper design of relationship between the required entities (Arulraj
and Pavlo 2017). It is mainly done so that they can be related in much similar way.
Relational data model comes up with various disadvantages like
 The overall hardware cost is bit high as it can separate the physical information from logical
data (Elmasri and Navathe 2017). For achieving this much powerful hardware system and
memory is needed.
 Design of relation database model creates complexity for its users.

6DATABASE DESIGN AND IMPLEMENTATION
Strength of top-down and bottom-up approaches
There are many benefits of Top-down approach
 User and business awareness are all about product. All the benefits can be achieved in the
beginning phase.
 One can replace the manual process with proper automation.
 An individual can easily implement password management for large number of users.
There are benefits of Bottom-up approaches
 Organization needs to focus on use of various resources by which various individual can
easily manage large number of application.
 Both operation and maintaining resources are not impacted severely along with bottom line
approaches.
Weakness of top-down and bottom-up approaches
There are various disadvantages of Top-down approaches like
 Organizational structure which is established needs to be changed in the rollout phase.
 Strategy is driven present infrastructure instead of the present business processes.
There are some disadvantages of Bottom-up approaches like
 The solution provided mainly works in the beginning phase only.
A minimum number of user account needs to be managed in the starting stage.

7DATABASE DESIGN AND IMPLEMENTATION
Task 2: Conceptual Modelling
Figure 1: Conceptual Model of Auto Seller Database
(Source: Created by Author)
Task 3: Logical Modelling
Normalization: In the UNF or un-normalized form, all the attributes reside in one entity.
There may not be any primary key in the table to identify the rows in the database. The UNF also
supports multivalued attributes. The database would look like as following.
AutoSeller (buyerFullName, CarMake, CarModel, SellDate, sellerFullName, SoldDate,
SoldPrice)
Now if the above UNF model is converted into the first normal form then the database
structure would be similar but the primary key for each group of data can be added so that finding
the row can be easy. The values on which the multivalued data depends would be repeated every
time a car is purchased. The following database will as below.

8DATABASE DESIGN AND IMPLEMENTATION
AutoSeller (buyerID, buyerFullName, carDI, CarMake, CarModel, sellerID,
sellerFullName, sellID, SoldDate, SoldPrice)
In the second normal form the real transmission of the database happens. As the second
normal form does not support any partial dependency, new tables are created with the attributes
that are partially depended on a non-key attribute.
Buyer (buyerID, fullName, address, contact)
Sold (purchaseID, sellerID, buyerID, soldDate, soldPrice, purchaseDate)
Cars (carID, carDetails, sellerFullName, address, contact)
Now the third normal form demands the database to be transitive dependency free. The
transitive dependency is in purchaseDate, buyerID, sellerID in the Sold entity. It is also in the
carID and sellerFullName, address, contact. Therefore after removing the transitive dependency
the following normalized database is achieved.
Buyer (buyerID, fullName, address, contact)
Purchase (purchaseID, sellerID, buyerID, purchaseDate)
Sold (purchaseID, sellerID, soldDate, soldPrice)
Seller (sellerID, fullName, address, contact)
Cars (CarID, make, model, color, price, year, mileage, bodyType, fuelType, engineSize,
fuelConsumption, CO2Emission)
Database Model:

9DATABASE DESIGN AND IMPLEMENTATION
Figure 2: Logical Model of Auto Seller Database
(Source: Created by Author)
Task 4: SQL Script
Create Database:
CREATE DATABASE IF NOT EXISTS autoseller;
USE autoseller;
Create Tables:
CREATE TABLE buyers (
buyerID int(11) NOT NULL,
fullName varchar(150) NOT NULL,
address varchar(150) NOT NULL,
contact varchar(150) NOT NULL
);
CREATE TABLE cars (

10DATABASE DESIGN AND IMPLEMENTATION
carID 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,
bodyType varchar(150) NOT NULL,
fuelType varchar(150) NOT NULL,
engineSize varchar(150) NOT NULL,
fuelConsumption varchar(150) NOT NULL,
CO2Emission varchar(150) NOT NULL
);
CREATE TABLE purchase (
purchaseID int(11) NOT NULL,
carID int(11) NOT NULL,
buyerID int(11) NOT NULL,
purchaseDate date NOT NULL
);
CREATE TABLE seller (
sellerID int(11) NOT NULL,
fullName varchar(150) NOT NULL,
address varchar(150) NOT NULL,
contact varchar(150) NOT NULL
);
CREATE TABLE sold (
purchaseID int(11) NOT NULL,
sellerID int(11) NOT NULL,
soldDate date NOT NULL,

11DATABASE DESIGN AND IMPLEMENTATION
soldPrice decimal(10,2) NOT NULL
);
ALTER TABLE buyers
ADD PRIMARY KEY (buyerID);
ALTER TABLE cars
ADD PRIMARY KEY (carID);
ALTER TABLE purchase
ADD PRIMARY KEY (purchaseID),
ADD KEY buyerID (buyerID),
ADD KEY carID (carID);
ALTER TABLE seller
ADD PRIMARY KEY (sellerID);
ALTER TABLE sold
ADD PRIMARY KEY (purchaseID),
ADD KEY sellerID (sellerID);
ALTER TABLE purchase
ADD CONSTRAINT purchase_ibfk_1 FOREIGN KEY (buyerID) REFERENCES
buyers (buyerID),
ADD CONSTRAINT purchase_ibfk_2 FOREIGN KEY (carID) REFERENCES cars
(carID);
ALTER TABLE sold
ADD CONSTRAINT sold_ibfk_1 FOREIGN KEY (purchaseID) REFERENCES
purchase (purchaseID),
ADD CONSTRAINT sold_ibfk_2 FOREIGN KEY (sellerID) REFERENCES seller
(sellerID);
COMMIT;
Insert Data:
INSERT INTO buyers (buyerID, fullName, address, contact) VALUES (1, 'Elijah
Copeland', '92 Panorama Road', '[phone]');
INSERT INTO buyers (buyerID, fullName, address, contact) VALUES (2, 'Hamish
Arthur', '17 Cedar Street', '[phone]');

12DATABASE DESIGN AND IMPLEMENTATION
INSERT INTO cars (carID, make, model, color, price, year, mileage, bodyType, fuelType,
engineSize, fuelConsumption, CO2Emission) VALUES (1, 'alfa-romero', 'Sports', 'red',
'150000.00', 2018, 10, 'convertible', 'gas', '80 ', 'mpfi', '.3');
INSERT INTO cars (carID, make, model, color, price, year, mileage, bodyType, fuelType,
engineSize, fuelConsumption, CO2Emission) VALUES (2, 'mercedes-benz', 's102', 'royal blue',
'250000.00', 2017, 7, 'wagon', 'diesel', '183', 'idi', '.25');
INSERT INTO purchase (purchaseID, carID, buyerID, purchaseDate) VALUES (1, 1, 2,
'2019-02-01');
INSERT INTO purchase (purchaseID, carID, buyerID, purchaseDate) VALUES (2, 2, 1,
'2019-02-05');
INSERT INTO seller (sellerID, fullName, address, contact) VALUES (1, 'Cars Guide', '45
Stanley Drive', '[phone]');
INSERT INTO seller (sellerID, fullName, address, contact) VALUES (2, 'Sell My Car Box
Hill', '60 Village Drive', '[phone]');
INSERT INTO sold (purchaseID, sellerID, soldDate, soldPrice) VALUES (1, 2, '2019-02-
01', '160000.00');
INSERT INTO sold (purchaseID, sellerID, soldDate, soldPrice) VALUES (2, 1, '2019-02-
05', '255000.00');

13DATABASE DESIGN AND IMPLEMENTATION
Bibliography:
Arasu, A., Babcock, B., Babu, S., Cieslewicz, J., Datar, M., Ito, K., Motwani, R., Srivastava, U.
and Widom, J., 2016. Stream: The stanford data stream management system. In Data Stream
Management (pp. 317-336). Springer, Berlin, Heidelberg.
Arulraj, J. and Pavlo, A., 2017, May. How to build a non-volatile memory database management
system. In Proceedings of the 2017 ACM International Conference on Management of Data (pp.
1753-1758). ACM.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management.
Cengage Learning.
Deelman, E., Vahi, K., Juve, G., Rynge, M., Callaghan, S., Maechling, P.J., Mayani, R., Chen, W.,
Da Silva, R.F., Livny, M. and Wenger, K., 2015. Pegasus, a workflow management system for
science automation. Future Generation Computer Systems, 46, pp.17-35.
Elmasri, R. and Navathe, S., 2017. Fundamentals of database systems. Pearson.
Fabregat, A., Korninger, F., Viteri, G., Sidiropoulos, K., Marin-Garcia, P., Ping, P., Wu, G., Stein,
L., D’Eustachio, P. and Hermjakob, H., 2018. Reactome graph database: efficient access to
complex pathway data. PLoS computational biology, 14(1), p.e1005968.
Hababeh, I., Khalil, I. and Khreishah, A., 2015. Designing high performance web-based computing
services to promote telemedicine database management system. IEEE transactions on services
computing, 8(1), pp.47-64.

14DATABASE DESIGN AND IMPLEMENTATION
Kneale, D., Thomas, J. and Harris, K., 2015. Developing and optimising the use of logic models
in systematic reviews: exploring practice and good practice in the use of programme theory in
reviews. PloS one, 10(11), p.e0142187.
Laudon, K.C. and Laudon, J.P., 2016. Management information system. Pearson Education India.
Poerner, N. and Winkelmann, R., 2017. Interfacing the BAS Speech Science Web Services and
the EMU Speech Database Management System. Preface & Acknowledgements, p.141.
Prout, A., Kepner, J., Michaleas, P., Arcand, W., Bestor, D., Bergeron, B., Byun, C., Edwards, L.,
Gadepally, V., Hubbell, M. and Mullen, J., 2015, September. Enabling on-demand database
computing with MIT SuperCloud database management system. In 2015 IEEE High Performance
Extreme Computing Conference (HPEC) (pp. 1-6). IEEE.
Stanescu, L., Brezovan, M. and Burdescu, D.D., 2016, September. Automatic mapping of MySQL
databases to NoSQL MongoDB. In 2016 Federated Conference on Computer Science and
Information Systems (FedCSIS) (pp. 837-840). IEEE.